… be scaled down compared to MySQL 5.5 default values. Such as performance_schema auto scales to count up to 445 tables… differently now. The query_cache_type is now off by default with default size of 1MB while in MySQL 5.5 and before it was “ON” by default with query cache size of 0 which makes it disabled. I wish query_cache…
Post: MySQL Wish for 2013 - Better Memory Accounting
December 31, 2012 By 6 Comments
… we’re still walking blind with MySQL is resource usage – specifically Memory Usage. I can’t count how many time I had… allocated for Innodb Buffer Pool or Query Cache but amount of memory used for example for Table Cache can be only guessed. Not… addition to monitoring it which I’m sure hosting providers, MySQL as a Service vendors will especially appreciate as currently they…
Post: Flexviews - part 3 - improving query performance using materialized views
April 4, 2011 By 20 Comments
… is similar to storing the results of a query in Memcache. When the cache “expires”, the contents must be fully recalculated.. An… the query would take significantly longer (40+ minutes): mysql> SELECT -> customer_id as `customer_id`, -> SUM(price * quantity) as `total_price`, -> COUNT(*) as…
Post: Using flow control functions for performance monitoring queries
May 24, 2008 By 12 Comments
…or CASE used in MySQL Queries as they are often abused used to create queries which are poorly …mysql> select count(*) cnt, avg(wtime) avw, sum(if(wtime>0.3,1,0))/count(*) soso, sum(if(wtime>1,1,0))/count… which will use content from the cache (or be served from the cache all together). Bots however …
Post: COUNT(*) vs COUNT(col)
April 10, 2007 By 45 Comments
… MySQL has cached number of rows in this table. This is why it is able to instantly answer COUNT(*) and COUNT(val2) queries, but not COUNT… more queries: mysql> select count(*) from fact where i explain select count(*) from fact where i select count(val) from fact where i explain select count…
Post: MySQL Prepared Statements
August 2, 2006 By 21 Comments
… statements. In newer versions you can also use prepared_stmt_count variable to track number of open statements diretly. You can… great future ahead for MySQL Prepared statements – many of the problems such as extra round trip, non working query cache or non working prepared statements for some of the statements as well as most of other problems can be fixed. MySQL…
Post: Impact of the sort buffer size in MySQL
October 25, 2010 By 8 Comments
…/bash NUMROW=100000 COUNT=0 while [ "$NUMROW" -gt "$COUNT" ] do UUID=`uuidgen` mysql test -e “insert into sorttest value (‘$UUID’);” let “COUNT=COUNT+1″ done… to perform 12 queries like ‘select * from sorttest order by data limit 78000,1′ with, of course, the query cache disabled. I also…
Post: How expensive is a WHERE clause in MySQL?
October 31, 2008 By 17 Comments
… much overhead does a trivial WHERE clause add to a MySQL query? To find out, I set my InnoDB buffer pool… a table scan with no WHERE clause takes: mysql> select sql_no_cache count(*) from t; +———-+ | count(*) | +———-+ | 8388608 | +———-+ 1 row in set (5.23… WHERE clause: mysql> select count(*) from t where a = current_date; (Pop quiz: do I need SQL_NO_CACHE on this query?) The average…
Post: MySQL Server Memory Usage
May 17, 2006 By 21 Comments
…, query_cache_size. If you’re using MyISAM seriously you can also add the size of Operation System cache you would like MySQL to… values to run some queries. There can be mutiple key caches. Multiple key caches can be created to accomodate query executions Query Parsing and optimization… memory consumption just 30MB not 3000MB as you might have counted.
Post: MySQL Server Variables - SQL layer or Storage Engine specific.
June 8, 2006 By 14 Comments
…and HEAP for in memory tables. So first MySQL versions did not bother naming variables with storage… MyISAM and Innodb. Some SQL level items as Query Cache also could use this feature. low_priority_updates…for MyISAM and MEMORY tables. max_write_lock_count Variable which may save you from read …
