I'm having trouble optimizing my MySQL VPS that I use.
I have a plan in RamNode with the following specs:
-
Intel® Xeon® CPU E3-1240 V2 @ 3.40GHz (4 Colors)
-
4GB Ram
-
135 GB SSD Raid 10
I'm having problems with slowness in one of the applications I've hosted, sometimes it even gives error max user connections .
The MySQLTunner test follows:
MySQLTuner 1.3.0 - Major Hayden
Bug reports, feature requests, and downloads at link
Run with '--help' for additional options and output filtering
[OK] Currently running supported MySQL version 5.5.42-cll
[OK] Operating on 64-bit architecture
-------- Storage Engine Statistics ------------------------------------ -------
[-] Status: + ARCHIVE + BLACKHOLE + CSV -FEDERATED + InnoDB + MRG_MYISAM
[-] Data in MyISAM tables: 136M (Tables: 300)
[-] Data in InnoDB tables: 44M (Tables: 202)
[-] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[!] Total fragmented tables: 220
-------- Security Recommendations ------------------------------------- ------
[OK] All database users have passwords assigned
-------- Performance Metrics ------------------------------------- ------------
[-] Up for: 1d 20h 25m 13s (3M q [23.681 qps], 251K conn, TX: 9B, RX: 605M)
[-] Reads / Writes: 57% / 43%
[-] Total buffers: 528.0M global + 3.6M per thread (400 max threads)
[OK] Maximum possible memory usage: 1.9G (48% of installed RAM)
[OK] Slow queries: 0% (17 / 3M)
[OK] Highest usage of available connections: 11% (47/400)
[OK] Key buffer size / total MyISAM indexes: 256.0M / 23.3M
[OK] Key buffer hit rate: 100.0% (49M cached / 5K reads)
[OK] Query cache efficiency: 76.0% (1M cached / 2M selects)
[!] Query cache prunes per day: 7322
[!] Sorts requiring temporary tables: 69% (144K temp sorts / 208K sorts)
[!] Joins performed without indexes: 21719
[OK] Temporary tables created on disk: 0% (4K on disk / 574K total)
[OK] Thread cache hit rate: 99% (47 created / 251K connections)
[OK] Table cache hit rate: 87% (1K open / 1K opened)
[OK] Open file limit used: 6% (1K / 15K)
[OK] Table locks acquired immediately: 99% (1M immediate / 1M locks)
[OK] InnoDB buffer pool / data size: 128.0M / 44.1M
[OK] InnoDB log waits: 0
-------- Recommendations -------------------------------------- ---------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
Enable the slow query log to troubleshoot bad queries
Adjust your join queries to always use indexes
Variables to adjust:
query_cache_size (> 64M)
sort_buffer_size (> 2M)
read_rnd_buffer_size (> 236K)
join_buffer_size (> 128.0K, or always use indexes with joins)
Follow my My.CNF
[mysqld]
max_connections = 400
max_user_connections=40
key_buffer_size = 256M
myisam_sort_buffer_size = 16M
read_buffer_size = 1M
table_open_cache = 2048
thread_cache_size = 128
wait_timeout = 20
connect_timeout = 10
tmp_table_size = 128M
max_heap_table_size = 64M
max_allowed_packet=268435456
net_buffer_length = 5500
max_connect_errors = 10
concurrent_insert = 2
read_rnd_buffer_size = 242144
bulk_insert_buffer_size = 2M
query_cache_limit = 2M
query_cache_size = 64M
query_cache_type = 1
query_prealloc_size = 87382
query_alloc_block_size = 21845
transaction_alloc_block_size = 2730
transaction_prealloc_size = 1364
max_write_lock_count = 2
log-error
external-locking=FALSE
open_files_limit=15000
default-storage-engine=MyISAM
innodb_file_per_table=1
[mysqld_safe]
[mysqldump]
quick
max_allowed_packet = 8M
[isamchk]
key_buffer = 128M
sort_buffer = 128M
read_buffer = 64M
write_buffer = 64M
[myisamchk]
key_buffer = 128M
sort_buffer = 128M
read_buffer = 64M
write_buffer = 64M
#### Per connection configuration ####
sort_buffer_size = 2M
join_buffer_size = 2M
thread_stack = 192K
log-slow-queries
If you can help me, I would appreciate it:)