… frequent cases with performance problems with MySQL is what they happen every so often or certain times. Investigating them we find out… cause is some batch jobs, reports and other non response time critical activities are overloading the system causing user experience to… need to know it is not MySQL problem, might be even not problem with your MySQL configuration, queries and hardware, even though…
Post: Troubleshooting MySQL Memory Usage
March 21, 2012 By 3 Comments
… variable which allows you to limit size of MEMORY tables (the limit applies both to implicit … 16384 INDEX_LENGTH: 0 CREATE_TIME: NULL UPDATE_TIME: NULL 3 rows in set (0.00 sec) Innodb …This is where your MySQL Support contract can be handy. Conclusion Understanding where MySQL can allocate memory …
Post: Percona Live MySQL Conference & Expo Was A Great Event
April 17, 2012 By 2 Comments
…were available. In 2013 we have none of those limitations: the conference is a day longer and isn’… example, had a set of vintage Legos and bean-bag chairs that stayed busy all the time. And I …for their new development milestone release for MySQL 5.6, released on Monday. MySQL 5.6 just keeps looking …
Post: Joining many tables in MySQL - optimizer_search_depth
April 20, 2012 By 6 Comments
…for this problem was to use set optimizer_search_depth=0, rarely … optimization, and full query execution time to less than 50ms. Low values…following explanation from Timour Katchaounov in MySQL mailing list archives I have …of min(number of tables, 7) essentially limiting search depth to no more …
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 2 Comments
…, o_orderdate, o_shippriority order by revenue desc, o_orderdate LIMIT 10; In-memory workload Now let’s see how effective… to MySQL 5.5 While with join_buffer_size set to 6M and read_rnd_buffer_size set to 6M, the query time for MySQL 5.6 becomes approximately equal to that of MySQL…
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
… n_name, c_address, c_comment order by revenue desc LIMIT 20; In-memory workload Now let’s see how … in-memory workload, the InnoDB buffer pool size is set to 6G and the buffer pool was warmed up, … and the query time for MySQL 5.6 and MariaDB 5.5 jumps to ~11min (this is the query time for MySQL 5….
Post: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact
March 12, 2012 By 12 Comments
… in preparation for the talk comparing the optimizer enhancements in MySQL 5.6 and MariaDB 5.5. We are taking a… optimizer enhancement Index Condition Pushdown (ICP). Its available in both MySQL 5.6 and MariaDB 5.5 Now let’s take…. Index Condition Pushdown Traditional B-Tree index lookups have some limitations in cases such as range scans, where index parts after…
Post: Distributed Set Processing with Shard-Query
May 14, 2011 By 27 Comments
…was all I could due to to EC2 and time limits. I think the results at 20 nodes are … really a trick question. The “result set” is a SET created by the output of a relational algebra expresion…compute resource which speaks SQL, but right now only MySQL storage nodes are supported. Amdahl’s law applies …
Post: Apache PHP MySQL and Runaway Scripts
May 20, 2008 By 20 Comments
… limitation in practice when you’re using MySQL with PHP as you can have single runaway query which takes a lot of time… function you can set an alarm and have script terminated after certain amount of seconds: Unlike set_time_limit() which specifies cpu time, pcntl_alarm… 2 simple scripts which run for 1000 seconds (spending this time on MySQL time) one is doing single query another 1000 queries 1…
Post: Can we improve MySQL variable handling ?
February 18, 2012 By 8 Comments
…-isolation REPEATABLE-READ transaction-prealloc-size 4096 updatable-views-with-limit YES userstat FALSE verbose TRUE wait-timeout 28800 Which is… time default, not the one server was started with) yet there seems not to be a way to read it: mysql> set global sort_buffer_size=DEFAULT; Query OK, 0 rows affected (0.00 sec) mysql> select @@global.sort…
