InnoDB in Percona Server 5.0.91 and MySQL 5.5.x has three options
These options were introduced in MySQL 5.1.38 InnoDB Plugin
Tuning these will engage more CPUs within InnoDB/XtraDB
I have discussed this in past posts in the DBA StackExchange along with other InnoDB tuning suggestions
Click here for the XtraDB features that the InnoDB Plugin does not have.
OBSERVATION #1
Since your buffer pool is 6G (6144M), the innodb_log_file_size should be 1536M (25% of 6G)
OBSERVATION #2
You have sync_binlog set to 1. This provides the safest ACID compliant setup. It can also slow things down dramatically. You say it seems as though its writing each row 1 at a time to disk. That's the case because each completed DML (INSERT, UPDATE, DELETE) and DDL (ALTER TABLE) statement gets written to the binary logs. The default for sync_binlog is 0. That let's the OS be responsible for flush binary log changes to disk.
OBSERVATION #3
You have innodb_io_capacity set at 10000. That's really 10000 IOPs you are expecting of mysql. Try lowering it.
There are some things to do in this respect
- When it comes to sync_binlog
- Comment it out of my.cnf
- Set sync_binlog to 100 or 200 to pace the flushing of binary logs
- You should make sure data disk has a battery-backed cache (with a good battery)
RECOMMENDATION
STEP 01) Set these options in my.cnf
[mysqld]
innodb_fast_shutdown = 0
innodb_log_file_size = 1536M
sync_binlog = ( you choose 0 or 100 )
STEP 02) Remove innodb_io_capacity. Let it be set to default.
STEP 03) Run this command in mysql
mysql> SET GLOBAL innodb_fast_shutdown = 0;
STEP 04) Apply settings
cd /mnt/Storage/mysql
service mysql stop
mv ib_logfile0 ib_logfile0.bak
mv ib_logfile2 ib_logfile1.bak
service mysql start
This should speed things up
Best Answer
When I head the word 'Percona Server', the first thing that jumps at me is : "Does the DB Server have multiple CPUs?" These days, that is taken for granted. What is unfortunate is that most people forget to configure MySQL or Percona Server to summon those multiple CPUs.
MySQL 5.1 with InnoDB 5.1 Plugin, MySQL 5.5 and Percona Server have options to increase threading. Here are those options along with other InnoDB features you may wish to tune:
innodb_thread_concurrency sets the upper bound on number of concurrent threads that InnoDB can hold open. Best round number to set for this is (2 X Number of CPUs) + Number of Disks. As I learned firsthand from the Percona NYC Conference back in May 2011, you should set this to 0 in order to alert InnoDB Storage Engine to find the best number of threads for the environment it is running in.
innodb_concurrency_tickets sets the number of threads that can bypass concurrency checking with impunity. After that limit is reached, thread concurrency checking becomes the norm again.
innodb_commit_concurrency sets the number of concurrent transactions that can be committed. Since the default is 0, not setting this allows any number of transactions to commit simultaneously.
innodb_thread_sleep_delay sets the number of milliseconds an InnoDB thread can be dormant before reentering the InnoDB queue. Default is 10000 (10 sec).
innodb_read_io_threads and innodb_write_io_threads (both since MySQL 5.1.38) allocate the specified number of threads for reads and writes. Default is 4 and maximum is 64.
innodb_replication_delay imposes thread delay on a slave is innodb_thread_concurrency is reached.
innodb_read_ahead_threshold allows linear readings of the set number of extents (64 pages [page = 16K]) before switching to asynchronous reading.
Time would escape me if I named more options. You can read about them in MySQL's Documentation.
Switching gears to RAID: Please stick with your RAID10. It is easier to maintain other redundant setups, especially RAID5.. Also, make sure you have the latest firmware for your RAID card. Otherwise, RAID firmware may conflict with the OS kernel resulting either in disk crashes or lower overall throughput.
As for
Is ext4 ok or should I create a separate xfs volume for the data?, keep in mind that ext3 places a max size limit of 2TB per file. If you are not dealing with TBs of data, don't sweat using ext3 or ext4.A separate data volume would increase disk performance since mysqld will not compete with the OS.