Also, On setting table_cache online. Most time you can decrease it online, however increasing it may cause issues because MySQL will not change the number of open files limit in this case.
Post: Implications of Metadata Locking Changes in MySQL 5.5
February 1, 2013 By 9 Comments
…Table Cache No matter if a connection accesses a table that is already in the Table Cache, any query to a table… like ‘Open%tables‘; +—————+——-+ | Variable_name | Value | +—————+——-+ | Open_tables | 30 | | Opened_tables | 0 | +—————+——-+ 2 rows in set (0.00 …
Post: Aligning IO on a hard disk RAID – the Benchmarks
June 9, 2011 By 13 Comments
… table_cache = 2048 max_allowed_packet = 16M binlog_cache_size = 16M max_heap_table_size = 64M thread_cache_size = 32 query_cache_size = 0 tmp_table_size… controller cache set to Write-Through and then to Write-Back. 1. Baseline – misalignment on the partition table, no LVM and no alignment settings… controller set to WT cache: The second is with WB cache: I couldn’t figure out what exactly happened with setting #3 when WB cache…
Post: Using Multiple Key Caches for MyISAM Scalability
November 26, 2008 By 8 Comments
…_schema,”_”,table_name,”;”) | +——————————————————————————————-+ | CACHE INDEX test.a IN test_a; | | CACHE INDEX test.b IN test_b; | +——————————————————————————————-+ 2 rows in set…
Post: A case for MariaDB's Hash Joins
May 31, 2012 By Leave a Comment
…_method=O_DIRECT query_cache_size=0 query_cache_type=0 MySQL 5.6.5 Configuration innodb_file_per_table=1 innodb_file… limited set of rows in the supplier table (left operand) to join to the lineitem table (right operand) so scanning the lineitem table (BNLH… from supplier table (left operand). Test Case D – Join a large data set (>1M rows) from one table with a large table The SQL…
Post: What to tune in MySQL Server after installation
September 29, 2006 By 118 Comments
… use MyISAM tables exclusively. Right size depends on amount of indexes, data size and workload – remember MyISAM uses OS cache to cache the… Very important for write intensive workloads especially for large data sets. Larger sizes offer better performance but increase recovery times so… with full OS crash. table_cache – Opening tables can be expensive. For example MyISAM tables mark MYI header to mark table as currently in use…
Post: How much overhead is caused by on disk temporary tables
August 16, 2007 By 26 Comments
… we have mysql> set max_heap_table_size=1000000000; Query OK, 0 rows affected (0.00 sec) mysql> set tmp_table_size=1000000000; Query… faster than MyISAM on disk table even in case temporary table is small enough to fit in OS cache and key_buffer. My next intension was to test placing table on…
Post: Enum Fields VS Varchar VS Int + Joined table: What is Faster?
January 24, 2008 By 30 Comments
… words about our data set we’ve used for this benchmark. We have 4 tables: 1) Table with ENUM: CREATE TABLE cities_enum ( id…(mean): 0.082196 2) Results for VARCHAR: select SQL_NO_CACHE city from cities_varchar WHERE state=’Minnesota’ limit 10000,5… an application and you need to have some table field with a small set of possible values, I’d still suggest you…
Post: Write contentions on the query cache
September 5, 2012 By 5 Comments
… | 0.162272 | | end | 0.000008 | | query end | 0.000004 | | closing tables | 0.000032 | | freeing items | 0.000035 | | logging slow query | …the query cache both when checking if a result is in the cache and when writing a result set into the cache. When …
Post: Why you should ignore MySQL's key cache hit ratio
February 28, 2010 By 28 Comments
…: you should care about the key cache hit ratio. Bad advice #2: you should set your key_buffer_size according to this… sound like on a machine that’s dedicated to MyISAM tables. Consider your mixture of storage engines (some InnoDB, some MyISAM… are more expensive than hits, then you care about cache misses, not cache hits. A Key_read is not guaranteed to be…
