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]   Go Down

Author Topic: coppermine performance for a huge site (10K users 14K albums 300K photos)  (Read 4442 times)

0 Members and 1 Guest are viewing this topic.

aod

  • Coppermine newbie
  • Offline Offline
  • Posts: 5

Dear friends,

I converted an album site with a proprietary software to coppermine, now I have 10K users, 14K albums  and 300K photos in coppermine (album.click21.com.br). I noticed some very slow querys in mysqldumpslow:

 Count: 1  Time=14.00s (14s)  Lock=0.00s (0s)  Rows=8.0 (8), root[root]@localhost
  SELECT *, cpg1410_pictures.votes FROM cpg1410_pictures
  LEFT JOIN cpg1410_albums ON
  cpg1410_pictures.aid = cpg1410_albums.aid
  WHERE approved = 'S' AND cpg1410_pictures.votes >= 'S' AND cpg1410_albums.aid IN (N,,{repeated 14809 times}N)  ORDER BY pic_rating DESC, cpg1410_pictures.votes DESC, pid DESC  LIMIT N ,N

Count: 1  Time=14.00s (14s)  Lock=0.00s (0s)  Rows=8.0 (8), root[root]@localhost
  SELECT *,cpg1410_pictures.votes FROM cpg1410_pictures
  LEFT JOIN cpg1410_albums ON
  cpg1410_pictures.aid = cpg1410_albums.aid
  WHERE approved = 'S' AND hits > N AND cpg1410_albums.aid IN (N,,{repeated 14809 times}N)   ORDER BY hits DESC, filename   LIMIT N ,N

Count: 1  Time=14.00s (14s)  Lock=0.00s (0s)  Rows=8.0 (8), root[root]@localhost
  SELECT *, cpg1410_pictures.votes FROM cpg1410_pictures
  LEFT JOIN cpg1410_albums ON
  cpg1410_pictures.aid = cpg1410_albums.aid
  WHERE approved = 'S' AND cpg1410_pictures.votes >= 'S' AND cpg1410_albums.aid IN (N,,{repeated 14810 times}N)  ORDER BY pic_rating DESC, cpg1410_pictures.votes DESC, pid DESC  LIMIT N ,N

Count: 4  Time=6.75s (27s)  Lock=0.00s (0s)  Rows=8.0 (32), root[root]@localhost
  SELECT
  cpg1410_albums.aid
  ,cpg1410_pictures.*
  ,cpg1410_albums.title as title
  FROM cpg1410_albums
  INNER JOIN cpg1410_pictures ON
  cpg1410_pictures.aid = cpg1410_albums.aid
  INNER JOIN cpg1410_users ON
  cpg1410_pictures.owner_id = cpg1410_users.user_id
  INNER JOIN click21_users ON
  click21_users.id = cpg1410_users.user_id  WHERE  N=N   AND approved = 'S' group BY cpg1410_albums.aid ORDER BY pid DESC  LIMIT N ,N

Is there a way I could improve that? I know the delay is caused by coppermine topn,2/toprated,2 in album view, but even  lastalb,2/lastup,2 makes the first page slow! How coul I improve these queries? I am willing to pay for support on this.
« Last Edit: December 27, 2007, 10:02:57 am by GauGau »
Logged

aod

  • Coppermine newbie
  • Offline Offline
  • Posts: 5
Re: coppermine performance for a huge site (10K users 14K albums 300K photos)
« Reply #1 on: December 27, 2007, 04:20:15 am »

btw, im using cpg1414
Logged

Joachim Müller

  • Dev Team member
  • Coppermine addict
  • ****
  • Offline Offline
  • Gender: Male
  • Posts: 47843
  • aka "GauGau"
    • gaugau.de
Re: coppermine performance for a huge site (10K users 14K albums 300K photos)
« Reply #2 on: December 27, 2007, 09:29:25 am »

I am willing to pay for support on this.
So do you want this thread to be moved to the "paid support" board?
Logged

Joachim Müller

  • Dev Team member
  • Coppermine addict
  • ****
  • Offline Offline
  • Gender: Male
  • Posts: 47843
  • aka "GauGau"
    • gaugau.de
Re: coppermine performance for a huge site (10K users 14K albums 300K photos)
« Reply #3 on: December 27, 2007, 10:02:41 am »

You already have an identical thread on the paid help board, so I'm marking this one accordingly and locking it. In the future, don't double-post!
Logged
Pages: [1]   Go Up
 

Page created in 0.019 seconds with 19 queries.