… is the only MySQL storage engine which defaults to HASH index type by default, instead of BTREE which makes indexes unusable for prefix matches… which again comes from the fact MEMORY tables use HASH indexes by default. I’ve created rather similar test table: CREATE… bad to be true, but there is a reason. HASH index stores list of matching values for each hash value. In…
Comment: Dropping unused indexes
January 15, 2009 By 6 Comments
Is it possible to find unused index also on a MyISAM table? The above query says “#1109 – Unknown table ‘index_statistics’ in information_schema” at my system (MySQL 5.0.67).
Post: Announcing Percona Server 5.5.25a-27.1
July 21, 2012 By Leave a Comment
… #1007254 (Vadim Tkachenko). If the tablespace has been created with MySQL 5.0 or older, importing that table could crash Percona…-innodb crashes on SELECT * FROM INNODB_TABLE_STATS or INNODB_INDEX_STATS. Bug fixed #896439 (Stewart Smith). Fixed typo for log… #988383 (Stewart Smith). Fixed upstream mysql bug #60743, typo in cmake/dtrace.cmake that was making dtrace unusable. Bug fixed #1013455 (Stewart…
Post: SHOW INNODB STATUS walk through
July 17, 2006 By 40 Comments
… for last 0 or 1 second they are pretty much unusable. To be honest I do not really like averages Innodb… 3771312 starting index read mysql tables in use 1, locked 1 LOCK WAIT 3 lock struct(s), heap size 320 MySQL thread id… starting index read, thread declared inside InnoDB 500 mysql tables in use 1, locked 1 3 lock struct(s), heap size 320 MySQL…
Post: How much overhead is caused by on disk temporary tables
August 16, 2007 By 26 Comments
… to fit all key blocks from temporary table index, so we get a lot of key writes …to 128M ? mysql> show global status like “key%”; +————————+———+ | Variable_name | Value | +————————+———+ | Key_blocks_not_flushed | 49361 | | Key_blocks_unused | 61201 | …
Post: Tuning InnoDB Concurrency Tickets
May 24, 2010 By 5 Comments
InnoDB has an oft-unused parameter innodb_concurrency_tickets that seems widely misunderstood. From the … sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> ALTER TABLE test_table ADD INDEX (id); — 5 Tickets Used Query OK, 3…, 0 rows affected (0.01 sec) mysql> CREATE TABLE child (id INT, parent_id INT, -> INDEX par_ind (parent_id), -> FOREIGN KEY…
Post: Economics of Performance Optimization
March 13, 2008 By 9 Comments
… are easy to fix – adding couple of proper indexes or tuning couple of MySQL settings. When low hanging fruits are fixed you… breaks because immediate replacement may not be available and keeping unused spare box can be expensive. Because fewer users use these… variety of companies on he market offering services around MySQL and LAMP and MySQL is one of them. Plus for large companies…
Comment: MySQL Server Memory Usage
September 1, 2006 By 21 Comments
… ‘Com_create_function’, ’0′ ‘Com_create_index‘, ’0′ ‘Com_create_table’, ’31′ ‘Com…Key_blocks_not_flushed’, ’0′ ‘Key_blocks_unused‘, ’28987′ ‘Key_blocks_used’, ’1332′ ‘…mysql.sock pid-file=/var/run/mysql/mysql.pid datadir=/db/disk2/mysql/data log-bin=/var/log/mysql/log log-slow-queries=/var/log/mysql…
Post: How many partitions can you have ?
December 5, 2009 By 19 Comments
…interesting case recently. The customer dealing with large MySQL data warehouse had the table which was had… The difference grows even larger if we remove index on column C – the “UPDATE” part of …be careful with number of partitions you use. Creating unused partitions for future use may cost you. …
Post: Living with backups
November 6, 2008 By 28 Comments
…tables which have dedicated buffers only to store indexes, while the actual data is always read from …the cache. It happens by replacing old and unused blocks with the newly read ones. And …simple copy operation from MySQL data directory will result in a total disaster after MySQL stops responding to…
