…query as Q{FLIGHT_NUMBER}.{QUERY…mysql socket=/var/lib/mysql/mysql.sock user=justin innodb_buffer_pool_size=64G innodb_log_file_size=4G innodb_file_per_table innodb…innodb_file_format=barracuda innodb_log_buffer_size=32M innodb_buffer_pool_instances=16 metadata_locks_hash_instances=32 table_open_cache…
Post: MySQL and Percona Server in LinkBench benchmark
May 8, 2013 By 18 Comments
…-definition-cache=1000 query_cache_size=0 query_cache_type=0 performance_schema=0 #56only loose-innodb_flush_neighbors=0 loose-metadata_locks_hash_instances=256 innodb_buffer_pool_instances=16 # MySQL…
Post: Return of the Query Cache, win a Percona Live ticket
August 26, 2011 By 4 Comments
…MySQL query cache…query cache is a bottleneck. Oracle released a similar change pretty soon afterwards, but theirs changed the wording to “Waiting on query cache lock…
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
…innodb_flush_methodblogpercona.commanual innodb_force_recoveryblogpercona.commanual Innodb_have_atomic_builtinsblogpercona.commanual innodb_io_capacityblogpercona.commanual innodb_lock_wait_timeoutblogpercona.commanual innodb_locks_unsafe_for_binlogblogpercona.commanual innodb…
Post: Implications of Metadata Locking Changes in MySQL 5.5
February 1, 2013 By 9 Comments
… bug: http://bugs.mysql.com/bug.php?id=67647 Metadata locking behaves differently for queries that are serviced from the Query Cache, let’s see what happens in that case. Metadata Locking and Query Cache How does metadata locking behave with query_cache…_increment primary key) ENGINE=InnoDB; Query OK, 0 rows affected (0.09 sec) session1 > insert into t1 values(null); Query OK, 1 row…
Post: Identifying the load with the help of pt-query-digest and Percona Server
December 29, 2011 By 3 Comments
…attack the right query in the right way. But vanilla MySQL does have its …information about the queries‘ execution plan (which includes things like whether Query cache was used …InnoDB_IO_r_bytes: 16121856 InnoDB_IO_r_wait: 0.001414 # InnoDB_rec_lock_wait: 0.000000 InnoDB_queue_wait: 0.000000 # InnoDB…
Post: SHOW INNODB STATUS walk through
July 17, 2006 By 40 Comments
… declared inside InnoDB 500 mysql tables in use 1, locked 1 3 lock struct(s), heap size 320 MySQL thread id 30899, query id 100627 localhost… inside InnoDB 500 mysql tables in use 1, locked 1 3 lock struct(s), heap size 368, undo log entries 1 MySQL thread id 9697561, query… or less expensive. If innodb_flush_logs_at_trx_commit=2 log writes are done to OS cache, and being sequential writes…
Post: Choosing innodb_buffer_pool_size
November 3, 2007 By 39 Comments
… include MySQL buffers – query cache, key_buffer, mysql threads, temporary tables, per thread sort buffer which can be allocated. There are also things like innodb… have other settings to play with. Finally you can try locking MySQL in memory by using –memlock – just be careful as in…
Post: MySQL Server Variables - SQL layer or Storage Engine specific.
June 8, 2006 By 14 Comments
…and HEAP for in memory tables. So first MySQL versions did not bother naming variables with storage…pages in general. Currently MyISAM and Innodb. Some SQL level items as Query Cache also could use this feature…. It works for storage engines with table locks or if LOCK TABLES are used. So in practice …
Post: Improved InnoDB fast index creation
November 6, 2011 By 4 Comments
…=InnoDB; Query OK, 0 rows affected (0.00 sec) mysql> INSERT INTO t(c) VALUES (RAND()); Query OK, 1 row affected (0.00 sec) mysql… | 0.161528 | | end | 0.000007 | | Waiting for query cache lock | 0.000002 | | end | 0.000007 | | query end | 0.000003 | | closing tables | 0.000008 | | freeing…
