… and things like it. Besides being just slow accessing information_schema can often impact server …00 sec) mysql> select count(*),sum(data_length) from information_schema.tables; +———-+——————+ | count(*) | sum(data_length) | +———-+——————+ | 130 | 2856365892 |…
Post: Optimizing slow web pages with mk-query-digest
April 7, 2011 By 14 Comments
…_query_time=0 and all the eXtra benefits from Percona slow query log patch), I knew some particular pages were taking…-query-digest –group-by=Thread_id –order-by=Query_time:sum in > out Spot on, the session I needed to focus… page response time. I can also convert that into a slow query log that lists all the queries that were executed…
Post: MySQL 5.6 vs MySQL 5.5 and the Star Schema Benchmark
March 11, 2013 By 7 Comments
… than data and the IO is slow (when the FS cache is cold, IO to slow IO subsystem happens) Repeated runs test…_name, index_name, count(*) pages, sum(is_old=’YES’) old, count(*) – sum(is_old=’YES’) hot, sum(number_records) records from information_schema…_name, index_name, count(*) pages, sum(is_old=’YES’) old, count(*) – sum(is_old=’YES’) hot, sum(number_records) records from information_schema…
Post: Color code your performance numbers
June 2, 2010 By 4 Comments
… and anything over that will be considered unacceptable: mysql> select sum(wtime=1.0 and wtime=3.)/count(*)*100 red_pct… the average response time for our good requests. This eliminates slow outliers which we just count by their number and allows…
Post: Identifying the load with the help of pt-query-digest and Percona Server
December 29, 2011 By 3 Comments
…(*) from auto_inc; Compare that to Percona Server with log_slow_verbosity=full: # Time: 111229 3:11:26 # User@Host: msandbox… memory consuming, so ideally you would want to download the “slow query log” to another machine and run it there. Analyzing… queries and shows the top queries with largest impact – longest sum of run time which typically (not always) shows queries causing…
Post: How To Test Your Upgrades - pt-upgrade
December 29, 2011 By 7 Comments
… pt-table-checksum. Capture your production queries, we recommend either slow query log (use long_query_time = 0) or tcpdump (other… as source of queries # –print simply returns the queries in slow log format and –sample # limits this to 100 queries only….1:55170 # Errors 0 0 # Warnings 0 0 # Query_time # sum 442ms 506ms # min 391us 480us # max 4ms 2ms # avg 867us…
Post: How to convert MySQL's SHOW PROFILES into a real profile
February 20, 2012 By 12 Comments
…query_id := 1; SELECT STATE, SUM(DURATION) AS Total_R, ROUND( 100 * SUM(DURATION) / (SELECT SUM(DURATION) FROM INFORMATION_SCHEMA…| end | 0.000002 | 0.00 | 1 | 0.0000020000 | | logging slow query | 0.000001 | 0.00 | 1 | 0.0000010000 | +———————-+———-+——-+——-+————–+…
Post: Speeding up GROUP BY if you want aproximate results
March 7, 2008 By 24 Comments
… had to kill it in the end. The reason for slowness was of course huge temporary table was required (there were… in on disk temporary table which as we know quite slow. Of course it would be possible to allocate more memory…_log_080306 group by crc32(page) having cnt>3) pv -> ; +———-+ | sum(cnt) | +———-+ | 1127031 | +———-+ 1 row in set (31.22 sec) As…
Post: Troubleshooting MySQL Memory Usage
March 21, 2012 By 5 Comments
…information_schema.global_temporary_tables where engine=’memory’; +——————————-+ | sum(data_length+index_length) | +——————————-+ | 126984 | +——————————-+ 1 row … detect memory leaks like valgrind are too slow to run in production. So the best…
Post: Using LoadAvg for Performance Optimization
December 4, 2006 By 5 Comments
… to VMSTAT output – it is something like moving average of sum of “r” and “b” columns from VMSTAT. Obviously minimum value… how much load it can handle before it becomes to slow down or being completely unable to handle the load, and… are stubborn users which would not go away even with slow responding site until their browsers timeout, which is as good…
