Mysql problem: too many connections

[CesarV]

Hi. I'm having problems with Plesk 9.2.3 Linux. At any moment the server gives the message: ERROR: PleskFatalException
Unable to connect to database: mysql_connect () [<a href='function.mysql-connect'> function.mysql-connect </ a>]: Too many connections

The configuration of MySQL is as follows:

[mysqld]
set-variable = local-infile = 0
datadir = / var / lib / mysql
socket = / var / lib / mysql / mysql.sock
# Default to using old password format for compatibility with mysql 3.x
# Clients (those using the mysqlclient10 compatibility package).
old_passwords = 1

[mysql.server]
user = mysql
basedir = / var / lib

[mysqld_safe]
log-error = / var / log / mysqld.log
pid-file = / var / run / mysqld / mysqld.pid

set-variable = key_buffer = 512M
set-variable = max_allowed_packet = 1M
set-variable = table_cache 512
set-variable = sort_buffer = 1M
set-variable = read_buffer_size = 1M
set-variable = read_rnd_buffer_size = 12M
set-variable = record_buffer = 1M
set-variable = myisam_sort_buffer_size = 128M
set-variable = thread_cache = 12
set-variable = thread_concurrency = 10
set-variable = max_connections = 20000
set-variable = net_write_timeout = 120
set-variable = connect_timeout = 30
set-variable = wait_timeout = 60

skip-innodb

[mysqldump]
quick
set-variable = max_allowed_packet = 16M

[mysql]

[isamchk]
set-variable = key_buffer = 128M
set-variable = sort_buffer_size = 128M
set-variable = read_buffer = 2M
set-variable = write_buffer = 2M

[myisamchk]
set-variable = key_buffer = 128M
set-variable = sort_buffer_size = 128M
set-variable = read_buffer = 2M
set-variable = write_buffer = 2M

[mysqlhotcopy]
set-variable = interactive-timeout

How can I solve the problem? How many connections Mysql default in Plesk? How do I optimize to resolve once and for all this problem?

Thank you.

 

[IgorG]

Here is default my.cnf for default Plesk installation:

# rpm -qf /etc/my.cnf
mysql-5.0.77-3.el5

# cat /etc/my.cnf
[mysqld]
set-variable=local-infile=0
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1

skip-bdb
log=/var/log/mmsql.log

set-variable = innodb_buffer_pool_size=2M
set-variable = innodb_additional_mem_pool_size=500K
set-variable = innodb_log_buffer_size=500K
set-variable = innodb_thread_concurrency=2
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
skip-bdb

set-variable = innodb_buffer_pool_size=2M
set-variable = innodb_additional_mem_pool_size=500K
set-variable = innodb_log_buffer_size=500K
set-variable = innodb_thread_concurrency=2

 

[CesarV]

Thank you for the return IgorG.

Do you think using the default MySQL will not have more problems or I have to optimize the MySQL?

I would not have to limit the maximum number of connections or is already pre-configured in Plesk?

Did you have any article of Parallels to talk about it?

I worry about this problem of falling due to excess Mysql simultaneous connections. Already raised to 20,000 the number of simultaneous connections and also the problem occurred.

Thanks for your help.

 

[IgorG]

We can only recommend to use default mysql configuration. A lot of hardloaded Plesk servers works now with this configuration without any problem. But of course you can try to optimize your mysql server but only on your own risk. We haven't any special recommendation about mysql optimization. There are common optimization rules can be applied and you can find them on mysql.org, etc. There are can be a lot of reasons why you have 'too many connections' error and I think it is required detailed investigation directly on your server - reading logs, testing, stracing, etc. I can suggest you contact Support Team if you can\t find a reason by yourself or try to use default mysql configuration.

 

[atomicturtle]

You might also want to check out mysqltuner (available in the atomic repo), it will look at your running config and make tuning recommendations, or warn you if you are pushing your config too hard for the resources available.

 

[EduardoSilvestre]

change my.cnf

Hello,

after use change my my.cnf i had a problem with ch_admin_passwd:

export PSA_PASSWORD='test1234567890'; /usr/local/psa/admin/bin/ch_admin_passwd
ch_admin_passwd: Service /etc/init.d/mysqld failed to stop
ch_admin_passwd: Service /etc/init.d/mysqld failed to stop
ch_admin_passwd: Can't stop special MySQL daemon
ch_admin_passwd: Can't stop special MySQL daemon

Any doubt?

 

[korsar]

Hello,

after use change my my.cnf i had a problem with ch_admin_passwd:

export PSA_PASSWORD='test1234567890'; /usr/local/psa/admin/bin/ch_admin_passwd
ch_admin_passwd: Service /etc/init.d/mysqld failed to stop
ch_admin_passwd: Service /etc/init.d/mysqld failed to stop
ch_admin_passwd: Can't stop special MySQL daemon
ch_admin_passwd: Can't stop special MySQL daemon

Any doubt?

It's strongly not recommended to use Plesk backend utilities to do any actions, because of there is no anyguarantee that it will not change behavior in the next releases.

To change admin password - RECOMMENDED way is:
#/usr/local/psa/bin/init_conf --update -passwd newpasswd

SUCCESS: Update of administrator's information complete.

# cat /etc/psa/.psa.shadow
newpasswd

If your Plesk instance is not configured yet you can use --init command instead of --update to perform initial configuring of it and set an admin password at the same time:

#/usr/local/psa/bin/init_conf --init -passwd newpasswd

 

[korsar]

Hello,

after use change my my.cnf i had a problem with ch_admin_passwd:

export PSA_PASSWORD='test1234567890'; /usr/local/psa/admin/bin/ch_admin_passwd
ch_admin_passwd: Service /etc/init.d/mysqld failed to stop
ch_admin_passwd: Service /etc/init.d/mysqld failed to stop
ch_admin_passwd: Can't stop special MySQL daemon
ch_admin_passwd: Can't stop special MySQL daemon

Any doubt?

It's strongly not recommended to use Plesk backend utilities to do any actions, because of there is noguarantee that it will not change behavior in the next releases.


To change admin password RECOMMENDED way is:
/usr/local/psa/bin/init_conf --update -passwd newpasswd

SUCCESS: Update of administrator's information complete.

[root@a10-52-37-57 ~]# cat /etc/psa/.psa.shadow
newpasswd

If your Plesk instance is not configured yet you can use --init command instead of --update to perform initial configuring of it and set an admin password at the same time:

[root@a10-52-37-5/usr/local/psa/bin/init_conf --init -passwd newpasswd