…table lock priority problem with pending WRITE query blocks any READ queries to execute on the table. mysql> show…
Post: Troubleshooting MySQL Memory Usage
March 21, 2012 By 3 Comments
…? If you can’t get fancy graph quickly you … tables: mysql> select sum(data_length+index_length) from information_schema.tables where …TIME: NULL 3 rows in set (0.00 sec) Innodb…is least intrusive. Run SHOW ENGINE INNODB STATUS and … memory allocation and will result in increased memory allocation …
Post: Best kept MySQLDump Secret
March 23, 2012 By 11 Comments
…get new structure in the dump but no data. Here is example: SESSION1: mysql> show tables; +——————–+ | Tables_in_dumptest | +——————–+ | A | | B | | C | +——————–+ 3 rows in set…
Post: InnoDB's gap locks
March 27, 2012 By 14 Comments
…query twice, you get the same result, regardless other session modifications on that table. This makes reads… possible to detect those gap locks using SHOW ENGINE INNODB STATUS: —TRANSACTION 72C, ACTIVE … http://dev.mysql.com/doc/refman/5.1/en/innodb-locks-set.html. Conclusion MySQL uses REPEATABLE…
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…
Post: Troubleshooting MySQL Upgrade Performance Regressions
February 15, 2012 By Leave a Comment
…RESULT/SMALL_RESULT. Check whatever stats are the same (run SHOW INDEXES FROM for tables…MySQL Version to get slower plan instead of new one getting faster. Check stats sampling settings…get more accurate results. In recent Percona Server versions and MySQL 5.6 you can also store innodb stats in table…
Post: MySQL Query Cache
July 27, 2006 By 69 Comments
…table gets modification all queries derived from this table are invalidated at once. Most of them quite likely would not have change their result set but MySQL…
Post: Eventual Consistency in MySQL
November 18, 2011 By 8 Comments
….TABLE_SCHEMA, K.TABLE_NAME) = (P.TABLE_SCHEMA, P.TABLE_NAME) AND…result set, it shows us which rows in Bar are orphans. mysql> INSERT INTO Foo (A,B) VALUES (111,2222), (333,444); mysql…mysql -N | mysql -E *************************** 1. row *************************** test.Bar.ID: 42 DIY RI We can get…
Post: Testing MySQL column stores
August 16, 2010 By 15 Comments
…a large data set against two MySQL column-store storage…avg(char_column) from table” where the column contains… that would be “show stoppers” for typical OLAP…results of queries over the large data set Speed of results of queries over the large data set… not able to get accurate results for all of the…
Post: How fast can you sort data with MySQL ?
August 18, 2007 By 26 Comments
…MySQL can sort 1.000.000 rows, or rather return top 10 rows from sorted result set…table completes in 0.22 seconds giving us about 4.5 Million of rows/sec. Obviously we can’t get sorted result set…
