…_write_io_threads = 4 innodb_file_per_table = 1 ## Disabling query cache query_cache_size = 0 query_cache_type = 0 You can see that… then that, I have also disabled query cache to avoid contention caused by query cache on write heavy workload. OK, so that… every partition would hold 100 million rows. Table Structure The table structure of the table with no secondary indexes is as follows: CREATE…
Post: New distribution of random generator for sysbench - Zipf
May 9, 2012 By 3 Comments
… also does not allow effectively test caching solution, each row can be equally put into cache or removed. That’s why there… percentage of rows, which makes this distribution good to test cache, but it is hard to emulate high IO load. That… – a ranking tutorial To see distribution on graphs, I took tables with 1mln rows and run row lookup 1 million times…
Post: Troubleshooting MySQL Memory Usage
March 21, 2012 By 3 Comments
… done Check for Table Cache Related Allocations There are cases when MySQL will allocate a lot of memory for table cache, especially if you’re using large blobs. It is easy to check though. Run “FLUSH TABLES” and see… down. What you might see instead is flushing tables regularly or reducing table cache reduces memory consumption to be withing the reason. Connection…
Comment: Too many connections? No problem!
March 15, 2012 By 28 Comments
… log-queries-not-using-indexes query_cache_type=1 query_cache_size=32M thread_cache_size=30 table_cache=4096 join_buffer_size=6M key…
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 2 Comments
… called the build step. After the hash table has been created, rows from table t2 are read and hash function is applied…-H dataset (InnoDB tables) with a Scale Factor of 2 (InnoDB dataset size ~5G). Note that query cache is disabled during these…’ optimizer_switch=’join_cache_incremental=on’ optimizer_switch=’join_cache_hashed=on’ optimizer_switch=’join_cache_bka=on’ join_cache_level=8 join_buffer…
Post: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact
March 12, 2012 By 12 Comments
I have been working with Peter in preparation for the talk comparing the optimizer enhancements in MySQL 5.6 and MariaDB 5.5. We are taking a look at and benchmarking optimizer enhancements one by one. So in the same way this blog post is aimed at a new optimizer enhancement Index Condition …
Comment: MySQL Query Cache
March 21, 2012 By 69 Comments
… have query_cache_type= DEMAND but still a single query that contains multiple joins with four tables uses “checking query cache” state even though the SQL SELECT statement does not contain SQL_CACHE in the begining. The strange thing is that it only happens for these four tables. Would there be…
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
…. Suppose you have the following query executed on the InnoDB table: SELECT non_key_column FROM tbl WHERE key_column=x… tuple, a point primary key lookup is made against base table, the number of these point primary key lookups will be… MySQL 5.6 and MariaDB 5.5. Note that query cache is disabled during these benchmark runs and that the disks…
Comment: Should you move from MyISAM to Innodb ?
March 9, 2012 By 53 Comments
… overhead as compare to MyIsam. I change 90% of my tables to innodb and currently going into queries and db optimization. Could someone really confirm where should i use Innodb. Big table ie. users? comments? etc… i just compared MyIsam to Innodb… be better once i tuned InnoDb settings? and add query cache? Thanks a lot for all the great info. Regards Jerome
Post: MariaDB 5.3 is released as GA!
March 5, 2012 By 17 Comments
…, faster subqueries, and elimination of useless tables in joins Thread pooling and a segmented key cache for improved scalability That’s really…
