cbogdan

Member
Jun 29, 2011
8
0
51
I have a VPS with 1.5G RAM.
I use mysqltunner, this is the output:

Code:
-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB +Federated -InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 150M (Tables: 228)
[!!] Total fragmented tables: 13

-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 1m 59s (2K q [17.151 qps], 106 conn, TX: 11M, RX: 254K)
[--] Reads / Writes: 94% / 6%
[--] Total buffers: 362.0M global + 5.4M per thread (150 max threads)
[OK] Maximum possible memory usage: 1.2G (78% of installed RAM)
[OK] Slow queries: 0% (0/2K)
[OK] Highest usage of available connections: 5% (8/150)
[OK] Key buffer size / total MyISAM indexes: 128.0M/22.1M
[OK] Key buffer hit rate: 96.4% (24K cached / 867 reads)
[OK] Query cache efficiency: 45.5% (731 cached / 1K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (1 temp sorts / 228 sorts)
[!!] Temporary tables created on disk: 38% (117 on disk / 301 total)
[OK] Thread cache hit rate: 92% (8 created / 106 connections)
[OK] Table cache hit rate: 91% (62 open / 68 opened)
[OK] Open file limit used: 1% (116/8K)
[OK] Table locks acquired immediately: 100% (1K immediate / 1K locks)

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries without LIMIT clauses
Variables to adjust:
    tmp_table_size (> 64M)
    max_heap_table_size (> 64M)
I know about 24 hours, but the problem is with temporary tables, and I cant figure out why from two days ago its start to show this increases of tmp_table_size and max_heap_table_size. Everything its happening after I enable eAccelerator.

The actual my.cnf is:

Code:
[mysqld]
log-slow-queries=/var/lib/mysql/slow.log
datadir=/var/lib/mysql
skip-locking
skip-innodb
long_query_time=5
max_connections=150
max_connect_errors=10
max_allowed_packet=32M
interactive_timeout=30
wait_timeout=100
query_cache_size=160M
query_cache_limit=64M
connect_timeout=10
table_cache=4000
thread_cache_size=96
tmp_table_size=64M
max_heap_table_size=64M
join_buffer_size=1M
sort_buffer_size=2M
read_buffer_size=2M
key_buffer_size=128M
thread_concurrency=3

[mysqld_safe]
open_files_limit=8192

[mysql.server]
user=mysql

[mysqldump]
quick
max_allowed_packet=96M

[mysql]
no-auto-rehash
#safe-updates

[isamchk]
key_buffer=64M
sort_buffer=64M
read_buffer=32M
write_buffer=32M

[myisamchk]
key_buffer=64M
sort_buffer=64M
read_buffer=32M
write_buffer=32M

[client]
socket=/var/lib/mysql/mysql.sock
port=5511
And the apache configuration from /usr/local/apache/conf/httpd.conf>

Code:
Timeout 300
TraceEnable Off
ServerSignature Off
ServerTokens Full
FileETag All
StartServers 8
<IfModule prefork.c>
MinSpareServers 5
MaxSpareServers 20
</IfModule>
ServerLimit 256
MaxClients 256
MaxRequestsPerChild 2000
KeepAlive On
KeepAliveTimeout 2
MaxKeepAliveRequests 200
I`m not so expert but unfortunable, I manage the VPS by my own. Please help me with sugestions for, "why the server reboot for 5-10 times per day, every time after a high load avarange, but with no important errors in error_log or other logs".

Thanq you!

Later edit: I have 3 websites on the vps, one of them with an everyday trafic of more than 7000, and the rests under 1000.
 
Last edited:

cPanelTristan

Quality Assurance Analyst
Staff member
Oct 2, 2010
7,606
33
238
somewhere over the rainbow
cPanel Access Level
Root Administrator
How do you know the issue is MySQL specifically? What has led you to that conclusion?

Next, have you checked the /var/lib/mysql/slow.log for slow queries by databases? Have you checked WHM > Daily Process Logs to see if anything stands out there for any specific account and/or page of that account with top processes?

The very best tool available is top which can be used in root SSH when the server load is high to watch for 5-10 minutes to see the highest using processes on the machine when the load is elevated.
 

srpurdy

Well-Known Member
Jun 1, 2011
101
0
66
cPanel Access Level
Root Administrator
You could try installing munin, this might help you track down the exact problem.

And yeah Top is a great tool I use it a lot, you should monitor TOP screen and see what process is causing the load first.
 

cPanelTristan

Quality Assurance Analyst
Staff member
Oct 2, 2010
7,606
33
238
somewhere over the rainbow
cPanel Access Level
Root Administrator
Munin can be server intensive. I would not personally recommend installing it onto a machine that has any load issues, since it will only exacerbate the situation in my opinion.