… separate key cache for all actively accessed tables (assuming there are only few of them), allocating key_cache proportional to their size and load, but no more than the index size (assuming table sizes are relatively static) To get accurate information about table…
Post: Why MySQL could be slow with large tables ?
June 9, 2006 By 141 Comments
… be recepie to disaster. The problem is not the data size – normalized data normally becomes smaller, but dramatically increased number of… is no random IO needed to populate the caches. With proper application architecture and table design you can build applications operating with…
Post: How is join_buffer_size allocated?
July 5, 2010 By 9 Comments
… 14184 join_init_cache(THD *thd,JOIN_TAB *tables,uint table_count) 14185 { … snip … 14268 cache->length=length+blobs*sizeof(char*); 14269 cache->blobs=blobs; 14270 *blob_ptr=0; /* End sequentel */ 14271 size…
Post: How much overhead is caused by on disk temporary tables
August 16, 2007 By 26 Comments
… overhead do on disk temporary tables cause compared to MEMORY tables. To have things comparable I used medium size table and types which can… faster than MyISAM on disk table even in case temporary table is small enough to fit in OS cache and key_buffer. My… the summary: key_buffer_size is important for MyISAM temporary tables performance to avoid OS writes OS Write cache is not as efficient…
Post: The tool I've been waiting for years
March 18, 2008 By 10 Comments
… easily add amount of cached data per table for MyISAM and Archive tables to INFORMATION_SCHEMA (or other system tables) which would be really… engines which cache everything in their own cache memory – Innodb, Falcon, Maria. Having information about how large portion of table is cached would allow… logs]# perl /tmp/fincore –justsummarize performance_log_080319.MYD page size: 4096 bytes 28415 pages, 111.0 Mbytes in core for…
Post: Falcon Storage Engine Design Review
January 12, 2007 By 19 Comments
… data cached in the same cache size. You might need only single row from 8-16K page but page cache requires you to cache whole… fixed cache size, but size range This is minor one. Unlike you would typically see Falcon uses minimum and maximum sizes for row cache instead… much performance it will give. As “SHOW TABLE STATUS” is not showing index sized in the release we tested it is hard…
Comment: What to tune in MySQL Server after installation
June 22, 2009 By 118 Comments
…_cache_size=8M table_cache=510 tmp_table_size=13M thread_cache_size=12 myisam_max_sort_file_size=100G myisam_max_extra_sort_file_size=100G myisam_sort_buffer_size=8M key_buffer_size…
Post: Enum Fields VS Varchar VS Int + Joined table: What is Faster?
January 24, 2008 By 30 Comments
…’ve used for this benchmark. We have 4 tables: 1) Table with ENUM: CREATE TABLE cities_enum ( id int(10) unsigned NOT NULL… short in this table so changing state from VARCHAR to ENUM or TINYINT affects row size significantly. In many cases size difference will…(mean): 0.082196 2) Results for VARCHAR: select SQL_NO_CACHE city from cities_varchar WHERE state=’Minnesota’ limit 10000,5…
Post: ALTER TABLE: Creating Index by Sort and Buffer Pool Size
June 27, 2012 By 4 Comments
… with different buffer pool sizes. Results are pretty interesting. I used modified Sysbench table for these tests because original table as initially created…. I used 10mil row table which would look as following in terms of data and index size: mysql> show table status like “sbtest… OS Cache It is not clearly seen from the graphs but with index built by sort enabled the performance of ALTER TABLE…
Post: Identifying the load with the help of pt-query-digest and Percona Server
December 29, 2011 By 3 Comments
… includes things like whether Query cache was used or not, if Filesort was used, whether tmp table was created in memory or…_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0 # InnoDB_trx_id: F00 # QC_Hit: No Full_scan: Yes Full_join: No Tmp_table: No Tmp_table…
