« MySQL at Sabre | Main | MySQL User Panel: Why MySQL? »
April 14, 2004
Optimizing MySQL/InnoDB Performance
Peter Zaitsev is going over optimizing MySQL/InnoDB Performance at MySQL 2004. Not sure if I didn't get the organization of the talk, but having a hard time organizing it to be readable.
Peter uses DBT2 benchmark, with either a large or small workload against 200 warehouse databases with 30Gb data. Filesystem EXT3, swap disabled. A benchmark series is 4 runs, 2 "large" followed by 2 "small".
(talking fast, can't keep up)
After running the benchmark he uses mysqladmin (mysqladmin -i10 -r extended) for stats, then enables the log-slow-queries option (set to 2 seconds) to track down slow queries (changes updates to selects and uses EXPLAIN).
There will be a flood of common slow queries, won't see the bad queries until some of those are resolved. After wards use "show processlist" to catch typical queries.
Then goes to SHOW INNODB STATUS to review statistics.
A few thoughts on InnoDB options
When running ALTER TABLE for large InnoDB table:
- set innodb_buffer_pool to 80% of physical memory
- increase innodb_log_file_size to 512M
To increase innodb_log_file_size
- shut down mysql
- move old logs to backup
- increase innodb_log_file_size to 512M
- restart and wait for new logs
- be careful, larger logs means longer recovery time
When creating indexes on table:
- create indexes using alter table is fater than during create (and having subsequent inserts go through the process)
SHOW INNODB STATUS is a good command to see runtime stats
- look at "Buffer Poola and Memory" section, check reads/s and writes/s
- increase innodb_buffer_pool to as much as 1800M
- increate innodb_thread_concurrency= (num_disks * num_cpus)*2
- set innodb_log_buffer_size=8M to change i/o to the logs if have high log i/o
Schema Changes for Optimization (some of these appear to be innodb config options):
- shorten primary key, avoid using 2 or more keys
- updating primary key's is expensive, try to avoid
- rebuilding the table will help, removes fragmentation and reduces page splits
- in MySQL 4.1, using per-file tables increases performance, better data clustering and less concurrency
- in MySQL 4.1 UTF8 does not affect performance, unexpected
- tuning connections in the applications can improve performance, innodb_thread_concurrency (default 4) helps with many connections
- set the innodb_flush_method option, can avoid flushing to log at commit with innodb_flush_log_at_trx_commit=0 (set to 2 to flush to OS cache)
- may get benefit from tweaking query_cache
Peter concludes by saying that optimization is a never ending job.
Posted by mike at April 14, 2004 11:06 AM
Hard Drive Recovery Group offers hard disk data recovery services for RAID, laptops and servers. Complete clean room and hard drive repair service.Trackback Pings
TrackBack URL for this entry:
http://mike.kruckenberg.com/mt/mt-tb.cgi/540
Listed below are links to weblogs that reference Optimizing MySQL/InnoDB Performance:
» Oxycodone gel extract. from Oxycodone hcl.
Oxycodone extraction. 5mg oxycodone. Farmacias oxycodone. Oxycodone. [Read More]
Tracked on December 4, 2007 10:17 AM