Jump to content

opening and closing database connections


Recommended Posts

Hello!I'm trying to choose the best method to optimise my site when connecting to a mysql database.The options that I can work out are:1. persistent connection (ie mysql_pconnect())2. normal connection - open at top of page, close at end of page3. open and close connection every time a query is performedMy site is pretty normal and I think I'm running about 20 or 30 queries on every page. (maybe that's not normal though?).I have been using the second option on that list. The site seems pretty slow and I've got the queries quite well optimised with lots of indicies, so I am thinking that the slowness may be related to the connections? Either because I'm not closing after every query or because I'm not leaving it open for the whole page.Thanks as ever!Henry

Link to post
Share on other sites

I usually use the second option too.Performance is not only affected by the number of queries, but also by the number of open connections. Your server will crash at a certain number of connections.You want as little connections open as is needed, ergo you want them closed as soon as possible. At least that's my opinion. But I never tried persistent connections. Perhaps you could try it and see what happens.I'm urged to discourage opening and closing with every query, since connecting takes time and resources. Use each connection effectively, as little as needed at a time.

Link to post
Share on other sites

Thanks Yoshida!As an add on I am wondering whether to make the effort to convert to object-style for mysql stuff. I only just learnt object-style and I'm not really looking to spend hours converting all my connection bits to object style but if it speeds things up a lot then I might. Any opinions?

Link to post
Share on other sites

The object and procedural methods should have comparable performance, they both do the same things, just a different way. Don't create a new connection for every query, that is a huge waste of resources. Connections aren't going to slow your site down that much, the reason your site is slow is because you have 20 queries on one page. Even 5-10 on one page is getting up there, it is best to try and keep it below 5. Split the pages up if you need to, but it sounds like you aren't being efficient with the queries. I have several sites that do a lot of data processing, and even if I am doing something like updating a user, which requires 1) authenticating the logged in user, 2) checking permissions for the user, 3) getting the list of users, and 4) updating the user, that's only 4 queries best case for the entire page, maybe upwards of 5 or 6 depending on the user info involved. So, if you're using 20 queries to generate a page and the queries are efficient, then you're showing way too much info on one page. If there's not that much info, then the queries are not efficient.If you want to paste some code I'll be happy to comment on it.

Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
×
×
  • Create New...