… like to see MySQL memory consumption plotted. I use “VSZ” columns from “ps” output on… 16384 INDEX_LENGTH: 0 CREATE_TIME: NULL UPDATE_TIME: NULL *************************** 2. row *************************** SESSION_ID: …would note though the output is a bit confusing as “Total Memory Allocated” is…
Post: Shard-Query EC2 images available
May 11, 2011 By 2 Comments
…bit varieties. Due to memory requirements, the InnoDB versions are only available on 64 bit instances. MySQL…the list of columns to show (click show/hide to change the columns) That the “public …threads=16 innodb-recovery-stats innodb-recovery-update-relay-log innodb-replication-delay=# innodb-…
Post: Hijacking Innodb Foreign Keys
January 2, 2012 By 2 Comments
… little bit funny when you will try to update data stored in it. If you do not change “parent_id” column you can update data just fine, however if you want to change the parent_id column, even if you… update all columns whenever they have changed or not: mysql> set foreign_key_checks=1; Query OK, 0 rows affected (0.00 sec) mysql…
Post: INSERT ON DUPLICATE KEY UPDATE and REPLACE INTO
January 18, 2007 By 15 Comments
…and INSERT ON DUPLICATE KEY UPDATE. Really, Why MySQL has both of these, especially…do read before write is a bit overrated from efficiency standpoint. As …update accessing previous column value, for example doing something like REPLACE INTO tbl (col1) VALUES (col1+10);. Instead of acting as update…
Post: How to debug long-running transactions in MySQL
March 8, 2011 By 6 Comments
…PROCESSLIST, and look in the Host column; there you’ll see a hostname… script that I use quite a bit. I simply start a screen …${port} > /root/tcpdumps/`date +%s`-tcpdump & mysql -e ‘show innodb status\Gshow full processlist’ > /… jobs that run in loops and update data, unless you make them commit …
Post: High-Performance Click Analysis with MySQL
December 22, 2008 By 13 Comments
… to save work. Avoid operations that update huge chunks of aggregated data at …can have the clicks column record the total, and the blue_clicks column record only blue… either using MySQL 5.1′s row-based replication, or in MySQL 5.0 and…any possible way, do it in manageable bits. A day at a time, …
Post: Multi Column indexes vs Index Merge
September 19, 2009 By 30 Comments
… columns. The better strategy is often to have combined multi-column index on (AGE,STATE). Lets see why it is the case. MySQL… table to make columns i1 and i2 highly correlated. In fact they are now the same: mysql [localhost] {msandbox} (test) > update idxtest set… some way. Because index is longer query execution takes a bit longer – 300ms So in this case we see index merge…
Post: The story of one MySQL Upgrade
October 8, 2010 By 8 Comments
… ON DUPLICATE KEY UPDATE had a unfair share of replication issues in MySQL 5.0. There…checksum in some cases. Rerunning check using BIT_XOR algorithm eliminated those false positives. Another…MySQL to see what is different in the tables. It turned out one of the float columns stored “-0″ in MySQL…
Post: MySQL Users Conference - Innodb
May 9, 2007 By 6 Comments
…Users Conference but I’m just starting find bits of time from processing accumulated backlog. The Theme… for statement duration for Inserts with auto-increment column. This starts giving you problems if you have… in INSERT IGNORE and ON DUPLICATE KEY UPDATE cases. Now In MySQL 5.1+ it is also possible…
Post: Finding out largest tables on MySQL Server
February 4, 2008 By 24 Comments
… MySQL instance is no brainier in MySQL 5.0+ thanks to Information Schema but…can save on counting zeros. The last column shows how much does the index …worth to look. Changing the query a bit to look for different sorting order or …5 minutes to run for 450 tables. UPDATE: To make things easier I’ve …
