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-optimization
http://www.zenphoto.org/news/eaccelerator-caching
The 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
You will have to do a more detailed analysis of your MySQL performance before you can address this problem. There might be many different causes and therefore solutions to the performance.
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.
HI,
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
Quote:but they need to be cached by php with flat files like WordPress
does with w3 total cache or Supercache."
As we said, that is what the static_html_cache plugin does and why it was made. It caches static html files that are served instead of executing php files and db queries.
Quote: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
No, sorry. These were done by a third party. We have never used them and cannot support or help with them.
Hello there!
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
I provided some tips but I'd still be curious about knowing your server specs. Zenphoto runs very lean so you may have something seriously misconfigured depending on traffic and server specs. If you want any advice you should post analytic reports and technical specs otherwise I can't provide any more advice.
The static_html_cache really is meant and should prevent database server load. If that does not work you really should check your server error log for file/folder permission errors. If those are wrong the cache html files are not written and therefore of course not used.
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!).
HI,
- 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.
- Papyrus, I am waiting for my webhost to supply me with info.
Thank you
Look at /cache_html folder. If it does not work your should get errors about permissions or else.
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.
Here is what my webhost support had to say:
"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. "
-
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 ?
-
Does Zen Photo support memcache ?
Thank you
You can delete any cache files you wish. Zenphoto will simply re-create them when someone visits the page. Delete them every 24 hours if you choose, but really, are you changing your site that frequently? You really need to delete cache files if they are obsoleted and that happens only if your site changes. If your site is so dynamic then HTML caching is a bad idea.
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.
Since your host apparently has no idea how Zenphoto works, here a info about how Zenphoto's caching works: http://www.zenphoto.org/news/caching
Hello Guys...
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
I forgot to mention but, when checking with more details the static_html_cache I noticed that, in the plugins configuration zone, the "Static HTML cache expire" was blank :/
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.
The static_Html_cache caches all pages like the name says as static html pages in the /cache_html folder.
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.
The sequence
Quote: 3585 Query SELECT FROM zp_administrators WHERE valid=1 ORDER BY rights DESC, id LIMIT 1
3585 Query SELECT FROM zp_administrators ORDER BY rights DESC, id
is indicative of a back-end (administrative)page load, so do the queries that do inserts and updates. So most likely all these queries are coming from the back-end.
Hello Guys, thanks for your answers. Replies for both below:
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'