…innodb_io_capacity = 30000 innodb_adaptive_flushing_method = keep_average innodb_flush_neighbor_pages=none innodb_max…consistently hit about 27000 per second, whereas without time-…max_heap_table_size = 64M sort_buffer_size = 4M join_buffer_size = 4M thread_cache_size = 1000 query_cache_size = 0 query…
Post: MySQL and Percona Server in LinkBench benchmark
May 8, 2013 By 18 Comments
… per second …innodb_log_buffer_size=128M innodb_max_dirty_pages_pct=80 innodb_file_format=barracuda innodb_file_per_table innodb_read_io_threads = 8 innodb_write_io_threads = 8 innodb_io_capacity = 5000 sync_binlog=0 max_connections=5000 table_open_cache=5000 table-definition-cache=1000 query…
Post: More on MySQL transaction descriptors optimization
April 26, 2013 By Leave a Comment
… single socket x 2 threads per socket), while the second one use 16 cores (8 cores per socket per node). So this test also… innodb_file_per_table = true innodb_read_io_threads = 8 innodb_write_io_threads = 8 innodb_io_capacity = 2000 max_connections=5000 table_open_cache=5000 query…
Post: SHOW INNODB STATUS walk through
July 17, 2006 By 40 Comments
…Innodb will print: ===================================== 060717 3:07:56 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 44 seconds… grow and innodb_max_purge_lag will …queries inside InnoDB, 0 queries in queue 1 read views open inside InnoDB…
Post: Modeling MySQL Capacity by Measuring Resource Consumptions
March 8, 2011 By 1 Comment
…seconds per second. Of course no system scale perfectly, so you would unlikely be able to get 16 queries per second…Innodb with MySQL we can use Innodb_data_reads , Innodb_data_writes, Innodb…Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= …
Post: Impact of logging on MySQL’s performance
February 10, 2009 By 8 Comments
…: [mysqld] user=root max_connections=3000 innodb_log_file_size=128M innodb_flush_log_at_trx_commit=1 innodb_file_per_table table_cache=2000… during the test it was executed roughly 20-25k queries per second. If all queries are logged – a slow log is populated at… doesn’t exceed the measurement error. The query rate in this case is about 1000 per second. Logging to /dev/null It is…
Post: How to Monitor MySQL with Percona's Nagios Plugins
February 22, 2012 By 11 Comments
…-check-mysql-innodb plugin checks for several indicators of severe internal problems within InnoDB, such as…small. The server is approaching max_connections. Overflowing the max_connections setting is a bit …in a variable such as Queries and alert on a queries-per-second threshold. (I don’t suggest …
Post: How much memory Innodb locks really take ?
July 13, 2006 By 11 Comments
… Engine: InnoDB Version: 10 Row_format: Compact Rows: 1638757 Avg_row_length: 61 Data_length: 100253696 Max_… MySQL thread id 7429, query id 24542 localhost root show innodb status Note, running statement in….5 seconds on this pretty old box, which means locking speed will be few millions per second…
Post: Edge-case behavior of INSERT...ODKU
October 27, 2012 By 6 Comments
…NULL, PRIMARY KEY(id), UNIQUE KEY(username) ) ENGINE=InnoDB; Now consider the following sequence of events. (root@…the id is equal to the auto-increment max value, which is the one that got updated…what the problem turned out to be. 1500 queries per second all trying to lock and update the same…
Post: Heikki Tuuri Innodb answers - Part I
October 26, 2007 By 19 Comments
… fix Innodb so it unlocks (or never locks) the rows if they were not matched by query where…heavily concurrent environments. Sets the max\ imum number of threads allowed inside InnoDB. Value 0 will disable the…innodb_flush_log_at_trx_commit=2, that is, to flush the log to the disk only once per second…
