Persistent connections use tons of memory...

Anon

Hi,

We run a PHP/MySQL site on a Linux Red Hat Server. Dual 500MHz, 512 MB Ram.

Once we start getting 200+ concurrent MySQL connections, the load shoots up (sometimes over 10) and pages take 10 seconds + to generate, when they normally take < 1 second. (Sometimes, it takes minutes to generate the pages).

When I do a ps, each MySQL connection is using 3% or more of memory.

Would we be better of using non-persistent connections in this instance, or is this behavior unusual and fixable?

Anon

You are probably better off REDUCING the maximum number of connections. The rational is this: If your pages generate quickly when there are a few connections, it doesn't matter what the max is. If you have a lot of access and it takes a long time, the time could be shortened by waiting for an existing connection to become available, rather than flooding the system.

Good luck.

Anon

If you are at the max concurrent connections limit, doesn't mysql simply deny access and send an error? How do you wait for a connection to become available?

You may want to examine just how long your queries actually take.
If a query takes 0.2 seconds at 1 user, logic dictates it will have to take at least 50 times that seconds at 100 users. (given that the dual processors do their job properly) In practice it will be even slower.

If you have many records, or you do lots of joins, you'll need tons of memory.
It may well be that your current memory settings are too small.
Do you have proper indexes on your tables?

In short, take a closer look at just how your database is performing.

Anon

You may be running out of file handles. If that happens your system will thrash and slow to a crawl.

What OS are you running? linux? If so, you can check the number of file handles in use by doing

'cat /proc/sys/fs/file-nr'

The first number is the max used in a long while, the second is the current number used, and the third number is the maximum your system can handle.

You can change it, as root, if you do a

'echo xxxx >/proc/sys/fs/file-max'

where xxxx is the new max you want. 20000 or so should be enough.

while you're there, look at /proc/sys/fs/inode-nr and inode-max

Anon

Hey,

Thanks everyone for replying.

cat /proc/sys/fs/file-nr
2547 1671 4096
cat /proc/sys/fs/inode-nr
16410 4507
cat /proc/sys/fs/inode-max
16384

So is this an inode problem???

If so, do I fix it the same way as the file max?

Anon

Yes, the fix is the same. You can add it to the end of your /etc/rc.d/rc.local file (for most linux distros) or whatever the equivalent is. Note that once you free up enough inodes, you may run out of file handles next, so look to add two lines, one for inodes and one for file handles

echo 65536 >/proc/sys/fs/inode-max
echo 65536 >/proc/sys/fs/file-max