MySQL Storage Engines

Archived Topics
1

MySQL supports several storage engines that act as handlers for different table types. MySQL storage engines include both those that handle transaction-safe tables and those that handle non-transaction-safe tables. The two most commonly used storage engines are InnoDB and MyISAM.

[ul]- InnoDB provides MySQL with a transaction-safe (ACID compliant) storage engine that has commit, rollback, and crash recovery capabilities.

  • MyISAM manages non-transactional tables. It provides high-speed storage and retrieval, as well as fulltext searching capabilities. [/ul]
    Since MyISAM does not have crash recovery capabilities, if MySQL was shutdown improperly your tables can get corrupted. Luckily, MySQL provides a way to repair these corrupted tables. You can either manually repair these tables by running the following query:
repair table table_name

or you can tell MySQL to automatically repair corrupted tables on startup by adding the following to the my.ini, usually located in C:\Program Files\MySQL\MySQL Server 5.0, file under the [mysqld] section:

myisam-recover=DEFAULT
2

Can we get this added to the my.ini file that the unified installer places?

3

This setting is in the my.ini file of the Unified Installer.

4

I have been watching the development of the Falcon engine for a while now, and I think it will work quite well for larger systems. It is very ram hungry, but it caches alot of record sets. There is an hour long webcast on the MySQL website if anyone is interested. I know I am

5

One gotcha about MyISAM, it is decent at running select queries during insert queries, however, it wont perform as well as InnoDB if there is deletes and updates to the table. This is because the locking mechanism for MyISAM is a table lock, meaning no queries can be ran on the table until the current query unlocks the table. You can still run a select query against the table because MyISAM inserts into the end of the file. Deletes and updates modify existing data, requiring a full table lock.

InnoDB is quite different. It only needs to lock the row it is working on, because of MVCC (Multiversion Concurrency Control). In a nutshell, it snapshots the existing table, and utilizes a log to maintain the table. A better description can be found here.

http://en.wikipedia.org/wiki/Multiversion_concurrency_control

In my experience, I have found InnoDB to be a faster engine for SQL tags, as well as a safer engine. If all you are doing is inserting into a table(historical data) and never updating, and deleting seldomly, MyISAM will work very well. I use MyISAM for this time of action only.

6

Good point - I agree. MyISAM for Historical Data tables, InnoDB for SQLTags tables.

Do you do that many concurrent writes with SQLTags? Do you regularly perform manual writes directly to SQLTags tables?

7

Hey Nathan.
Here are the stats for my current biggest project (soon to be dwarfed though)
Datasource Status
Total Queries 165,911,014
Query Duration
(Min / Avg / Max) 0.00 / 0.00 / 203.58 (pulling a ton of historical data, partitioned the data, now it runs sub second)
Throughput 54.06 queries/sec

SQL Tags
Tag Loads: 19,196
Value Changes: 179,145,201

Total Scans 17,077,908
Scan Duration
(Min / Avg / Max) 0.00 / 0.00 / 1.13
Throughput 4.94 scans/sec

Total Client Reqs 36,164,626
Client Req Duration
(Min / Avg / Max) 0.00 / 0.00 / 4.23
Throughput 7.73 client reqs/sec
Total Tag Changes 179,145,201
Tag Change Duration
(Min / Avg / Max) 0.00 / 0.00 / 1.05
Throughput 43.66 tag changes/sec

Total Writes 0
Write Duration
(Min / Avg / Max) 0.00 / 0.00 / 0.00
Throughput idle

I dont do any manual writes to SQLTags, only manual reads in some cases. Either way, in a heavy update environment, which SQL tags is, MVCC should still perform better then MyISAM, but I have never done any benchmark.

8

Hi Kyle,

How do you currently have your partitions set up? I’ve been playing around with range partitioning, but was curious as to what you’re using for history.

Regards,

9

Kyle - mind posting your DB server specs? Are you running anything else on it?

If it’s ok with you, I’d like to look at including your system with our benchmark numbers, especially when your monster project matures.

10

@Colby
Currently, I partition like this

