Author Topic: Mysql Usage  (Read 12953 times)

0 Members and 1 Guest are viewing this topic.

Offline khansahib

  • Full Member
  • ***
  • Posts: 100
    • View Profile
Mysql Usage
« on: January 19, 2011, 07:19:59 AM »
Hi,

I have seen that the 4images databases are using large percentage of CPU, i contacted my server provider here is what they recommended.

Quote
I could see more number of open connections to the MySQL service. Please contact your developer to close the connections properly in the coding. You can close the connections using the function mysql_close() in the PHP code:

Please suggest.

-Regards.

Offline batu544

  • Sr. Member
  • ****
  • Posts: 336
    • View Profile
    • Free Celebrity wallpapers
Re: Mysql Usage
« Reply #1 on: January 19, 2011, 05:29:55 PM »
Hi,
     Need some experts comment on this.. My website's traffic is not so huge.. just 2000 -3000 page view per day.. suddenly my host suspended by database saying that my queries are overloading the mySQL database..  I don't see any wrong query , all are just simple select and update queries .. but no idea how it created high load on the server...  I am in process of changing my host..

   Is really 4images queries takes much CPU ??

Thanks
batu544

Offline khansahib

  • Full Member
  • ***
  • Posts: 100
    • View Profile
Re: Mysql Usage
« Reply #2 on: January 21, 2011, 05:17:55 AM »
can anybody please help us on this?

Offline budduke

  • Hero Member
  • *****
  • Posts: 506
    • View Profile
    • http://www.budduke.com
Re: Mysql Usage
« Reply #3 on: February 08, 2011, 08:54:07 PM »
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
Code: [Select]
// 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...
Buddy Duke
www.budduke.com

Offline batu544

  • Sr. Member
  • ****
  • Posts: 336
    • View Profile
    • Free Celebrity wallpapers
Re: Mysql Usage
« Reply #4 on: February 11, 2011, 06:19:29 AM »
Well guys.. when I checked my statistics for my home page, I got surprised..

my homepage is doing 200 sql queries for a single display..  8O

Code: [Select]
Page generated in 1.066121 seconds with 223 queries, spending 0.461000 seconds doing MySQL queries and 0.605121 doing PHP things. GZIP compression enabled
Page generated in 1.290053 seconds with 223 queries, spending 0.477000 seconds doing MySQL queries and 0.813053 doing PHP things. GZIP compression enabled
Page generated in 1.028376 seconds with 225 queries, spending 0.393000 seconds doing MySQL queries and 0.635376 doing PHP things. GZIP compression enabled
Page generated in 1.248478 seconds with 222 queries, spending 0.499000 seconds doing MySQL queries and 0.749478 doing PHP things. GZIP compression enabled


Offline V@no

  • If you don't tell me what to do, I won't tell you where you should go :)
  • Global Moderator
  • 4images Guru
  • *****
  • Posts: 17.849
  • mmm PHP...
    • View Profile
    • 4images MODs Demo
Re: Mysql Usage
« Reply #5 on: February 11, 2011, 07:25:00 AM »
That sounds like original code of the seo mod with image/cat name in the url...if so, check out that topic for optimized version
Your first three "must do" before you ask a question:
Please do not PM me asking for help unless you've been specifically asked to do so. Such PMs will be deleted without answer. (forum rule #6)
Extension for Firefox/Thunderbird: Master Password+    Back/Forward History Tweaks (restartless)    Cookies Manager+    Fit Images (restartless for Thunderbird)

Offline batu544

  • Sr. Member
  • ****
  • Posts: 336
    • View Profile
    • Free Celebrity wallpapers
Re: Mysql Usage
« Reply #6 on: February 11, 2011, 01:44:02 PM »
Thank you V@no... I was thinking I have already installed the optimized version of that MOD.. but when I verified today.. it's not installed.. so I did it and the results are here... :)



