[ svepomalo @ 31.07.2015. 15:06 ] @
| Ovako, na AWS-u imam RDS sa mysqlom(5.6) jednu instancu samo, bez replikacije. Prvo sam imao 6gb a sada 4gb, smanjio instancu zbog troskova. Malo usera imam, nemam neki znacajno veci load. CPU ne prelazi 5% ali me buni memorija. Naime, od 6GB free 5.3GB, od trenutnka promene instance na 4gb RAM pada konstantno (u zadnja 3 dana) i sada je trenunto na 1.87GB. Sve tabele su mi INNODB a koristim aws-ova defaultna podesavanja za bazu. U njihovom monitoringu je swap usage 0%, read latency je od 0 do 2ms a write je max bio na 4ms. Generalno na site nisam primetio sporiji response, radi kao sto i radilo. Ne umem bas da procitam ENGINE INNODB STATUS pa vas molim za neku analizu, da li da se brinem ili sta bi trebao da promenim. Znam za innodb_buffer_pool_size da treba da je preporucljivo da bude 3/4 od memorije ako se ne varam. Evo sta kaze show full procces list(deluje mi da je sve ok): Code: +-------+----------+--------------------+--------+---------+------+-------+-----------------------+ | Id | User | Host | db | Command | Time | State | Info | +-------+----------+--------------------+--------+---------+------+-------+-----------------------+ | 1 | rdsadmin | localhost:12943 | mysql | Sleep | 7 | | NULL | | 54212 | admin | 172.31.4.127:34192 | proddb | Sleep | 2147 | | NULL | | 54213 | admin | 172.31.4.127:34193 | proddb | Sleep | 2156 | | NULL | | 54214 | admin | 172.31.4.127:34194 | proddb | Sleep | 2150 | | NULL | | 54284 | admin | 172.31.34.87:38157 | proddb | Sleep | 1811 | | NULL | | 54285 | admin | 172.31.34.87:38158 | proddb | Sleep | 1820 | | NULL | | 54286 | admin | 172.31.34.87:38159 | proddb | Sleep | 1814 | | NULL | | 54355 | admin | 172.31.4.127:34322 | proddb | Sleep | 1669 | | NULL | | 54356 | admin | 172.31.4.127:34323 | proddb | Sleep | 1678 | | NULL | | 54357 | admin | 172.31.4.127:34324 | proddb | Sleep | 1671 | | NULL | | 54408 | admin | 10.0.0.250:34925 | proddb | Query | 0 | init | show full processlist | | 54534 | admin | 172.31.34.87:38407 | proddb | Sleep | 1275 | | NULL | | 54535 | admin | 172.31.34.87:38408 | proddb | Sleep | 1283 | | NULL | | 54536 | admin | 172.31.34.87:38409 | proddb | Sleep | 1277 | | NULL | | 54627 | admin | 172.31.34.87:38521 | proddb | Sleep | 1135 | | NULL | | 54628 | admin | 172.31.34.87:38522 | proddb | Sleep | 1144 | | NULL | | 54629 | admin | 172.31.34.87:38523 | proddb | Sleep | 1138 | | NULL | | 54829 | admin | 172.31.34.87:38765 | proddb | Sleep | 799 | | NULL | | 54830 | admin | 172.31.34.87:38766 | proddb | Sleep | 808 | | NULL | | 54831 | admin | 172.31.34.87:38767 | proddb | Sleep | 802 | | NULL | | 54869 | admin | 172.31.4.127:34873 | proddb | Sleep | 768 | | NULL | | 54870 | admin | 172.31.4.127:34874 | proddb | Sleep | 777 | | NULL | | 54871 | admin | 172.31.4.127:34875 | proddb | Sleep | 770 | | NULL | | 54997 | admin | 172.31.34.87:38970 | proddb | Sleep | 628 | | NULL | | 54998 | admin | 172.31.34.87:38971 | proddb | Sleep | 637 | | NULL | | 54999 | admin | 172.31.34.87:38972 | proddb | Sleep | 631 | | NULL | | 55060 | admin | 172.31.34.87:39039 | proddb | Sleep | 508 | | NULL | | 55061 | admin | 172.31.34.87:39040 | proddb | Sleep | 517 | | NULL | | 55062 | admin | 172.31.34.87:39041 | proddb | Sleep | 510 | | NULL | | 55176 | admin | 172.31.34.87:39160 | proddb | Sleep | 239 | | NULL | | 55177 | admin | 172.31.34.87:39161 | proddb | Sleep | 247 | | NULL | | 55178 | admin | 172.31.34.87:39162 | proddb | Sleep | 241 | | NULL | | 55247 | admin | 172.31.4.127:35279 | proddb | Sleep | 55 | | NULL | | 55248 | admin | 172.31.4.127:35280 | proddb | Sleep | 64 | | NULL | | 55249 | admin | 172.31.4.127:35281 | proddb | Sleep | 58 | | NULL | | 55283 | admin | 172.31.34.87:39271 | proddb | Sleep | 1 | | NULL | +-------+----------+--------------------+--------+---------+------+-------+-----------------------+ 36 rows in set (0.00 sec) SHOW ENGINE INNODB STATUS\G: \*************************** 1. row *************************** Type: InnoDB Name: Status: ===================================== 2015-07-31 13:45:37 2b018f54b700 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 36 seconds ----------------- BACKGROUND THREAD ----------------- srv_master_thread loops: 11443 srv_active, 0 srv_shutdown, 260532 srv_idle srv_master_thread log flush and writes: 271956 ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 9753 OS WAIT ARRAY INFO: signal count 12610 Mutex spin waits 34382, rounds 42988, OS waits 781 RW-shared spins 9474, rounds 266280, OS waits 8843 RW-excl spins 161, rounds 7628, OS waits 121 Spin rounds per wait: 1.25 mutex, 28.11 RW-shared, 47.38 RW-excl ------------ TRANSACTIONS ------------ Trx id counter 14367561 Purge done for trx's n:o < 14367436 undo n:o < 0 state: running but idle History list length 3593 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 14360948, not started MySQL thread id 55060, OS thread handle 0x2b018b857700, query id 2521991 172.31.34.87 admin cleaning up ---TRANSACTION 14360694, not started MySQL thread id 55062, OS thread handle 0x2b018eb23700, query id 2521727 172.31.34.87 admin cleaning up ---TRANSACTION 14353507, not started MySQL thread id 54997, OS thread handle 0x2b018bba4700, query id 2515065 172.31.34.87 admin cleaning up ---TRANSACTION 14353259, not started MySQL thread id 54999, OS thread handle 0x2b018f0b9700, query id 2514817 172.31.34.87 admin cleaning up ---TRANSACTION 14345871, not started MySQL thread id 54869, OS thread handle 0x2b018ee70700, query id 2507795 172.31.4.127 admin cleaning up ---TRANSACTION 14345621, not started MySQL thread id 54871, OS thread handle 0x2b018f64f700, query id 2507549 172.31.4.127 admin cleaning up ---TRANSACTION 14339205, not started MySQL thread id 54829, OS thread handle 0x2b018f857700, query id 2501825 172.31.34.87 admin cleaning up ---TRANSACTION 14338917, not started MySQL thread id 54831, OS thread handle 0x2b018f60e700, query id 2501535 172.31.34.87 admin cleaning up ---TRANSACTION 14328305, not started MySQL thread id 54627, OS thread handle 0x2b018efb5700, query id 2491359 172.31.34.87 admin cleaning up ---TRANSACTION 14327924, not started MySQL thread id 54629, OS thread handle 0x2b018f5cd700, query id 2490971 172.31.34.87 admin cleaning up ---TRANSACTION 14318570, not started MySQL thread id 54534, OS thread handle 0x2b018eb64700, query id 2482232 172.31.34.87 admin cleaning up ---TRANSACTION 14318322, not started MySQL thread id 54536, OS thread handle 0x2b018baa0700, query id 2481984 172.31.34.87 admin cleaning up ---TRANSACTION 0, not started MySQL thread id 54408, OS thread handle 0x2b018f54b700, query id 2527878 10.0.0.250 admin init SHOW ENGINE INNODB STATUS ---TRANSACTION 14300207, not started MySQL thread id 54355, OS thread handle 0x2b018ecea700, query id 2464398 172.31.4.127 admin cleaning up ---TRANSACTION 14299958, not started MySQL thread id 54357, OS thread handle 0x2b018f58c700, query id 2464140 172.31.4.127 admin cleaning up ---TRANSACTION 14290905, not started MySQL thread id 54284, OS thread handle 0x2b018f816700, query id 2455570 172.31.34.87 admin cleaning up ---TRANSACTION 14290659, not started MySQL thread id 54286, OS thread handle 0x2b018b64f700, query id 2455324 172.31.34.87 admin cleaning up ---TRANSACTION 14282177, not started MySQL thread id 54212, OS thread handle 0x2b018f712700, query id 2447240 172.31.4.127 admin cleaning up ---TRANSACTION 14281931, not started MySQL thread id 54214, OS thread handle 0x2b018f302700, query id 2446994 172.31.4.127 admin cleaning up ---TRANSACTION 14367558, not started MySQL thread id 1, OS thread handle 0x2b018ad0c700, query id 2527872 localhost 127.0.0.1 rdsadmin cleaning up -------- FILE I/O -------- I/O thread 0 state: waiting for completed aio requests (insert buffer thread) I/O thread 1 state: waiting for completed aio requests (log thread) I/O thread 2 state: waiting for completed aio requests (read thread) I/O thread 3 state: waiting for completed aio requests (read thread) I/O thread 4 state: waiting for completed aio requests (read thread) I/O thread 5 state: waiting for completed aio requests (read thread) I/O thread 6 state: waiting for completed aio requests (write thread) I/O thread 7 state: waiting for completed aio requests (write thread) I/O thread 8 state: waiting for completed aio requests (write thread) I/O thread 9 state: waiting for completed aio requests (write thread) Pending normal aio reads: 0 [0, 0, 0, 0] , aio writes: 0 [0, 0, 0, 0] , ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0 Pending flushes (fsync) log: 0; buffer pool: 0 2378 OS file reads, 770947 OS file writes, 513215 OS fsyncs 0.00 reads/s, 0 avg bytes/read, 18.72 writes/s, 9.17 fsyncs/s ------------------------------------- INSERT BUFFER AND ADAPTIVE HASH INDEX ------------------------------------- Ibuf: size 1, free list len 0, seg size 2, 30 merges merged operations: insert 217, delete mark 1, delete 0 discarded operations: insert 0, delete mark 0, delete 0 Hash table size 5449511, node heap has 655 buffer(s) 45.94 hash searches/s, 78.28 non-hash searches/s --- LOG --- Log sequence number 1076931839 Log flushed up to 1076931839 Pages flushed up to 1076931273 Last checkpoint at 1076931273 0 pending log writes, 0 pending chkp writes 423818 log i/o's done, 7.81 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 2818179072; in additional pool allocated 0 Dictionary memory allocated 335140 Buffer pool size 168064 Free buffers 163601 Database pages 3808 Old database pages 839 Modified db pages 6 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 0, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 2311, created 1497, written 309780 0.00 reads/s, 0.03 creates/s, 10.39 writes/s Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 3808, unzip_LRU len: 0 I/O sum[0]:cur[0], unzip sum[0]:cur[0] ---------------------- INDIVIDUAL BUFFER POOL INFO ---------------------- ---BUFFER POOL 0 Buffer pool size 21008 Free buffers 20380 Database pages 545 Old database pages 212 Modified db pages 1 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 0, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 290, created 255, written 51391 0.00 reads/s, 0.00 creates/s, 1.14 writes/s Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 545, unzip_LRU len: 0 I/O sum[0]:cur[0], unzip sum[0]:cur[0] ---BUFFER POOL 1 Buffer pool size 21008 Free buffers 20575 Database pages 358 Old database pages 0 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 0, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 177, created 181, written 5008 0.00 reads/s, 0.00 creates/s, 0.00 writes/s Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 358, unzip_LRU len: 0 I/O sum[0]:cur[0], unzip sum[0]:cur[0] ---BUFFER POOL 2 Buffer pool size 21008 Free buffers 20383 Database pages 543 Old database pages 209 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 0, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 277, created 266, written 11804 0.00 reads/s, 0.00 creates/s, 0.17 writes/s Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 543, unzip_LRU len: 0 I/O sum[0]:cur[0], unzip sum[0]:cur[0] ---BUFFER POOL 3 Buffer pool size 21008 Free buffers 20502 Database pages 410 Old database pages 0 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 0, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 236, created 174, written 58363 0.00 reads/s, 0.00 creates/s, 2.06 writes/s Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 410, unzip_LRU len: 0 I/O sum[0]:cur[0], unzip sum[0]:cur[0] ---BUFFER POOL 4 Buffer pool size 21008 Free buffers 20446 Database pages 483 Old database pages 0 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 0, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 362, created 121, written 79121 0.00 reads/s, 0.03 creates/s, 2.97 writes/s Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 483, unzip_LRU len: 0 I/O sum[0]:cur[0], unzip sum[0]:cur[0] ---BUFFER POOL 5 Buffer pool size 21008 Free buffers 20401 Database pages 517 Old database pages 209 Modified db pages 5 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 0, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 282, created 235, written 30788 0.00 reads/s, 0.00 creates/s, 1.22 writes/s Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 517, unzip_LRU len: 0 I/O sum[0]:cur[0], unzip sum[0]:cur[0] ---BUFFER POOL 6 Buffer pool size 21008 Free buffers 20507 Database pages 425 Old database pages 0 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 0, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 320, created 105, written 37748 0.00 reads/s, 0.00 creates/s, 1.28 writes/s Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 425, unzip_LRU len: 0 I/O sum[0]:cur[0], unzip sum[0]:cur[0] ---BUFFER POOL 7 Buffer pool size 21008 Free buffers 20407 Database pages 527 Old database pages 209 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 0, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 367, created 160, written 35557 0.00 reads/s, 0.00 creates/s, 1.56 writes/s Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 527, unzip_LRU len: 0 I/O sum[0]:cur[0], unzip sum[0]:cur[0] -------------- ROW OPERATIONS -------------- 0 queries inside InnoDB, 0 queries in queue 0 read views open inside InnoDB Main thread process no. 3310, id 47285614962432, state: sleeping Number of rows inserted 211471, updated 1036950, deleted 71435, read 382807729 5.42 inserts/s, 3.78 updates/s, 4.94 deletes/s, 3714.87 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT ============================ 1 row in set (0.00 sec) SHOW VARIABLES WHERE variable_name LIKE 'innodb%'; +------------------------------------------+------------------------+ | Variable_name | Value | +------------------------------------------+------------------------+ | innodb_adaptive_flushing | ON | | innodb_adaptive_flushing_lwm | 10 | | innodb_adaptive_hash_index | ON | | innodb_adaptive_max_sleep_delay | 150000 | | innodb_additional_mem_pool_size | 8388608 | | innodb_api_bk_commit_interval | 5 | | innodb_api_disable_rowlock | OFF | | innodb_api_enable_binlog | OFF | | innodb_api_enable_mdl | OFF | | innodb_api_trx_level | 0 | | innodb_autoextend_increment | 64 | | innodb_autoinc_lock_mode | 1 | | innodb_buffer_pool_dump_at_shutdown | OFF | | innodb_buffer_pool_dump_now | OFF | | innodb_buffer_pool_filename | ib_buffer_pool | | innodb_buffer_pool_instances | 8 | | innodb_buffer_pool_load_abort | OFF | | innodb_buffer_pool_load_at_startup | OFF | | innodb_buffer_pool_load_now | OFF | | innodb_buffer_pool_size | 2753560576 | | innodb_change_buffer_max_size | 25 | | innodb_change_buffering | all | | innodb_checksum_algorithm | innodb | | innodb_checksums | ON | | innodb_cmp_per_index_enabled | OFF | | innodb_commit_concurrency | 0 | | innodb_compression_failure_threshold_pct | 5 | | innodb_compression_level | 6 | | innodb_compression_pad_pct_max | 50 | | innodb_concurrency_tickets | 5000 | | innodb_data_file_path | ibdata1:12M:autoextend | | innodb_data_home_dir | /rdsdbdata/db/innodb | | innodb_disable_sort_file_cache | OFF | | innodb_doublewrite | ON | | innodb_fast_shutdown | 1 | | innodb_file_format | Antelope | | innodb_file_format_check | ON | | innodb_file_format_max | Antelope | | innodb_file_per_table | ON | | innodb_flush_log_at_timeout | 1 | | innodb_flush_log_at_trx_commit | 1 | | innodb_flush_method | O_DIRECT | | innodb_flush_neighbors | 1 | | innodb_flushing_avg_loops | 30 | | innodb_force_load_corrupted | OFF | | innodb_force_recovery | 0 | | innodb_ft_aux_table | | | innodb_ft_cache_size | 8000000 | | innodb_ft_enable_diag_print | OFF | | innodb_ft_enable_stopword | ON | | innodb_ft_max_token_size | 84 | | innodb_ft_min_token_size | 3 | | innodb_ft_num_word_optimize | 2000 | | innodb_ft_result_cache_limit | 2000000000 | | innodb_ft_server_stopword_table | | | innodb_ft_sort_pll_degree | 2 | | innodb_ft_total_cache_size | 640000000 | | innodb_ft_user_stopword_table | | | innodb_io_capacity | 200 | | innodb_io_capacity_max | 2000 | | innodb_large_prefix | OFF | | innodb_lock_wait_timeout | 50 | | innodb_locks_unsafe_for_binlog | OFF | | innodb_log_buffer_size | 8388608 | | innodb_log_compressed_pages | ON | | innodb_log_file_size | 134217728 | | innodb_log_files_in_group | 2 | | innodb_log_group_home_dir | /rdsdbdata/log/innodb | | innodb_lru_scan_depth | 1024 | | innodb_max_dirty_pages_pct | 75 | | innodb_max_dirty_pages_pct_lwm | 0 | | innodb_max_purge_lag | 0 | | innodb_max_purge_lag_delay | 0 | | innodb_mirrored_log_groups | 1 | | innodb_monitor_disable | | | innodb_monitor_enable | | | innodb_monitor_reset | | | innodb_monitor_reset_all | | | innodb_old_blocks_pct | 37 | | innodb_old_blocks_time | 1000 | | innodb_online_alter_log_max_size | 134217728 | | innodb_open_files | 2000 | | innodb_optimize_fulltext_only | OFF | | innodb_page_size | 16384 | | innodb_print_all_deadlocks | OFF | | innodb_purge_batch_size | 300 | | innodb_purge_threads | 1 | | innodb_random_read_ahead | OFF | | innodb_read_ahead_threshold | 56 | | innodb_read_io_threads | 4 | | innodb_read_only | OFF | | innodb_replication_delay | 0 | | innodb_rollback_on_timeout | OFF | | innodb_rollback_segments | 128 | | innodb_sort_buffer_size | 1048576 | | innodb_spin_wait_delay | 6 | | innodb_stats_auto_recalc | ON | | innodb_stats_method | nulls_equal | | innodb_stats_on_metadata | OFF | | innodb_stats_persistent | ON | | innodb_stats_persistent_sample_pages | 20 | | innodb_stats_sample_pages | 8 | | innodb_stats_transient_sample_pages | 8 | | innodb_status_output | OFF | | innodb_status_output_locks | OFF | | innodb_strict_mode | OFF | | innodb_support_xa | ON | | innodb_sync_array_size | 1 | | innodb_sync_spin_loops | 30 | | innodb_table_locks | ON | | innodb_thread_concurrency | 0 | | innodb_thread_sleep_delay | 10000 | | innodb_undo_directory | . | | innodb_undo_logs | 128 | | innodb_undo_tablespaces | 0 | | innodb_use_native_aio | ON | | innodb_use_sys_malloc | ON | | innodb_version | 5.6.21 | | innodb_write_io_threads | 4 | +------------------------------------------+------------------------+ 119 rows in set (0.00 sec) |