One of the very frequent cases with performance problems with MySQL is what they happen every so often or certain times… need to know it is not MySQL problem, might be even not problem with your MySQL configuration, queries and hardware, even though… replication thread. For example if I need to delete old data instead of DELETE FROM TBL WHERE ts
Post: Troubleshooting MySQL Memory Usage
March 21, 2012 By 3 Comments
…MySQL is troubleshooting memory usage. The problem usually starts like this – you have configured MySQL to use reasonable global buffers, such as innodb…limit size of MEMORY tables (the limit…
Post: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact
March 12, 2012 By 12 Comments
… in preparation for the talk comparing the optimizer enhancements in MySQL 5.6 and MariaDB 5.5. We are taking a… optimizer enhancement Index Condition Pushdown (ICP). Its available in both MySQL 5.6 and MariaDB 5.5 Now let’s take…. Index Condition Pushdown Traditional B-Tree index lookups have some limitations in cases such as range scans, where index parts after…
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 2 Comments
…, o_shippriority order by revenue desc, o_orderdate LIMIT 10; In-memory workload Now let’s see… at the status counters. Counter Name MySQL 5.5 MySQL 5.6 MySQL 5.6 w/ join_buffer_size=6M…Innodb_data_read 5G 5.4G 2.2G 5.4G 2.2G 2.2G Innodb_data_reads 329115 355323 143808 335526 16164 15506 Innodb…
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
…name, c_address, c_comment order by revenue desc LIMIT 20; In-memory workload Now let’s see …Innodb_data_read 1.84G 1.89G 1.53G 1.89G 1.53G Innodb_data_reads 120552 123872 100551 103011 77213 Innodb… MySQL 5.5, as can be seen by the numbers reported for the status counter Innodb_rows_read. While MySQL…
Post: Announcing Percona XtraBackup 2.0.0 GA
April 4, 2012 By 11 Comments
… also had a number of other limitations (e.g. could not be … which calculated deltas by scanning data files. Which meant those two feature… XtraBackup against debug versions of InnoDB. This is only for very advanced… at the upcoming Percona Live MySQL Conference and Expo (register now!). Specifically…
Post: How to recover a single InnoDB table from a Full Backup
January 25, 2012 By 7 Comments
…InnoDB data we are going to recover only the “salaries” table: Discard the tablespace of the salaries table: mysql…mysql/data/employees/ Import the new tablespace: mysql> set FOREIGN_KEY_CHECKS=0; mysql> ALTER TABLE salaries IMPORT TABLESPACE; mysql> set FOREIGN_KEY_CHECKS=1; mysql… of limitations and…
Post: Hijacking Innodb Foreign Keys
January 2, 2012 By 2 Comments
… to HardCore MySQL business – foreign Keys. MySQL supported Foreign Keys for Innodb for many years, yet rudimentary support initially added in MySQL 3.23… often can be very inefficient. As results of Foreign Key limitations you might need to get rid of them, yet this… Innodb is able to drop indexes without rebuilding table since MySQL 5.1 (Innodb Plugin) dropping foreign keys which should only require meta data…
Post: Modeling MySQL Capacity by Measuring Resource Consumptions
March 8, 2011 By 1 Comment
… moment. The amount of resources system has will place a limit on amount of queries system can ran, for example if… Cary Millsap. So how we can use this data to estimate capacity of MySQL system ? We can look at CPU and IO… provide our estimates. If we’re running Innodb with MySQL we can use Innodb_data_reads , Innodb_data_writes, Innodb_os_log_fsyncs for your disk IO…
Post: How to debug long-running transactions in MySQL
March 8, 2011 By 6 Comments
… long time without committing, and has modified data, then other transactions could block and fail …mysql -ss -e ‘SELECT p.HOST FROM information_schema.innodb_lock_waits w INNER JOIN information_schema.innodb….processlist p on b.trx_mysql_thread_id = p.ID LIMIT 1′) if [ "${host}" ]; then echo “Host …
