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

[raj1126]

hello guys,

mysql usage is more than 99%, and site is not loading due to it. I have tried rebooting the VPS, but it soon start using 99% and site again goes down.
[SLIDE]http://lulzimg.com/i9/1d3cc486.png[/SLIDE]

Please help me guys... or please link to some tutorial on web, becoz i tried and didn't find anything. thanks in advance

 

[DXS]

What script are you using for your site?

 

[raj1126]

i'm using vbulletin. VPS is having 512 MB RAM

 

[DXS]

On Line 46 in your config.php change the value from false to true

Before:

$config['Database']['force_sql_mode'] = false;

After:

$config['Database']['force_sql_mode'] = true;

If it isn't that, then you may need to optimize your tables.

 

[raj1126]

thanks for the help DXS
well, after editing the congig.php, the site is loading now but mem% of mysql is still more than 3......

 

[Jack-T]

Incorrect.

thanks for the help DXS
well, after editing the congig.php, the site is loading now but mem% of mysql is still more than 3......

Then values are incorrect. your cpu might be high due to it being a VPS,
Have you tried doing the following?

cat /proc/cpuinfo

Seeing how many cores you have and the hosts CPU.

How many users does your site have?
Are you using a control panel?

 

[Rapid4All]

hmm, try to optimize your my.cnf
nano /etc/my.cnf

If you are not sure about those value, you can use mysqltuner.pl, its not perfect but it gives us good advices

Also, you should read this thread, have some useful infomation there
http://www.wjunction.com/showthread.php?t=12287

 

[l0calh0st]

Try to access phpmyadmin, optimize tables and restart sql server.

 

[Tango]

Buy some more ram

it could be a problem with your host overselling VPS's though

 

[Cheetah]

turn off apache . repair and optimize the database. then enable apache.also optimize ur my.cnf values

 

[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-mysql-performance-with-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

 

[Tango]

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