Need help to optimize mySQL database

[tinorebel]

Hy, I'm runnin a photo gallery selling pictures on-line.

www.tripodart.net/website/gal/index.php?cat=0

My problem is, the database is runnin too slowly, it takes from 6 to 10 seconds to generate the pages requested. (when it dose).
I'd be ready to pay a reasonable price (from €50 to €150?) to anyone who can help me solve the problem.

I have no idea of MySQL, just had a look at the control panel and found may have some bad index. Tryed to export my database on other server but have no idea how to modify cpg settings to connect to bakuped database.

I'M IN A HURRY, the sooner I get help the better it is and I'll be pleased to reward your quikness !!
Post your ideas and proposals in this thread, I'll be waiting....

[rctruckz]

Before you pay for someone to mess with your DB, check that its not the host causing the slow down

I had a look at the link you posted & it is slow as you said. The other thing that can cause the page to load slower is the Theme, although some Themes look nice etc, the more complicated ones do take longr to load due to the amount of images which makes up the Theme.... Try changing the Theme to a basic one & see if its still as slow (you can do this without it messing up your current theme so you can revert back to your own theme easily) ..If when you try a more basic theme the slow down is the same, then its NOT the script

[Sami]

it would be better if you enable debug mode

[tinorebel]

ok, debug mode eneabled for all!
Hope this helps find the problem....
Thanks for your response!

[tinorebel]

Before you pay for someone to mess with your DB, check that its not the host causing the slow down

I had a look at the link you posted & it is slow as you said. The other thing that can cause the page to load slower is the Theme, although some Themes look nice etc, the more complicated ones do take longr to load due to the amount of images which makes up the Theme.... Try changing the Theme to a basic one & see if its still as slow (you can do this without it messing up your current theme so you can revert back to your own theme easily) ..If when you try a more basic theme the slow down is the same, then its NOT the script

Changing to classic theme dosent solve the problem. so it should not be the script. How can I check if it is the host slowing down? I dont think so because other pages of the website -other than cpg- load quite quickly (see "prices" or "reportaje") maby I'm wrong?

Hope there is an easy solution, if so I'll be ready to reward this clever guy who fid's it!!

[Sami]

you are having 105 query each time !!! first of all remove random image

[tinorebel]

you are having 105 query each time !!! first of all remove random image

REMOVED RANDOM FILES.
.......still a bit slow

----------Page generated in 16.725 seconds - 28 queries in 0.05 seconds - Album set : AND aid IN (1,2,3,59,48) ; Meta set: AND aid IN (1,2,3,59,48) ;

but is getting better!

Thanks bmossavari!
Your help is crucial!

[Sami]

okey remove "statistics" by editing your theme.php and remove the placeholder {STATISTICS} from $template_cat_list

[tinorebel]

okey remove "statistics" by editing your theme.php and remove the placeholder {STATISTICS} from $template_cat_list

Sorry mbossavari but I cant find nither statistics nor template_cat_list in my theme.php file.....     
maby because:

// ------------------------------------------------------------------------- //
// This theme has had redundant CORE items removed                           //
// ------------------------------------------------------------------------- //

I'm actually on cpg 1.4.8, but my theme.php file is from version 4.3 because of the shop mod.

I tried looking on older versions of theme.php and found {STATISTICS} in cpg 1.32 theme.php

I'm I wrong? missing some stuff?

Thanks for your support!

[Sami]

- if you don't have $template_cat_list then copy it from sample/theme.php to your theme.php
- try to [temproary] disable every MOD that you actualy have

[tinorebel]

- if you don't have $template_cat_list then copy it from sample/theme.php to your theme.php
- try to [temproary] disable every MOD that you actualy have

The only mod I have is the online shop by gaugau, I'll try to temporary disable it.

shall I paste all this code in my theme.php file?  :

Your support is extraordinary!

// HTML template for the category list
$template_cat_list = <<<EOT
<!-- BEGIN header -->
        <tr>
                <td class="tableh1" width="80%" align="left"><b>{CATEGORY}</b></td>
                <td class="tableh1" width="10%" align="center"><b>{ALBUMS}</b></td>
                <td class="tableh1" width="10%" align="center"><b>{PICTURES}</b></td>
        </tr>
