forum.coppermine-gallery.net

Support => cpg1.3.x Support => Older/other versions => cpg1.3 Miscellaneous => Topic started by: Sci7 on September 01, 2005, 11:19:26 pm

Title: Optimisation
Post by: Sci7 on September 01, 2005, 11:19:26 pm
I've been trying to speed up my coppermine install.  I've followed my own suggestions (http://forum.coppermine-gallery.net/index.php?topic=15653.msg97567#msg97567)

I've removed all database queries involving count, and hard coded the $count variable where possible.

I've removed banning and deleting of banned users

The following queries appear to be particularly resource intensive and appear to be duplicating effort - can they be removed - can anyone give me a hint as to if they are "normal" and what they are doing.

I've turned the filmstrip off in the config, but aren't the below queries relating to the filmstrip?

Any further ideas for optimisation would be appriciated.

Code: [Select]
    [12] => SELECT pid, filepath, filename, url_prefix, filesize, pwidth, pheight, ctime, aid, title, caption,hits,owner_id,owner_name from cpg134_pictures WHERE aid='2'    ORDER BY filename ASC  LIMIT 18790 ,10
    [13] => SELECT title,keyword from cpg134_albums WHERE aid='2'
    [14] => SELECT * from cpg134_pictures WHERE aid='2'    ORDER BY filename ASC  LIMIT 18793 ,1
    [15] => SELECT title,keyword from cpg134_albums WHERE aid='2'
    [16] => SELECT * from cpg134_pictures WHERE aid='2'    ORDER BY filename ASC  LIMIT 18794 ,1
    [17] => SELECT title,keyword from cpg134_albums WHERE aid='2'
    [18] => SELECT * from cpg134_pictures WHERE aid='2'    ORDER BY filename ASC  LIMIT 18795 ,1
    [19] => SELECT title,keyword from cpg134_albums WHERE aid='2'
    [20] => SELECT * from cpg134_pictures WHERE aid='2'    ORDER BY filename ASC  LIMIT 18796 ,1
    [21] => SELECT title,keyword from cpg134_albums WHERE aid='2'
    [22] => SELECT * from cpg134_pictures WHERE aid='2'    ORDER BY filename ASC  LIMIT 18797 ,1
Title: Re: Optimisation
Post by: Sci7 on September 04, 2005, 02:10:45 am
A day or so on in experimenting, I no longer think it is the filmstrip's request that is causing my problems. Though to remove the filmstrip associated queries the number of items in the filmstrip has to be set to 0 in the config, turning it off in the config doesn't stop the queries.

I wish to use the PID for links to image pages to ensure static urls, however I have noticed the load times for PID requests are much slower than those involving albums and positions:

Request: displayimage.php?pos=-51
Page generated in 72.685 seconds - 23 queries in 51.057 seconds


Request: displayimage.php?album=2&pos=2
Page generated in 7.033 seconds - 20 queries in 6.54 seconds

Any pointers on a fast way to display images and captions based on PIDs?
Title: Re: Optimisation
Post by: Stramm on September 04, 2005, 07:54:10 am
how many pics do you have in your db?
Title: Re: Optimisation
Post by: Sci7 on September 04, 2005, 09:32:13 am
I've got around 22000 in one album, so I expect that's exacerbating the problem.

Do you have a feel for how much it would help to reduce that - say 4 albumns of 5000?  - and is there an easy way to do it?

Title: Re: Optimisation
Post by: Stramm on September 04, 2005, 09:40:19 am
it doesn't matter at all how many pics you have in one album. Important is how many pics you have in general.

I'm running mine only with 10k pics in total, 10k users and 35k comments. In certain cases I have performance problems already too. Mainly if a user clicks the 'user galleries'. That's doing some crazy queries.

I thought about introducing an cpg_pictures_archive table. That'll reduce the load on the main cpg_pictures table

edit: for the 'user galleries' probem I've posted another solution on the modifications board
Title: Re: Optimisation
Post by: Sci7 on September 04, 2005, 01:09:59 pm
it doesn't matter at all how many pics you have in one album. Important is how many pics you have in general.
Do you know why that is?

Looking at the queries on the _pictures table  I saw they were all limited as:
Code: [Select]
WHERE aid='x'

so I thought reducing album size would speed up those queries. I've just experimented with a 1 picture album and a PID based request for the image contained with in it resulted in:

Code: [Select]
Page generated in 0.143 seconds - 16 queries in 0.015 seconds :)
That's despite my 22000 in the other album. I'll split my large album into 5 or 10 smaller ones when I get a chance and see how that affects performance.
Title: Re: Optimisation
Post by: Stramm on September 04, 2005, 01:14:33 pm
cause it always searches through the entire picture table to find all aid=x pictures.
Title: Re: Optimisation
Post by: Sci7 on September 04, 2005, 03:09:16 pm
Splitting my album, into 7 X 3000 or so images didn't help - as Stramm predicted.

I'm now considering a separate PID X AID table.

or writing a simpler displayimage.php  for my needs

Title: Re: Optimisation
Post by: Sci7 on September 05, 2005, 03:07:45 am
I just created an index on AID in _pictures and all became really fast.
Title: Re: Optimisation
Post by: Stramm on September 05, 2005, 10:01:58 am
you should check your entire sql layout. An index on aid is supposed to be there
Title: Re: Optimisation
Post by: kiig on September 05, 2005, 04:35:53 pm
I've also got an index on AID on both 1.3.x and 1.4.1 versions...
Title: Re: Optimisation
Post by: Sci7 on September 05, 2005, 10:12:00 pm
The index I had there was on both  aid and approved putting the index on aid only has made a diffference.

http://www.biomedimages.com/mouse+eye-images.html