over 500 views and no comments...
To start off, I am not a sql guru, I work with databases allot but not as much as other people, that being said, you have an interesting question there, got me thinking also, so I started doing some testing on my own.
I loaded the normal 4images site, I also loaded 2 other competitor gallery scripts, and ran them all through some of my own testing and they all seem to work about the same. If anything, it looked to me like 4images ran better in regards to database.
The comment that your provider gave you about mysql_close()
All the reading and talking that I did with other people about how you should handle database communications are like this.
You should open the connection to the database in the header of the page (4images does this in the page_header.php file)
You should close the connection in the footer of the page (4images does this in the page_footer.php file)
It looks like 4images is doing everything correctly, they are opening and closing the connection every time you render a page, so the only way that connections would still be open is if the page never made it to the page_footer code, like if the page took to long to render and the user left the site before the copyright stuff at the bottom showed up?
When doing this testing, I talked allot with my provider because they restart the sql service every hour or two so I could not monitor the things I wanted to. They said that they found that by restarting the service every hour, it clears up any bad scripting that is messing with the CPU usage. They did not say my scripts were bad, they just said it is their policy that they follow on their shared servers.
in regards to CPU time...
in the includes/constant.php file you will see
1
2
3
| // Debug contants
// define("PRINT_STATS", 1);
// define("PRINT_QUERIES", 1);
|
if you remove the // in front of the stats line, your pages will now display at the bottom how long it took to render everything on the page.
removing the // in front of the print_queries will show you all the sql queries that were made while creating the page.
If you think about it, there is no way around using CPU power while this is happening, if 100 people all pull up a page on your site at the same time, that is 100+ queries all happening at the same time on the server. Yes it will pull allot of power. From what I see, most sites that are running high volume have had to move off of shared hosts because of the usage.
The normal UNMODIFIED default templates that 4images has draw the smallest amount of CPU. You can switch your site to one of them and see if the CPU usage goes down. The biggest draw that I have seen on sites is when you do ANYTHING with RANDOM sql calls (like random images). If you are using anything that has say 5 random images cycling through, that is hard on a server to do.
When you start adding more thumbnails on the page, like next/prev thumbnails, newest images, etc...
The script calls the database for all those thumbnails. So the more busy/pretty your page, the more SQL traffic that will result. I am not saying you should not have thumbnails on your pages but every thumbnail is a call to the database to get the path to that thumbnail.
I do not see that changing unless they completely redo the core of 4images to handle the calls differently.
I am not sure if I helped in any way but I enjoyed looking into it on my end and I still feel 4images is the best out there and that is why I use it.
Hopefully this post will bring more people into the conversation...
Author