mysql usage 99.8%|Site Not Loading|Please help to optimize

[raj1126]

Edited the my.cnf file and now getting database error....... can someone help. Please
http://aiorapid.com/

[Rapid4All]

Did you restart ? PLease paste your my.cnf here

[BlaZe]

Use MySQL Tuner

Simple steps here : http://www.howtoforge.com/tuning-mys...ith-mysqltuner

[raj1126]

@Rapid4All
bro, please give me your my.cnf and httpd.conf file in text format. Please. I'll will simply copy paste the content. here is my.cnf from my VPS

[mysqld]
max_connectios = 400
key_buffer = 64M
myisam_sort_buffer_size = 32M
join_buffer_size = 1M
read_buffer_size = 1M
sort_buffer_size = 2M
table_cache = 4000
thread_cache_size = 286
interactive_timeout = 25
wait_timeout = 7000
connect_timeout = 10
max_allowed_packet = 16M
max_connect_errors = 10
query_cache_limit = 2M
query_cache_size = 12M
query_cache_type = 1
tmp_table_size = 16M
skip-innodb

[mysqld_safe]
open_files_limit = 8192

[mysqldump]
quick
max_allowed_packet = 16M

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

[Mr Happy]

Everyone's looking at this from the mysql settings so I'll try and find the cause of the problem as it sounds like this happened rather suddenly and hasn't built up over a day or two. vBulletin has hundreds of very poorly coded plugins available. It could be one of these that's eg stuck in a loop or something that's eating your usage.

Go into your vBulletin config.php file and add at the very end of it just before the ?> add

define('DISABLE_HOOKS', true); 

This will turn off all your plugins, products and everything else you've added to vbulletin. If this solves your problem then you know it's a plugin that's causing a problem.

In your ACP Turn them all off and then remove the line of code from the config.php. Turn them back on one at a time watching the site to see what happens. When the memory jumps again then you'll find the cause of the problem.

This may not fix the problem as we still don't know your site or how much traffic you get.

edit:

just saw you did post your site aiorapid.com and it's highly unlikely to be caused by an increase in traffic. Find the bug like I suggested. Optimising your mysql won't do anything to get rid of the bug.

[Rapid4All]

You cant use my configuration because have many differences there.

with 512 Ram, I suggest you change :
max_connections = 100;
key_buffer_size = 16M;
table_cache = 512;

Change then restart your mysql server.

[raj1126]

my site is Aiorapid.com
and its traffic is 5k UV

Now, i'm naot able to login in my Kloxo panel, it also giving SQL DB error. Please help. Please give me the default content of my.cnf and httpd.conf

[Gavo]

if you messed up my.cnf rename it to get the service running again, the fix the file and rename back

mv /etc/my.cnf /etc/my.cnfOLD

[Dione]

[mysqld]
datadir =/var/lib/mysql
socket =/var/lib/mysql/mysql.sock
log_slow_queries =/var/log/mysql/mysql-slow.log
port = 3306
skip-locking
max_connections = 200
key_buffer = 16M
myisam_sort_buffer_size = 32M
table_cache = 1024
thread_cache_size = 512
join_buffer_size = 2M
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 512K
net_buffer_length = 256K
thread_stack = 256K
tmp_table_size = 16M
query_cache_limit = 2M
query_cache_size = 16M
query_cache_type = 1
thread_cache_size = 256
thread_concurrency = 4
long_query_time = 2
skip-bdb
skip-innodb

[mysqldump]
quick
max_allowed_packet = 32M

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

[mysqld_safe]
open_files_limit = 8192

[isamchk]
key_buffer = 16M
sort_buffer_size = 16M
read_buffer = 16M
write_buffer = 16M

[myisamchk]
key_buffer = 16M
sort_buffer_size = 16M
read_buffer = 16M
write_buffer = 16M

[mysqlhotcopy]
interactive-timeout

[client]
socket=/path/to/mysql.sock


Try this for 512 Memory