…Using where | +—-+————-+————-+——-+—————+———–+———+——+——+————-+ 1 row in set (0.01 sec) SELECT count(*) FROM coordinates FORCE INDEX (x_y_col_a) …for this is simply a missing feature of the MySQL optimizer – and it has to do with using x …
Post: MySQL 5.5 and MySQL 5.6 default variable values differences
February 21, 2013 By 8 Comments
… be scaled down compared to MySQL 5.5 default values. Such as performance_schema auto scales to count up to 445 tables… sense in my opinion. MySQL 5.6 adds a lot more optimizer switches which you can play with: mysql [localhost] {msandbox} (test) > select * from var55 where variable_name=’OPTIMIZER_SWITCH’ \G *************************** 1. row *************************** VARIABLE_NAME: OPTIMIZER_SWITCH VARIABLE_VALUE…
Post: MySQL: what read_buffer_size value is optimal ?
September 17, 2007 By 25 Comments
…more I work with MySQL Performance Optimization and Optimization for other applications the better I understand I have to…and averaged results. MySQL 5.1.21-beta was used for tests. mysql> select count(*) from dt2 where slack like “a%”; +———-+ | count(*) | +———-+ | 4705992 | +———-+ …
Post: How to convert MySQL's SHOW PROFILES into a real profile
February 20, 2012 By 12 Comments
…mysql> SHOW PROFILE FOR QUERY 1; +———————-+———-+ | Status | Duration | +———————-+———-+ | starting | 0.000032 | | checking permissions | 0.000005 | … snip … | init | 0.000021 | | optimizing…), 2) AS Pct_R, COUNT(*) AS Calls, SUM(DURATION) / COUNT(*) AS “R/…
Post: 10+ Ways to Crash or Overload MySQL
November 13, 2007 By 20 Comments
… avoid use of prepared statements and keep max_prepared_stmt_count very low. Prepared Statements and Blob Data If you’re… tree inside MySQL which of course depends on query size which is controlled by max_allowed_packet. However some MySQL optimizations such as… max_prepared_stmt_count are designed to protect from typical application mistakes not from someone intentionally trying to bring MySQL Server down…
Post: Computing 95 percentile in MySQL
November 28, 2008 By 5 Comments
… similar values. The “average” is the evil of performance optimization and often as helpful as “average patient temperature in … if MySQL only provides you the avg: mysql> select count(*),avg(wtime) from performance_log_081128 where page_type=’search’; +———-+—————–+ | count(*) | avg(…
Post: Beware of MySQL Data Truncation
February 7, 2009 By 4 Comments
…of allowed range have it: mysql> explain select count(*) from article_comment where article_id=4300000000; ….00 sec) MySQL optimizer behaves rather strange in such case (at least in MySQL 5.0)…
Post: Time for Zero Administration effort at MySQL ?
January 8, 2012 By 4 Comments
Preparing Optimizing MySQL Configuration talk for Percona Live in Washington,DC I noticed how many Variables did MySQL get over years – it is pushing 400 these days even if we do not count variables/options which… natural step I would like to see taken is having MySQL to chose sensible values for them automatically, based on system…
Post: Finding an optimal balance of I/O, CPU, and RAM for MySQL
May 16, 2011 By 4 Comments
… MySQL scales as you add more memory to the server. Vadim recently benchmarked the effects of increasing memory and CPU core count… storage — specifically, the Virident tachIOn drive — has more bandwidth than MySQL can fully utilize at present. Therefore, to decrease the price… done. You can read the full details in our Scaling MySQL With Virident Flash Drives and Multiple Instances of Percona Server…
Post: Distributed Set Processing with Shard-Query
May 14, 2011 By 27 Comments
…of compute resource which speaks SQL, but right now only MySQL storage nodes are supported. Amdahl’s law applies to the… node result set merge optimization enabled: ON DUPLICATE KEY UPDATE `origin_airport_id`=VALUES(`origin_airport_id`), `count(*)`=`count(*)` + VALUES(`count(*)`), `sum(AirTime)`=`sum…
