HI,
I scanned entire Zen Photo forum and needed help reducing load on db server used by Zen Photo. The high load on db server cause high CPU and memory usage when there is high traffic to website.
The only two I found on forums was:
http://www.zenphoto.org/news/server-side-optimizationhttp://www.zenphoto.org/news/eaccelerator-cachingThe first one actually incresed CPU usage while the second fix caused my website went down.
The above were the only 2 I could find in the forums after looking extensively.
As mentioned, the fixes to high load on db server does not work as I have already tested. The fixes listed above are also outdated because there is no functions-db.php in zp-core directory they are referring to. The name of php file been changed to functions-db-MySQL.php in newer versions.
Other people with high traffic will experience high CPU and memory due to high sql queries so I know I am not only person with this problem.
Requesting help with an updated fix to help reduce load on db server. Thank you
Zenphoto
Comments
Without details there is still some speculation that can be done.
You can try using HTML caching. This will save rendered pages and re-serve them from a cache rather than re-generating them. There is a plugin for this.
You can make indexes on your tables based on your sites typical access paths, but to do this you will have to do a database access analysis.
It is not terribly surprising that the two articles you fond no longer work. Much has changed in Zenphoto since 2009. At any rate, we do not recommend changing the core files since these will certainly cause maintenance issues for the future.
The static html did not help much and not sure how to make indexes on your tables based on your sites typical access paths.
The detailed analysis by my web host tech support told me the problem of high CPU and memory load is cause by following. Here is his email to me:
"Database queries per second continue to be very high. Since those optimization
features are outdated, perhaps you should submit a ticket to the software
company with your version and ask them for updated info on how to enable caching
to reduce your queries per second.
Current average:
Queries per second avg: 1825.323
That is an enormous rate of queries. We are caching the queries at the db
server level, but they need to be cached by php with flat files like WordPress
does with w3 total cache or Supercache."
I know I am not only one with this problem for Zen Photo websites that have very high traffic. I assume other high traffic Zen Photo websites would have high load on their databases as well.
Can you please provide update the outdated 2009 fixes below for 2013:
http://www.zenphoto.org/news/server-side-optimization
http://www.zenphoto.org/news/eaccelerator-caching
I am looking for 2013 way to enablecaching to database.
Thank you for your help
A few good first steps if you want a blunt hammer are to increase the MySQL cache size and query cache limits.
I'm seeing relatively high traffic numbers and not even broaching any limits on my MySQL server, so I'd be curious to know your server stats and your MySQL database server stats.
But what I'd really do if you can is run the MySQL tuner and look for any weak spots:
http://www.techerator.com/2011/08/optimize-your-mysql-server-with-the-mysql-tuner-script/
https://raw.github.com/major/MySQLTuner-perl/master/mysqltuner.pl
Note that the cache is not active if you are loggedin as the admin.
We use Zenphoto on our own server with quite hight traffic at times (ok, that server is quite above the average shared hosting ones) and what we never encounter is any issue with the db access (and we are not even using the static html cache at the moment!).
Thank you for your help Papyrus
1. How can you check if the static html plugin is working correctly?
When I log into Zen Photo, I see it under Options->Plugin and when I ftp into website, I see it under domainname.com/cache_html/pages directory created. I also see files in the directory but not many.
2. Papyrus, I am waiting for my webhost to supply me with info.
Thank you
As always with Zenphoto cached files are created on request, not permilinary. So on the first visit of a page that is not cached it will be cached.
Also if you are logged in as the admin the caches are disabled since you might want to see your changes made.
"The cache directory has an enormous amount of files, so I stopped the search as it was just wasting cpu and time. Perhaps asking the forum folks, not sure what to tell you.
I recommend clearing the cache files. I have seen some file caching systems that don't clean up their files build up an incredible load on the filesystem. If you find that its safe to purge the cache, I have a cache cleaning script to delete all files older than 24 hours. This is standard practice on file cache type setups. However, instead of using file cache, which is better than no cache at all, if the script supports it, memcached is far faster than file cache and automatically purges after 24 hours. memcache uses ram to store objects, not the filesystem. "
1. Is it safe to setup a crontab to delete cached files that are older than 24 hours - to delete all the files in the directories in cache_html and cache ?
2. Does Zen Photo support memcache ?
Thank you
I am surprised that your site is recommending you use a memory cache when they are at the same time telling you that your file cache is too large. Typically servers have much more file storage than memory storage.
The other suspect thing about what they have told you is that "searching the cache files" seems to be an issue. Zenphoto has no need to "search" the cache files. It knows exactly the name that the file will have should it be present. Also, initially you have said that it is SQL searches that are the problem. Cache file references make no database references.
Zenphoto does not directly use any memory caching support. Most file systems have caching built in so such should not be needed.
Short answer is that I have no clue what your site people are getting at and I rather suspect that they do not either.
I've read this thread with attention and appreciate acrylic and sbillard input.
I'm running a moderate size gallery site (50K users/day) that you can see here: http://galeria.obviousmag.org/
Some information about my setup:
NGINX server with the official rewrite rules
Template customised by me (index.php, album.php and image.php)... markup only, using the functions from a existent plugin.
Zenphoto version 1.4.5.7 [59c22b2fba] (Official build)
MySQLi version: 5.1.67
Active plugins:
crop_image
deprecated-functions
exampleMacros
html_meta_tags
rss
seo_cleanup
seo_zenphoto
sitemap-extended
static_html_cache
uploader_jQuery
zenphoto_sendmail
zpCaptcha
I installed the static_html_cache to minimize SQL queries on my DB but, i'm having lots of requests, as you can see in this file:
http://galeria.obviousmag.org/mysqld.txt
Note the timestamp and the volume of queries.
I've checked the directory and file permission (nginx:nginx) so there is no issue on this and I can see several files on this dir with today timestamp.
Perhaps the problem is:
* the static_html_cache malfunctioning? how can I debug this?
* incompatibility with another plugin?
* Made something silly when customising my new template set and disable something?
* should I disable any plugin?
Thanks for your attention
That was the default install option. So, can you guys confirm that leaving that field blank tell's the plugin not to cache?
If so, why the files are being created anyway?
Thanks.
PS: I entered 86400 as expire value and I'm going to monitor the query rate.
You find info about the various caches available here:
http://www.zenphoto.org/news/caching
So note that it is not used if you are loggedin as a the admin.
But since you are using apparently a custom theme there might be issues. Since you hide the general data I cannot even tell if you enabled the plugin. Generally it is set to expire after one day. If the field is empty for whatever just set a value yourself.
For example you seem to use a lazy load script. This of course loads further stuff after the page has been loaded. The cache cache on page access so what is not loaded then cannot be cached. So probably the script is cached but it still executes. That is something you have to figure out yourself.
A view to the error logs might be a
Some of the queries you see are from the backend like various INSERT ones.
acrylian:
I've made a very light alteration in the layout. I get a standard theme, and altered the markup only, haven't altered any code or functions.
Just customised index.php, album.php and image.php
You can check it here:
http://galeria.obviousmag.org/galeria/image.txt
http://galeria.obviousmag.org/galeria/album.txt
http://galeria.obviousmag.org/galeria/index.txt
I've entered the an explicit expire value several hours ago... but I still experiencing a high level of SELECTS from the albums page.
You ca check a fresh refresh on my mysqld.log here:
http://galeria.obviousmag.org/mysqld.txt
I think that's more notorious on album pages. I see lots of
Query SELECT * FROM `zp_administrators` WHERE `user`='bjr' AND `valid`=1 LIMIT 1
Query SELECT * FROM `zp_administrators` WHERE `user` = 'bjr' AND `valid` = '1' LIMIT 1
Query SELECT `value` FROM `zp_options` WHERE `name`='Theme_colors' AND `ownerid`=0 AND `theme`='obvious'
Query SELECT * FROM `zp_albums` WHERE `parentid` IS NULL ORDER BY `sort_order`
Query SELECT * FROM `zp_albums` WHERE `folder` = 'animais' LIMIT 1
Query SELECT * FROM `zp_albums` WHERE `folder` = 'arquitetura' LIMIT 1
Query SELECT * FROM `zp_albums` WHERE `folder` = 'canvas' LIMIT 1
Query SELECT * FROM `zp_albums` WHERE `folder` = 'cinema' LIMIT 1
Query SELECT * FROM `zp_albums` WHERE `folder` = 'design-interiores' LIMIT 1
Query SELECT * FROM `zp_albums` WHERE `folder` = 'eros' LIMIT 1
Query SELECT * FROM `zp_albums` WHERE `folder` = 'fashion' LIMIT 1
Query SELECT * FROM `zp_albums` WHERE `folder` = 'fotografia' LIMIT 1
Query SELECT * FROM `zp_albums` WHERE `folder` = 'gastronomia' LIMIT 1
Query SELECT * FROM `zp_albums` WHERE `folder` = 'musica' LIMIT 1
Query SELECT * FROM `zp_albums` WHERE `folder` = 'turismo' LIMIT 1
So, trying to identify from where all selects are coming from, I've identified some causes and want to hear from you guys if it's plausible:
* I make some php includes do insert some html off-site elements (header file with my html menu).
* I use random imagens in the albums... so every page load I get a new image. That explains the high level of selects?
sbillard:
That was very strange, because I'm logged of from the back-end. So, why I see queries like this every few seconds? From where are they coming from?
`
67144 Query SELECT ``name`, `value` FROM `zp_options` WHERE (`theme`="" OR `theme` IS NULL) AND `ownerid`=0
67144 Query INSERT INTO `zp_options` (`name`, `value`, `ownerid`, `theme`, `creator`) VALUES ('GD_FreeType_Path','/home/www/galeria.obviousmag.org/plugins/gd_fonts',0,"",'zp-core/lib-GD.php')
67144 Query SELECT `value` FROM `zp_options` WHERE `name`='locale' AND `ownerid`=0
67144 Query INSERT INTO `zp_options` (`name`,`ownerid`,`theme`,`value`) VALUES ('zp_plugin_zenphoto_sendmail',0,"",'8197') ON DUPLICATE KEY UPDATE `value`='8197'
These queries are unavoidable.
We of course cannot know what your theme `obvious` might also be doing. But if, as acrylian notes, it is making references after the page load (lazy processing) then those references are not cached and will be repeated each time.
If you cannot change your theme and your server is having trouble with the traffic then you will need to get a higher capacity server.
About the lazy processing, the js call to lazy load was there by mistake. I don't use it. As I said, I haven't modified any core structure from the default plugin (that I used to customise the obvious template).
I understand that some core call should be made to the MySQL. My point is to try to understand if those kinds of queries - dozens every second - are a normal behaviour for a full cached page.
In optimal conditions, the kind of queries you see in my log are normal? For instance, if the index.html (homepage) is fully cached, there will be queries going on to the mysql?
The option of showing "random images", in theory, increase the query rate to the DB?
I'm not having problems with my server... I'm just curious about the large amount of queries and try to understand if there are any way to avoid them.
Thanks for your patience!
Does the site behave differently if you use the stock `default` theme? Be sure that when you are observing your are NOT logged in since there is no caching of pages requested by the administrator.
Look also at your server access logs. What is the request rate there for pages?
And, finally, if you are not having problems with your server what is the issue? SQL processing time is small relative to HTML rendering time. The HTML cache is (as maybe the name implies) intended to reduce the processing for HTML rendering. SQL queries should be a concern only if they are overloading your SQL server or limiting your site throughput.
Thanks for your patience and reply. one of the main problems was my interpretation of cache. In my mind, a burned cache on a filesystem doesn't need of sql activity. I realize now that are much more things going on through ZP core.
For the record, after entering the ttl cache value in static_html_cache that was empty, and setting the thumbnails not to random, seems to helped a lot in decreasing the amount of sql activity.
The fact that my gallery instance is getting substantially more access, and the fact that i'm seduced by the simplicity of ZP, motivated me to proactively try to understand some behaviours in the core and pay more attention to that.
Now I'm having other weird problems that I'll describe in another topic, so we can close this one
thanks once more
You might also look at server side cache settings, most server have something like Xcache, eAccelerator or the like. Those even might be running as your site seems quite fast to me. Maybe ask your host if you are concerned.
As I said, I'm pretty happy with the ZP performance. To be honest, being an ex-php-gallery user, I'm amazed by the simplicity and performance.
Just to share with you a bit more information... yesterday I have another usage peak with a viral effect on facebook and hundreds of simultaneous accesses. Check the graphs below on my dedicated server to ZP:
BW: http://galeria.obviousmag.org/tmp/bw.png
CONNs: http://galeria.obviousmag.org/tmp/con.png
SQL: http://galeria.obviousmag.org/tmp/sql.png
As you can see in the SQL graph, yesterday 12h, I've implemented the following tweaks:
- cached the queries on the sql server (is was turned off, shame on me);
- changed all albums to show the last thumb and not a random one;
- inserted a ttl value in the static_html_cache plugin;
besides that, even with the traffic peak showed above, ZP performed great
Following your recommendation, I've downloaded a fresh copy of ZP 1.4.5.7. Using the default theme and functions, I've implemented a new markup for my layout, making sure that I'm not using old methods. I'm going to put it in production next weekend.
Thanks