Witam,
mam baze SQL na serwerze Enterprise SP-128 z HW i SSD.
Poniżej przedstawiam statystyki bazy. Problem jest ze w szczycie jest 2 - 4 razy więcej instertow i update i serwer nie najlepiej sobie z tym radzi.
Jak sadzicie czy szukać mocniejszej maszyny czy w tej próbować ulepszać konfiguracje? A może master - slave na 2 lub 3 serwery?
Bazy to glownie InnoDB.
Połączenie do mysql przez stunnel - może to jest problem?
Kod:
Total ø per hour ø per minute ø per second
1,249 M 2.62 M 43.61 k 726.82
Query type ø per hour %
stmt prepare 737 M 1.544 M 60.27%
stmt execute 737 M 1.544 M 60.27%
stmt close 737 M 1.544 M 60.27%
insert 164 M 342.762 k 13.38%
commit 153 M 320.716 k 12.52%
select 153 M 319.563 k 12.48%
update 3,818 k 8.000 k 0.31%
set option 1,337 k 2.802 k 0.11%
delete 941 k 1.972 k 0.08%
update multi 142 k 296.882 0.01%
insert select 27 k 55.880 0.00%
change db 1,497 3.136 0.00%
show plugins 1,096 2.296 0.00%
show table status 1,053 2.206 0.00%
show variables 569 1.192 0.00%
Query type ø per hour %
show create table 514 1.077 0.00%
show tables 451 0.945 0.00%
show fields 414 0.867 0.00%
create table 374 0.784 0.00%
show triggers 276 0.578 0.00%
show databases 253 0.530 0.00%
show slave status 237 0.497 0.00%
show master status 237 0.497 0.00%
show keys 208 0.436 0.00%
alter table 186 0.390 0.00%
show binlogs 137 0.287 0.00%
drop table 85 0.178 0.00%
unlock tables 85 0.178 0.00%
lock tables 85 0.178 0.00%
rename table 68 0.142 0.00%
Query type ø per hour %
show grants 61 0.128 0.00%
truncate 54 0.113 0.00%
show status 35 0.073 0.00%
admin commands 30 0.063 0.00%
create db 27 0.057 0.00%
flush 20 0.042 0.00%
show processlist 15 0.031 0.00%
show procedure status 10 0.021 0.00%
show warnings 10 0.021 0.00%
show function status 10 0.021 0.00%
show storage engines 9 0.019 0.00%
drop db 5 0.010 0.00%
create index 4 0.008 0.00%
optimize 1 0.002 0.00%
Kod:
key_buffer = 16M
max_allowed_packet = 16M
thread_stack = 192K
thread_cache_size = 8
myisam-recover = BACKUP
max_connections = 300
table_cache = 1024
thread_concurrency = 12
query_cache_limit = 32M
query_cache_size = 512M
max_tmp_tables = 1024
tmp_table_size = 128M
table_open_cache = 4096
sort_buffer_size = 64M
join_buffer_size = 128M
bulk_insert_buffer_size = 128M
innodb_buffer_pool_size =16G
innodb_additional_mem_pool_size = 128M
innodb_open_files = 16384
open-files-limit = 16384
read_buffer_size = 16M
Kod:
-------- Performance Metrics -------------------------------------------------
[--] Up for: 19d 21h 31m 8s (1B q [726.674 qps], 26M conn, TX: 344B, RX: 192B)
[--] Reads / Writes: 47% / 53%
[--] Total buffers: 16.7G global + 208.4M per thread (300 max threads)
[OK] Maximum possible memory usage: 77.7G (61% of installed RAM)
[OK] Slow queries: 0% (4K/1B)
[OK] Highest usage of available connections: 30% (92/300)
[OK] Key buffer size / total MyISAM indexes: 16.0M/123.0K
[OK] Key buffer hit rate: 100.0% (4B cached / 5K reads)
[OK] Query cache efficiency: 83.0% (747M cached / 899M selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 731K sorts)
[OK] Temporary tables created on disk: 0% (15K on disk / 40M total)
[OK] Thread cache hit rate: 98% (319K created / 26M connections)
[OK] Table cache hit rate: 48% (1K open / 2K opened)
[OK] Open file limit used: 0% (68/16K)
[OK] Table locks acquired immediately: 100% (341M immediate / 341M locks)
[OK] InnoDB buffer pool / data size: 16.0G/941.6M
[OK] InnoDB log waits: 0
Pozdrawiam