Poor Gallery Performance - Help!

[devenh]

Is there anything I can do to improve the performance of my EE galleries?

As I have added photos to EE (now about 12k), the time it takes to navigate and display photos has increased. I've even been getting complaints from customers. Please visit http://www.biglens.com and test for yourselves. Does it seem slow to you?

Here are my server's php settings: http://cairo.nshoster.com/phpinfo.php

Here is the server's load (I'm on the server named Cairo, load is usually about 5%): http://www.bluewho.org/

I have of course defragged and optimized the database. Server is a Pentium 4 - 2.4 GHz systems with 1 GB ram, and 2 80 GB hard drives.

Does this look like an EE issue, a server issue, or both? Thanks for your help.

Deven

[Pekka]

Go to server info page and purge uncookied data. If it does not have compression on set it on after the purge.

If uncookied data set grows big in RC4 it will slow things down. Release version will have automatic maintenance and other new stuff there which makes sure there is no slowdown,ever.

Also, your server should have mysql query cache on - now it is not.

I would really like to try your database on my server (just for you in hidden location) PM me if you can let me have a database dump to try out (no images needed).

[Pekka]

Also, if you can show me your my.cnf it would give some clues.

[devenh]

Pekka,

I have purged the cookie data. I will see if I can get the query cache turned on. Will PM you later (busy right now ...).

Where would I find my.cnf?

Deven

[devenh]

... and is the query cache turned on in EE or is it a setting the ISP has to configure? How can you tell that it is off?

Deven

[devenh]

Regarding the query cache, I ran this query

SHOW VARIABLES LIKE 'have_query_cache';

and it returned YES, so it looks like the cache is on.

Deven

[Pekka]

I noticed that your CPU load in almost 5, which means 5 X CPU overload - is this a dedicated server (your own) or shared server where there are other websites as well?

I also noticed resetting uncookied system made almost no difference. This is a good indication that the problem is not in EE code.

my.cnf is usually in /etc/my.cnf

Here is my.cnf for this server (MySQL 3):

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
set-variable = max_connections=200
set-variable = table_cache=1024
set-variable = wait_timeout=120
set-variable = thread_cache_size=128
set-variable = key_buffer=32M
set-variable = sort_buffer=2M
set-variable = record_buffer=2M
set-variable = join_buffer=2M

[mysql.server]
user=mysql
basedir=/var/lib

