Beefy Boxes and Bandwidth Generously Provided by pair Networks
Don't ask to ask, just ask
 
PerlMonks  

Re^2: Optimize DBI connect to avoid max_user_connections

by Alex-WisdomSeeker (Initiate)
on May 29, 2014 at 19:21 UTC ( #1087857=note: print w/replies, xml ) Need Help??


in reply to Re: Optimize DBI connect to avoid max_user_connections
in thread Optimize DBI connect to avoid max_user_connections

I will do so, maybe there is another problem, too. The DB went down again this time with lots of "Waiting for table lock" errors. It really is a small site with around 500 visitors per day, I did not have this prblems with my old host so maybe it is a managed server problem. Will go through the scripts and look for not closed db connections again. Thanks a lot so far !
  • Comment on Re^2: Optimize DBI connect to avoid max_user_connections

Replies are listed 'Best First'.
Re^3: Optimize DBI connect to avoid max_user_connections
by fishmonger (Chaplain) on May 29, 2014 at 19:52 UTC

    Sounds like you're using MyISAM. Alter the tables to use the InnoDB engine instead of MyIASM and most if not all of those table lock warnings should go away. InnoDB uses row locks instead of table locks. It can/will use full table locks when warranted for the operation.

      Ok, will give it a try if the problem will persist. Any ideas if the the problem may be in the code ? Until now I did not have an "or db-disconnect & exit" command after the querys if they do not succeed. Could this be the problem so the script goes persistent and waits for the query to finish ? The MyAdmin log showed nothing suspicious just regular querys. Its driving me mad, so sorry for bothering everyone :-(

        If you don't do an explicit disconnect, the module should do it for you when the handle goes out of scope or as part of the garbage collection process when the script ends. However, that doesn't always happen. I had a script connecting to a pervasive db and didn't have an explicit disconnect. Latter I learned that connections weren't being disconnected which caused the db to lockup when it reached the max connections.

        The next time this problem comes up connect to the mysql cli or you can use phpmyadmin to run the following command.
        show full processlist;

        That will show you the connections and processes being executed.

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://1087857]
help
Chatterbox?
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others rifling through the Monastery: (3)
As of 2017-11-18 09:30 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    In order to be able to say "I know Perl", you must have:













    Results (277 votes). Check out past polls.

    Notices?