Horrible PHP/MySQL Query Performance On Ubuntu Server 10.04

Question

I just did a clear install of Ubuntu Server 10.04 with the standard LAMP config..

Then I made a small script to test insert speeds into a table with 3 columns.

When I run the script on any of my other machines (centos and fedora), I get about 2000-3000 inserts per second.

On the Ubuntu machine, I get about 20 inserts per second (same exact script).

I tried switching the script to use sockets instead, but that changed nothing.

When I do mysql dumps and restores, mysql performs very well and loads 1,000,000 records into the table in about 20 seconds.

Why is PHP getting such awful query rates into mysql? (i tried using pdo and mysqli - they both get the same results).

Currently installed:

libmysqlclient16                5.1.41-3ubuntu12
mysql-client-5.1                5.1.41-3ubuntu12
mysql-client-core-5.1           5.1.41-3ubuntu12
mysql-common                    5.1.41-3ubuntu12
mysql-server                    5.1.41-3ubuntu12
mysql-server-5.1                5.1.41-3ubuntu12
mysql-server-core-5.1           5.1.41-3ubuntu12
php5-mysql                      5.3.2-1ubuntu4.2
libapache2-mod-php5             5.3.2-1ubuntu4.2
php-apc                         3.1.3p1-2
php5-cli                        5.3.2-1ubuntu4.2
php5-common                     5.3.2-1ubuntu4.2
php5-mysql                      5.3.2-1ubuntu4.2

EDIT/SOLVED:

I did a reinstall of Ubuntu, same problem. I installed Fedora 13, SAME PROBLEM.... then after playing around with the tables, I noticed it was an InnoDB problem (MyISAM was inserting thousands of rows per second).

As it turns out, innodb_flush_log_at_trx_commit was defaulted to "1", which means it flushes to disk on every write... which was murdering my performance. Since this is a crappy consumer dell machine, when I switched to innodb_flush_log_at_trx_commit=0 (flush every second), the performance sky rocketed. I am now able to insert 1,000,000 rows in 45 seconds. :D

Answer 1

Sounds like your MySQL config needs tuning. A base MySQL config on any distro will be on the conservative side, assuming they're not the only service running on the server. You should spend some time and tune it. If your tables are in InnoDB format, make sure you tune the innodb_buffer_pool_size variable to approximately 80% of your system memory (assuming it's the only service to be run).

That's likely the first place to look when comparing the OSes. Each distro has their own standards. You should match the configs if you want to run a fair test between them.

Hope this helps!

Answer 2

I agree with troyengel. You need to compare your my.cnf on the servers that are giving different behaviours to see where the bottleneck might be. From your own testing, it seems more likely to be related to your MySQL config, and less likely to be related to your PHP config. -- dxjones