… limit on amount of queries system can ran, for example if we have query which requires 1 CPU second and 1 IO to execute and… at procfs for MySQL process: root@ubuntu:/var/log/mysql# cat /proc/19018/stat 19018 (mysqld) S 1 19018 19018 0 -1 4202752 198731… of MySQL process in 1/100 of the second. (This is pretty idle test system). So 347 and 5303 correspond to 3.47 seconds…
Post: Slow Query Log analyzes tools
September 6, 2006 By 84 Comments
… to be generated less in 1 second, which issues many queries during generation. Second – if you enable option to log queries which do not use… be slowest queries – query taking 10ms and run 1.000 times per second puts more load on server than 10 seconds query running once per second. We… slow query log first to fix worse queries and then come to find more optimization candidates. So “tail -f mysql-slow.log | mysql_slow…
Post: Flexviews - part 3 - improving query performance using materialized views
April 4, 2011 By 20 Comments
…| customer_id | total_price | total_lines | +——+————-+————-+————-+ | 1 | 689 | 770793 | 3811 | | 2 | 6543 | 754138 | 3740…mysql> call flexviews.refresh( -> flexviews.get_id(‘demo’, -> ‘dashboard_customer_sales’),’BOTH’,NULL); Query OK, 0 rows affected (7.01 sec) The second…
Post: The Optimization That (Often) Isn't: Index Merge Intersection
December 14, 2012 By 6 Comments
… user Note the low V/M here. This query *always* takes about 3.3 seconds to execute. Yet it appears so benign… > 2938575 AND parent_id=0 AND status=1 ORDER BY user_id LIMIT 1; mysql> EXPLAIN SELECT user_id FROM users USE INDEX…: Using where 1 row in set (0.00 sec) Making this change turns what was a 3 second query into a millisecond query. It…
Post: Using flow control functions for performance monitoring queries
May 24, 2008 By 12 Comments
… 95% 0.3 second response time we would fail, same as 99% requests served within 1 second. Here is another example: mysql> select count(*) cnt, avg(wtime) avw, sum(if(wtime>0.3,1,0))/count… this query we’re looking at response time for different pages and we can find “search” page responds within 1 second in about…
Post: Percona XtraDB Cluster reference architecture with HaProxy
June 20, 2012 By 63 Comments
… the output of hostname command. Based on that, for the second node, the differences in the configuration should be the following… user. mysql> grant process on *.* to ‘clustercheckuser’@'localhost’ identified by ‘clustercheckpassword!’; Query OK, 0 rows affected (0.00 sec) mysql> flush privileges; Query OK… it. mysql> create database sbtest; Query OK, 1 row affected (0.01 sec) mysql> grant all on sbtest.* to ‘sbtest’@'%’ identified by ‘sbpass’; Query OK…
Post: Implications of Metadata Locking Changes in MySQL 5.5
February 1, 2013 By 9 Comments
…query: session10 > show profile for query 11; +——————————–+———-+ | Status | Duration | +——————————–+———-+ | starting | 0.000018 | | Waiting for query cache lock | 0.000003 | | checking query cache for query… When using MySQL 5.1 the second CREATE …
Post: Air traffic queries in LucidDB
October 26, 2009 By 20 Comments
… of queries and times for all systems. – Lame query “count start” LucidDB: SELECT count(*) FROM otp.”ontime”; 1 row selected (55.165 seconds) Both… “Origin” ORDER BY c DESC; LucidDB: 27.664 seconds InfoBright: 7.29 sec MonetDB: 1.7 sec – Q4: SELECT “Carrier”, count(*) FROM… external data (there is even access to MySQL via JDBC driver) just in SQL queries (compare with single LOAD DATA statement in…
Post: Computing 95 percentile in MySQL
November 28, 2008 By 5 Comments
…queries and have average response time of 1 second. What does it mean ? Really nothing – may be one page view was 10000 seconds…
Post: How to use tcpdump on very busy hosts
April 18, 2011 By 17 Comments
…-query-digest –filter ‘$event->{time} && $event->{time} > 1‘” to exclude all queries which it believes took longer than the servers long-query-time of 1 second… responses from mysqld) #1 has an obvious flaw — if your long-query-time is 1 second, and mkqd believes that a query which actually took…
