Re: InnoDB Performance

[Gary Richardson]

> I've got IDE hdd. Is there simple way to check, if it's overloaded?
> Would RAID1 help? (I don't know if in raid1 there are parralel reads or
> maybe one disk is only a mirror)

If it's IDE, probably not. Moving the database to a different
subsystem would give more IO. You can use iostat to check the io on
the disk, but you kinda need iostats to compare against.


> But if it would be too high, server would use swap, what makes more io
> calls. :(

Yup, that's why you'd need more ram.

> 700mb of innodb tables (where phpbb_posts_text contains 170000 records =
> 116mb and phpbb_search_wordmatch contains about 5000000 records = 500mb,
> rest is smaller) and 200mb of myisam tables

I think you also have to include indexes in your memory usage.. I'd
probably up the RAM, especially if your webserver is running on the
same box.

> As I wrote in reply to kernel's message, I've changed settings to:
> 
> innodb_data_file_path = ibdata1:128M:autoextend
> innodb_buffer_pool_size=150M
> innodb_additional_mem_pool_size = 50M

Increasing your file size to 128M doesn't really help. You should
probably set it to something like:

innodb_data_file_path = ibdata1:1G;ibdata2:1G;ibdata3:1G:autoextend

This will create 3x 1 gig table files. You're data is around 1G, so
this should give you some headroom. InnoDB uses more space for
transactions and such than just the byte size of your rows * number of
rows. The autoextend allows the last file to grow if/when you run out
of space. I prefer to add files when I start running out of room
instead of letting it autogrow.