… Rows: 1638757 Avg_row_length: 61 Data_length: 100253696 Max_data_length: 0 Index_length: 128974848 Data_free: 0 Auto_increment: 1638401 Create_time… | +———-+ 1 row in set (4.44 sec) Looking at SHOW INNODB STATUS we can see: Total number of lock structs in row lock hash table…
Post: Identifying the load with the help of pt-query-digest and Percona Server
December 29, 2011 By 3 Comments
…InnoDB statistics (such as IO read operations, the number of unique pages the query accessed, the length of time query waited for row locks…
Post: SHOW INNODB STATUS walk through
July 17, 2006 By 40 Comments
…innodb_sync_spin_loops can be used to ballance between wasting CPU time running spin locks…lock structs in row lock hash table is number of row lock structures allocated by all transactions. Note not same as number of locked rows… for graphing and monitoring. “16384 avg bytes/read” shows average size …
Post: Investigating MySQL Replication Latency in Percona XtraDB Cluster
March 3, 2013 By 13 Comments
…-rng=on –max-requests=0 –max-time=300 run …. per-request statistics: min: 0.68ms avg: 0.88ms max: 306.80ms approx… would consider medium size transaction modifying 1 million of rows is rather long time. So make sure to have a good understanding…=xtrabackup wsrep_cluster_name=DPE binlog_format=ROW default_storage_engine=InnoDB innodb_autoinc_lock_mode=2 innodb_locks_unsafe_for_binlog=1 wsrep_sst_auth…
Post: Improved InnoDB fast index creation
November 6, 2011 By 4 Comments
….000118 | | System lock | 0.000007 |…row *************************** Name: t Engine: InnoDB Version: 10 Row_format: Compact Rows: 4195067 Avg_row_length: 29 Data_length: 125452288 Max_data_length: 0 Index_length: 278839296 Data_free: 1838153728 Auto_increment: 4587468 Create_time…
Post: Modeling MySQL Capacity by Measuring Resource Consumptions
March 8, 2011 By 1 Comment
…If we’re running Innodb with MySQL we can use Innodb_data_reads , Innodb_data_writes, Innodb_os_log_fsyncs for …avg 95% stddev median # ============ ======= ======= ======= ======= ======= ======= ======= # Exec time 8338s 1us 284s 5ms 13ms 298ms 185us # Lock time 71s 0 3ms 39us 54us 28us 35us # Rows…
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
…Innodb_row_lock_current_waitsblogpercona.commanual Innodb_row_lock_timeblogpercona.commanual Innodb_row_lock_time_avgblogpercona.commanual Innodb_row_lock_time_maxblogpercona.commanual Innodb_row_lock_waitsblogpercona.commanual Innodb_rows_deletedblogpercona.commanual Innodb_rows…master_net_avg_wait_…
Post: Shard-Query EC2 images available
May 11, 2011 By 2 Comments
…INNODB innodb-buffer-pool-instances=2 innodb-buffer-pool-size=5600M innodb-file-format=barracuda innodb-file-per-table innodb-flush-log-at-trx-commit=1 innodb…
Post: Optimizing slow web pages with mk-query-digest
April 7, 2011 By 14 Comments
…avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 0 519 # Exec time 2 148s 11us 33s 285ms 53ms 2s 26us # Lock time…InnoDB trxID 1153145C (2/0%), 11531626 (2/0%)… 43 more # Last errno 0 # Users prod # Query_time…
Comment: MySQL Server Memory Usage
September 1, 2006 By 21 Comments
…at writing time (18:37 …’Sort_rows‘, ’1344092495′ ‘Sort_scan’, ’935242′ ‘Table_locks_immediate’, ’895966639′ ‘Table_locks_waited’,…iostat 5 2 avg-cpu: %user %…innodb_additional_mem_pool_size=20M innodb_file_io_threads=4 innodb_lock_wait_timeout=15 # CHANGED from 50 innodb_log_files_in_group=3 innodb…
