One of the very frequent cases with performance problems with MySQL is what they happen every so often or certain times… need to know it is not MySQL problem, might be even not problem with your MySQL configuration, queries and hardware, even though… good idea. It also often helps with monopolizing replication thread. For example if I need to delete old data instead of…
Comment: What to tune in MySQL Server after installation
April 28, 2012 By 116 Comments
… opened. But when I send the command for more than five employees then database is…. Because there are several queries with while loop in my application. I request to all …mysql.jdbc.StandardSocketFactory.connect(StandardSocketFactory.java:156) at com.mysql.jdbc.MysqlIO.(MysqlIO.java:276) at com.mysql…
Comment: Moving Subtrees in Closure Table Hierarchies
May 2, 2012 By 7 Comments
… in a database using MySQL and I’m wondering how can I create a unordered list with indentation (for each level) with… so because you’re “walking the path”. I’m using MySQL, PHP or Coldfusion (I have to do it in both… and then put it in an array or structure to loop over… Thanks, Annie.
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 2 Comments
… briefly explain these optimizations. Batched Key Access Traditionally, MySQL always uses Nested Loop Join to join two or more tables. What this… As I have told before MySQL has only supported one join algorithm and that is Nested Loop Join. MariaDB has introduced a… from Hash Join as compared to Nested Loop Join, but for now it looks like Nested Loop Join is a much better general…
Comment: How to load large files safely into InnoDB with LOAD DATA INFILE
March 29, 2012 By 28 Comments
… “\\”) { $line .= ; } } right at the beginning of the main loop. Then it works for me like a charm transferring the biggest table we have (160GB , varchar columns). Also, rather then time’ing the mysql commands in the loop (second shell), I added –show-warnings to the command…
Post: Infinite Replication Loop
October 10, 2011 By 2 Comments
… with infinite replication loops. I decided to write a blog post about these infinite loop of binary log statements in MySQL Replication. To… on mysql 1 Note: you can also check all the statements that were in the loop using mysqlbinlog. For example what was the loop of events from server-id 2? # mysqlbinlog mysql-relay-bin.000x…
Post: How to debug long-running transactions in MySQL
March 8, 2011 By 6 Comments
… traffic for a while, use mk-query-digest to convert this into a query log, and examine the log. In MySQL 5… simply start a screen session and run it in a loop with a 30-second sleep in between. Notice that it… — InnoDB is designed for lots of short, small transactions. Don’t design batch jobs that run in loops and update data, unless…
Post: Troubleshooting MySQL Upgrade Performance Regressions
February 15, 2012 By Leave a Comment
… it without side load. Setting up 2 MySQL Servers side by side (for example with MySQL Sandbox) can especially be helpful. Once you have spotted the query which performs differently between MySQL Server versions… PROFILE to see where differences are. Running query in the loop and getting o_profile sample can also be a good…
Post: Is your MySQL Application having Busy IO by Oracle Measures ?
January 8, 2012 By 1 Comment
…/sec would be exceptional for traditional MySQL as MySQL optimizer with its nested loops joins does not favor sequential scans even for analytic queries executions… are several reasons for that. First because MySQL can be used for free we do not need to optimize the hardware for license costs…
Post: MySQL Limitations Part 4: One thread per connection
October 27, 2010 By 14 Comments
… implemented is a pool of threads, which was originally coded for MySQL 6.0, but is available now in MariaDB. Unfortunately it…, if you’re running a data warehouse on MySQL, and in some cases for other uses too (note that most databases that… to use nested-loop joins whenever possible, because of things like the lower startup cost for the query). But MySQL isn’t a…
