It is pretty understood the tables which have long rows tend to be slower than tables with short rows. I was interested to check if the row length is the only thing what matters or if number of columns we have to work with also have an important role. I was interested in peak row [...]
What to do with MySQL Full Text Search while migrating to Innodb ?
September 10, 2009 By 12 Comments
It is rather typical for systems to start as MyISAM but as system growths to move to Innodb. The reason of the move could be just desire for better data consistency guaranty or being bitten repairing multiple GB MyISAM table few times, though Table Locks is probably the most important issue – with modern multi [...]
Getting annoyed with MyISAM multiple key caches.
May 1, 2009 By 11 Comments
As I’ve wrote few times using multiple key caches is a great way to get CPU scalability if you’re using MyISAM. It is however very annoying – this feature really looks half baked to me. The problem with multiple key caches and mapping of tables to the different files is – there is no way [...]
Should you move from MyISAM to Innodb ?
January 12, 2009 By 59 Comments
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 [...]
Recovering CREATE TABLE statement from .frm file
December 17, 2008 By 5 Comments
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 [...]
AUTO_INCREMENT and MERGE TABLES
September 11, 2008 By Leave a Comment
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 [...]
Can you Trust CHECK TABLE ?
September 11, 2008 By 6 Comments
Take a look at this:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | mysql> repair table a3; +---------+--------+----------+----------+ | Table | Op | Msg_type | Msg_text | +---------+--------+----------+----------+ | test.a3 | repair | status | OK | +---------+--------+----------+----------+ 1 row in set (0.10 sec) mysql> select * from a3 order by i; +------------+ | i | +------------+ | 2147483648 | | 11 | | 13 | | 14 | | 2147483647 | +------------+ 5 rows in set (0.00 sec) |
The sort order is obviously wrong while CHECK TABLE is not reporting any error
ANALYZE: MyISAM vs Innodb
September 3, 2008 By 9 Comments
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 [...]
Beware of MyISAM Key Cache mutex contention
August 12, 2008 By 10 Comments
Today I was working with the client loading data to MyISAM tables at very high rate. Hundreds of millions rows are loaded daily into single MySQL instance with bursts up to 100K of records/sec which need to be inserted (in the table with few indexes). It was good not all records had to go to [...]
Concurrent inserts on MyISAM and the binary log
May 14, 2008 By 7 Comments
Recently I had an interesting surprise with concurrent inserts into a MyISAM table. The inserts were not happening concurrently with SELECT statements; they were blocking and the process list was filling up with queries in Locked status. My first thought was that the customer had deleted from the table, which leaves “holes” in the middle [...]
