Closing database connections

[eluser]Jonas G[/eluser]
Hi group

I'm having an issue with a website that gets ALOT of data pushed by xml. The xml is being parsed and inserted into a database using active record. The problem is that after some days of operating I get an 'max_user_connections' exeeded error.

Do I need to manually close the database connection?

Jonas

[eluser]TheFuzzy0ne[/eluser]
Just how busy is your Web site? Do you have any statistics?

Can you not devise some kind of file caching mechanism, that will do larger inserts, but much less frequently?

Also, is your database connection persistent?

[eluser]Jonas G[/eluser]
I need the inserts to be instant (it's for a football livescore app). It get's an xml file containing approx. 10 inserts every 10 seconds or something like that. Sometimes a lot more.

I don't know about connection persistent

I will read up on that.

[eluser]TheFuzzy0ne[/eluser]
In ./system/application/config/database.php, what is the value of $db['default']['pconnect'], assuming default is the database connection config you're using?

[eluser]Jonas G[/eluser]
Value is TRUE

[eluser]TheFuzzy0ne[/eluser]
Just how much of this data is there in your database at any one time? If you did a database dump now, how big would it be?

The only alternative I can think of is to either store and index the data with Zend Search Lucene, or find another Web host. It might be worth asking them what they think, because usually, limits like that are imposed on shared servers, if you expect the server to be very busy, then a shared host is not for you, you'd need a dedicated server.

[eluser]NogDog[/eluser]
[quote author="Jonas G" date="1239839806"]Value is TRUE[/quote]
I would suggest setting it to false. Use of PHP persistent connections can result in a number of problems including "zombie" connections if your server is not specifically configured and tuned to properly make use of them. (In fact, I wish that the default CI configuration setting for this were false instead of true.)

[eluser]Jonas G[/eluser]
[quote author="TheFuzzy0ne" date="1239843670"]Just how much of this data is there in your database at any one time? If you did a database dump now, how big would it be?[/quote]

DB dumb would be around 50MB and growing
EDIT: Make that 700 MB :S

I do plan to switch server when the site goes into production but to keep costs down I develop on mediatemple...

[eluser]Jonas G[/eluser]
[quote author="NogDog" date="1239845629"]
I would suggest setting it to false. [/quote]

I have done this now and after closing the site for the night all the open connections should have timed out. I will see how it goes with pconnect off

Thanks for all your help