Hello,
I have a server with 4 GB RAM. Our site receives about 8,000-10,000 visits per day, has over 12,000 pages, and the database is about 3-4 GB.
I don't want to have to upgrade or get a dedicated MySQL server unless I have to. However, MySQL is taking a lot of memory, and the site keeps hanging. The load has been hovering around 1.5-2, and sometimes up to 3 or 4. I've run mysqltuner, and adjusted the settings. It told me to set innodb_buffer_pool_size to 3G, but of course that is most of our memory. Even lower values does not really help, however.
Here is some informatio about the resources it is using:
Excerpt from top output, ordered by swap:
From /etc/my.cnf:
mysqltuner, though it's not been 24 hours. It isn't stable for that long:
I don't see any other option besides getting more RAM, but I wanted to check here first. I might just split off MySQL onto its own server.
Anything I can possibly do?
I have a server with 4 GB RAM. Our site receives about 8,000-10,000 visits per day, has over 12,000 pages, and the database is about 3-4 GB.
I don't want to have to upgrade or get a dedicated MySQL server unless I have to. However, MySQL is taking a lot of memory, and the site keeps hanging. The load has been hovering around 1.5-2, and sometimes up to 3 or 4. I've run mysqltuner, and adjusted the settings. It told me to set innodb_buffer_pool_size to 3G, but of course that is most of our memory. Even lower values does not really help, however.
Here is some informatio about the resources it is using:
Code:
USER PID %CPU %MEM VSZ RSS TTY STAT START TIME COMMAND
mysql 5950 38.6 52.4 3794756 1877204 pts/1 Sl 12:40 36:48 /usr/sbin/mysql
Code:
top - 14:27:32 up 6 days, 16:40, 1 user, load average: 1.82, 1.91, 1.83
Tasks: 144 total, 2 running, 142 sleeping, 0 stopped, 0 zombie
Cpu(s): 34.1%us, 2.2%sy, 0.0%ni, 17.5%id, 46.1%wa, 0.2%hi, 0.0%si, 0.0%st
Mem: 3579200k total, 3567792k used, 11408k free, 14872k buffers
Swap: 1959920k total, 917208k used, 1042712k free, 313368k cached
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ SWAP COMMAND
5950 mysql 15 0 3706m 1.9g 4868 S 43.9 56.3 39:55.73 1.7g mysqld
Code:
[mysqld]
max_connections = 25
max_allowed_packet=64M
skip-external-locking
key_buffer = 10M
open_files_limit=11454
table_cache = 1536
sort_buffer_size = 128K
read_buffer_size = 128K
read_rnd_buffer_size = 128K
myisam_sort_buffer_size = 128K
query_cache_size= 16M
join_buffer_size = 256K
max_heap_table_size = 48M
tmp_table_size = 48M
wait_timeout = 45
interactive_timeout = 300
thread_cache_size = 8
innodb_buffer_pool_size = 3G
innodb_thread_concurrency = 4
character-set-server=utf8
innodb_file_per_table=1
default-storage-engine=MyISAM
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
[isamchk]
key_buffer = 10M
read_buffer = 128K
write_buffer = 128K
[myisamchk]
key_buffer = 10M
sort_buffer_size = 128K
read_buffer = 128K
write_buffer = 128K
Code:
-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.5.30-cll
[OK] Operating on 64-bit architecture
-------- Storage Engine Statistics -------------------------------------------
[--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 169M (Tables: 961)
[--] Data in InnoDB tables: 2G (Tables: 975)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[--] Data in MEMORY tables: 0B (Tables: 6)
[!!] Total fragmented tables: 350
-------- Performance Metrics -------------------------------------------------
[--] Up for: 1h 50m 27s (6M q [927.872 qps], 966 conn, TX: 2B, RX: 1B)
[--] Reads / Writes: 67% / 33%
[--] Total buffers: 3.1G global + 896.0K per thread (25 max threads)
[!!] Maximum possible memory usage: 3.1G (91% of installed RAM)
[OK] Slow queries: 0% (7/6M)
[OK] Highest usage of available connections: 24% (6/25)
[OK] Key buffer size / total MyISAM indexes: 10.0M/46.2M
[OK] Key buffer hit rate: 100.0% (13M cached / 3K reads)
[OK] Query cache efficiency: 34.9% (1M cached / 3M selects)
[!!] Query cache prunes per day: 12858098
[OK] Sorts requiring temporary tables: 0% (1K temp sorts / 295K sorts)
[!!] Joins performed without indexes: 91
[OK] Temporary tables created on disk: 0% (254 on disk / 38K total)
[OK] Thread cache hit rate: 99% (6 created / 966 connections)
[OK] Table cache hit rate: 43% (363 open / 830 opened)
[OK] Open file limit used: 0% (33/11K)
[OK] Table locks acquired immediately: 100% (3M immediate / 3M locks)
[OK] InnoDB data size / buffer pool: 2.3G/3.0G
-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
MySQL started within last 24 hours - recommendations may be inaccurate
Reduce your overall MySQL memory footprint for system stability
Enable the slow query log to troubleshoot bad queries
Adjust your join queries to always utilize indexes
Variables to adjust:
*** MySQL's maximum memory usage is dangerously high ***
*** Add RAM before increasing MySQL buffer variables ***
query_cache_size (> 16M)
join_buffer_size (> 256.0K, or always use indexes with joins)Anything I can possibly do?
