Advanced search  

News:

cpg1.5.48 Security release - upgrade mandatory!
The Coppermine development team is releasing a security update for Coppermine in order to counter a recently discovered vulnerability. It is important that all users who run version cpg1.5.46 or older update to this latest version as soon as possible.
[more]

Pages: [1]   Go Down

Author Topic: coppermine performance for a huge site (10K users 14K albums 300K photos)  (Read 3803 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: 47844
  • 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: 47844
  • 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.016 seconds with 19 queries.