How to Create Connection Pool

How and What you need to create a MySQL pool

[tpatek]

My site isn't working very well at all because I keep getting MySQL socket errors. Someone suggested to create a connection pool. I do believe that creating a mysql connection pool would help out my site vastly, as well as make it run faster considering I have at least 300 MySQL connections consecuatively to 1 database. The problem is that I have no idea how to create a MySQL connection pool.

If you could please help me and tell me what I need to do, and what software I need on my server that would help me out a ton.

Currently, I am running Plesk Reloaded 7.5

Thanks a Million - Truly

Tom

[FalseDawn]

socket errors are more indicative of memory or configuration problems than problems with running out of connections - otherwise you would get a "max connections exceeded" type error

If you indeed have 300 users connecting simultaneously, and a maximum setting of 300 connections, then connection pooling will not help.
Connection pooling is meant to provide efficient re-use of connections - it will not increase the maximum you have available.

300 simultaneous connections is a _lot_
What hardware are you using - RAM, CPU etc?

Have you monitored your server load?, Been checking logs etc?

[tpatek]

I got this info from Plesk:

CPU GenuineIntel, Intel(R) Pentium(R) 4 CPU 3.00GHz
Version psa v7.5.4_build75050926.17 os_FedoraCore 2
OS Linux 2.6.14-051115a

CPU usageLast 1 minute 5.33
Last 5 minutes 8.47
Last 15 minutes 16.06

And I have 2GB of RAM

any idea what the problem could be?

Also, my.cnf looks like this:

[mysqld]
safe-show-database
innodb_data_file_path=ibdata1:10M:autoextend
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
set-variable = max_connections=500
set-variable = max_connect_errors=10000
set-variable = wait_timeout=30
set-variable = interactive_timeout=30
set-variable = thread_cache=40
set-variable = back_log=500
skip-locking
skip-name-resolve

[FalseDawn]

Your load figures are very high.
Is this your own dedicated machine or a VPS?

Try running "top" from a shell and see what processes are using the most resources. My guess is that mysql will be using most of it.

Without knowing exactly what applications and scripts you are running, it's difficult to suggest effective optimizations.
Some suggestions might be a php accelerator like eaccelerator or apc, tweaking your mysql configuration file (this will involve a more in-depth understanding of your mysql server stats and the effect that tweaking parameters can have - too complex to go into here).

At the end of the day, the hardware may well not be up to the task and you might simply need to upgrade!

[aspdaddy]

The connection pool will reduce the load because it intercepts all the calls to the constructor/destructor. 300 Connections might not exceed your max limit but the resources used creating and destroying all those connections will kill the machine. Check out you webserver/OS documentation, You can set it up easily in Windows & Apache. You might wanna look at SQL Relay for Linux.