Switch to Innodb?

MySQL & MariaDB Other MySQL® Questions
1

Hi All

I’ve never used Innodb so I’m unsure if switching to it will give me a benefit. So if you can shed any insight that would be great.

I don’t use foreign keys, I don’t care about ACID, I don’t do full text searches. I say this, because every site mentions if you use any of those 3 then you will use Innodb or MyISAM. Not much help after that.

My thought is that the row level locking will speed up things for me as the DB is heavily read/write to. The table is less than 1MB in size and uses indexes.

I have a two dual core processors and will soon have 4GB ram (2GB now). The slow query log pretty much only shows queries in two tables, both which match the above descriptions. One is 6,000 rows the other is 2,000.

Advice would be great.

2

Compare the status variables table_locks_immediate with table_locks_waited.

The ratio:
table_locks_immediate/table_locks_waited
should be more than a couple of thousand.

If it’s not then you can benefit from changing to InnoDB.

3

Table_locks_immediate 8,852 k
Table_locks_waited 126 k

The ratio is about 70 so I think that means I’m a candidate? I restarted MySQL fairly recently so I’ll watch it as it’s used longer. Thanks!

4

It’s not a extremely high figure but it is high enough for you to be a candidate.

Just remember to set this variable:
innodb_flush_log_at_trx_commit=0

Otherwise there is a very big chance that writes will be very slow for you.

Also remember to make sure that you set the variable
innodb_buffer_pool_size
is set to value that preferably can store the entire DB or a max of 80% of available memory (but since you said that your DB is a few MB then this shouldn’t be a problem).

Read more about the parameters for InnoDB here