… to sum them all up: mysql> show create table ex1; … mysql> select count(*) from ex1; +———-+ | count(*) | +———-+ | 73027220 | +———-+ 1 row in set (0.00 sec) mysql…any two numbers sum to zero: mysql> select a.val, b….
Post: How fast can you sort data with MySQL ?
August 18, 2007 By 26 Comments
…) As you can see from this show status output MySQL only counts completely sorted rows in Sort_rows variable. …; mysql> select * from gt order by i desc limit 10; 10 rows in set (1.34 sec) mysql> show …your sort_buffer_size large enough so there is zero sort_merge_passes may not be optimal. World …
Post: Time for Zero Administration effort at MySQL ?
January 8, 2012 By 4 Comments
… many Variables did MySQL get over years – it is pushing 400 these days even if we do not count variables/options which do not have matching SHOW VARIABLES value, and settings done by… natural step I would like to see taken is having MySQL to chose sensible values for them automatically, based on system…
Post: SHOW INNODB STATUS walk through
July 17, 2006 By 40 Comments
…SHOW INNODB STATUS output, showing what you can learn from SHOW INNODB STATUS output and how to use this info to improve MySQL…information is event counters – “reservation count” and “signal count” show how actively innodb uses internal… you may have these at zeroes while workload being IO bound still…
Post: MySQL 5.6 vs MySQL 5.5 and the Star Schema Benchmark
March 11, 2013 By 7 Comments
…strange results, however, which showed MySQL 5.6.10 to be much slower than MySQL 5.5.30 … LRU for zero milliseconds before being able to move to the hot list. In MySQL 5….mysql> select sq.*, pages / (@@innodb_buffer_pool_size / 16384) * 100 pct_buffer_pool from ( select table_name, index_name, count…
Post: Innodb Table Locks
July 31, 2012 By 4 Comments
…MySQL thread id 53038, OS thread handle 0x7ff759b22700, query id 3918786 localhost root Sending data select count…instances (note – same table gets counted twice) in use but zero tables are locked. Innodb does… on MySQL level will prevent tables from being accessed and will not show up in SHOW ENGINE …
Post: Recovery deleted ibdata1
August 10, 2012 By 9 Comments
… number and last checkpoint in “SHOW ENGINE INNODB STATUS” output. If checkpoint age is zero, all pages are flushed: — LOG — …MySQL: root@localhost:/var/lib/mysql# /etc/init.d/mysql restart After the restart all InnoDB tables are reachable: mysql> select count(*) from sbtest; +———-+ | count…
Post: Impact of the sort buffer size in MySQL
October 25, 2010 By 8 Comments
… COUNT=0 while [ "$NUMROW" -gt "$COUNT" ] do UUID=`uuidgen` mysql test -e “insert into sorttest value (‘$UUID’);” let “COUNT=COUNT… to interpret the results. The figure below shows a graphical representation of the results. The … number of sort merge passes felt to zero at 6.4MB since the total size …
Post: Realtime stats to pay attention to in Percona XtraDB Cluster and Galera
November 26, 2012 By 4 Comments
… server. This tool takes what are usually global counters in SHOW GLOBAL STATUS and calculates the change each second and reports… transaction counts. I have seen production clusters having performance issues where I noticed that the Ops and Bytes went to Zeros on… replication is performing well, because the Flow control columns are zeroes, but we do see some replication conflicts. Mostly these are…
Post: Finding out largest tables on MySQL Server
February 4, 2008 By 27 Comments
Finding largest tables on MySQL instance is no brainier in MySQL 5.0+ thanks to Information Schema but I still wanted … index size in GB so I can save on counting zeros. The last column shows how much does the index take compared to… whatever database you have active. It does not work with MySQL before 5.0 still of course 
