1

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

|improve this question
  • 1
    Maybe you should give us the script since in my experience this is not a general problem of the Ubuntu LAMP stack – txwikinger Aug 7 '10 at 16:57
  • are the MySQL versions the same? are the my.cnf params the same? are the table types used the same? This is MySQL, you can't just install it and hope - it has to be tuned to your workload. provide more detail. – troyengel Aug 7 '10 at 17:00
  • The script is just a loop executing the same basic insert over and over (with random data) using prepared statements. Like I said, I ran it on other systems and got far better results.. so this is a problem with this system, not the script. – Ian Aug 7 '10 at 20:16
  • As a warning, you've lost you're durability guarantee (the D in ACID). When a client commits a transaction they are usually safe in assuming the data is safe. Now since the log isn't written when the transaction is committed, there is a window where the machine could die and lose committed data. Would it be possible for you to do more work inside a single transaction instead? – Ryan Bair Aug 19 '10 at 3:28
  • +1 for re-editing to add the [SOLVED] answer – regilero Jan 9 '11 at 13:48
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!

|improve this answer
0

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

|improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.