reducing serverload ... sql ruduction help

Status
Not open for further replies.
erect

erect

New member
Jun 3, 2007
3,795
154
0
Esoterica
twitter.com
One of my sites has recently experienced some downtime due to too many sql connections (25) at any given time. I've upgraded to vps for this 1 site just to make the problem go away but this issue will come up again as traffic continues to grow. I need to knock this one out now so the site is able to sustain growth without more downtime.

The site in question executes about 6 sql requests per page load, which doesn't sound that bad. Each page also scraped information from a few resources so curl+php was working right alongside the sql commands.

Each page took about 8 seconds to load the 1st time, reduction to <2 seconds for future loads after the page was in cache. It's much faster than that now that I'm on my vps ($25/month for 512RAM/20G/200BW ... not bad I say).

I guess my question is how, exactly, do you execute sql commands and get them out of systems resources as quick as possible. I'm not using mysql_pconnect so I understood the sql executed and shut down immediately until the next command was issued. Am I wrong and the mysql connection is open for the entire page loading process? If this is the case I need to work on my page load time vs. trimming the fat on my sql statements.

Thx for any help, I'm a hack at heart and don't know as much as I should about script efficiency.
 


Have you added indexes on the database fields that are most searched for ?
This makes a major difference in select queries. (But it makes inserts a little slower)

Also print some timestamps on different places on your page (using php's microtime() function) so you can diagnose where the bottlenecks are.

If you use mysql_connect() the connection is indeed open the entire time of the page load. Therefore if your page takes 8 seconds to load it doesn't take much traffic to hit the too many connections error. A way around this is to close and reopen the connection on the parts that take the longest to finish (the curl scraping probably)
 
What is the nature of the information you're scraping/displaying? (without giving too much away ;)),Does it really have to be loaded fresh with each page hit?

Whenever I do this sort of stuff, I cache as much as possible ie a background process which is running every 30 mins to update the cache with fresh info, then load pages for the user from the cache.

Is there a way you can cache the curl results?

Also, what sort of SQL commands are you using? Just straight 1-table selects, or joins as well?

You're right to sort this out now - if you've got a shoddy architecture, throwing hardware at it will very quickly produce diminshing returns as the site scales.
 
Please make sure you CLOSE the SQL connection after the request.
No, they do not close automagically.

::emp::
 
See if you can reuse that same SQL connection over for the same session/same page i.e. open the connection on the page request, and then pass that same connection around to your other routines to reuse.

OOP is great for this.
 
reuse the connection (within the same script), DON'T keep opening and closing it in the same script. as a matter of fact, you don't even have to explicitly close it at all because it automatically gets closed once the script finishes executing.

also, if you're having huge result sets and are worried about memory consumption (during the script running time), use mysql_free_result to unload a particular result set after you're done with it.

make sure you have proper indexes setup (based on how you're using the database). you can even setup mysql to log all queries that aren't using indexes so you can see where you need to work on. also setup mysql to log slow queries (absolute must) which take more than 1 second.

it's hard to offer more advice without seeing the script itself but that should be a good starting point.
 
When I scrape stuff, I like to unset large arrays and variables when Im done using them. Wont help with MySQL but every little bit helps when youre on vps.
 
Status
Not open for further replies.
Top Bottom