…for MyISAM for same tables: mysql> show table status like “%bool%” \G *************************** 1. row *************************** Name: bbool Engine: MyISAM Version: 10 Row_format: Fixed Rows: 2097152 Avg_row…
Post: Innodb Table Locks
July 31, 2012 By 4 Comments
…table instances (note – same table gets counted twice) in use but zero tables are locked. Innodb does not need any row locks for…Table Level Locks. For MyISAM tables running UPDATE query on the table is essentially equivalent to locking table for write (on MySQL…
Post: How FLUSH TABLES WITH READ LOCK works with Innodb Tables
March 23, 2012 By 20 Comments
…all (use mysql -A in this case, to prevent it from getting field list from all tables…
Post: Wow. My 6 year old MySQL Bug is finally fixed in MySQL 5.6
August 30, 2012 By 2 Comments
…mysql> select count(*) from trunc where i=4147483647; +———-+ | count(*) | +———-+ | 0 | +———-+ 1 row…MySQL continues to be very loose by default. Consider running with sql_mode=strict_all_tables…
Post: Troubleshooting MySQL Memory Usage
March 21, 2012 By 5 Comments
…For prepared statements you might want to look at Prepared_stmt_count…tables (both in memory and not): mysql> select * from information_schema.global_temporary_tables \G *************************** 1. row *************************** SESSION_ID: 7234 TABLE_SCHEMA: test TABLE…
Post: MySQL Indexing Best Practices: Webinar Questions Followup
August 16, 2012 By 4 Comments
…: Typically you do not need all your indexes to be in… a SELECT competition_id, COUNT(user_id) AS user_count FROM user_competition… an analyze on a table with 11M rows I’ve seen cardinality…mysql use index for group by? A: If you have Index on the column MySQL can avoid temporary table or filesort for…
Post: Modeling MySQL Capacity by Measuring Resource Consumptions
March 8, 2011 By 1 Comment
… will be consuming all CPU when we’re… at procfs for MySQL process: root@ubuntu:/var/log/mysql# cat /…_WRITTEN: 0 ROWS_FETCHED: 485139 ROWS_UPDATED: 0 TABLE_ROWS_READ: 610954 … avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 98 1753697 # Exec …
Post: Tools and Techniques for Index Design Webinar Questions Followup
October 4, 2012 By 5 Comments
… of MySQL. Q: How do the upcoming changes in MySQL 5.6 change the best practices for … the grouping column to your index, so all rows in a given group are read together….for estimating the theoretical cost of a query, by counting how many lookups to index entries or table rows will be necessary for…
Post: MySQL 5.5 and MySQL 5.6 default variable values differences
February 21, 2013 By 8 Comments
… 56 rows…MySQL 5.5 default values. Such as performance_schema auto scales to count up to 445 tables…tables or if you do many create/drop of innodb tables. optimizer_switch is the catch all variable for…
Post: Timezone and pt-table-checksum
September 25, 2012 By 1 Comment
…+———-+————+ | b890c395 | 763d97f1 | +———-+————+ Naturally, pt-table-sync finds this and reports that all 3 rows in this …table-sync when looking at rows: SELECT /*foo.bar:1/1*/ 0 AS chunk_num, COUNT… always use UTC as your timezone for MySQL to prevent this extra logic from…
