MySQL freezing everyday

[siren@]

Now here is the question, which MySQL is freezing?

Are client sites using MySQL operating properly?

Is Plesk stopping responding?

Which side of the mysql is it and which .ini are you editing?

/swsoft/plesk/databases/mysql/data/my.ini is the client side

/swsoft/plesk/mysql/data/my.ini is the Plesk side.

 

[Toepes]

- Now here is the question, which MySQL is freezing?|
The client side is freezing. Websites using MySQL stop working.

- Are client sites using MySQL operating properly?
This is quite hard to answer. There are several websites using PHPNuke, PHP Nuke Platinum, Invision Powerboard and PHPbb. Only thing I can tell is that everything worked fine when it was running on a Linux server. Now that they are transferred to a clean installed Windowsserver the problem startet.
What I did after restoring all databases is run mysqlcheck –A –r and I noticed some less problems than before.

- Is Plesk stopping responding?
Yes Plesk keeps on working.

- Which side of the mysql is it and which .ini are you editing?
I am editing the client side of My.ini
The pleskside is the default as installed by plesk

 

[siren@]

Statistics itself should not be accessing the MySQL on the client side.

My guess is the problem revolves around the MySQL my.ini settings and databases on the client side. Possibly around the PHP code, maybe even the php connect code used by the sites.

Check traffic on the sites also during the heavy times when you are having issues.

MySQL and PHP are both built and run better on Linux.

Do a full check of all tables and optimize them and see what happens.

 

[siren@]

edit misposte

 

[igoldman]

I can say defiantly that There are alot's of problem with statistics.exe process.
Also I don't understand why SW SOFT doesn't fix it for permanent and we need to suffer.

 

[siren@]

I manage over 15 Plesk Windows Servers and have no problems with Statistics running on any of them.

One of these servers has in excess of 700 domains and over 50GB of site data so how you can say there are problems with statistics itself is beyond me.

Something is wrong in the system, maybe related to statistics, but I highly doubt that is the problem itself if you are running 7.5.x or higher and MySQL client side is crashing.

 

[igoldman]

I would like to bring your attention to the following :

User: XPARTYWSERV\Plesk Administrator
Running task: "C:\Program Files\SWsoft\Plesk\admin\bin\statistics.exe"
Started: Mon Oct 24 05:00:00 2005
The task output is attached to the e-mail Ended successfully: Mon Oct 24 13:17:31 2005


Tell me please ,

how can it be that the statistics take 6 hours ro run !!

 

[Toepes]

Just a short information:

today i have done several times mysqlcheck -A -r

now the cronjob statistics worked 2x and there is no problem. once by hand and once automaticly.

I will let it run now every hour to see what happens.

Who knows .. maybe it was a problem in one of the databases.

Keep you informed

 

[siren@]

Don't just run Mysqlcheck in -A -r run -A -o to optimize them all also.

 

[Toepes]

Just had a hangup. Plesk was still working, client mysql was crashed.

Now i will try to optimize.

fyi 4PSA asked sw to look at this problem as they haven't seen it before

PS> This is the last job:

User: WWW\Plesk Administrator
Running task: "C:\Program Files\SWsoft\Plesk\admin\bin\statistics.exe"
Started: Mon Oct 24 21:00:00 2005
The task output is attached to the e-mail
Ended with code 1: Mon Oct 24 21:05:49 2005

 

[siren@]

Open up Task Manager and look at the mysqld-nt processes. How much ram/processor are they using?

 

[Toepes]

One task is about 24MB the other (i assume it is the one from the sites) was 84MB.
I noticed that the last one sometimes goes up to more than 120MB

This is the my.ini (system has 1GB memory)
(is ini from C:\Program Files\SWsoft\Plesk\Databases\MySQL\Data)

[MySQLD]
port=3306
basedir=C:\\Program Files\\SWsoft\\Plesk\\Databases\\MySQL
datadir=C:\\Program Files\\SWsoft\\Plesk\\Databases\\MySQL\\Data
default-character-set=latin1
default-storage-engine=INNODB
query_cache_size=64M
table_cache=1024
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=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
max_connections=300
key_buffer=48M
max_allowed_packet=1M
sort_buffer=2M
net_buffer_length=4K
old_passwords=1
wait_timeout=120

[client]
port=3306

 

[igoldman]

Hello ,
I didn't say it before , but I have 2 servers which are using Plesk CP , 1 server is dedicated server only for 1 web site and the 2nd server is a shared web hosting server.

the dedicated server is not using mysql , which means that there are no databases.
I have configured the cronjob statistics to run every 5 hours.
let's ask how much ram and cpu the statistics.exe process is using ?

cpu = 45 up to 50 %
ram = 6,608K

I don't know if the mysql is crushing there because there are no mysql databases there so I don't care if the mysql is crushing.


now , let's jump to the 2nd server which is the shared web hosting server for my rest clients.

in this server the mysql is very important because my clients are using mysql databases in there web sites.

hope that this new info will be usable.

thanks to everyone here which helping me resolving this big issue.

 

[siren@]

Toepes Your my.ini looks good. If you can grab something like MySQL Administrator from the MySQL site (or an after market product) and monitor the queries after optimizing during a period that it may crash (i.e. statistics running) I'm guessing you might find a run away querie (if the optimize doesn't fix it.).

igoldman have you tried mysqlcheck -A -r -o?

Try that first and let us know the results.

 

[igoldman]

I have run mysqlcheck -A -r -o

and nothing have been improved

mysql still crush

 

[siren@]

Download MySQL administrator from mysql.com. Monitor the connections and see "who" is connecting, the user name.

You can use this to track which database is causing to many connections.

You can then request them to change or update there software because that is probably the root of the problem.

 

[igoldman]

Hello,
I did exactly what you told me to do.

I have found a user with 250 connection so I have limited him to 1 connection until he will fix his web site.

after that I have started manually the statistics.exe process

and the statistics started to run , I have start to see the logs and in specific log the statistics stuck.

I didn't think twice and I have deleted the logs and started again the statistics.exe process

and what was happen ?

The mysql has crushed

 

[Toepes]

I just noticed something. As stats I use awstats. I use this as default for all websites. Previously PERL was disabled, but this week i enabled PERL so clients coold keep stats fot more than a year, and now statistics.exe crashed !

Now I started Statistics.exe and no problem occured. When i enabled Perl, even backup crashed.

Could Perl have to do something with this ?

 

[igoldman]

Hello,

I have run the statistics again , and found another DB user in mysql which have alot's of connection.
and I found somthing interesting according to siren theory ,
if I will continue to work according to it's theory then I will find my self without customers because I will need to close to every customer his web site otherwise the statistics will not work.

so mabe I need to delete all web sites and then run the statistics.exe process ? !

what do you say ?

so , the DB user which I have limited before he is not the reason why the statistics result in alot's of mysql connection.
probably the reason is because PLESK is very big BUG.
it's a **** of bugs.

people , SW SOFT engineers , in this way we can't run web hosting company.

I now start thinking of moving to hosting controller.

 

[Toepes]

Well i disabled Statistics, and last night the server went down when starting the backup job. Exactly 1 minute afther the backup job startet, mysql freezed. So Statistics probably isn't the problem

So far my idea of statistics being the cause.