Re: Show innodb engine status tuning db server
Posted by:
Frwa Onto
Date: September 17, 2013 08:52AM
Hi Rick,
Below are the values you wanted.
1)mysql> show variables like 'innodb%';
+-----------------------------------------+------------------------+
| Variable_name | Value |
+-----------------------------------------+------------------------+
| innodb_adaptive_hash_index | ON |
| innodb_additional_mem_pool_size | 1048576 |
| innodb_autoextend_increment | 8 |
| innodb_autoinc_lock_mode | 1 |
| innodb_buffer_pool_size | 5368709120 |
| innodb_checksums | ON |
| innodb_commit_concurrency | 0 |
| innodb_concurrency_tickets | 500 |
| innodb_data_file_path | ibdata1:10M:autoextend |
| innodb_data_home_dir | |
| innodb_doublewrite | ON |
| innodb_fast_shutdown | 1 |
| innodb_file_io_threads | 4 |
| innodb_file_per_table | ON |
| innodb_flush_log_at_trx_commit | 1 |
| innodb_flush_method | |
| innodb_force_recovery | 0 |
| innodb_lock_wait_timeout | 120 |
| innodb_locks_unsafe_for_binlog | OFF |
| innodb_log_buffer_size | 1048576 |
| innodb_log_file_size | 5242880 |
| innodb_log_files_in_group | 2 |
| innodb_log_group_home_dir | ./ |
| innodb_max_dirty_pages_pct | 90 |
| innodb_max_purge_lag | 0 |
| innodb_mirrored_log_groups | 1 |
| innodb_open_files | 300 |
| innodb_rollback_on_timeout | OFF |
| innodb_stats_method | nulls_equal |
| innodb_stats_on_metadata | ON |
| innodb_support_xa | ON |
| innodb_sync_spin_loops | 20 |
| innodb_table_locks | ON |
| innodb_thread_concurrency | 8 |
| innodb_thread_sleep_delay | 10000 |
| innodb_use_legacy_cardinality_algorithm | ON |
+-----------------------------------------+------------------------+
36 rows in set (0.00 sec)
2)mysql> show global status like 'innodb%';
+-----------------------------------+-------------+
| Variable_name | Value |
+-----------------------------------+-------------+
| Innodb_buffer_pool_pages_data | 72921 |
| Innodb_buffer_pool_pages_dirty | 0 |
| Innodb_buffer_pool_pages_flushed | 716678 |
| Innodb_buffer_pool_pages_free | 254043 |
| Innodb_buffer_pool_pages_misc | 716 |
| Innodb_buffer_pool_pages_total | 327680 |
| Innodb_buffer_pool_read_ahead_rnd | 42 |
| Innodb_buffer_pool_read_ahead_seq | 894 |
| Innodb_buffer_pool_read_requests | 1091499714 |
| Innodb_buffer_pool_reads | 16114 |
| Innodb_buffer_pool_wait_free | 0 |
| Innodb_buffer_pool_write_requests | 40140112 |
| Innodb_data_fsyncs | 576409 |
| Innodb_data_pending_fsyncs | 0 |
| Innodb_data_pending_reads | 0 |
| Innodb_data_pending_writes | 0 |
| Innodb_data_read | 1157484544 |
| Innodb_data_reads | 19225 |
| Innodb_data_writes | 979941 |
| Innodb_data_written | 24788483584 |
| Innodb_dblwr_pages_written | 716678 |
| Innodb_dblwr_writes | 33349 |
| Innodb_log_waits | 72 |
| Innodb_log_write_requests | 2441738 |
| Innodb_log_writes | 379320 |
| Innodb_os_log_fsyncs | 402512 |
| Innodb_os_log_pending_fsyncs | 0 |
| Innodb_os_log_pending_writes | 0 |
| Innodb_os_log_written | 1291926528 |
| Innodb_page_size | 16384 |
| Innodb_pages_created | 2439 |
| Innodb_pages_read | 70514 |
| Innodb_pages_written | 716678 |
| Innodb_row_lock_current_waits | 0 |
| Innodb_row_lock_time | 3393067 |
| Innodb_row_lock_time_avg | 7854 |
| Innodb_row_lock_time_max | 29610 |
| Innodb_row_lock_waits | 432 |
| Innodb_rows_deleted | 2157134 |
| Innodb_rows_inserted | 205261 |
| Innodb_rows_read | 6984249761 |
| Innodb_rows_updated | 190187 |
+-----------------------------------+-------------+
3)This machine got 8 GB ram.
Thank you for the command I can see wow there are 432 times lock wait. So what is your suggestion here?
Subject
Views
Written By
Posted
2478
September 15, 2013 09:37AM
1037
September 16, 2013 07:14PM
Re: Show innodb engine status tuning db server
1576
September 17, 2013 08:52AM
1016
September 18, 2013 07:21PM
1082
September 19, 2013 09:08AM
990
September 23, 2013 12:48PM
983
September 24, 2013 08:45PM
1217
September 24, 2013 10:01PM
912
September 26, 2013 11:29PM
1048
September 27, 2013 03:53AM
932
September 27, 2013 11:52PM
1001
September 28, 2013 12:39PM
952
September 29, 2013 08:40AM
957
October 01, 2013 01:43AM
975
October 01, 2013 10:14PM
891
October 01, 2013 10:35PM
920
October 02, 2013 07:43PM
1023
October 02, 2013 11:08PM
974
October 03, 2013 07:48PM
985
October 04, 2013 09:04PM
1059
October 06, 2013 01:43PM
876
October 07, 2013 09:06AM
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion
of Oracle or any other party.