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):
+-------+----------+--------------------+--------+---------+------+-------+-----------------------+
| 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)