… replication. We will see how to change the replication topology when using GTID-based replication. That will show where GTIDs shine and where…> change master to master_port=10002; mysql> start slave; # And then check the number of records from the t table s1> select count(*) from t…
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 Performance. To start with basics SHOW INNODB STATUS…
Post: How to debug long-running transactions in MySQL
March 8, 2011 By 6 Comments
…to figure out which transaction is blocking others. There are some INFORMATION_SCHEMA tables that make this simple. Just query the tables… that holds any locks from SHOW INNODB STATUS and assume that …SELECT queries since its inception. That case is still open and I am still stumped as to how a SELECT…
Post: How much memory Innodb locks really take ?
July 13, 2006 By 11 Comments
…`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 mysql> show table status like “sample” \G; *************************** 1. row …table and see how long does it take: mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> select count(i) from… or want to lock every row in your table/database which…
Post: How (not) to find unused indexes
October 16, 2009 By 2 Comments
…SELECT * FROM sales WHERE status=’archived’; # query 2 +—-+————-+——-+——+—————+——+———+——+——-+————-+ | id | select_type | table…from tablescan to index at about 34%. How am I supposed to find unused indexes then? You really have to…
Post: How much overhead is caused by on disk temporary tables
August 16, 2007 By 26 Comments
… CREATE TABLE `gt` ( `i` int(10) unsigned NOT NULL, `c` char(50) NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 mysql> select * from … takes fraction of second to do, but it is not how it happens in practice. mysql> show global status like “key%”; +————————+———+ | Variable_…
Post: Efficient Boolean value storage for Innodb Tables
April 23, 2008 By 6 Comments
…to much larger savings if you have hundreds of such columns. Lets see how things look for MyISAM for same tables: mysql> show table status…
Post: How fast can MySQL Process Data
April 9, 2008 By 3 Comments
…to keep it simple) (In reality I did multiple runs to get accurate results, but I show only one here) mysql> select sum(i) from…table: CREATE TABLE `m4` ( `c` char(128) NOT NULL ) ENGINE=MEMORY DEFAULT CHARSET=latin1 mysql> show table status…
Post: How to convert MySQL's SHOW PROFILES into a real profile
February 20, 2012 By 12 Comments
SHOW PROFILES shows how…to demonstrate: mysql> SET profiling=1; mysql> pager cat > /dev/null mysql> SELECT * FROM…SHOW PROFILE FOR QUERY 1; +———————-+———-+ | Status | Duration | +———————-+———-+ | …table that would allow me to subtract the SUM(DURATION) from…
Post: How much memory Innodb Dictionary can take ?
May 6, 2010 By 3 Comments
…once table is accessed and keeps until server is shut down. In XtraDB we have an option to restrict that limit. So how… select count(*) from INNODB_SYS_INDEXES; +———-+ | count(*) | +———-+ | 451773 | +———-+ 1 row in set (2.75 sec) In this case The memory stats from SHOW INNODB STATUS…
