>>>>> "Ozlinx" == Ozlinx <Opec> writes:
Ozlinx> My question are:
Ozlinx> 1) What settings should I have for MySQL to handle a large amount
Ozlinx> simultanous connection.
>> It depends on what the connections are doing. If they are mostly
>> sleeping then 'max_connections' is good enough. If not, then it
>> depends on your queries. (Your current setup doesn't looks quite ok)
Sorry about the language above; The last second edit before send
wasn't that good.
I meant that your current (mysqladmin variables) setup looks quite ok.
Ozlinx> The connections are active most of the times. Each connection does select,
Ozlinx> insert etc.
In most case the only thing one need to increase is the key_buffer and
this you have already done.
Ozlinx> 2) Are the settings that I have got now adequate to handle the
>> As you have problems, something could probably be done better.
>> Can you post the output of 'mysqladmin variables' when your machine
>> has been up a day ?
Ozlinx> Not quite 24 hrs but close to it :)
Not my day (or rather night). I meant 'mysqladmin extended-status'
Could you please repost the status.
Ozlinx> 3) Was the problem cause by the lare amout of connections? this
Ozlinx> seem pretty
Ozlinx> obvious but I would like it confirm.
>> Can't you change your program to use pconnect() instead of connect();
>> This will use persistent connections, which should give you much
>> better performance!
>> The problem looks like a TCP/IP connection problem in FreeBSD (but
>> without more information it's very hard to verify this)
Ozlinx> I will try to use pconnect instead and see how I go. But the last time I use
Ozlinx> it I got the error saying that the max_connection has been exceeded.
Ozlinx> This is the way I understand the pconnect to work:
Ozlinx> Apache Child check to see if it already has a DB handle before making the
Ozlinx> new connection. If already exists use it
Ozlinx> otherwise create one. Now if I have a lot of childs being forked by Apache
Ozlinx> (max of 256) then if I need to connect to 4 different DB
Ozlinx> then, the max number of connection this would require is 4x256 = 1024. Now
Ozlinx> we have 3 machines that need these connection so the total
Ozlinx> number of connection require is : 3x 1024 = 3072. Now I did increase the
Ozlinx> max_connect to 4096 however when I restarted MySQL server
Ozlinx> it only give me 2463. If this is the case then I still have the same
Ozlinx> problem. So my next questions would be:
Ozlinx> 1) Is my understanding of pconnect() correct? If so then the problem still
Ozlinx> remains otherwise:
Sorry, I don't know how the php pconnect work(). On the other hand
you can solve this by always connecting to the same database and use
full table paths: database.table
Another option is to execute the 'use database' command as the first
command when you use a connection.
Ozlinx> 1.2) If my understanding of pconnect() is correct then how do I bump up the
Ozlinx> max_connect, do I have to modify mysql source to
Ozlinx> acheive this?.
No, you don't have to modify the MySQL source (MySQL allows you to
have up to 16384 connections). You may have to recompile your FreeBSD
kernel to support much more file descriptors. What is your current
file descriptor limit?
By the way (no flames please); I have had some problems with a
FreeBSD machine lately which also behaves likes your (and without
reasons). Any change you can install Linux on a second harddisk and
try if MySQL behaves better on Linux in this case? It would be VERY
interesting to know if this is a FreeBSD or a MySQL problem!