MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:John G. Heim Date:January 26 2010 4:19pm
Subject:Re: optimization
View as plain text  
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. 

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