MySQL freezing everyday

[Support-MK]

Please make sure that you have both Plesk (8306 port) and user side (3306 port) MySQL services upgraded. And also, please note, that this issue is not fixed fully yet. Yes, it should not be an issue for most of us any more, but in rare cases it can reoccur again.

If both services are upgraded - then most probably you have another issue doesn't mentioned here.

 

[budwaiser]

I have 4.1.12. In which way i can upgrade Mysql?

Please help me

Thank you

 

[Rubal]

Have a look the the KB Article:

http://kb.swsoft.com/article_147_1077_en.html

Thanks

 

[noslzzp]

Hey gang,

We were experiencing this problem under Linux. We don't think the problem lies with the server or the mysql configuration directly.. We believe the problem lies with the php.ini file and its setting for persistent connections.

If you host a lot of sites, more than likely you will have customers that have poorly written PHP applications and code that don't properly close db connections, etc. What will happen over time is that the number of open connections will grow to reach the maximums set by both the php.ini file and the my.conf files.

If the maximums are reached by the php.ini file, PHP related applications will start to fail on connection. As a test in your debugging, ask yourself which specific app/server caused the error.

If the maximums are reached by the my.conf you will see failures in system level applications. Again, if you notice an error, see if it was system service or a web application.

Typically, all you would need to do is turn off persistency in php.ini since this is where most of your database connectivity will originate from. You will probably rarely run into a maximum connections issue at the system level - especially if you've already set the my.conf connection max to some high number.

So, in your php.ini file, just set:

mysql.allow_persistent = Off

and then simply restart the web server. Restarting the web server will drop all those unclosed connections to mysql.

My guess is that the mysql team added code in later releases that cut down on idle persistent connections - which is why some of you my have a working solution with the database upgrade.

Hope this helps.

 

[dinfiesta]

Originally posted by kawanda
Okay
So I'm not the only one with this problem eh?

I'm still looking for a solution to this buzzle!

The server specs are:
Pentium 4 2.8GHz
1GB RAM
2x80GB

Around 1 month ago I contacted SWsoft about this issue, and they told me this:



And my reseller didn't know what the hell is going on (at the time of the post,) so I just disabled the statistics.exe task from the task manager, and I was until now running the task manually to keep an eye on the MySQL process and make a move if needed!

I installed "MySQL Administrator", and by monitoring the server I noticed that the number of connections increase steady before the MySQL server hangs!

Also I noticed that you can't restart the MySQL service unless you kill the process...

And this is a copy from my.ini

[MySQLD]
port=3306
basedir=C:\\Program Files\\SWsoft\\Plesk\\Databases\\MySQL
datadir=C:\\Program Files\\SWsoft\\Plesk\\Databases\\MySQL\\Data
tmpdir=e:\\temp\\
default-character-set=latin1
default-storage-engine=INNODB
query_cache_limit=1M
query_cache_size=32M
query_cache_type=1
table_cache=1024
tmp_table_size=7M
thread_cache=128
myisam_max_sort_file_size=100G
myisam_max_extra_sort_file_size=100G
myisam_sort_buffer_size=2M
key_buffer_size=16M
read_buffer_size=2M
read_rnd_buffer_size=256K
sort_buffer_size=2M
join_buffer=1M
innodb_additional_mem_pool_size=2M
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=1M
innodb_buffer_pool_size=16M
innodb_log_file_size=10M
innodb_thread_concurrency=8
max_connections=300
key_buffer=128M
max_allowed_packet=17824768
sort_buffer=2M
net_buffer_length=8K
old_passwords=1

innodb_table_locks=1
innodb_lock_wait_timeout=30 

connect_timeout=10
interactive_timeout=60
wait_timeout=60

[client]
port=3306

[mysqldump]
quick
max_allowed_packet=16M

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

[mysqlhotcopy]
interactive-timeout

I'll continue on my experiments and see if siren method would fix this EVIL problem....

I will try this config...I have same issue.

 

[orkunerbil]

I know it is not a solution for the problem, but it is bad for a company to have a customer calling the company and saying your mysql is down. So what you may do...

