MySQL Forums
Forum List  »  MyISAM

Re: 5.1 to 5.5 upgrade causes significant write slowdown (MyISAM + indexes)
Posted by: Rick James
Date: January 26, 2013 12:40AM

> I was curious as to whether increasing the memory allocation (in my.cnf) had anything to do with this, but if anything it should help. Right? We have 16GB installed. 8GB for INNODB and 4GB for MyISAM in my.cnf. That still leavs 4GB for OS (plenty I would think).

Wrong. See
http://mysql.rjweb.org/doc.php/memory
If you increase cache too much, you end up swapping. Swapping is worse than having a smaller cache.
With 8G+4G, you have not left much room for MyISAM _data_ caching. That happens in the OS.

> (Over 107 MYISAM tables and not certain as to consequences)

The consequences, and what to do about them:
http://mysql.rjweb.org/doc.php/myisam2innodb

> with innodb storage engine. The insert job was on track to complete in ~2.5 hours!
Were you INSERTing one row at a time? With sync_binlog and autocommit ON? What about innodb_flush_log_at_trx_commit?

> I was reading that our new CPU (4-core) supprots Hyperthreading. Not sure what impact that has on MySQL.
Generally, it does not matter. A single connection will use only a single core (hyperthread). Only when you have lots of busy connections can you use multiple cores. Even then, 5.5 tops out at about 8 cores.

> MyISAM index size = ~800 MB
You aren't even using the 4G you specified for key_buffer_size. The key_buffer is where all the index work happens. (Unless you can find the magic wand to wave and get it to "rebuild indexes via sorting" instead of "via key_buffer". Scott??)

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: 5.1 to 5.5 upgrade causes significant write slowdown (MyISAM + indexes)
1936
January 26, 2013 12:40AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.