forum.coppermine-gallery.net

Support => cpg1.4.x Support => Older/other versions => cpg1.4 miscellaneous => Topic started by: aod on December 27, 2007, 04:19:01 am

Title: coppermine performance for a huge site (10K users 14K albums 300K photos)
Post by: aod on December 27, 2007, 04:19:01 am

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.

Title: Re: coppermine performance for a huge site (10K users 14K albums 300K photos)
Post by: aod on December 27, 2007, 04:20:15 am

btw, im using cpg1414

Title: Re: coppermine performance for a huge site (10K users 14K albums 300K photos)
Post by: Joachim Müller on December 27, 2007, 09:29:25 am

Quote from: aod on December 27, 2007, 04:19:01 am

I am willing to pay for support on this.

So do you want this thread to be moved to the "paid support" board?

Title: Re: coppermine performance for a huge site (10K users 14K albums 300K photos)
Post by: Joachim Müller on December 27, 2007, 10:02:41 am

You already have an identical thread (http://forum.coppermine-gallery.net/index.php?topic=49274.0) on the paid help board (http://forum.coppermine-gallery.net/index.php?board=30.0), so I'm marking this one accordingly and locking it. In the future, don't double-post!