Optimisation

[Sci7]

I've been trying to speed up my coppermine install.  I've followed my own suggestions

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 Expand


    [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


[Sci7]

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?

[Stramm]

how many pics do you have in your db?

[Sci7]

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?

[Stramm]

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

[Sci7]

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 Expand


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 Expand

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.

[Stramm]

cause it always searches through the entire picture table to find all aid=x pictures.

[Sci7]

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

[Sci7]

I just created an index on AID in _pictures and all became really fast.

[Stramm]

you should check your entire sql layout. An index on aid is supposed to be there

[kiig]

I've also got an index on AID on both 1.3.x and 1.4.1 versions...

[Sci7]

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