Friday, April 17, 2009

InnoDB write performance

InnoDB write performance

My Master node is bottlenecked while writing to its internal disks.
Is the only option available is to repartition the database and create two partitions (or upgrade Hardware)?
Thanks.

==========

No, that is not the only option. There may be others, but depends on how you are performing inserts. Others options, but not limited to, may be:

1. If you have auto_commit on, bundle up your inserts in a transaction.
2. Do bulk inserts instead of each one as a separate one, like insert into ... values(a,b,c), (d,e,f), ....,(x,y,z);
3. Reset your global variables depending on your server's performance - I know this is a very generic and weird statement, but cannot elaborate until provided with the details.

Thanks & Regards,
Parvesh Garg
http://optimmysql.blogspot.com

-------------------------------
"Language shapes the way we think, and determines what we can think about."
-- B. L. Whorf

============
These tools have been helpful to determine where the I/O issues exist:

iostat - to see what drives are taking up the I/O; or viewing wait times/bottlnecks

top - view process lists (also ps ); and memory/swap/cpu utilization

vmstat - to view memory utilization


Depending how the db is used, there's a list of several innodb options, for starters these might be helpful to understand.

- innodb_max_dirty_pages_pct
- innodb_table_locks
(ref: http://dev.mysql.com/doc/refman/4.1/en/innodb-parameters.html)

- **transaction_isolation
(ref: http://dev.mysql.com/doc/refman/5.0/en/innodb-transaction-isolation.html)

If swap is enabled on the O/S/drive,will disabling it improve performance?

Try to determine which files are being accessed most frequently. (can the data/logs transaction logs be placed on different disks?)

Hopefully some of this will help out.

- Klinton

No comments: