… a new table on the master: mysql> create table test.t (id int not null auto_increment primary key); Executing SHOW TABLES FROM test on both slaves shows that the table has… status\G [...] Auto_Position: 1 -> GTID-based positioning # Slave #2 mysql> show slave status\G [...] Auto_Position: 0 -> File-based positioning Conclusion Enabling…
Comment: MySQL Performance Forums
June 9, 2013 By 18 Comments
… the resultset is huge, it eventually starts creating a temp table on disk. Now during those 300 second, other ‘innocuous’ queries… completely different table (unrelated to the select query) also get hang up. If I do show processlist, the update queries show up with… some other which show state as ‘null’ and Info says ‘commit’ The ‘Table_locks_waited’ remains zero while the ‘Table_locks_immediate’ keeps…
Post: The small improvements of MySQL 5.6: Duplicate Index Detection
May 31, 2013 By Leave a Comment
… TABLE test ADD INDEX (col2); Query OK, 0 rows affected (0.25 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> SHOW CREATE TABLE test\G *************************** 1. row *************************** Table: test Create Table: CREATE TABLE `test` ( `id` int(11) NOT NULL…
Post: MySQL Query Patterns, Optimized - Webinar questions followup
June 3, 2013 By 2 Comments
… there are different types of subqueries (correlated, non-correlated, derived tables, scalar subqueries) and we saw in my presentation that sometimes…-by-case decision. There are other drawbacks to using temporary tables in this way, for example when using replication, a temporary… were trying to determine the last episode of each TV show. Couldn’t you have used a SUBQUERY to fetch and…
Post: Choosing a MySQL HA Solution - Post-Webinar Q&A
June 7, 2013 By Leave a Comment
… replicate a specific database, a set of databases, or specific tables; you can also configure the slave to replicate everything EXCEPT a specified set of databases and tables. With this method, every event is still written to the… large number of long-running queries in the output of SHOW PROCESSLIST that might be a sign that trouble is brewing…
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… handle 0x2b3c1c776940, query id 16838101 localhost root Copying to tmp table create table t as SELECT ld.label_id, cai.inventory_id…) group by ld.label_id, cai.inventory_id All 4 tables in the query are locked!
Post: Replication in MySQL 5.6: GTIDs benefits and limitations - Part 2
May 30, 2013 By 6 Comments
… the replication topology when using GTID-based replication. That will show where GTIDs shine and where improvements are expected. This is…; # And then check the number of records from the t table s1> select count(*) from t; +———-+ | count(*) | +———-+ | 2 | +———-+ Great! So again…
Post: Percona Server for MySQL 5.5.31-30.3 now available
May 24, 2013 By Leave a Comment
…_memory and innodb_descriptors_memory status variables in the Extended Show Engine InnoDB Status to improve InnoDB memory usage diagnostics. Bugs… the MySQL 5.6 version, which removed MyISAM internal temporary table mutex contention. Bug fixed #1179978. Release notes for Percona Server…
Post: ZFS on Linux and MySQL
May 24, 2013 By 17 Comments
…’ll have to start the snapshot while holding a “flush tables with read lock” and the flush operation will take some… a lot of random data that doesn’t compress well showed a compression ration of 1.70 with gzip-1. Real…
Post: How to recover table structure from InnoDB dictionary
April 22, 2013 By 4 Comments
… an option: Table was dropped and innodb_file_per_table is ON Frm file corrupt, zeroed out, lost or SHOW CREATE TABLE crashes MySQL There is yet another source of information about the table structure – InnoDB dictionary. Let’s review tables from…_COLUMNS stores fields names and type information of the table. CREATE TABLE `SYS_COLUMNS` ( `TABLE_ID` bigint(20) unsigned NOT NULL, `POS` int…
