MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:mos Date:January 26 2010 4:53pm
Subject:Re: optimization
View as plain text  
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
>>departmental stuff.
>>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:
>To unsubscribe:

optimizationJohn G. Heim25 Jan
  • Re: optimizationJaime Crespo Rinc√≥n26 Jan
  • Re: optimizationJohn G. Heim26 Jan
    • Re: optimizationmos26 Jan
      • Re: optimizationKeith Murphy26 Jan
  • Re: optimizationJohn G. Heim26 Jan
    • Re: optimizationKeith Murphy26 Jan
      • Re: optimizationKyong Kim27 Jan