MySQL Performance: MySQL => Innodb tweaks, feedback on my ideas?

Innodb tweaks, feedback on my ideas? [message #3637]

Sun, 19 October 2008 15:46

3molo
Messages: 12
Registered: April 2008

Junior Member

Im looking for tips how to speed up Innodb. I've got a dedicated machine for one table only, the innodb table. The problem is I/O, I have slow queries (+2 seconds) on the database. It's a cache and will eventually be replaced with memcached.

Some facts:
~1000 queries per second.
33% inserts and 64% SELECT
The data is BLOB and size varies alot.
The table is being truncated every 4 hours due to I/O
Loosing a few rows in case of system or mysqld failure is OK

The machine is a 2x dual core 3.2ghz xeon, 4G of ram. 3x300G scsi3 in raid 5, raid controller has RAM.

These are the non-standard innodb_ changes I think will benefit me:
innodb_flush_log_at_trx_commit = 0 # to minimize disk I/O..
innodb_log_buffer_size = 8M # maximum to minimize disk I/O, data safety is not the issue
innodb_buffer_pool_size = 2G # currentyl i386, so cant allocate much more I figure
innodb_log_file_size = 128M # low because there's no need in rolling back that much transactions due to the nature of the data

I'm also curios about the following tweaks, but I think it's a good idea to apply above changes first, let a few days pass and then try these separately.
innodb_flush_method=0_DIRECT
--skip=innodb_doublewrite.

I'm very greatful for feedback and ideas regarding this!

Report message to a moderator