… with infinite replication loops. I decided to write a blog post about these infinite loop of binary log statements in MySQL Replication. To… to the time those looping statement were first inserted. Another way to see it is by monitoring your MySQL server with cacti, you… were in the loop using mysqlbinlog. For example what was the loop of events from server-id 2? # mysqlbinlog mysql-relay-bin.000x…
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
… referring to the amazing MySQL manual, especially the option and variable reference table. ….commanual Delayed_errorsblogpercona.commanual delayed_insert_limitblogpercona.commanual Delayed_insert_threadsblogpercona.commanual delayed_insert_timeoutblogpercona.commanual delayed_queue_sizeblogpercona…
Post: Sharing an auto_increment value across multiple MySQL tables
October 4, 2010 By 9 Comments
… favorite two: Option #1: Use a table to insert into, and grab the insert_id: CREATE TABLE option1 (id int not null…_increment) engine=innodb; # each insert does one operations to get the value: INSERT INTO option1 VALUES (NULL); # $connection->insert_id(); Option #2: Use… each statement Option 1 – nest each loop in a transaction Option 2 – nest each loop in a transaction The raw results are…
Post: High-Performance Click Analysis with MySQL
December 22, 2008 By 13 Comments
… is important is that nested-loop joins on large data sets are very expensive. If MySQL supported sort-merge or hash… bypass the hard-to-scale database server for the initial insertion, because you can write CSV files with any programming language… problem is that MySQL doesn’t tend to perform well on a data warehousing workload. The nested-loop joins are not…
Post: Fighting MySQL Replication Lag
September 22, 2008 By 6 Comments
… query design mistakes which result in low hanging fruit troubleshooting MySQL Replication Lag First fact you absolutely need to remember is…/delete with LIMIT in the loop, controlling maximum amount of values per batch in multiple row insert statement or Fetching data you… rows examined than modified. The next common mistake is using INSERT … SELECT – which is in similar to what I just described…
Post: Side load may massively impact your MySQL Performance
November 13, 2011 By 7 Comments
…–mysql-user=msandbox –mysql-password=msandbox –mysql-socket=/tmp/mysql_sandbox5516.sock prepare Running Sysbench and MySQLDump. Note we run them in the loop to… on this test system. It is worth to note MySQL actually uses midpoint insertion for its buffer pool replacement policy . Unfortunately by…
Post: Percona Server 5.1.58-12.9
August 17, 2011 By 2 Comments
… remaining loop from an unimplemented feature degraded the performance when using compressed tables and has been removed (buf_LRU_insert_zip_clean). Bugs Fixed: #802825 / #61341 in MySQL (Yasufumi Kinoshita). Compatibility Collations Two new…
Comment: Pitfalls of converting to InnoDB
March 8, 2007 By 16 Comments
… found. I’m using PHP 5.1.6, MySQL 5.0.24a default installs from Ubuntu 6.10… with a “$mysqli->autocommit(false)”, then executes an insert SQL statement, and completes with a “$mysqli->commit()”. …I haven’t tuned anything). So your script could loop for 5000 seconds. Perhaps a better solution is …
Comment: SELECT LOCK IN SHARE MODE and FOR UPDATE
August 2, 2007 By 51 Comments
… while loop i write condition for LOCKING 3. if(mysql_query(“LOCK TABLE t1 WRITE”)){ Insert / update in t3 and t4 update/insert t1 mysql_query(“UNLOCK TABLES”); } 4.End while loop But Its not going inside if(mysql_query…
Post: SHOW INNODB STATUS walk through
July 17, 2006 By 40 Comments
… to start really worry for most CPUs. innodb_sync_spin_loops can be used to ballance between wasting CPU time running…, process no 3946, OS thread id 1151088992 inserting, thread declared inside InnoDB 500 mysql tables in use 1, locked 1 3 lock… 368, undo log entries 1 MySQL thread id 9697561, query id 188161264 localhost root update insert into child values(2,2) Foreign…
