…MySQL manual, especially the option and variable reference table. But just as frequently, I want to look up blog posts on variables….commanual timestampblogpercona.commanual tmp_table_sizeblogpercona.commanual tmpdirblogpercona…. unique_checksblogpercona.commanual updatable_views_with_limitblogpercona…
Post: Troubleshooting MySQL Memory Usage
March 21, 2012 By 5 Comments
…Tables MEMORY tables can take memory. There are implicit MEMORY tables which are allocated for query execution, which size can be controlled by tmp_table_size…tables you can create both as permanent and temporary. There is a max_heap_table_size variable which allows you to limit size of MEMORY tables…
Post: MySQL Server Variables - SQL layer or Storage Engine specific.
June 8, 2006 By 14 Comments
…tables in MyISAM format in “mysql” database. Plus HEAP/MEMORY tables are used for temporary tables…tmp_table_size This variable specifies maximum size implicit temporary table (created during query execution automatically) will grow as MEMORY table…
Post: When Does InnoDB Update Table Statistics? (And When It Can Bite)
October 6, 2011 By 7 Comments
…Tmp_tables: 1 Tmp_disk_tables: 0 Tmp_table_sizes: 0 SET timestamp=1316767697; show table status from `db1`; As you can see db1 has about 294 tables … to update all the tables‘ statistics. Luckily, this can be controlled with Percona Server with the variable innodb_stats_auto_update (from MySQL …
Post: Can we improve MySQL variable handling ?
February 18, 2012 By 8 Comments
…tmp-table-size 16777216 tmpdir /tmp transaction-alloc-block-size 8192 transaction-isolation REPEATABLE-READ transaction-prealloc-size 4096 updatable…variable global value Right now I can select: mysql> select @@global.sort_buffer_size; +—————————+ | @@global.sort_buffer_size | …
Post: Identifying the load with the help of pt-query-digest and Percona Server
December 29, 2011 By 3 Comments
….4% 89 0.0022 1.00 0.01 UPDATE wp_options # 7 0x7AEDF19FDD3A33F1 0.1381 1.7%… data about the underlying tables involved and the query execution plan used by MySQL. The end result might…The tmp table size needed per query is 270.35k, which is not much considering the fact that tmp_tbl_size variable is …
Comment: Database problems in MySQL/PHP Applications
November 6, 2006 By 13 Comments
…table_cache = 3072 open_files_limit = 9216 tmp_table_size=1000M sort_buffer_size = 100M read_buffer_size = 100M read_rnd_buffer_size = 100M myisam_sort_buffer_size…
Comment: Why MySQL could be slow with large tables ?
June 20, 2007 By 141 Comments
… max_heap_table_size=50M tmp_table_size=64M max_allowed_packet=16M table_cache=1800 record_buffer=10M sort_buffer_size=24M read_buffer_size=9M max… set-variable=max_connections=1500 log_slow_queries=/var/log/mysql-slow.log sql-mode=TRADITIONAL concurrent_insert=2 low_priority_updates=1…
Post: Improved InnoDB fast index creation
November 6, 2011 By 4 Comments
…table | 0.002255 | | After create | 0.000050 | | copy to tmp table | 61.816063 | | rename result table…table rebuilds without affecting the table size. mysql> SET expand_fast_index_creation=ON; Query OK, 0 rows affected (0.00 sec) mysql> ALTER TABLE…variable…
Comment: MySQL Server Memory Usage
September 1, 2006 By 21 Comments
…tables‘, ’102′ ‘Com_update‘, ’15146253′ ‘Com_update_multi’, ’0′ ‘Connections’, ’263413′ ‘Created_tmp_disk_tables‘, ’0′ ‘Created_tmp_files’, ’4001′ ‘Created_tmp_tables…
