…] (inclusive). Quorum is computed using weighted sum over group members. Percona XtraDB…TEMPORARY TABLE statement was used, but it will still replicate in case DROP TABLE statement is used on a temporary table…Bug fixed #1130888 (Seppo Jaakola). If MySQL replication threads were started before running…
Post: Troubleshooting MySQL Memory Usage
March 21, 2012 By 5 Comments
… these tables going away if you close connection. In Percona Server you can do better as you can query temporary tables too: mysql> select sum(data_length+index_length) from information_schema.global_temporary_tables where engine=’memory’; +——————————-+ | sum(data_length+index…
Post: How to convert MySQL's SHOW PROFILES into a real profile
February 20, 2012 By 12 Comments
…sample database to demonstrate: mysql> SET profiling=1; mysql> pager cat > /dev/null mysql> SELECT * FROM … := 1; SELECT STATE, SUM(DURATION) AS Total_R, ROUND( 100 * SUM(DURATION) / (SELECT SUM(DURATION) FROM …of the time was spent working with temporary tables. But there’s something still missing: …
Post: Distributed Set Processing with Shard-Query
May 14, 2011 By 27 Comments
…from all the nodes. A single temporary table is used to store the …speaks SQL, but right now only MySQL storage nodes are supported. Amdahl’…SUM(`count(*)`) AS `count(*)`, SUM(`sum(AirTime)`) AS `sum(AirTime)`, SUM(`sum(DepDelay)`) AS `sum(DepDelay)`, SUM(`sum(DepDelay >= 0) flight_delayed`) AS `sum…
Post: Using GROUP BY WITH ROLLUP for Reporting Performance Optimization
September 17, 2007 By 14 Comments
… side (you can store result in temporary table and run sum() and sort query on that table instead if amount of groups is much… | +——+———+ 11 rows in set (29.68 sec) Use of extra temporary table for buffering helps us to get result set we’re… using filesort as group by execution method, not temporary table as ordinary GROUP BY: mysql> explain select grp, count(*) cnt from dt where…
Post: Speeding up GROUP BY if you want aproximate results
March 7, 2008 By 24 Comments
… huge temporary table was required (there were about 5 million of distinct pages visited during that day) which resulted in on disk temporary table which as we know quite slow. Of course it would be possible to allocate more memory to the temporary table or… I could trick MySQL to do group by a hash of the page instead of page itself: mysql> select sum(cnt) from (select…
Post: Choosing innodb_buffer_pool_size
November 3, 2007 By 39 Comments
… Operating System needs you also have MySQL needs – these include MySQL buffers – query cache, key_buffer, mysql threads, temporary tables, per thread sort buffer which… case you have large amount of tables). I could tell you some numbers, for example sum up all your global buffers plus…
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
… following query executed on the InnoDB table: SELECT non_key_column FROM … is: select c_custkey, c_name, sum(l_extendedprice * (1 – l_discount)) as … mentioned above. Counter Name MySQL 5.5 MySQL 5.6 MySQL 5.6 w/ …Using where; Rowid-ordered scan; Using temporary; Using filesort 1 SIMPLE customer eq…
Post: How well does your table fits in innodb buffer pool ?
December 9, 2010 By 12 Comments
… MySQL Server does not provide any information of this type, Percona Server however adds number of tables …FROM (SELECT index_id, COUNT(*) cnt, SUM(dirty = 1) dirty, SUM(hashed = 1) hashed FROM innodb_buffer_… pool rather than using some form of temporary measures. I often check these stats during …
Post: Using LoadAvg for Performance Optimization
December 4, 2006 By 5 Comments
… it is something like moving average of sum of “r” and “b” columns from VMSTAT…single batch job on the server with MySQL, Load Average is likely to be …if there are a lot of locks (table/row level locks) or other limiting …more so load drops. This is however temporary relive only as there are stubborn …
