MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:John G. Heim Date:January 26 2010 7:18pm
Subject:Re: optimization
View as plain text  
Just to be clear, you're suggesting I convert all of the spamassassin, 
drupal, and mediawiki tables to innodb too? Or just my own database? What 
about the mysql database itself? I wouldn't convert those tables, would I?

----- Original Message ----- 
From: "Keith Murphy" <bmurphy@stripped>
To: <mysql@stripped>
Sent: Tuesday, January 26, 2010 11:06 AM
Subject: Re: optimization

I would recommend the same to you about reading High Perf. MySQL as Baron,
et al wrote a great book about performance on MySQL. That being said, it has
been my experience that in 99% of client cases they don't really need to run
two different types of tables. If I were you, I would use InnoDB exclusively
unless there is legitimate reason to do otherwise. In an environment that is
running 25% writes and a decent query rate you are bound to have contention
issues with MyISAM. While there are always going to be edge cases for
MyISAM, your default should be innodb and your config should reflect this.

Changing your tables to InnoDB is a simple ALTER TABLE which you can script
if there are a number of tables to convert.  Allocate as much of your
available RAM as possible to the innodb_buffer_pool (typically 50 - 80% of
total RAM) and I bet you would see a dramatic difference. That is
simplifying things somewhat, but should give an idea.


On Tue, Jan 26, 2010 at 11:53 AM, mos <mos99@stripped> wrote:

> 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:
> Mike
> 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:
> --
> MySQL General Mailing List
> For list archives:
> To unsubscribe:

Chief Training Officer
Paragon Consulting Services

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