…memory is being used by current MEMORY tables: mysql> select sum(data_length+index_length) from information_schema.tables where engine=’memory‘; …+ 211680) Lock system 318875832 (318747272 + 128560) Recovery system 0 (0 + 0) Threads 425080 (406936 + 18144) Dictionary memory…
Post: How much memory Innodb locks really take ?
July 13, 2006 By 11 Comments
… bits per locked row; Now lets see how much exclusinve locks take: mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> select… consumes exactly same amount of memory: History list length 5 Total number of lock structs in row lock hash table 6092 LIST OF TRANSACTIONS… fraction of memory used on serious systems. Furtermore you would unlikely need or want to lock every row in your table/database which…
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
…MySQL manual, especially the option and variable reference table… shared_memory_base_…table_definition_cacheblogpercona.commanual table_lock_wait_timeoutblogpercona.commanual Table_locks_immediateblogpercona.commanual Table_locks_waitedblogpercona.commanual table_open_cacheblogpercona.commanual table…
Post: Helgrinding MySQL with InnoDB for Synchronisation Errors, Fun and Profit
November 24, 2011 By 2 Comments
…9090== by 0x74E4DF: get_schema_tables_result(JOIN*, enum_schema_table_state) (sql_show.cc:6238)…lock order violation, thus a potential deadlock. The locks in question are LOCK_global_system_variables and LOCK… and using MySQL 5.5 atomic operation primitives with proper memory barriers, so that…
Comment: MySQL Server Memory Usage
September 1, 2006 By 18 Comments
…_dir=/var/log/mysql innodb_log_arch_dir=/var/log/mysql innodb_table_locks=0 innodb_buffer_pool_size=1800M # USE ALL MEMORY AVAILABLE #innodb_log_buffer_size=8M # Lowered from 32M according to MySQL…
Post: PHP Large result sets and summary tables.
July 6, 2007 By 8 Comments
… by memory_limit PHP config variable because that only controls memory which passes via PHP memory management which does not apply to MySQL result… cause things breakage Table Locks – Table locks are not cleared until you fetch whole result set if you’re reading from tables directly (if you…
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 if… engines with table locks or if LOCK TABLES are used. So in practice this option is used for MyISAM and MEMORY tables. max_write_lock_count Variable…
Post: Modeling MySQL Capacity by Measuring Resource Consumptions
March 8, 2011 By 1 Comment
… such as network IO or memory to store temporary table, but let us ignore … a look at procfs for MySQL process: root@ubuntu:/var/log/mysql# cat /proc/19018/stat … ======= ======= ======= ======= ======= ======= # Exec time 8338s 1us 284s 5ms 13ms 298ms 185us # Lock time 71s 0 3ms 39us 54us 28us …
Post: The Doom of Multiple Storage Engines
May 8, 2010 By 22 Comments
… cases in Optimizer – such as in memory nature of MEMORY storage engine, clustering by Primary key …. We could get rid of SQL level table locks and using Innodb internal data dictionary instead … way without nasty “FLUSH TABLE WITH READLOCK” and hoping nobody is touching “mysql” database any more. …
Post: How much memory can MySQL use in the worst case?
February 12, 2009 By 17 Comments
…, stored routines, etc) uses memory to execute; so do temporary tables. The following query creates at least 2 temporary tables: select * from ( select…: mysql> set @a := repeat(‘a’, 1024 * 1024 * 100); Query OK, 0 rows affected (2.34 sec) mysql> select get_lock(@a, 1); +—————–+ | get_lock…
