… Query | 324 | Sending data | select count(*) from A,B | 0 | 0 | …table. mysql> show processlist; +——-+——+———–+———-+————+——+——————————+——————————+———–+—————+———–+ | Id | User | Host |… using Innodb tables …
Post: Best kept MySQLDump Secret
March 23, 2012 By 11 Comments
… mysqldump –single-transaction to get consistent backup for their Innodb tables without making database read only. In most cases … set (0.00 sec) mysql> select count(*) from A; +———-+ | count(*) | +———-+ | 2359296 | +———-+ 1 row in set (1.73 sec) mysql> select * from C; +——+ | …
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_buffer_size, key_buffer_size etc, you have reasonable amount of connections but yet MySQL takes much more… prepared statements you might want to look at Prepared_stmt_count to see how many prepared statements are allocated on server…
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
… in MySQL 5.6 code? As MRR was used in both MySQL 5.6 and MariaDB 5.5. Handler_mrr_rowid_refills counts… rows than MySQL 5.5, as can be seen by the numbers reported for the status counter Innodb_rows_read. While MySQL 5… thing is that though both MariaDB and MySQL 5.6 are reporting high numbers for Innodb_rows_read, which is completely in…
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…
Comment: ext4 vs xfs on SSD
April 24, 2012 By 23 Comments
… matter look at the bug list or patch list for MySQL/InnoDB at any one point in time. The list will be… it, not for a pay check. So in the first count, you are reacting to a patch issue in a way…
Comment: Innodb Performance Optimization Basics
March 28, 2012 By 70 Comments
…innodb innodb_data_home_dir = innodb_data_file_path = ibdata1:10M:autoextend innodb_log_group_home_dir = /usr/local/mysql/data innodb_buffer_pool_size = 2000M innodb…innodb_log_file_size = 65M innodb_log_buffer_size = 8M innodb_flush_log_at_trx_commit = 1 innodb_lock_wait_timeout = 50 A count command…
Post: Innodb vs MySQL index counts
November 29, 2011 By 6 Comments
… contains 8 indexes inside InnoDB, which is different from the number of indexes 7 defined in the MySQL This customer was running… the INFORMATION_SCHEMA.INNODB_SYS_INDEXES table, which exists in Percona Server 5.1, but doesn’t appear in MySQL until 5… Percona Server or MysqL 5.6. Another person (I’m looking at you, Baron) was adverse to trusting INNODB_SYS_INDEXES from…
Post: ANALYZE: MyISAM vs Innodb
September 3, 2008 By 9 Comments
…innodb table which is same but uses Innodb format: mysql> insert into antest_innodb…
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
…MySQL manual, especially the …innodb_change_bufferingblogpercona.commanual innodb_checksumsblogpercona.commanual innodb_commit_concurrencyblogpercona.commanual innodb_concurrency_ticketsblogpercona.commanual innodb_data_file_pathblogpercona.commanual Innodb_data_fsyncsblogpercona.commanual innodb…
