…mysql –mysql-host=localhost –mysql-table-engine=innodb –mysql-db=test –oltp-table-name=md_cache_test_small –oltp-table-size=1100000 –mysql-user=msandbox –mysql-password=msandbox –mysql-socket=/tmp/mysql… innodb_old_blocks_time variable to some value will prevent important data to be pushed …
Post: MySQL caching methods and tips
April 4, 2011 By 5 Comments
… in the database. Eventually the cache contents expire. When this happens, the application attempts to get the cached value and fails. At this time, the application must compute the results, then place them into the cache. Additionally, memory… the MySQL query cache, since the rate of invalidation can not be controlled, and multiple cache entries may be invalidated by a single table…
Post: How innodb_open_files affects performance
November 18, 2009 By 4 Comments
… when table is open – open table is purely logical state and appropriate .ibd file may be open or closed. Furthermore besides MySQL table_cache Innodb maintains its own (called data dictionary) which keeps all tables ever accessed since table start – there is… many (67) files stay open while the maximum InnoDB: allowed value would be 64. InnoDB: You may need to raise the…
Post: Cache Performance Comparison
August 9, 2006 By 74 Comments
… was selecting from MySQL table. Query vas very simple – lookup by primary cache, so this is kind of peak performance MySQL can provide. Of… CHARSET=utf8; INSERT INTO `test` VALUES (‘test’,'MyTestString’); 3) You need all caches to work, ie query cache enabled, memcached running, apc enabled…
Post: Write contentions on the query cache
September 5, 2012 By 5 Comments
…000004 | | checking permissions | 0.000004 | | Opening tables | 0.000021 | | System lock | 0.000009 | |…MySQL need so many accesses to the query cache lock? The answer is in the way the query cache…value of query_cache_min_res_unit. But this could increase the fragmentation of the cache…
Post: MySQL automatic data truncation can backfire
January 9, 2007 By 10 Comments
… case today. There is set of cache tables which cache certain content in MyISAM tables and queries for these tables such as: select data from… expected. What was unexpected however is how MySQL executed select queries if key value would be out of signed int range. Instead… we have value outside of rage of values which can possibly be in the database we have MySQL truncating this value to 2^32…
Post: Review of MySQL 5.6 Defaults Changes
October 8, 2012 By 10 Comments
… posted the great summary of defaults changes in MySQL 5.6 compared to MySQL 5.5 In general there are a lot… for value 0 which would mean limit disabled. Very few users actually need DOS prevention this variable strives to provide as MySQL… 5000 tables. There is also little “savings” in keeping this value low – 64K or even more would be good default. query_cache_size…
Post: Is there room for more MySQL IO Optimization?
January 3, 2013 By 11 Comments
… which need to be cached — system files, binary log, FRM files, MySQL MyISAM system tables etc. Starting MySQL 5.5 MySQL uses asynchronous IO which…. This could be confirmed with number of innodb_data_reads (values over 100 second intervals) | Innodb_data_reads | 54561 | | Innodb_data…
Post: 10+ Ways to Crash or Overload MySQL
November 13, 2007 By 20 Comments
… them using mysql_stmt_send_long_data call – Server buffers such data until you have executed prepared statement. Innodb Table Cache Leak – Innodb…-8K per table though complex tables can require larger sizes, so this is mainly the problem for smaller servers. Table Cache Merge Tables – Table Cache is allocated… all cases. Server Side Variables – Each server side can hold value up to max_allowed_packet in size (1M by default…
Post: New SpecJAppServer results at MySQL and Sun.
November 12, 2008 By Leave a Comment
…table_cache = 6000 read_rnd_buffer_size = 2M sort_buffer_size = 32k thread_cache = 16 query_cache_size = 0M thread_concurrency = 8 max_heap_table_size=200M log-output = FILE log-slow-queries=/tmp/mysql…