[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

and here is my.cnf for the other server (MySQL 4):


[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
max_connections = 500
key_buffer = 32M
myisam_sort_buffer_size = 3M
join_buffer_size = 2M
read_buffer_size = 2M
sort_buffer_size = 2M
table_cache = 3000
thread_cache_size = 128
wait_timeout = 30
connect_timeout = 20
max_allowed_packet = 16M
max_connect_errors = 10
query_cache_limit = 1M
query_cache_size = 30M
query_cache_type = 1
skip-innodb

[mysql.server]
user=mysql

[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid


I suspect that the problem could be that you have way too small caches in my.cnf. If query cache is on and does not seem to affect then it has wrong settings for the server.

There is a small utility script at http://photography-on-the.net/ee/beta/mysqlinfo.zip (edit your MySQL username and pass to it and upload it to server and open to browser). It works only when safe mode is off so it should work ok in your server. It shows you detailed info on how MySQL and system is using the machine. It's not my script but it's freeware.

About the speed of EE: Amount of photos in exhibition affects exhibition listing speed so that the further you go in pages the speed will be slower (in linear fashion). This speed decrease is always linear and not affected by amount of photos in EE - only by number of photos in exhibition.

From image...

http://photography-on-the.net/stuff/speed.gif

...you see how this happens (max scale is 1s, server is 3.06GHz P4/1GB RAM). EE has about 5000 images in all:

- the first ramp is exhibition with 800 images, browsed 10 per page through.
- second ramp is same exhibition, browsed 50 per page through
- third ramp is exhibition with 120 images, browsed 10 per page through
- fourth ramp is exhibition with 3100 images, browsed 50 per page through.

Above figures are without query cache. When MySQL query cache hits, each performance measure item goes to near zero.

What this means in practice? It means that if you do not have thousands of photos per exhibition the speed of EE should be very fast. And if you do have very large exhibitions, the speed slows down only on big page numbers. If you have only 100-200 photos in each exhibition and you have slow speeds even on page 1, then look for the system configuration for resolution. Amount of photos in EE database does not govern speed issues.

PS. There is a bug in list php performance measurement code - the main SQL query is put to PHP speed graph - that is why you see PHP graph rise when MySQL is having problems...

You can fix this by changing line

stop_php_timer (__LINE__ . " - main query");

to

stop_mysql_timer (__LINE__ . " - main query");

in list.php

I'm really interested in finding out and solving these speed issues, even if EE is not directly the cause.

[devenh]

Here is the mysqlinfo.php info:

21:53:23 up 5 days, 18:33, 1 user, load average: 4.30, 4.69, 4.83
146 processes: 139 sleeping, 2 running, 4 zombie, 1 stopped
Mem: 1024936k av, 999184k used, 25752k free, 0k shrd, 88024k buff
451796k active, 506080k inactive
Swap: 1052216k av, 221232k used, 830984k free 302140k cached

PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME CPU COMMAND
31185 nobody 15 0 1240 1240 848 R 5.6 0.1 0:00 0 top


Http processes currently running = 25
Mysql processes currently running = 3

Netstat information summary
3 FIN_WAIT1
3 FIN_WAIT2
8 SYN_RECV
18 ESTABLISHED
105 LISTEN
638 TIME_WAIT


Here is the my.cnf file:

[mysqld]
set-variable = max_connections=500
safe-show-database


The query cache is enabled, but the query cache size is zero.

Should I ask that they use your MySQL 4 settings?

Deven

[devenh]

Things are working better now that my ISP added these settings to my.cnf:

join_buffer_size = 2M
read_buffer_size = 2M
sort_buffer_size = 2M
table_cache = 3000
thread_cache_size = 128
query_cache_limit = 1M
query_cache_size = 30M
query_cache_type = 1

Also cleaned up the uncookied and message tables which reduced the database size CONSIDERABLY.

Deven

[Pekka]

There WAS something I would do in code to speed that 12000 photos database up. The problems was in categories. Technical explanation is that how MySQL uses indexes is decided on each execution of a query. Sometimes it fails to see the best option. There is a way to suggest how indexes should be used and I merely made a new category index usage instruction there. I also added a different index tune for author's order vs. other sorting methods.

http://photography-on-the.net/ee/beta/indexfix.zip contains list.php and photo.php with index tune-ups (for RC 4 version). Upload those on your server (backup old files in case there is some trouble).

Here is also a reminder text from EE's optimize page. It is very important:


A note about speed

MySQL uses "index system" to accelerate speed and EE uses that system extensively. After new clean install MySQL index system may not be active, so you may need to do one backup and restore in EE Backup make index system active again.

Also, make sure that you have checked these items to gain full speed:

* you have at least 7 photos in EE
* you have more than 5 photographers in database
* you have more than 5 cameras in database
* you have more than 5 lenses in database
* you have more than 5 lights in database
* you have more than 5 categories in database
* you have more than 5 workflows in database
* you have more than 5 paths (size, thumb, microthumb) in database
* Uncookied data should not grow too big (e.g. 2MB, see server info page).

Those extra items can just be "dummy"ones, no one will see them if they are not used in photos. You may set extra paths' ftp server as OFF to hide them from upload lists.



After you have checked the speed on performance page, untick "Enable speed measurements" and save the setting (update) .

In the end it would be best to find out these index tweaks for different sorting arrangements, different size of exhibitions and different amount of photos so that tweaks would be altered on the fly to suit the current situation. I will do that one day when I have two weeks extra time :)