Advanced search  

News:

CPG Release 1.6.26
Correct PHP8.2 issues with user and language managers.
Additional fixes for PHP 8.2
Correct PHP8 error with SMF 2.0 bridge.
Correct IPTC supplimental category parsing.
Download and info HERE

Pages: [1] 2   Go Down

Author Topic: Need help to optimize mySQL database  (Read 15294 times)

0 Members and 1 Guest are viewing this topic.

tinorebel

  • Coppermine regular visitor
  • **
  • Offline Offline
  • Gender: Male
  • Posts: 87
  • live long and prosper!
    • www.tripodart.net
Need help to optimize mySQL database
« on: July 07, 2006, 04:51:31 pm »

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 8)!!
Post your ideas and proposals in this thread, I'll be waiting....


« Last Edit: November 13, 2006, 08:26:33 am by GauGau »
Logged
Live long and prosper!
www.tripodart.net

rctruckz

  • Coppermine novice
  • *
  • Offline Offline
  • Gender: Male
  • Posts: 27
    • RC Truckz
Re: Need help to optimize mySQL database
« Reply #1 on: July 07, 2006, 04:57:32 pm »

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 :)
Logged

Sami

  • VIP
  • Coppermine addict
  • ***
  • Offline Offline
  • Gender: Male
  • Posts: 3686
  • BMossavari
    • My Project
Re: Need help to optimize mySQL database
« Reply #2 on: July 07, 2006, 05:00:44 pm »

it would be better if you enable debug mode
Logged
‍I don't answer to PM with support question
Please post your issue to related board

tinorebel

  • Coppermine regular visitor
  • **
  • Offline Offline
  • Gender: Male
  • Posts: 87
  • live long and prosper!
    • www.tripodart.net
Re: Need help to optimize mySQL database
« Reply #3 on: July 07, 2006, 05:22:41 pm »

ok, debug mode eneabled for all! :D
Hope this helps find the problem.... ???
Thanks for your response!
Logged
Live long and prosper!
www.tripodart.net

tinorebel

  • Coppermine regular visitor
  • **
  • Offline Offline
  • Gender: Male
  • Posts: 87
  • live long and prosper!
    • www.tripodart.net
Re: Need help to optimize mySQL database
« Reply #4 on: July 07, 2006, 05:33:46 pm »

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!! ;)
Logged
Live long and prosper!
www.tripodart.net

Sami

  • VIP
  • Coppermine addict
  • ***
  • Offline Offline
  • Gender: Male
  • Posts: 3686
  • BMossavari
    • My Project
Re: Need help to optimize mySQL database
« Reply #5 on: July 07, 2006, 05:44:28 pm »

you are having 105 query each time !!! first of all remove random image
Logged
‍I don't answer to PM with support question
Please post your issue to related board

tinorebel

  • Coppermine regular visitor
  • **
  • Offline Offline
  • Gender: Male
  • Posts: 87
  • live long and prosper!
    • www.tripodart.net
Re: Need help to optimize mySQL database
« Reply #6 on: July 07, 2006, 06:14:09 pm »

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! ;D

Thanks bmossavari!
Your help is crucial!
Logged
Live long and prosper!
www.tripodart.net

Sami

  • VIP
  • Coppermine addict
  • ***
  • Offline Offline
  • Gender: Male
  • Posts: 3686
  • BMossavari
    • My Project
Re: Need help to optimize mySQL database
« Reply #7 on: July 07, 2006, 06:30:15 pm »

okey remove "statistics" by editing your theme.php and remove the placeholder {STATISTICS} from $template_cat_list
Logged
‍I don't answer to PM with support question
Please post your issue to related board

tinorebel

  • Coppermine regular visitor
  • **
  • Offline Offline
  • Gender: Male
  • Posts: 87
  • live long and prosper!
    • www.tripodart.net
Re: Need help to optimize mySQL database
« Reply #8 on: July 07, 2006, 07:04:35 pm »

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!
Logged
Live long and prosper!
www.tripodart.net

Sami

  • VIP
  • Coppermine addict
  • ***
  • Offline Offline
  • Gender: Male
  • Posts: 3686
  • BMossavari
    • My Project
