ART's MySQL v.4 using YUM faulting & restarting on Plesk

General Discussion of atomic repo and development projects.

Ask for help here with anything else not covered by other forums.
perfectpenguin
New Forum User
New Forum User
Posts: 4
Joined: Fri Jun 03, 2005 10:32 am

ART's MySQL v.4 using YUM faulting & restarting on Plesk

Unread post by perfectpenguin »

Hi,

In the absence of my more senior/knowledgable colleague I am left in a difficult situation where our Webserver has recently starting having a problem with the MySQL service and is affecting log-ins and POP3 connections to users mailboxes etc.

It's restarting approximately 3 times a day and symptoms include messages on the console login screen to the effect of 'too many connections' etc.

Has anybody else seen this and can you help stop it from occuring?? Your help would be MUCH appreciated!

Thanks.
scott
Atomicorp Staff - Site Admin
Atomicorp Staff - Site Admin
Posts: 8355
Joined: Wed Dec 31, 1969 8:00 pm
Location: earth
Contact:

Unread post by scott »

Yes, you've got too many connections. :P Entropy being what it is, youve grown past the default number of connections. You need to increase how many mysql will allow (note, this of course means you're moving the problem, now you'll get a performance hit). Set max_connections=X in my.cnf, where X is how many connections you'll allow.
perfectpenguin
New Forum User
New Forum User
Posts: 4
Joined: Fri Jun 03, 2005 10:32 am

Unread post by perfectpenguin »

Ah ha, probably one of the most simple things then...I hope. Thanks for that, I'll look into the Max Connections settings.
CBiLLL
Forum User
Forum User
Posts: 14
Joined: Sat May 21, 2005 9:21 am

Unread post by CBiLLL »

I am having simlar problem since I have set up a php forum that uses mysql and my email would act like server is down or something but clicking on recieve email again would get the email ..

Also some people saying that the new php forum seem alot slower on dial up than the old cgi forum ..

I have Art Mysql 4.0+ (lastest version) installed and looked into the /etc/my.cnf but I didn't see the Set max_connections=X in there.

Do I just add a line Set max_connections=X and how much should I set it for? What was the default connections?

This is what in my.cnf right now

Code: Select all

[mysqld]
innodb_data_file_path=ibdata1:10M:autoextend
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
     
[mysql.server]
user=mysql
basedir=/var/lib

[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
I have a dual 1.4G mhz running FC1 with about 1 gig of ram server at my provider office.. what max setting or any other setting do you recommand I should have for a system like this?


I am kinda a newbie at mysql but a fast learner 8-)


Thanks
Bill
scott
Atomicorp Staff - Site Admin
Atomicorp Staff - Site Admin
Posts: 8355
Joined: Wed Dec 31, 1969 8:00 pm
Location: earth
Contact:

Unread post by scott »

I believe the default is 20, so you could double it to 40 or so. Just keep in mind that setting it too high can have a negative impact on performance. Also looking at your config you'll also get a really big improvement by turning on query caching with the following:

[mysqld]
query_cache_type=1
query_cache_size = 32M
CBiLLL
Forum User
Forum User
Posts: 14
Joined: Sat May 21, 2005 9:21 am

Unread post by CBiLLL »

Thank you for the suggestion!

I'll go put it in my.cnf and see how much improvement.

The site I am trying to improve is http://www.findmall.com but I'll double it for now and give it a few days testing to see if I get any postive or negtive input from the users and add that cache string that you posted too! ..


If any other suggestion I am all ears and willing to try it out ..


Thanks Scott!

Here what I have now just to be sure I did it correctly .. let me know if I made a mistake please.

Code: Select all

[mysqld] 
innodb_data_file_path=ibdata1:10M:autoextend 
datadir=/var/lib/mysql 
socket=/var/lib/mysql/mysql.sock
Set max_connections=40
query_cache_type=1 
query_cache_size = 32M
      
[mysql.server] 
user=mysql 
basedir=/var/lib 

[safe_mysqld] 
err-log=/var/log/mysqld.log 
pid-file=/var/run/mysqld/mysqld.pid 


Thanks again!,

Bill
CBiLLL
Forum User
Forum User
Posts: 14
Joined: Sat May 21, 2005 9:21 am

Unread post by CBiLLL »

I just firgued out that Set max_connections=40 is not suppose to go there

oops!

I couldn't start mysql back up .. where am I suppose to adjust this or add this max connection at please for mysql?

Thanks!
Bill
scott
Atomicorp Staff - Site Admin
Atomicorp Staff - Site Admin
Posts: 8355
Joined: Wed Dec 31, 1969 8:00 pm
Location: earth
Contact:

Unread post by scott »

you just want:
max_connections=40

the set syntax is for mysql 3.x I believe
CBiLLL
Forum User
Forum User
Posts: 14
Joined: Sat May 21, 2005 9:21 am

Unread post by CBiLLL »

Thanks...

Trying it out now ..


Bill
phatPhrog
Forum Regular
Forum Regular
Posts: 118
Joined: Fri Feb 04, 2005 6:02 pm
Location: S.E.U.S.
Contact:

Please explain these

Unread post by phatPhrog »

Just wondering. Does your yum install of mysql 4.0.25 rewrite the my.cnf?

Reason: My current my.cnf since upgrading to 7.5.4 FC2 server.

Please note max_connections=500 - overkill, isn't it? (please don't misunderstand. I'm not saying your rpm's did this. the my.cnf was never changed in any previous update/install. just wondering how the current my.cnf was configured with these settings as we didn't make these changes)

Code: Select all

[mysqld]
safe-show-database
innodb_data_file_path=ibdata1:10M:autoextend
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
skip-networking

connect_timeout=15
interactive_timeout=100
join_buffer_size=1M
key_buffer=256M
max_allowed_packet=16M
max_connections=500
max_connect_errors=10
myisam_sort_buffer_size=64M
read_buffer_size=2M
read_rnd_buffer_size=2M
sort_buffer_size=2M
table_cache=1024
thread_cache_size=100
thread_concurrency=4
wait_timeout=300
query_cache_size=128M
query_cache_limit=1M
query_cache_type=1
[mysql.server]
user=mysql
basedir=/var/lib

[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
scott
Atomicorp Staff - Site Admin
Atomicorp Staff - Site Admin
Posts: 8355
Joined: Wed Dec 31, 1969 8:00 pm
Location: earth
Contact:

Unread post by scott »

No it doesnt, at most it might create a file called /etc/my.cnf.rpmnew. 500 is definitely overkill. I typically put mine around 40-50
Post Reply