Posts Tagged ‘MyISAM

Why use MyISAM?

Simplicity

So far, this has read somewhat like a paid advertisement for InnoDB. However, MyISAM has some very real advantages. One of these is the simplicity of the engine, it is very well understood and it’s easy to write third party tools to interact with it. There are very high quality free tools, such as mysql hot copy available for MyISAM. It is much more difficult to write tools for an engine as complicated as InnoDB and this can be easily seen in the number of them available. Also, this simplicity allows for an ease of administration that is not there with InnoDB.

Optimization

MyISAM’s other main advantage is how long it has been around. There are many systems, Drupal for example, that are very much optimized for that particular engine. This is not to say that they perform poorly on InnoDB, but they are not optimized for it. For example,while many of Drupal’s core queries are well indexed and use the primary key (thus benefiting from InnoDB’s primary key clustering), some could be improved. The node table has a primary key on (nid, vid). Having this index is a good idea, but it is a two integer index and there are eleven secondary indexes based on it. This doesn’t mean much when you use MyISAM, but under InnoDB it means each of those secondary indexes has two integer sized leaves identifying the primary key.

Another fact, is that there are some workloads MyISAM is better suited for. For example,Drupal’s built in search functionality performs horribly on InnoDB for very large datasets, for example 100k+ rows. These tables are best left MyISAM. Fortunately, MySQL allows for mixing engines like this.

Resource Usage

It is readily accepted in computer science that there is often a trade off  between speed and memory footprint. We have seen through the above benchmarks that InnoDB has some fast algorithms, however, this comes at a price. Not only does InnoDB use more memory than MyISAM, but the actual data files are often quite a bit larger. Add to this the fact that InnoDB has at least one quite large log file and you have a significant increase in resource use. This makes MyISAM a good fit for a resource limited server. However, if you’re concerned at all with high levels of concurrency, it is likely you have the funds to buy a server that can handle these increased resource demands.

Tags : , , , , , , , , ,

InnoDB and MyISAM Benchmark Results

The ACID properties of InnoDB were relaxed when running the performance comparisons. The log buffer is configured to write out updates to the log file at each commit, butonly flushes to disk at set time intervals. The following statement is used to configure this behavior.

innodb-flush-log-at-trx-commit=2

Using the value above, the log file is flushed once per second, essentially batching writes to disk,rather than committing transactions individually. The result is higher throughput, especially for update-intensive and I/O bound workloads. When using a value of “2”, as above, only an operating system crash or a power outage can erase the last second of transactions. However, InnoDB’s crash recovery is not affected and continues to work regardless of the value.

With a value of “0” used in the statement above, a mysqld process crash can erase the last second of transactions, though again InnoDB’s crash recovery is unaffected. The default value of “1” is required in the statement above for full ACID-compliance.

Sysbench Results

Both Read / Write and Read-Only Sysbench tests were run to compare the performance and scalability of InnoDB and MyISAM, with Transactions Per Second (TPS) recorded for each storage engine as more cores were enabled in the host server. Initial runs were made with 6-cores, and then repeated as the server was scaled with 12, 18, 24, 30 and then 36-cores. In allcases, the number of concurrent database connections was set at 64.

As the data below demonstrates, InnoDB delivers very good scalability up to 30-cores. From 30 to36-cores, performance continues to increase, though scalability is reduced.

MyISAM demonstrates almost zero scalability from 6 to 36 cores, with performance significantly lower than InnoDB.

Read-Write Results

As the graph below shows, InnoDB delivered 35x higher throughput than MyISAM, while achieving 85% – 90% scalability from 6 to 36-cores. Above 30-cores, the scalability curve starts to flatten out as the number of hot mutexes grow, but performance does still continue to increase.

Clearly table locking in MyISAM reduces throughput for Read / Write workloads.

Figure 1: InnoDB Delivers 35x Higher Performance than MyISAM with up to 90% Scalability

Read-Only Results

InnoDB delivered 4.6x higher throughput than MyISAM, while achieving 90% – 95% scalability from 6 to 36-cores. Above 30-cores, scalability flattens out as the server is again saturated by anumber of hot mutexes.

Figure 2: InnoDB Delivers 4.6x Higher Performance than MyISAM with up to 95%Scalability

 

Tags : , , , , , , , , , ,

Determining InnoDB Resource Requirements

InnoDB clearly requires far more memory for these reasons,  but it gets slightly difficult to pin down exactly how much more memory. This is true for several reasons:

a. How did you load your database?

InnoDB table size is not a constant. If you took a straight SQL dump from a MyISAM table and inserted it into an InnoDB table, it is likely larger than it really needs to be. This is because the data was loaded out of primary key order and the index isn’t tightly packed because of that. If you took the dump with the ­­order ­by ­primary argument to mysql dump, you likely have a much smaller table and will need less memory to buffer it.

b. What exactly is your table size?

This is an easy question to answer with MyISAM: that information is directly in the output of “SHOW TABLE STATUS”. However, the numbers from that same source for InnoDB are known to be estimates only. The sizes shown are the physical sizes reserved for the tables and have nothing to do with the actual data size at that point. Even the row count is a best guess.

c. How large is your primary key?

It was mentioned above that InnoDB clusters the data for a table around the primary key.This means that any secondary index leaves must contain the primary key of the data they “point to.” Thus, if you have tables with a large primary key, you will need more memory to buffer a secondary index and more disk space to hold them. This is one of the reasons some people argue for short “artificial” primary keys for InnoDB tables when there isn’t one “natural” primary key.

In summary, there is no set method that will work for everyone to predict the needed resources. Worse than that, your needed resources will change with time as more inserts to your table increase its size and fragment the packing of the BTree. The best advice to be offered is use the mysql report tool available here (http://hackmysql.com/mysqlreport) to monitor your available innodb_buffer_pool and adjust accordingly, the most important InnoDB tunable. It is important to not run at 100% usage of the innodb buffer, as this likely means that you’re not buffering as much as you could for reads, and that you are starving your write buffer which also lives in the same global innodb_buffer.

Tags : , , , , , , , , , , ,