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!