MySQL freezing everyday

[cirrus]

To All

This issue has been left unresolved for me so far,
and I'm now almost confident, it is being caused by MySQL version used in plesk.

The updated release of MySQL addresses these issues as it seems,

Regards

 

[siren@]

You can upgrade the non-plesk MySQL simply by installing the new version.

 

[igoldman]

it doesn't help
I have deleted all the big log files (log files which are more then 10 MB)

and the problem still exist

the mysql hang ( I am talking about the mysql for client which run on port 3306)

also , I publish the my.ini mysql configuration settings which I used on my server:

[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 = 768M
query_cache_type = 1
query_prealloc_size = 16384
query_alloc_block_size = 16384
table_cache = 1500
tmp_table_size = 128M
thread_cache_size = 128
myisam_max_sort_file_size=100G
myisam_max_extra_sort_file_size=100G
myisam_sort_buffer_size=64M
key_buffer_size=2M
read_buffer_size=1M
read_rnd_buffer_size = 524288
sort_buffer_size = 2M
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=999
key_buffer=96M
max_allowed_packet = 16M
sort_buffer=256K
net_buffer_length=4K
old_passwords=1
join_buffer_size = 1M
wait_timeout = 900
connect_timeout = 10
max_connect_errors = 10
bulk_insert_buffer_size = 8M
[client]
port=3306

 

[siren@]

igoldman

What are your server specs?

 

[igoldman]

my server specification is:

CPU : Pentium 4 3000 MHZ socket 775 , 1 MB cache
Motherboard : ASUS
Memory : 2 GB , Kingstone DDR 400 MHZ
HD : SATA

 

[siren@]

Well, lets take a look at your my.ini here:

query_cache_size = 768M

Overall I would say just replace your my.ini with:

[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=32M
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=5M
sort_buffer=2M
net_buffer_length=4K
old_passwords=1
[client]
port=3306

And your problems will go away. You area allocating over 1/2 your system to mysql based off what you listed. That is not the way to be running it. I'm not even sure you would want to allocate 768MB to mysql on a 4GB system.

 

[igoldman]

Hello siren ,

I would like to ask how can I run the statistics.exe every hour if it

takes 5 hours for it to finishing the statistics processing.

I host 80 domains accounts in the same server.

also , I would like to ask you what is your suggestion for the log files ?

what to do with the rotation of log files ?

 

[siren@]

Let me explain this easy.

Run once every 24 hours you are processing 24 hours of log files

Run once ever hour you are processing 1 hour of log files.

So you are processing 1/24th the data every hour than you when running it every 24 hours.

As for log files Plesk processes them adds them to the the logs inside plesk.

To be honest though, for those running my.ini files like the one posted about with 768MB to query caching on a 2GB system the problem probably has nothing to do with MySQL but has to do with Memory issues.

 

[igoldman]

Hello siren ,
first of all thank you very much of trying helping me.

I have run the statistics.exe process in 12:00 PM (20 min ago)

and the MYSQL is hang , stop working.

for example I get from 1 web site which use mysql database the following error msg :

mySQL error: Too many connections
mySQL error code:
Date: Sunday 23rd of October 2005 12:17:57 PM

so , as I understood that the statistics.exe process is using alot's of connection.

in my.ini file , the following is configured :

max_connections=300

 

[siren@]

Raise your max connections to 500.

 

[igoldman]

I have raise it to be 600

and still there are problems

also , I have configured it to run every 5 hours

say the true , I realy disappointed from SW SOFT software engineers.

if I can't leave my personal computer and every time be in ready that the mysql will not stop working then I am realy in bad situation and I can't continue with this.

the problem is so bad that I need to take for every place I go a laptop and 3rd generation of modem with fast internet connection and to have the ability that if a problem will happen then I will be able to handle it.

also , I can't sleep , my clients call to me in the night that the mysql stop working.

 

[siren@]

Have you talked with SW-Soft support? I work on a variety of servers and haven't had these issues.

In fact the servers run well and I sleep well at night, well most nights.

I take a laptop with me everywhere, but that is because I do server administration, not because I'm worried about a site going down.

 

[Toepes]

i just started statistics.exe by hand and mysql crashed. I assume there must be a reason for that.

Normally mysql quits several times a day, now that i disbabled statistics the server continued working. When I started statistics.exe mysql froze after maybe 6 minutes.

 

[siren@]

First, what version of Plesk?

Secondly no one has said they have attempted to contact SW-Soft Support or an outside support option.

There is only so much I can say as I'm not looking at the server itself.

What I do know is the multiple servers I manage none of them have this problem.

 

[Toepes]

Version 7.5.4 for windows
Server is running windows 2003
1GB mem
3GHz.


Regarding support I just put a message to 4psa.

 

[resellertr]

we have same error on plesk 7.0.3 and 7.5.4. statistics.exe causing this problem.

I checked there are many mysql connection belongs to user, admin. I had to setup max connection limit to 1000 but mysql is still freezing.

 

[siren@]

I just realized what seemed wrong about this.

Are you running 2 MySQL servers or 1? Plesk as of 7.5 is designed to run one database for Plesk and one for the users.

Also post the version from the statistics file (right click properties then you can view the version).

 

[resellertr]

For Plesk 7.0.3;

there is only one mysql mysql "too many connection" error is a big problem for us.

For Plesk 7.5.4;

there are two mysql, but server is slowing because of this error.

 

[siren@]

You still didn't post version of statistics.exe.

 

[Toepes]

Statistics version 7, 5, 4005, 0

There are 2 running Mysqld processes. on 1 server.