The latest from the MySQL community

A while back a Sun engineer posted an article claiming that the best way to scale MySQL is to shard your database in many instances on a single server, each of which runs in threads that individually have low performance. The Sun way has always been to get high throughput with high latency. [...]

There is significant portion of customers which are still using MyISAM when they come to us, so one of the big questions is when it is feasible to move to Innodb and when staying on MyISAM is preferred ?

I generally prefer to see Innodb as the main storage engine because it makes life much simpler in the end for most users - you do not get to deal with recovering tables on the crash or partially executed statements. Table locks is no more problem, hot backups are easy, though there are some important things which we have to consider on case by case basics before recommending the move.

Is MyISAM used as default or as a choice ? This is the most important question to ask upfront. Sometimes MyISAM is there just because it is default, in other cases this is deliberate choice with system being optimized to deal with MyISAM limits, for example there is a dedicated slave available for all long reporting queries. In case …

So lets say you have .frm file for the table and you need to recover CREATE TABLE statement for this table. In particular when we do Innodb Recovery we often get .frm files and some mess in the Innodb tablespace from which we have to get data from. Of course we could relay on old backups (and we do ask for them for a different reason anyway) but there is never guaranty there were no schema changes in between.

So how to recover CREATE TABLE from .frm file ?

Recovering from .frm for Innodb Table

If we simply copy .frm file back to the database we will see the following MySQL creative error message:

PLAIN TEXT SQL:

1. mysql> SHOW TABLES;
…

The newest development in the partitioning code
is WL#4571.

This new feature makes it possible to tie a
partition using MyISAM to a specific cache index.
The syntax for how to do is available in the
above worklog entry.

We found this feature to be useful for enabling
higher performance of parallel ALTER TABLE
(WL#2550). When adding
a primary key to a MyISAM table the key cache in
MyISAM limited scalability of Parallel ALTER TABLE
severely, so adding several key caches, essentially
one per partition we can ensure that the ALTER TABLE
can be fully parallelised (all other ALTER TABLE
on MyISAM already scales perfectly).

We also have some ideas on how to solve the base
…

In a previous post, I was trying to figure out the most optimal way to switch from two large innodb table space files to using innodb_file_per_table to take advantage of some of the benefits of this setting. I had one part of it solved, which was to stop MySQL, add innodb_file_per_table to the my.cnf, then restart, perform a "no-op" alter of "ALTER TABLE t1 ENGINE=InnoDB" which would cause the table to be re-created an it's own .ibd file. The remaining problem was how to be able to resize the huge table space files after converting all the tables to a smaller size (in my case from 10GB to 10MB).

Someone suggested a better way:

1. Alter all innodb tables to MyISAM
2. Stop the server
3. Add innodb_file_per_table to my.cnf
4. Change innodb_data_file_path to new settings (10MB tablespaces) in my.cnf
5. Move all innodb files (logs, data) to a backup directory
6. Restart MySQL
7. Alter …

How would you expect AUTO_INCREMENT to work with MERGE tables ? Assuming INSERT_METHOD=LAST is used I would expect it to work same as in case insertion happens to the last table... which does not seems to be the case. Alternatively I would expect AUTO_INCREMENT to be based off the maximum value across all tables, respecting AUTO_INCREMENT set for the Merge Table itself. Neither of these expectations really true:

PLAIN TEXT SQL:

1. mysql> CREATE TABLE a1(i int UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY);
2. Query OK, 0 rows affected (0.01 sec)
3.  
4. mysql> CREATE TABLE a2 LIKE a1;
5. Query OK, 0 rows affected (0.00 sec)
6.  
7. mysql> INSERT INTO a1 VALUES(2);
8. Query OK, 1 row affected (0.00 …

Take a look at this:

PLAIN TEXT SQL:

1. mysql> repair TABLE a3;
2. +---------+--------+----------+----------+
3. | TABLE   | Op     | Msg_type | Msg_text |
4. +---------+--------+----------+----------+
5. | test.a3 | repair | STATUS   | OK       |
6. +---------+--------+----------+----------+
7. 1 row IN SET (0.10 sec)
8.  
9. mysql> SELECT * FROM a3 ORDER BY i;
10. +------------+
11. | i          |
12. +------------+
13. | 2147483648 |
14. |         11 |
15. |         13 |
16. |         14 |
17. | 2147483647 | …

Following up on my Previous Post I decided to do little test to see how accurate stats we can get for for Index Stats created by ANALYZE TABLE for MyISAM and Innodb.

But before we go into that I wanted to highlight about using ANALYZE TABLE in production as some people seems to be thinking I advice to use it.... a lot. In fact I should say I see more systems which have ANALYZE abused - run too frequently without much need than systems which do not run ANALYZE frequently enough.

First it is worth to note MySQL only saves very basic cardinality information for index prefixes for index stats and these rarely change. There is no histograms or any other skew metrics etc. MySQL optimizer also uses number of rows in the table for many decisions but this is computed live (maintained for …

It felt like the right time for us to look back at some useful commands for table maintenance that some of us may not have mastered as much as we might like to think.

In my post about gathering index statistics, I referred to OPTIMIZE TABLE, ANALYZE TABLE, and REPAIR TABLE — but I never explained in depth what the different commands do, and what the differences between them are. That is what I thought I would do with this post, focusing on InnoDB and MyISAM, and the differences in how they treat those commands. I will also look at different cases and see which one is right for in each case.

(more…)

In previous search benchmarks, I utilized random content generated with Drupal's devel module. In these latest benchmarks, I used an actual sanitized copy of the Drupal.org community website database, with email addresses and passwords removed. The first tests were intended to confirm that Xapian continues to perform well with large amounts of actual data. Additional tests were performed to measure the effect of various MySQL tunings and configurations. The following data was derived from several hundred benchmarks run on an Amazon AWS instance over the past week using the SearchBench module.

These tests confirm that Xapian continues to offer better search performance than Drupal's core search module. Contrary to popular belief, the data also shows that using the InnoDB storage engine for search tables significantly outperforms using the MyISAM storage engine for search tables, especially when your database server has sufficient RAM. The …