…test. All OS caches are dropped at this time as well. These set of queries were tested …be good for raw performance, but there are limitations which MyISAM imposes that are difficult to work …=/mnt/mysql56/data basedir=/usr/local/mysql socket=/var/lib/mysql/mysql.sock user=justin innodb_buffer_pool_…
Post: More on MySQL transaction descriptors optimization
April 26, 2013 By Leave a Comment
… optimization in MySQL 5.6 virtually eliminates contention on the mutex protecting the list of transactions, its applicability is quite limited. Namely… innodb_io_capacity = 2000 max_connections=5000 table_open_cache=5000 query_cache_type=OFF performance_schema=0 warmup To warmup server and…
Post: MySQL Query Cache
July 27, 2006 By 70 Comments
…Query Cache Query Cache Status Query Cache Configuration I would just mention couple of points – as protection from one query wiping your all query cache option query_cache_limit was implemented which limits…
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
… MySQL manual, especially the option and…query_alloc_block_sizeblogpercona.commanual query_cache_limitblogpercona.commanual query_cache_min_res_unitblogpercona.commanual query_cache_sizeblogpercona.commanual query_cache_typeblogpercona.commanual query_cache_wlock_invalidateblogpercona.commanual query…
Post: Should we give a MySQL Query Cache a second chance ?
April 10, 2011 By 64 Comments
… as MySQL Query Cache is most useful for complex expensive queries, for which tracking exact rows used from query syntax may be hard. Query Cache Storage Engines MySQL Query Cache now stores queries in local memory, which is great however memory amount might be limited…
Post: MySQL 5.5 and MySQL 5.6 default variable values differences
February 21, 2013 By 8 Comments
…/dist/5.6.10 | | BACK_LOG | 50 | 80 | | OPEN_FILES_LIMIT | 1024 | 5000 | | INNODB_AUTOEXTEND_INCREMENT | 8 | 64 | | MAX_CONNECT_ERRORS… differently now. The query_cache_type is now off by default with default size of 1MB while in MySQL 5.5 and before it was “ON” by default with query cache size of 0 which makes it disabled. I wish query_cache…
Post: Identifying the load with the help of pt-query-digest and Percona Server
December 29, 2011 By 3 Comments
… extra stats such as information about the queries‘ execution plan (which includes things like whether Query cache was used or not, if Filesort… query execution plan used by MySQL. The end result might be that you end up limiting the number of results returned by the query, by using a LIMIT…
Post: Review of MySQL 5.6 Defaults Changes
October 8, 2012 By 10 Comments
… which would mean limit disabled. Very few users actually need DOS prevention this variable strives to provide as MySQL is typically protected by Firewall to begin with. open_files_limit = 5000 strangely enough this is… low – 64K or even more would be good default. query_cache_size = 1M Query cache remains off by default but default size is raised…
Post: MySQL Limitations Part 4: One thread per connection
October 27, 2010 By 14 Comments
… seriously limiting MySQL in core use cases (links: part 1, 2, 3). This post is about the way MySQL … connection to MySQL, because it just requires creating a thread (or taking one from a cache). This is…of things like the lower startup cost for the query). But MySQL isn’t a data warehouse DBMS first …
Post: How MySQL Query Cache works with Transactions
January 29, 2008 By 5 Comments
As MySQL Manual Says Query Cache works with transactions with Innodb tables but it does not … modified in transaction it becomes uncachable by query cache until that transaction is committed. Not only query cache can’t be used inside the… change visibility in different transaction modes. In most cases this limitation should not cause many problems (compared to general coarse table…