<!-- END header -->
<!-- BEGIN catrow_noalb -->
        <tr>
                <td class="catrow_noalb" colspan="3"><table border="0"><tr><td align="left">{CAT_THUMB}</td><td align="left"><span class="catlink"><b>{CAT_TITLE}</b></span>{CAT_DESC}</td></tr></table></td>
        </tr>
<!-- END catrow_noalb -->
<!-- BEGIN catrow -->
        <tr>
                <td class="catrow" align="left"><table border="0"><tr><td>{CAT_THUMB}</td><td><span class="catlink"><b>{CAT_TITLE}</b></span>{CAT_DESC}</td></tr></table></td>
                <td class="catrow" align="center">{ALB_COUNT}</td>
                <td class="catrow" align="center">{PIC_COUNT}</td>
        </tr>
        <tr>
            <td class="tableb" colspan="3">{CAT_ALBUMS}</td>
        </tr>
<!-- END catrow -->
<!-- BEGIN footer -->
        <tr>
                <td colspan="3" class="tableh1" align="center"><span class="statlink"><b>{STATISTICS}</b></span></td>
        </tr>
<!-- END footer -->
<!-- BEGIN spacer -->
        <img src="images/spacer.gif" width="1" height="7" border="" alt="" /><br />
<!-- END spacer -->

EOT;

[Sami]

- yes and remove {STATISTICS}

Update
- Sorry I must go now hope you could find your way
- also search for improve ,optimize, tweak gallery would be helpfull for you

[Joachim Müller]

The only mod I have is the online shop by gaugau, I'll try to temporary disable it.

Such a mod doesn't exist - I never created such a mod. It usually helps if you post a link to the mod thread.
Please use the code button (the one with the hash sign) in your postings, it makes your postings more readable.

[tinorebel]

 
sorry, Gaugau you are right, the mod I have installed is from Casper!!

http://forum.coppermine-gallery.net/index.php?topic=26454.0

Anyway I'm going to temporary disable it to see if this helps find the bug!

thanks for your hints!

[Sami]

I see that you have much better page generation score

Code Select Expand

Page generated in 0.55 seconds

did you disable Casper's mod ?

[tinorebel]

Yes,  I'm actually disableing Casper's Shop Mod and seems much better

good performance on single pictures visualization

Still some problems on categories .....

"Page generated in 17.436 seconds - 28 queries in 0.052 seconds - Album set : AND aid IN (52,53,54,69) ; Meta set: AND aid IN (52,53,54,69) ;"

I'm going to compleat the dis-installation of the shop mod and see if helps.
:-\

Thanks for all your sugestions!!

[tinorebel]

I think I dis-installed the wole casper's shop mod and still get slow main page generation:

==========================
Page generated in 17.697 seconds - 106 queries in 0.165 seconds - Album set : ; Meta set: ;



I hope it will be posible to spot the bug more easly now.....
...and then to re-install the caspers shop mod, because this fitted perfectly my needs.

If someone is brave enough to solve the mystery I'm ready to reward him-her.

Thanks to all who contribute to this subject.

[Sami]

as you can see your databse is fine , you have 106 query on 0.165 sec.
I think you have web server issue
when i check your gallery , sometimes it generate the whole page under 1 sec and sometimes it generate same page on 17 sec. this is not CPG tuning problem cause the gallery execute same php request (as said before your database is just fine) the problem rise from server side, last time I checked your gallery with Firefox ,it gave me downlaod spalsh to download index.php
all these say about server side issue not app issue
ask your host for support

[tinorebel]

ask your host for support

I just posted a msg to my host support team and hope they can solve my problem.
I was wandering if there is a way to tell cpg to change the adress of the database:
My host provided a server some time ago to host MySQL databases and now there is a newer one, maby changing to the newest server would improve speed. Can I do that?


My sincere gratitude to mbossovary for your support, it's really appreciated!

[Sami]

as I said before you don't have MySql server issue but if you want to change your server , you can:
- first create backup of your databse
- create your databse on new server
- create a user with full privileges on that databse
- update your include/config.inc.php with new database and user info (you should also change mysql server address )