MySQL freezing everyday

[adrianodalpra]

Hi,

We got a weird issue with the mySQL here.
It simply freezes everynight.

When we try to stop the mySQL Service it does not stops.
It only happens when I kill the mysqld-nt process.

After it stopped, we start it again and everythink backs to normal.

We stopped all scheduled process that may cause this problem, but mySQL still freezing...

We have a box with Plesk 7.5.4 for Windows running and mySQL 4.1.13a-nt

Anyone got a problem like this or knows how to solve it?

Thanks for help.

Adriano Galle Dal Prá
Advant Tecnologia

 

[torwill]

have you found a solution to this? i am having the same problem. i have tried to optimize the database, disable consistent connection, but neither helps.

 

[Gris@]

It is usually problem of all MySQL servers (not only used with Plesk). I've heard that in the past there were some scripts that were used to automatically bring it up.
Try to reinstall it.

 

[adrianodalpra]

Hi Torwill,

I partially solved the problem by increasing the log size of InnoDB.

But sometimes, now weekely, mySQL still freezing, but a automatic daily reboot solved the problem.

It´s not the ideal, but can mantain your box on-line without disrupting your customers business.

If anyone find a permanent solution for this problem, please notify me.

Note: When you reconfigure InnoDB´s log size on my.ini file, you must delete the old logs, otherwise your mySQL server service will fail to start.

Adriano Galle Dal Prá
Advant Tecnologia

 

[somkiat999]

Dear adrianodalpra,

where i can delete old log file

please you suggest me

thank you so much

 

[somkiat999]

Dear adrianodalpra,

now i can increased log size of InnoDB.

but mysql still down every day

please you suggest

how much for good log file?

 

[siren@]

How about posting your my.ini file from the database directory?

And which MySQL is it? Is it the MySQL for users or the MySQL for plesk?

 

[somkiat999]

Dear silen,

Below is my.ini
my problem about Mysql for user
please you suggest me
thank you so much

[MySQLD]
port=3306
basedir=C:\\Plesk\\Databases\\MySQL
datadir=C:\\Plesk\\Databases\\MySQL\\Data
;character-sets-dir=C:\\Plesk\\Databases\\MySQL\\share\\charsets
default-character-set=tis620
default-storage-engine=INNODB
query_cache_size=8M
open_files_limit=5000
table_cache=32
tmp_table_size=7M
thread_cache=32
myisam_max_sort_file_size=100G
myisam_max_extra_sort_file_size=100G
myisam_sort_buffer_size=2M
key_buffer_size=2M
read_buffer_size=1M
read_rnd_buffer_size=256K
sort_buffer_size=256K
innodb_additional_mem_pool_size=200M
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=10M
innodb_buffer_pool_size=16M
innodb_log_file_size=500M
innodb_thread_concurrency=8
max_connections=9000
key_buffer=4M
max_allowed_packet=1M
sort_buffer=256K
net_buffer_length=4K
old_passwords=1
log=sql.log
[client]
port=3306
default-character-set=tis620
;character-sets-dir=C:\\Plesk\\Databases\\MySQL\\share\\charsets

 

[siren@]

Try setting:

query_cache_size=64M
table_cache=1024
key_buffer_size=32M
read_buffer_size=16M
read_rnd_buffer_size=2M
sort_buffer_size=2M
innodb_additional_mem_pool_size=24M
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=10M
innodb_buffer_pool_size=64M
innodb_log_file_size=10M
innodb_thread_concurrency=8
key_buffer=48M
sort_buffer=2M

And see if that corrects your problem. That is from a server I manage that we optimized the my.ini file on when it was having issues. It has no issues now at all.

 

[somkiat999]

thank you so much larry

 

[cirrus]

Hello All

Did the sugesstions really resolve your issues?

I have been dealing with this issue for a long time as well, and usually what happens is that, IIS, DNS, Mail server and everything else stops responding,

Regards
Cirrus

 

[siren@]

If IIS, your mail server, etc... and freezing afterwards that is not the same problem our client was seeing or, from what he has said, the OP was seeing.

The first step on an issue such as yours would be to check the event log and see if there are any errors and correct them.

 

[Toepes]

Same problem overhere. Myslq keeps freezing every day. Sometimes even several times a day.

Only thing i noticed is that when you open the taskmanager and look at the 2 processes mysqld-nt.exe that they grow in size during the day.
Starting with a total of abour 40mb it grows in a few hours to 120MB total.

I tried several settings for my.ini as proposed on different foras but nothing really helps.

If someone has more suggestions where to look I would appreciate to hear them.

 

[cirrus]

Siren,

I agree, but why as soon as I restart MySQL, everything gets back to normal?

 

[siren@]

First do you have any errors in your Event Manager?

Second have you checked the mysql log files?

Third what are your system specs? If you have MySQL optimized for 2GB ram but only have 1GB that could cause major issues.

 

[cirrus]

Hello Siren,

There is no error in event log, and also since the MySql log file is overwritten today, I can not extract anything now

The server Ram is 1 GB

 

[siren@]

You need to check your settings in your my.ini then because mysql doesn't overwrite it's logs by default. It sets it to an 01, 02, etc... and makes a new one.

 

[igoldman]

Hello,
I have also encounter such problem.
the mysql crush because of the statistics.exe process.

by the way , I have opened a support ticket , and the following is what I have wrote to them:

Hello,
I encounter problems regarding statistics system.
as I know that the file statistics.exe is the system which provide details regarding traffic usage of my clients (domain accounts and clients accounts).
also this system provide statistics information regardind each domain account.
what do I try to say ? I am saying that this statistics.exe is very important file which provide us several details regarding each domain.
we have been encounter for long period problems regarding this statistics system.
sometimes the statistics.exe works good and sometimes the statistics.exe result in stuck in our server.
the strange thing that only web sites which are using mysql databases stop to work.
which means that the mysql which work on port 3306 is hang.
other web sites which are not using MYSQL databases don't stop working and they don't encounter problems because of the statistics.exe process.
The immediately solution we have found is to stop immediately the statistics.exe and after that restart the Mysql process and also the IIS process.
after we are doing the above steps then the web sites which have stopped to work (because the MYSQL on port 3306 is hang ) becoming alive and every thing works good.
but we don't have information regarding domain accounts.

we have decided to investigate the issue , so we have run manualy the statistics.exe which located under %plesk_bin% directory.
after we have run the process we again have encounter the same issue.
but now we have take print screen from our server.
You may look on the print screen we have took from our server on the following links:

http://www.wserv.co.il/statistics1.jpg

http://www.wserv.co.il/statistics2.jpg

by the way , Plesk support team didn't fix the problem.
we still continue to encounter such problem with the statistics.exe process


by the way , the server RAM is 2 GB

 

[Toepes]

Oke i just stopped the cronjob statistics. After your message I noticed that the hangup could be caused by the statistics.

I will keep you informed.

 

[siren@]

Two things I noticed are that in the posted images you appear to be running seperate application pools for all sites. Unless you are running a high end server this is going to bog it down and create issues. You are better off to put all low resource usage sites on shared application pools.

The other thing is you are better off to run statistics.exe every hour. It takes less system resources and time to process 1 hour of log files than 24 hours of logfiles.