Get yourself a copy of the book High Performance MySQL 2nd Edition
Publisher: O'Reilly Media; 2 edition (June 18, 2008) Language: English
ISBN-10: 0596101716 ISBN-13: 978-0596101718
Here is a brief preview of the first edition:
At 10:19 AM 1/26/2010, John G. Heim wrote:
>From: "Jaime Crespo RincÃ³n" <jcrespo@stripped>
>Sent: Monday, January 25, 2010 5:30 PM
>2010/1/25 John G. Heim <jheim@stripped>:
>>I have a server with 16Gb of RAM and a dual-core 2Ghz processor. It is
>>running the latest mysql-server from debian lenny (5.0.1). I have databases
>>for drupal, moodle, spamassassin, horde3, and a small database for
>>The problem is that inserts/updates are sometimes very slow, on the order of
>>a minute. I am hoping somebody can sspot something wrong in my config.
>>Here's the optimization settings section (for your convenience). The whole
>>my.cnf is reproduced below that:
>Are your databases using MyISAM or InnoDB?
>Both. Maybe that's the problem? I started creating database tables for my
>own web apps with the default mysql configuration. I believe the default
>database engine is MyISAM. But then I wanted to use foreign keys and I saw
>that it required me to use InnoDB. So I converted some tables to InnoDB
>but not all. Maybe it was a mistake not to convert all of them.
>After that, I installed drupal, moodle, and mediawiki. I haven't looked at
>what kind of tables those packages create. They may not specify it and the
>default is still whatever it is when you install mysql, MyISAM I think.
>>* If MyISAM, you could be suffering contention problems on writes
>because of full table locks. No easy solution but engine change or
>database sharding. Also key_buffer, (and the other buffers) coud be
>too small for 16GB of RAM. Are you really using more thant 10% of it?
>You could also disable other engines if unused.
>* If InnoDB, you have not set innodb_buffer_pool_size nor log size.
>You could increase the pool to >50% of ram available.
>Those are very general suggestions. It depends a lot on your hardware
>(slow storage?), other apps installed on the same machine or the load
>of the server, among others.
>Well, it could be that the disks aren't real fast. The server is also
>running a print server (lprng). I don't think that's very CPU intensive
>but it might be slowing down writes.
>MySQL General Mailing List
>For list archives: http://lists.mysql.com/mysql
>To unsubscribe: http://lists.mysql.com/mysql?unsub=1