One performance gotcha with MEMORY tables you might know about comes from the fact it is the only MySQL storage engine which defaults to… comes from the fact MEMORY tables use HASH indexes by default. I’ve created rather similar test table: CREATE TABLE `test` ( `id` int(11… becomes a huge problem when delete (or key update) operation needs to be performed. In this case the value to be deleted…
Post: Should we give a MySQL Query Cache a second chance ?
April 10, 2011 By 63 Comments
…MySQL Query Cache so it once again is appealing to improve performance for MySQL Applications ? Make it Lockless Can we re-implement MySQL… MySQL Query Cache now stores queries in local memory, which is great however memory …as re-populating the cache after table update invalidated a lot of items. …
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
…Performance_schema_rwlock_classes_lostblogpercona.commanual Performance_schema_rwlock_instances_lostblogpercona.commanual Performance_schema_table_handles_lostblogpercona.commanual Performance_schema_table_instances_lostblogpercona.commanual Performance…memory…
Post: MySQL Query Cache
July 27, 2006 By 69 Comments
… helpful for MySQL Performance optimization tasks but there are number of things you need to know. First let me clarify what MySQL Query… of usable memory – Queries are constantly being invalidated from query cache by table updates, this means number of queries in cache and memory used…
Post: 10+ Ways to Crash or Overload MySQL
November 13, 2007 By 20 Comments
…memory. Memory Tables If you can create memory tables you can create any number of them and even though there is max_heap_table… and allowing other users to do updates – Innodb only can purge data after … can reduce system performance beyond being functional. General Overload As MySQL does not have …
Post: MySQL Server Variables - SQL layer or Storage Engine specific.
June 8, 2006 By 14 Comments
… You still have some tables in MyISAM format in “mysql” database. Plus HEAP/MEMORY tables are used for temporary tables which may become MyISAM … tables have “holes” (deleted rows) which can help significantly. delay_key_write Delay updating indexes for MyISAM tables. Good for performance but tables …
Post: Shard-Query EC2 images available
May 11, 2011 By 2 Comments
… to memory requirements, the InnoDB versions are only available on 64 bit instances. MySQL will … database About the cluster For best performance, there should be an even data …the flight. Some data hand updated.’; — – Table structure for table `ontime_fact` — CREATE TABLE `ontime_fact` ( `date_id`…
Post: The Doom of Multiple Storage Engines
May 8, 2010 By 22 Comments
… in truly hot way without nasty “FLUSH TABLE WITH READLOCK” and hoping nobody is touching “mysql” database any more. Single Storage Engine… do not log the changes, hence being faster for update operations. One could also lock them in memory to ensure predictable in memory performance.
Post: Performance Schema tables stats
May 2, 2011 By 2 Comments
…Performance Schema was mainly in memory workload and against single tables. Now after adding multi-tables…performance schema tables. 1. table_io_waits_summary_by_table mysql> select * from table_io_waits_summary_by_table…
Post: Modeling MySQL Capacity by Measuring Resource Consumptions
March 8, 2011 By 1 Comment
…resources such as network IO or memory to store temporary table, but let us ignore them…or benchmarked) system performance to provide our estimates. If we’re running Innodb with MySQL we can… ROWS_FETCHED: 485139 ROWS_UPDATED: 0 TABLE_ROWS_READ: 610954 SELECT_COMMANDS: 181243 UPDATE_COMMANDS: 0 OTHER_…