1)Find services from Administrator Tools on 2003.
2)Find Mysql Service on the list.
3)Click on recovery.
4)Select restart the service at the first and second failure, I would advice you to leave the third failure as take no action, so you may understand that your mysql freezes more then twice in the period of time you choose.
5)Write 2 days for reseting the counter.(That's my choice, write what you want.)
6)Write one minute for the waiting period before restarting the service.

 

[hd2002]

Did anyone get below error in your event view when u suffer the plesk hang.


Warning message 1

Event Type: Warning
Event Source: IISService
Event Category: None
Event ID: 1013
Description: A process serving application pool 'DefaultAppPool'
exceeded time limits during shut down. The process id was 'ProcessID'.
Warning message2

Event Type: Warning
Event Source: IISService
Event Category: None
Event ID: 1010
Description: A process serving application pool 'DefaultAppPool' failed
to respond to a ping. The process id was 'ProcessID'.


Cheers,

HD

 

[Shazan]

I've upgraded MySQL to 4.1.21 and disabled pconnect but the problem still persists... Has someone been able to fix it?

 

[greg mandel]

to whom it may concern on this ticket.
i have had this problem on plesk for windows server for a long time. it is very frustrating. there are no easy or miracle answers, either.
.ini files, .cnf files., php. ini files. there are just no easy answers. we've tried them all. done a ton of reading and found posts and things on the internet saying what the problem is, and the fix... and, they aint.

SW tries to help but as of yet no cures. in fact once you get their attention, SW always tries to help out regardless of issue, so in this regard i cannot knock them. i sure wish there was an answer to this problem, however.

if anyone has that magical cure, you be sure to let me know....
my thought is if you think you have the cure, you probably don't. we upgrade version of 4.x and that didnt help either.

personally i never experienced this kind of problem on servers not running vz or plesk. however that isnt to say that servers without vz or plesk don't exhibit the problem, either. just my owen experience with mysql on wink2 and win2k3 servers.

those having the problem you are warned. there is no easy answer except maybe start offloading php/mysql sites to a more lightly loaded node whether you think you should have to or not.

hope it helps.

p.s. somewhere in this thread aw stats calc. was intertwined. i'll attest these servers have troubles completing aw calc, for sure, when domains start adding up on a server. we have more than one plesk in windows server to observe the same problems over time. it takes a long time to calc, and eventually won't complete as time goes on. we turned them off to save the serever the resource and offer something else now upon request. again, never had this problem prior to vz/plesk server with AW, however we dont like AW or webalizer anyway, so no great loss.

 

[beginner]

Please help me !

Hi all!

I own a small dedicate server with Microsoft Windows Server 2003 and:

CPU: Pentium 4 Xeon 3,0 G
Memory: 256 MB


I installed a board within over 4OOO member and 75000 Post with ~ 400 Members online in same time.

Could you tell my how to config the my.ini file for better running ?

Thanks !

 

[aaronsinclair]

How I fixed mysqldump.exe and mysql hanging along with mysql too many processes.

My .02c

Here's what i've done of 5 of my windows plesk servers to resolve this issue.

Too many connections. Edit c:\windows\php.ini
Persistant connections Off

Edit %plesk_dir%/databases/mysql/data/my.ini and set num connections to 300-500 depending on server specs.

Here's the killer. When a customer schedules a site backup thorugh their control panel it executes mysqldump.exe wich hangs and in turn causes Plesk CP mysqld-nt.exe process to stop responding as well. in turn IIS, FTP, SMTP all stop responding as well.

Killing mysqldump.exe generally allows me to restart mysql processes, however on some occasions I must kill mysqld-nt.exe processes as well.

This ALWAYS happens shortly after 12.00PM when I have just got off to sleep and is caused by the customer scheduled backups.

I have disabled customer scheduled backups as follows.

Start > Run > regedit
HKEY_LOCAL_MACHINE > SOFTWARE > PLESK > PSA Config > Config
Select SystemBackupEnabled key and set value data to 0

(The above location is is for 7.5.x, older vers are slightly different).

Hope this helps someone,

 

[hellfish]

Restart the service

