… x 200G EBS volumes in software RAID 10. Now let’s come to the MySQL configuration. MySQL Configuration I used Percona Server 5.5.22-55 for the tests. Following is the configuration that I used: ## InnoDB options innodb…
Post: How to Monitor MySQL with Percona's Nagios Plugins
February 22, 2012 By 8 Comments
… its data directory. InnoDB is getting stuck, has long-running transactions, or other problems. The pmp-check-mysql-innodb plugin checks for several indicators of severe internal problems within InnoDB, such as contention that crosses…; it is not unheard of for a monitoring agent or RAID utility software to do this. The pmp-check-unix-memory…
Post: Aligning IO on a hard disk RAID – the Theory
June 9, 2011 By 15 Comments
…: In this case the RAID controller is using 32KB stripe unit and that can fit in 2 standard InnoDB pages (16KB in…. In first case when reading or writing a single InnoDB page RAID will only read or write to a single disk because… about each of them briefly: InnoDB page You don’t need to do anything to align InnoDB pages – file system takes care…
Post: Aligning IO on a hard disk RAID – the Benchmarks
June 9, 2011 By 11 Comments
… Schedulers And Queue Size ############################# sda | [deadline] 128 # RAID Controller ############################################ Controller | LSI Logic MegaRAID SAS…mysql socket=/var/run/mysqld/mysqld.sock innodb_file_per_table = true innodb_data_file_path = ibdata1:10M:autoextend innodb…
Post: Innodb Performance Optimization Basics
November 1, 2007 By 70 Comments
… IO Subsystem – directly attached storage with plenty of spindles and RAID with battery backed up cache is a good bet. Typically… so it would not swap out MySQL out of memory. MySQL Innodb Settings The most important ones are: innodb_buffer_pool_size 70-80…). With these basic innodb performance tunings you will be better of when majority of Innodb users which take MySQL with defaults run it…
Post: Reasons for MySQL Replication Lag
July 29, 2011 By 6 Comments
… which switches RAID cache in write-through mode which can cause serve performance degradation. Configuration Changes Have you changed MySQL configuration ? OS… include setting sync_binlog=1, enabling log_slave_updates, setting innodb_flush_log_at_trx_commit=1. Also note there is… as increasing buffer pool size could cause performance problems. MySQL Restart MySQL Restarts may include recovery time but what is the most…
Post: How InnoDB handles REDO logging
February 3, 2011 By 18 Comments
… InnoDB cannot log at all, your transaction will never complete. Both the MySQL configuration and hardware will affect the speed at which InnoDB can log. You can request that InnoDB force its REDO… is to use a battery backed raid card. This allows updates to be written to the raid cards battery protected memory which…
Post: When would you use SAN with MySQL ?
March 9, 2009 By 18 Comments
… memory while write buffering can too be done by MySQL and local RAID (with BBU) quite efficiently. It is worth to say… are very latency critical. Now what is about MySQL/Innodb specifics ? First, in MySQL if you’re looking for durable transactions the log… queries executed having only one IO at the time if Innodb or MyISAM tables are used. There is read-ahead functionality…
Post: Make your file system error resilient
August 24, 2011 By 8 Comments
… Inodes per group: 2048 Inode blocks per group: 128 RAID stride: 8 RAID stripe width: 80 Flex block group size: 16 Filesystem… is not worth tradeoff. Even more if you’re running Innodb chances are you will not buy you more “up time” either as Innodb is very sensitive to corruptions and if any of file system errors are reported back to MySQL/Innodb it will…
Post: The perils of uniform hardware and RAID auto-learn cycles
November 16, 2010 By 16 Comments
… went sharply up, the buffer pool got more dirty pages, InnoDB started creating more pages, and the percentage of dirty pages… the buffer pool went up. In addition, samples of SHOW INNODB STATUS showed a long pending flush list (hundreds), and usually… simultaneously pretty recently. I asked the customer to check the RAID controller quickly on one of the failing machines. The result…
