Suggestions to reduce DB queries? (not repeated)

[Leny]

Hi folks.... Before write this post I was reading the post: http://forum.coppermine-gallery.net/index.php?topic=28455.0 and some others.

My situation is:

1) CPG 1.4 installed.
2) Debug mode enabled.
3) Link to my gallery: http://www.moholy.com.ar/06portada.htm
4) Logging & statistics:

• Logging mode: normal
• Log ecards: no
• Keep detailed vote statistics: yes
• Keep detailed hit statistics: yes

5) 15000 queries allowed per hour in my webhosting. And I dont want to change it.
6) I can have just ONE user for my DB. So, I cant do what the mentioned post says.

In consecuence, I need to reduce DB queries at all!
Just opening the main page of the gallery, the script did 70 queries! (Page generated in 0.138 seconds - 70 queries in 0.003 seconds - Album set : ; Meta set: ... I think is a lot.

What can I do to reduce them? 

Thanks in advance!

[Nibbler]

You'll need to remove features. Disable things and see how it affects the number of queries. If you don't want to do that then you'd need to go through the code and add caching.

Other than that try uploading pictures that nobody likes in order to drive down your traffic.

[Joachim Müller]

Disable detailed hit_stats - they are a huge resource eater.

[Leny]

Well... thanks for your reply!

Nibbler:
Can u tell me what such features do u mean?... im not a programmer so I dont know how to add caching.

GauGau:
Ok... I did it.
At first sight there is no changes in amount of queries.

I think this version eat more resources than older versions. For about a year i used CPG 1.3.x and everything was fine. Im using v.1.4.x since january and ....  (now, phpBB bridged). Im not saying this version is bad!! I think is brilliant and "I know" my webserver sucks... but, is what there is   :-\ 

[Joachim Müller]

cpg1.4.x contains more features than cpg1.3.x. If you enable all those extra features, cpg1.4.x is bound to consume more resources. If you haven't enabled the extra features that cpg1.4.x comes with (compared to cpg1.3.x), then there should only be a very minor increase in resources-consumption.

[Leny]

Right. I'll compare and see what features disable.
Thanks a lot... like ever

[Joachim Müller]

You might want to disable the regular stats at the bottom of the index page (saying X files in X albums and X categories with X comments viewed X times). If you not just silence the output, but get rid of the query that creates the stats in the first place, you'll reduce your page load. Unfortunately, there's no option in coppermine's config to do so, but afaik there are postings that explain how to accomplish this.

Another option is to reduce the number of albums displayed on your index page (best option would be to turn albums display on categories page off).

To me, your site seems to load OK, so I can't see how the huge mysql load could be caused. After all, with "only" 46819 hits your site is not too busy imo. Are you sure that your site is not under attack by someone who is trying to hammer it? Do you have other mysql-driven apps on your webserver?

Joachim

P.S. Actual link to your gallery is http://www.moholy.com.ar/copper/index.php instead of http://www.moholy.com.ar/06portada.htm - please don't send us around to your frameset.

[Stramm]

You might want to disable the regular stats at the bottom of the index page (saying X files in X albums and X categories with X comments viewed X times). If you not just silence the output, but get rid of the query that creates the stats in the first place, you'll reduce your page load. Unfortunately, there's no option in coppermine's config to do so, but afaik there are postings that explain how to accomplish this.

how to do that you can look up in the modpack (index.php). Also there's another option, I named it 'fast stats'. It shows 'absolute stats' and takes not into account that there may be some images/ albums/ comments the logged in user may not be able to see (private albums etc.)

[Nibbler]

Most of your traffic won't be on the index page though. You should concentrate on the pages that are most frequently used on your gallery and on removing queries that run on all the pages. If you don't use banning you can remove (from init.inc.php) the ban check, if you don't use plugins you can disable them in config and probably server-side favourites (init.inc.php). It's not about the actual database load caused by Coppermine, it's about an arbitrary limit on queries. 15k/hour is not very much.

[Leny]

Nibbler: thanks for the good ideas. And I know.... this limit sucks. 

Stramm: yeah! thanks... I`ll take them in mind. 


GauGau: I thought the same (someone who is trying to hammer it). My CPG is running for 2 years with no problem... I know the traffic of my site and still now is regular.
I have phpBB forum (bridged) and Lazarus guestbook using the DB.

Watching the site stats this are the number for march:
Nº - Acces - Kbytes - URL
1 - 11866 7.36% - 209.23 Mb 13.76% - /copper/displayimage.php
2 - 7579 4.70% - 478.36 Mb 31.47% - /foro/viewtopic.php
3 - 3588 2.23% - 163.19 Mb 10.74% - /copper/thumbnails.php

In addition, I receive one or two spam requests to register in the forum, but this number is no critical, i suppose.
I dont know how to see if someone is trying to hammer the site. 

P.S. Actual link to your gallery is http://www.moholy.com.ar/copper/index.php instead of http://www.moholy.com.ar/06portada.htm - please don't send us around to your frameset.

Im sorry.... and, what is "Joachim"?

[Joachim Müller]

[ot]

and, what is "Joachim"?

It's my first name - I'm German. . It is a popular name in Germany. It is originally a name from the bible, so it is originally a Hebrew name. Afaik the father of the virgin Mary was named "Jojakim" (which appears to be the original Hebrew spelling). Surprisingly, it's the name of a Star Trek character as well.
The "ch" in "Joachim" is a sound you don't have in English - it sounds like you're clearing your throat .

Joachim Müller (aka GauGau)
[/ot]

[hlabout]

Leny,

I was searching the forum for Lazarus and saw that you are using the lazarus guestbook with coppermine.
I like to use this guestbook because it has some nice extra features over AGB.
I now have AGB installed and tried to replace / update it with Lazarus.
Could not get it to work, any tips on how you integrated lazarus?

Harald

[Joachim Müller]

You already asked the same question in another thread and got the answer

You're off topic here: open your own thread with your request.

. Nobody said that you should try to hijack another, unrelated thread. Instead, you have been told to start a thread of your own. Locking. Trying to talk to Leny using this thread is silly: he hasn't been active for more than a year, so he won't notice your reply.