…Rows_sent | Rows_examined | Rows_read | +——-+——+———–+———-+———+——+————————-+—————————–+———–+—————+———–+ | 10219 | root | localhost | dumptest | Query | 324 | Sending data | select count…Innodb…
Post: Troubleshooting MySQL Memory Usage
March 21, 2012 By 3 Comments
… you might want to look at Prepared_stmt_count to see how many prepared statements are allocated …mysql> select * from information_schema.global_temporary_tables \G *************************** 1. row *************************** SESSION_ID: 7234 TABLE_SCHEMA: test TABLE_NAME: my ENGINE: InnoDB…
Post: Best kept MySQLDump Secret
March 23, 2012 By 11 Comments
…–single-transaction to get consistent backup for their Innodb tables without making database read only. In … mysql> select count(*) from A; +———-+ | count(*) | +———-+ | 2359296 | +———-+ 1 row in set (1.73 sec) mysql> select * from C; +——+ | t | +——+ | test | +——+ 1 row…
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… reading more 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 line…
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: 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… are locked. select count(1),field1 from Table where REQUEST_TIME >= ’2012-03-26′ group by field1; 14 rows in set (15…
Post: MySQL Users Conference - Innodb
May 9, 2007 By 6 Comments
…for MySQL – Innodb. Innodb Storage Engine was covered in a lot of talks, many of them done by Innodb users….sure if Heikki will find a way to count exact number of auto increment values needed …the problems because they target at least MySQL 5.1 with row level replication readily available. Speaking …
Post: ANALYZE: MyISAM vs Innodb
September 3, 2008 By 9 Comments
…row count in the table) It is worth to note if you do ALTER TABLE Innodb…
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
…MySQL manual, especially the …Innodb_row_lock_timeblogpercona.commanual Innodb_row_lock_time_avgblogpercona.commanual Innodb_row_lock_time_maxblogpercona.commanual Innodb_row_lock_waitsblogpercona.commanual Innodb_rows_deletedblogpercona.commanual Innodb_rows_insertedblogpercona.commanual Innodb_rows…
Post: How much memory Innodb locks really take ?
July 13, 2006 By 11 Comments
…=InnoDB DEFAULT CHARSET=utf8 mysql> show table status like “sample” \G; *************************** 1. row *************************** Name: sample Engine: InnoDB Version: 10 Row_format: Compact Rows: 1638757 Avg_row_length…) mysql> select count(i) from sample lock in share mode; +———-+ | count(i) | +———-+ | 1638400 | +———-+ 1 row in set (7.02 sec) Looking at SHOW INNODB STATUS…
