Re: How to create a working connection pool?

[Jason Sage]

I don't know how to make a connection pool for MySQL but frankly in my testing - I'm NOT sure it's the right thing to do at all. Why? MySQL connects WHEN the connection is used - not when you create it and give it the credentials. Like you set up your connection, pass it server, username, etc.. but it's when you run a query that the security is checked etc.

Now if you were using one of those DBMS systems that has PAY per CONNECTION like Microsoft did for their MS-SQL (possibly still does it the same way) ... then a connection pool makes a lot of sense to me personally.

You asked if multiple threads can use the same connection.. in my experience .. NO. It's asking for trouble. I found the best bet is to make your main process get create a master list of the connections you're going to use... and then "replicate them" into each thread separately .. in a loop or something. Then each thread manages connections separately.

START THREAD, RUN QUERIES, THREAD FINISH, DELIVER RESULTS, RESET THINGS for next go around. suspend until next time needed.

Now this is just my experience - but so far this paradigm has worked well for me.

Again.. On systems that each connection uses a lot of resources just have "allocated" or there is a pay-per--simultaneous-connection (or easy to reach maximum limit of simultaneous connections) I'd write a connection pool myself.. but all I've read about and seen with MySQL - it's light enough at the libmysql.dll level - (the raw native API) ... it's light weight enough and the way it works in general - to me I find it totally acceptable to just load, copy them to each thread, connect, and wait for work.

Now in PHP - I WOULD NOT DO THIS! I connect only to connections I need - when I need them.. because of the start up expense - you fire the PHP code, then you connect etc.. then you use... in the scenario you described - you're all READY TO WORK - and are waiting for the trigger to be pulled...

PHP - with persistent connections? I haven't used those yet - but I'd personally want to read about them in some detail before decided on the "brute force" approach or the more conservative connection pool approach.

I hope this helps you a bit!
--Jason P Sage



Edited 1 time(s). Last edit at 08/06/2010 09:58PM by Jason Sage.