PARTITION BY LIST (day(t_stamp)) (PARTITION p0 VALUES IN (1) ENGINE = MyISAM, PARTITION p1 VALUES IN (2) ENGINE = MyISAM, PARTITION p2 VALUES IN (3) ENGINE = MyISAM, PARTITION p3 VALUES IN (4) ENGINE = MyISAM, PARTITION p4 VALUES IN (5) ENGINE = MyISAM, PARTITION p5 VALUES IN (6) ENGINE = MyISAM, PARTITION p6 VALUES IN (7) ENGINE = MyISAM, PARTITION p7 VALUES IN (8) ENGINE = MyISAM, PARTITION p8 VALUES IN (9) ENGINE = MyISAM, PARTITION p9 VALUES IN (10) ENGINE = MyISAM, PARTITION p10 VALUES IN (11) ENGINE = MyISAM, PARTITION p11 VALUES IN (12) ENGINE = MyISAM, PARTITION p12 VALUES IN (13) ENGINE = MyISAM, PARTITION p13 VALUES IN (14) ENGINE = MyISAM, PARTITION p14 VALUES IN (15) ENGINE = MyISAM, PARTITION p15 VALUES IN (16) ENGINE = MyISAM, PARTITION p16 VALUES IN (17) ENGINE = MyISAM, PARTITION p17 VALUES IN (18) ENGINE = MyISAM, PARTITION p18 VALUES IN (19) ENGINE = MyISAM, PARTITION p19 VALUES IN (20) ENGINE = MyISAM, PARTITION p20 VALUES IN (21) ENGINE = MyISAM, PARTITION p21 VALUES IN (22) ENGINE = MyISAM, PARTITION p22 VALUES IN (23) ENGINE = MyISAM, PARTITION p23 VALUES IN (24) ENGINE = MyISAM, PARTITION p24 VALUES IN (25) ENGINE = MyISAM, PARTITION p25 VALUES IN (26) ENGINE = MyISAM, PARTITION p26 VALUES IN (27) ENGINE = MyISAM, PARTITION p27 VALUES IN (28) ENGINE = MyISAM, PARTITION p28 VALUES IN (29) ENGINE = MyISAM, PARTITION p29 VALUES IN (30) ENGINE = MyISAM, PARTITION p30 VALUES IN (31) ENGINE = MyISAM

However, I think I will be changing to a partition by month sub partition by day scheme. Ill let you know how it goes. Even partitioning just by day, massive performance increase.

@Nathan
Server specs:
Dell R200
Windows Server 2003
Xeon X3230
4 Gb Ram
2x 300GB 15k SAS in RAID 1

Iti s currently running the IA stack, MySQL, server for our antivirus, and thats about it. We run an online disk defrag utility which probably slows it down a bit( wish I could run all linux, then there would be no need for defrag). Might change to an ESXi or Xen hosted VM setup, then FPMI would run in Linux, MySQL in another Linux VM, and Kepware and FSQL in WS2003. Would need to bump to 16 Gb ram to do that though, for MySQL caching to help alleviate the disk IO bottleneck

11

I wonder where your main performance boost is coming from, since from what I’ve read (and a quick test I just threw together) partitioning like that doesn’t let you benefit from partition pruning.

[quote]Pruning can also be applied for tables partitioned on a DATE or DATETIME column when the partitioning expression uses the YEAR() or TO_DAYS() function. [/quote] (Taken from Partition Pruning)

I created a table with that partitioning, and then ran an “explain partitions” on a select query that gets data from one hour on one day. The result shows that it will scan all partitions and all rows…

I’m just in the process of trying to figure out the ends and outs of partitioning, which is why I’m so curious. Am I missing something about how you’re querying it, or how the partitions are affecting your results?

12

Hmmmm. I also noticed that, but I wonder where my performance came from then… I did repair and optimize the table. I bet thats it. Let me alter my table to do a range for those 31 values, and Ill get back to you

13

Ill try this
mikaelronstrom.blogspot.com/2006 … 25345.html

EDIT
Yes, this is a good way to partition for historical data. You could partition by week, and end up with 19+ years with this setup. Remember that there is a maximum of 1024 partitions per table