… to the MySQL configuration. MySQL Configuration I …= 1 ## Disabling query cache query_cache_size = 0 query_cache_type … KEY (`transactionid`), KEY `marketsegment` (`price`,`customerid`), KEY `registersegment` (`cashregisterid`,`price`,`customerid`), KEY `pdc…index which has 3 secondary indexes. …
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 2 Comments
…this benchmark, I have used TPC-H Query #3 and ran it on TPC-H dataset (InnoDB …=’join_cache_incremental=on’ optimizer_switch=’join_cache_hashed=on’ optimizer_switch=’join_cache_bka=on’ join_cache_level=8…Hash Join and Key-ordered Scan disabled) You can see that the lowest query time is for MySQL 5.6…
Post: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact
March 12, 2012 By 12 Comments
… in preparation for the talk comparing the optimizer enhancements in MySQL 5.6 and MariaDB 5.5. We are taking a… optimizer enhancement Index Condition Pushdown (ICP). Its available in both MySQL 5.6 and MariaDB 5.5 Now let’s take… for filtering records. For example, suppose you have a key defined as: KEY `i_l_partkey` (`l_partkey`,`l_quantity`,`l_shipmode…
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
…MySQL 5.5 and ~5 hours in case of MySQL 5.6 and MariaDB 5.5. Note that query cache is disabled…and o_orderdate < date_add( '1993-08-01' ,interval '3‘ month) and l_returnflag = ‘R’ and c_nationkey =…. Good Plan id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE orders …
Post: Crashes while using MyISAM with multiple key caches
July 18, 2009 By Leave a Comment
…of years I have ran into random MySQL crashes in production when multiple key caches were used. Unfortunately this never … early 2006, this is 3.5 years ago when I was still with MySQL living in Seattle area…. … and crashing I’d like to see them disabled as run time and enabled with –enable-buggy-…
Post: MyISAM Scalability and Innodb, Falcon Benchmarks
October 12, 2007 By 10 Comments
… in MySQL 4.1 As Monty explained us in MySQL 4.1 the change to key cache locking ….3.4.so pthread_mutex_unlock 237062 3.2171 mysqld key_cache_read 215254 2.9211 mysqld find_key_… confirmation that key_cache is a problem is benchmark run with disabled key_cache (=0). Results for MyISAM with key_buffer_…
Post: Aligning IO on a hard disk RAID – the Benchmarks
June 9, 2011 By 11 Comments
… different MySQL configurations, I didn’t try out different MySQL …cache_size = 16M max_heap_table_size = 64M thread_cache_size = 32 query_cache_size = 0 tmp_table_size = 64M key…cache: The second is with WB cache: I couldn’t figure out what exactly happened with setting #3 when WB cache was disabled…
Post: To pack or not to pack - MyISAM Key compression
May 13, 2006 By 3 Comments
…MySQL will need to scan keyblock from the start uncompressing keys to find matching key…KEYS=0|1|DEFAULT. Value 0 disables compression for all keys. Value 1 forces it for all keys. Value DEFAULT will use key… index scan PACK_KEYS=DEFAULT Q1: 3.50s Q2: 0…. to have keys packed so cache hit is better…
Post: Using CHAR keys for joins, how much is the overhead ?
June 18, 2007 By 18 Comments
…, KEY `i` (`i`), KEY `j` (`j`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 mysql>… row data from OS Cache. So what if we…keys – uuid, sha1/md5 based etc. Latin1 encoding indeed gave significant improvement to 3…key compression is not disabled Joining on Shorter CHAR keys is significantly faster than Long keys…
Comment: Why MySQL could be slow with large tables ?
November 29, 2006 By 139 Comments
…MySQL 4.1.8 One could could call it trivial fast task, unfortunately I had unique key on varchar(128) as part of the schema. The load took some 3…. The problem is – unique keys are always rebuilt using key_cache, which means we’re… with ALTER TABLE DISABLE KEYS as it does not affect unique keys…
