mysql innodb default configuration performance

[João Pinto]

Hello,
I have tried to import data from an sql create/insert export file with a few
records (3K records for the bigger table) and it took 1m25s .
I have tunned the innodb configuration on /etc/mysql/my.cnf based on a file from
a FreeBSD system I had access to, added:
# Set .._log_file_size to 25 % of buffer pool size
innodb_log_file_size = 64M
innodb_log_buffer_size = 2M
innodb_flush_log_at_trx_commit = 0
innodb_fast_shutdown = 1
Removed the logfiles for recreation
 sudo rm /var/lib/mysql/ib_logfile*
Restarted mysql and ran the same import, it now takes 2s .

(I am sure its not the system cache, without the mysql innodb configuration
change I always get the same BAD performance).

I am not sure filling this as a bug is the correct procedure, but since this
makes the default mysql install almost unusable at least for a few KBs of
consecutive inserts on with innodb tables I believe it was the best option.

[Matt Zimmerman (mdz)]

Please send a unified diff (diff -u) of your old and new configurations.

[João Pinto (joaopinto)]

Created an attachment (id=4750)
My mysql conf

Here is my new conf.
I don't have the original file anymore, I was using the stock configuration
file from mysql-server 4.0.24, you can check it from the package.

[Adam Conrad (adconrad)]

This should definitely be looked at and tweaked for dapper, FWIW. Since I'll
probably be doing the initial MySQL syncs and whatnot, I'll toss this on my plate.

[João Pinto (joaopinto)]

Just to notice that this problem is still in dapper :

lamego@ubuntu:~/ircsvs/local$ time mysql -u root ptlink_services < backup/services_20060413_2014.sql

real 0m50.068s
user 0m2.382s
sys 0m0.978s
lamego@ubuntu:~/ircsvs/local$ sudo /etc/init.d/mysql stop
Password:
Stopping MySQL database server: mysqld.
lamego@ubuntu:~/ircsvs/local$ sudo vi /etc/mysql/my.cnf (Hered I have added the innodb options)
lamego@ubuntu:~/ircsvs/local$ sudo rm /var/lib/mysql/ib_logfile*
lamego@ubuntu:~/ircsvs/local$ sudo /etc/init.d/mysql start
Starting MySQL database server: mysqld.
...
Checking for crashed MySQL tables in the background.
lamego@ubuntu:~/ircsvs/local$ time mysql -u root ptlink_services < backup/services_20060413_2014.sql

real 0m18.842s
user 0m1.095s
sys 0m0.522s

[Alan Tam (at)]

innodb_flush_log_at_trx_commit = 0 may cause committed transaction to be lost during system crash, hence unsuitable for a default.
http://dev.mysql.com/doc/refman/5.0/en/innodb-parameters.html