How about scheduling a restarting of the MySQL service every night or every two nights?

Wouldn't that take only a few minutes and kill all of the active connections, making a fresh start again?

I'm trying to find how to do this using Plesk Scheduler but I would like to hear some comments about this.

 

[jorisw]

Upgrading to MySQL 4.1.22 fixed the problem for me.

Best Regards
Joris Willekens

 

[greg mandel]

Set persistent connections to off in .ini file:
Never tried that one I don't think.

Restarting MySQL on a routine basis. Thought of it, and if poster still needs it:
You can I think schedule this in Task Scheduler using a .cmd batch file that uses net start/stop, since MySQL for both Plesk and web sites is registered as Windows Services. I myself don't like the idea, however it is easy to do if you want to try it, and net stop might kill mysql response and than hang but not stop, which is classic MySQL behavior in Windows OS. I wouldn't do it, and if I did, not for Plesk MySQL.

Upgrading version:
We tried that on recommendation from Plesk. Didn't help.

I find that MySQL performance on our Plesk in VPS servers is very sensitive to CPU utilization, and subject to getting stuck during periods of moderate or high CPU utlization. In fact I find our Plesk in VPS servers performance sensitive to CPU utilization in general. Much more so than our pre Plesk/VZ servers in the past. Just more so for MySQL. MySQL seems to be low on the totem poll to receive allocation of CPU time among all the other services. Or maybe another resource I am not seeing. Further, the server that is busier has more problems with MySQL than the other. Seems to fit as well or better than anything else I have observed, or tried/.

 

[cygnuzz]

After upgrade my plesk mysql to mysql 4.1.18, the new problem appear, plesk miscellaneous service sometimes failed to run with message "Unhandled exception

Execute file name: ", and after that domainmng.exe and usermng.exe failed to run also, causing some domain can not be accessed. Please enlightened me, what kind of problem now that I'm dealing with

 

[igoldman]

You need to upgrade MYsql to latest version.

The current latest version is:
4.1.22

Also You need to setup the file my.ini correctly otherwise there are going to be problems.

How to configure my.ini ? You need to learn the basic of mysql.

http://dev.mysql.com/downloads/mysql/4.1.html

you need to download : Without installer (unzip in C:\)

The 42.5M file.

by the way, There are 2 instance of MySQL.
The 1st is for customers databases.
The 2nd is for Plesk internal database

 

[Shazan]

I don't believe this has something to do with the MySQL version.

I've upgraded to 4.1.22 and then 5.0.27 and the problem isn't fixed yet.

 

[igoldman]

Trust me.
Use MySQL version 4.1.22 for MYsql instance which is for customers databases. Also configure correctly the configuration file (the file calles my.ini).

And the issue will stop.

Until you don't configure correctly my.ini the issue will continue.

How to configure correctly ? you need to learn the basics of mysql.

if you are not professional then you may order professional services from us.

 

[Shazan]

I appreciate your help igoldman but I've DOZENS of Plesk servers and the problem arises only on one of them.

my.ini IS optimized, trust me. I've also tried one of them copied from a working server and it didn't fix the issue.

And as I've already said, the upgrade to 4.1.22 didn't fix it too.

If you have a "magic" my.ini, post it here please, but I am sure you had to do with a different issue than mine (and most of others).

 

[igoldman]

Originally posted by Shazan

my.ini IS optimized, trust me. I've also tried one of them copied from a working server and it didn't fix the issue.

Read the following books:
http://www.oreilly.com/catalog/hpmysql/
http://www.oreilly.com/catalog/mysqlckbk2/

After that you will have the possibilty to say that your my.ini is optimized.

Originally posted by Shazan

And as I've already said, the upgrade to 4.1.22 didn't fix it too.

OK. Keep using that version.
Also You need to optimize all your costomers databases.
There is a command which you may run and it will optimize all databases at once.

Originally posted by Shazan
If you have a "magic" my.ini, post it here please, but I am sure you had to do with a different issue than mine (and most of others). [/B]

I can't provide professional services for free. SWsoft doesn't provide even support for free. So why me ?

Also , each server required customized my.ini that depends on the load of the server and many others parameters.