… | 3740 | | 3 | 5337 | 742034 | 3674 | | 4 | 5825 | 738420 | 3593 | | 5 | 5803 | 733495 | 3670 | | 6 | 1579 | 732507 | 3666 | | 7 | 9316… in set (32.03 sec) mysql> select sum(total_lines) cnt from dashboard_top_customers\G *************************** 1. row *************************** cnt: 155187034 …
Post: Flexviews - part 3 - improving query performance using materialized views
April 4, 2011 By 12 Comments
Post: Performance Schema tables stats
May 2, 2011 By 2 Comments
… TOP 5 accessed tables via mysql> select OBJECT_NAME,COUNT_STAR from table_io_waits_summary_by_table order by COUNT_STAR DESC LIMIT 5… | 1829666 | | sbtest47 | 1825035 | +————-+————+ 5 rows in set (0.01 sec) 2. There is table with statistic per index: mysql> select * from table_io…_OF_BYTES_WRITE: 326303744 or we can get top tables that required read IO mysql> select * from file_summary_by_instance order by…
Post: How fast can you sort data with MySQL ?
August 18, 2007 By 26 Comments
… bad ! Even though MySQL does not optimize “get top N sorted rows” very well it takes just 2.5 times longer than full…_buffer_size=10000000; mysql> select * from gt order by i desc limit 10; 10 rows in set (1.34 sec) mysql> show status… large sort_buffer_size ? mysql> set sort_buffer_size=100000000; Query OK, 0 rows affected (0.00 sec) mysql> select * from gt order…
Post: MySQL VIEW as performance troublemaker
August 12, 2007 By 53 Comments
… explain due to several layers of cascaded views built on top of one another so it is easy to write the… of comments left by the given user ? mysql> select count(*) from comments where user_id=5; +———-+ | count(*) | +———-+ | 1818 | +———-+ 1 row in set (0…, 0 rows affected (0.00 sec) mysql> select * from user_counts where user_id=5; +———+——+ | user_id | cnt | +———+——+ | 5 | 1818 | +———+——+ 1 row in set…
Post: Identifying the load with the help of pt-query-digest and Percona Server
December 29, 2011 By 3 Comments
…atomically, not just for new connections as in MySQL. This is very helpful for measurement as …SELECT wp_terms wp_term_taxonomy wp_term_relationships # 5 0xA3EEB63EFBA42E9B 0.1999 2.5% 51 0.0039 1.00 0.00 SELECT… output ranks the queries and shows the top queries with largest impact – longest sum of …
Comment: MySQL random freezes could be the query cache
March 29, 2009 By 23 Comments
… 44 0 24080K 5096K select 0 5:36 0.00% snmpd 630 root 1 44 0 10480K 2136K select 0 0:05 0… 12208K 5116K CPU2 2 0:00 0.00% top 800 root 1 5 0 5684K 996K ttyin 0 0:00 0.00… root 1 5 0 5684K 944K ttydcd 2 0:00 0.00% getty 449 root 1 44 0 2180K 472K select 0 0:00 0.00% devd 20895 mysql 1500 44 0 409M 250M ucond…
Post: How Percona does a MySQL Performance Audit
November 24, 2008 By 17 Comments
… elect to use in some cases): vmstat 5 5 iostat -dx 5 5 If you don’t know how to …top 10 most expensive queries, in total execution time. By the way, the slow query logs in the stock MySQL… each query, I run EXPLAIN if possible (rewriting non-SELECT queries if needed, and using careful judgment for …
Post: Goal driven performance optimization
December 22, 2008 By Leave a Comment
… to look from the top of the stack. If you look at MySQL log you may …mysql> select count(*),avg(wtime),avg(utime/wtime) cpu_ratio, avg(mysql_time/wtime) mysql_ratio ,avg(sphinx_time/wtime) sphinx_ratio, avg((wtime-mysql… I’d recommend to looking down to 5 minutes or even 1 minute intervals and …
Post: MySQL EXPLAIN limits and errors.
July 24, 2006 By 6 Comments
… it can for MyISAM. EXPLAIN may take long time In MySQL 5.0 with addition of greedy join the problem on looking… Derived tables (selects in FROM clause) are used. MySQL will execute these subselects on explain to provide plan for top most statement, so if you made an error in select in…
Post: Faster MySQL failover with SELECT mirroring
February 1, 2009 By 9 Comments
…you can fail over in both directions. Aside from MySQL Cluster, which is more special-purpose, this is probably…against these three sets of stats to find the top tables on the active server and left-join …the result: db_table active passive + SELECT passive site.benefits 8.30 5.73 1.32 . 3.13 0.94…
