… mysql> stop slave; mysql> change master to master_port=10002; mysql> start slave; # And then check the number of records from the t table… executed: s1> show slave status\G [...] Executed_Gtid_Set: 219be3a9-c3ae-11e2-b985-0800272864ba:1, 3d3871d1-c3ae-11e2-b986-0800272864ba:1-4 We can see…
Comment: INSERT INTO ... SELECT Performance with Innodb tables.
May 22, 2013 By 22 Comments
… output from show engine innodb status: —TRANSACTION BB0E42A, ACTIVE 3 sec fetching rows, thread declared inside InnoDB 334 mysql tables in use 4, locked 4 3264… localhost root Copying to tmp table create table t as SELECT ld.label_id, cai.inventory_id, COUNT(1) as installed_count from…
Post: Choosing a MySQL HA Solution - Post-Webinar Q&A
June 7, 2013 By Leave a Comment
… at least 4 NIC ports. Why 4? Two …you’ll get roughly 1.67x the throughput of …set of databases and tables. With this method, …MySQL is up and running. That said, there are plenty of other MySQL and OS-level status…SHOW PROCESSLIST that might be a sign that trouble is brewing. Or if you watch your MySQL…
Post: Galera Flow Control in Percona XtraDB Cluster for MySQL
May 2, 2013 By 5 Comments
… unlike anything you find in standard async MySQL replication. It is my belief that the…the certification index, which is a hash table. A small fc_limit does however keep…fraction of time (out of 1.0) since the last SHOW GLOBAL STATUS that flow control is effect,…3 node3 Sync T/T 0 4 0 3 0 4.3K 0.0 0 …
Post: Is there room for more MySQL IO Optimization?
January 3, 2013 By 11 Comments
…pretty well representative. I have an old test server having 4*7200RPM SATA hard drives in RAID10. It has 8GB …natural state of the table as it would be in the production over time: mysql> show table status like “post_channel” \G *************************** 1. row *************************** Name: …
Post: ALTER TABLE: Creating Index by Sort and Buffer Pool Size
June 27, 2012 By 4 Comments
…4*SATA disks in RAID10. I used 10mil row table which would look as following in terms of data and index size: mysql> show table status like “sbtest” \G *************************** 1. row …
Post: SHOW INNODB STATUS walk through
July 17, 2006 By 40 Comments
… walk through SHOW INNODB STATUS output, showing what you can learn from SHOW INNODB STATUS output and how to use this info to improve MySQL Performance… show innodb status —TRANSACTION 0 80157600, ACTIVE 4 sec, process no 3396, OS thread id 1148250464, thread declared inside InnoDB 442 mysql tables in use 1…
Post: Identifying the load with the help of pt-query-digest and Percona Server
December 29, 2011 By 3 Comments
…tables involved and the query execution plan used by MySQL…Tables # SHOW TABLE STATUS FROM `wp_blog_one ` LIKE ‘wp_terms’\G # SHOW CREATE TABLE `wp_blog_one `.`wp_terms`\G # SHOW TABLE STATUS…
Post: Implications of Metadata Locking Changes in MySQL 5.5
February 1, 2013 By 9 Comments
…show status like ‘Open%tables‘; +—————+——-+ | Variable_name | Value | +—————+——-+ | Open_tables | 30 | | Opened_tables | 1 | +—————+——-+ 2 rows in set (0.00 sec) session10 > show profile for query 18; +———————-+———–+ | Status…
Post: Efficient Boolean value storage for Innodb Tables
April 23, 2008 By 6 Comments
… bit(1) NOT NULL, `b8` bit(1) NOT NULL, `b9` bit(1) NOT NULL, `b10` bit(1) NOT NULL ) ENGINE=MyISAM mysql> show table status like ‘bbool’ \G *************************** 1… `t6` tinyint(4) NOT NULL, `t7` tinyint(4) NOT NULL, `t8` tinyint(4) NOT NULL, `t9` tinyint(4) NOT NULL, `t10` tinyint(4) NOT NULL…