Re: Need help to optimize mySQL database
« Reply #9 on: July 07, 2006, 07:09:15 pm »

- 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
Logged
‍I don't answer to PM with support question
Please post your issue to related board

tinorebel

  • Coppermine regular visitor
  • **
  • Offline Offline
  • Gender: Male
  • Posts: 87
  • live long and prosper!
    • www.tripodart.net
Re: Need help to optimize mySQL database
« Reply #10 on: July 07, 2006, 07:22:56 pm »

- 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;


Logged
Live long and prosper!
www.tripodart.net

Sami

  • VIP
  • Coppermine addict
  • ***
  • Offline Offline
  • Gender: Male
  • Posts: 3686
  • BMossavari
    • My Project
Re: Need help to optimize mySQL database
« Reply #11 on: July 07, 2006, 07:25:29 pm »

- 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
« Last Edit: July 07, 2006, 07:34:27 pm by bmossavari »
Logged
‍I don't answer to PM with support question
Please post your issue to related board

Joachim Müller

  • Dev Team member
  • Coppermine addict
  • ****
  • Offline Offline
  • Gender: Male
  • Posts: 47843
  • aka "GauGau"
    • gaugau.de
Re: Need help to optimize mySQL database
« Reply #12 on: July 08, 2006, 10:21:45 am »

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.
Logged

tinorebel

  • Coppermine regular visitor
  • **
  • Offline Offline
  • Gender: Male
  • Posts: 87
  • live long and prosper!
    • www.tripodart.net
Re: Need help to optimize mySQL database
« Reply #13 on: July 08, 2006, 12:25:48 pm »

 :-[
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! ;)
Logged
Live long and prosper!
www.tripodart.net

Sami

  • VIP
  • Coppermine addict
  • ***
  • Offline Offline
  • Gender: Male
  • Posts: 3686
  • BMossavari
    • My Project
Re: Need help to optimize mySQL database
« Reply #14 on: July 08, 2006, 12:32:58 pm »

I see that you have much better page generation score
Code: [Select]
Page generated in 0.55 seconds:)
did you disable Casper's mod ?
Logged
‍I don't answer to PM with support question
Please post your issue to related board

tinorebel

  • Coppermine regular visitor
  • **
  • Offline Offline
  • Gender: Male
  • Posts: 87
  • live long and prosper!
    • www.tripodart.net
Re: Need help to optimize mySQL database
« Reply #15 on: July 08, 2006, 02:08:39 pm »

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

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!! ;)
Logged
Live long and prosper!
www.tripodart.net

tinorebel

  • Coppermine regular visitor
  • **
  • Offline Offline
  • Gender: Male
  • Posts: 87
  • live long and prosper!
    • www.tripodart.net
Re: Need help to optimize mySQL database
« Reply #16 on: July 08, 2006, 03:28:47 pm »

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.
Logged
Live long and prosper!
www.tripodart.net

Sami

  • VIP
  • Coppermine addict
  • ***
  • Offline Offline
  • Gender: Male
  • Posts: 3686
  • BMossavari
    • My Project
Re: Need help to optimize mySQL database
« Reply #17 on: July 08, 2006, 04:24:13 pm »

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
« Last Edit: July 08, 2006, 05:12:46 pm by bmossavari »
Logged
‍I don't answer to PM with support question
Please post your issue to related board

tinorebel

  • Coppermine regular visitor
  • **
  • Offline Offline
  • Gender: Male
  • Posts: 87
  • live long and prosper!
    • www.tripodart.net
Re: Need help to optimize mySQL database
« Reply #18 on: July 08, 2006, 05:02:41 pm »



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! :D
Logged
Live long and prosper!
www.tripodart.net

Sami

  • VIP
  • Coppermine addict
  • ***
  • Offline Offline
  • Gender: Male
  • Posts: 3686
  • BMossavari
    • My Project
Re: Need help to optimize mySQL database
« Reply #19 on: July 08, 2006, 05:16:00 pm »

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 )
Logged
‍I don't answer to PM with support question
Please post your issue to related board
Pages: [1] 2   Go Up
 

Page created in 0.044 seconds with 19 queries.