…_size = 2M sort_buffer_size = 32k thread_cache = 16 query_cache_size = 0M thread_concurrency = 8 max_heap_table_size=200M log-output = FILE log-slow-queries=/tmp/mysql-slow.log long_query_time…
Post: 10+ Ways to Crash or Overload MySQL
November 13, 2007 By 20 Comments
… You can build query (with derived tables) which uses as many temporary tables as you like and you can size them so… implementing some form of limit on undo segment size. Another possibility is to use queries which use large temporary tables or sort… Tree The query is internally presented using parse tree inside MySQL which of course depends on query size which is controlled by max_allowed…
Post: read_buffer_size can break your replication
June 6, 2012 By 12 Comments
… larger than the max_allowed_packet (33554432). MasterB: masterB> mysqlbinlog data/mysql-bin.000004 | grep block_len #Begin_load_query: file_id… you see the difference? 33555308 is larger than the max_allowed_packet size 33554432 so the Master2 has written the some blocks…_buffer_size = 16M We run the LOAD DATA INFILE again and now this is the output on both servers: #Begin_load_query…
Comment: What to tune in MySQL Server after installation
October 8, 2012 By 118 Comments
…Querys] #log-slow-queries = /var/log/mysqlslowq.log #long_query_time = 5 #log-queries-not-using-indexes [Cache] query_cache_type = 1 query_cache_size = 64M query…
Comment: table_cache negative scalability
April 14, 2010 By 23 Comments
… max_connections = 400 table_cache = 20000 open_files_limit = 40000 max_binlog_size = 500M max_allowed_packet = 20M tmp_table_size = 100M max_heap_table_size = 100M query_cache_size = 100M default_table_type…
Comment: table_cache negative scalability
April 14, 2010 By 23 Comments
… max_connections = 400 table_cache = 20000 open_files_limit = 40000 max_binlog_size = 500M max_allowed_packet = 20M tmp_table_size = 100M max_heap_table_size = 100M query_cache_size = 100M default_table_type…
Post: Shard-Query EC2 images available
May 11, 2011 By 2 Comments
… in size. This will give us some interesting information about how Shard-Query will perform on each database. To do the size comparison, I used the du utility: InnoDB file size on disk…: Compact Rows: 6697533 Avg_row_length: 241 Data_length: 1616904192 Max_data_length: 0 Index_length: 539279360 Data_free: 4194304 Auto…
Post: TMP_TABLE_SIZE and MAX_HEAP_TABLE_SIZE
January 19, 2007 By 17 Comments
… tables that the server creates on the fly while processing queries. However, the two types of tables differ in that MEMORY… the max_heap_table_size system variable to impose a maximum size on MEMORY tables. For individual tables, you can also specify a MAX…-table-size” introduced to set maximum expected on-disk temporary table size and avoid mix-up of tmp_table_size and max_heap_table_size…
Post: Modeling MySQL Capacity by Measuring Resource Consumptions
March 8, 2011 By 1 Comment
….11 0 # Tmp tbl size 554.04M 0 12.83M 324.99 0 32.65k 0 # Query size 294.90M 14 783 158… [1.0], V/M = 0.02 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 98 1753697 # Exec time 52… 0 0 0 0 # Tmp tbl size 0 0 0 0 0 0 0 0 # Query size 61 180.07M 95 142 107…
Post: Troubleshooting MySQL Memory Usage
March 21, 2012 By 5 Comments
…, user variables, huge network buffer (can grow up to max_packet_size per connection) are all connection buffers and so if you… allocated for query execution, which size can be controlled by tmp_table_size and which also only exist for duration of query execution so… permanent and temporary. There is a max_heap_table_size variable which allows you to limit size of MEMORY tables (the limit applies…