Code: [Select]
Page generated in 1.177956 seconds with 52 queries, spending 0.458000 seconds doing MySQL queries and 0.719956 doing PHP things. GZIP compression enabled
Page generated in 1.124450 seconds with 52 queries, spending 0.410000 seconds doing MySQL queries and 0.714450 doing PHP things. GZIP compression enabled
Page generated in 1.208340 seconds with 55 queries, spending 0.467000 seconds doing MySQL queries and 0.741340 doing PHP things. GZIP compression enabled
Page generated in 1.052096 seconds with 55 queries, spending 0.407000 seconds doing MySQL queries and 0.645096 doing PHP things. GZIP compression enabled


Thank you

Offline relu

  • Newbie
  • *
  • Posts: 44
    • View Profile
    • Pulsarmedia
Re: Mysql Usage
« Reply #7 on: November 15, 2012, 02:11:56 AM »
What can generate this? i have 765 queries for a page.... i think are too much.



SELECT cat_name
,cat_parent_id FROM 4images_categories WHERE cat_id '1'
Querytime0

SELECT cat_name
,cat_parent_id FROM 4images_categories WHERE cat_id '2'
Querytime0

SELECT cat_name
,cat_parent_id FROM 4images_categories WHERE cat_id '3'
Querytime0

SELECT cat_name
,cat_parent_id FROM 4images_categories WHERE cat_id '5'
Querytime0

SELECT cat_name
,cat_parent_id FROM 4images_categories WHERE cat_id '6'
Querytime0

SELECT cat_name
,cat_parent_id FROM 4images_categories WHERE cat_id '7'
Querytime0

SELECT cat_name
,cat_parent_id FROM 4images_categories WHERE cat_id '8'
Querytime0

SELECT cat_name
,cat_parent_id FROM 4images_categories WHERE cat_id '9'
Querytime0

SELECT cat_name
,cat_parent_id FROM 4images_categories WHERE cat_id '10'
Querytime0

SELECT cat_name
,cat_parent_id FROM 4images_categories WHERE cat_id '11'
Querytime0

SELECT cat_name
,cat_parent_id FROM 4images_categories WHERE cat_id '12'
Querytime0

SELECT cat_name
,cat_parent_id FROM 4images_categories WHERE cat_id '13'
Querytime0

SELECT cat_name
,cat_parent_id FROM 4images_categories WHERE cat_id '14'
Querytime0

SELECT cat_name
,cat_parent_id FROM 4images_categories WHERE cat_id '15'
Querytime0

SELECT cat_name
,cat_parent_id FROM 4images_categories WHERE cat_id '16'
Querytime0

SELECT cat_name
,cat_parent_id FROM 4images_categories WHERE cat_id '17'
Querytime0

SELECT cat_name
,cat_parent_id FROM 4images_categories WHERE cat_id '18'
Querytime0

SELECT cat_name
,cat_parent_id FROM 4images_categories WHERE cat_id '19'
Querytime0

SELECT cat_name
,cat_parent_id FROM 4images_categories WHERE cat_id '20'
Querytime0

SELECT cat_name
,cat_parent_id FROM 4images_categories WHERE cat_id '21'
Querytime0

SELECT cat_name
,cat_parent_id FROM 4images_categories WHERE cat_id '22'
Querytime0

SELECT cat_name
,cat_parent_id FROM 4images_categories WHERE cat_id '23'
Querytime0

SELECT cat_name
,cat_parent_id FROM 4images_categories WHERE cat_id '24'
Querytime0

SELECT cat_name
,cat_parent_id FROM 4images_categories WHERE cat_id '25'
Querytime0

SELECT cat_name
,cat_parent_id FROM 4images_categories WHERE cat_id '26'
Querytime0

SELECT cat_name
,cat_parent_id FROM 4images_categories WHERE cat_id '27'
Querytime0

SELECT cat_name
,cat_parent_id FROM 4images_categories WHERE cat_id '29'
Querytime0

SELECT cat_name
,cat_parent_id FROM 4images_categories WHERE cat_id '30'
Querytime0

SELECT cat_name
,cat_parent_id FROM 4images_categories WHERE cat_id '31'
Querytime0
« Last Edit: November 15, 2012, 05:39:02 AM by Rembrandt »

Rembrandt

  • Guest
Re: Mysql Usage
« Reply #8 on: November 15, 2012, 05:41:57 AM »
Hi!
in the php files looking for:

SELECT cat_name
,cat_parent_id


mfg Andi