Piwigo.org

mendip_discovery · 2014-04-24 12:06:28

Hello,

Well I have been running Piwigo on a VPS server from 1&1. I had to do this due to the cheaper cloud based hosting services used to block my install of Piwigo as its a bit of a monster with server load (generating images etc).

I have a half decent setup with 2 cpu and 4GB of RAM. But most of the time the server is running hard to keep up with traffic. Recently its getting silly again and the server gets a max out on connections to the database then falls over corrupting the database.

Is there a way to lessen the burden of Piwigo on the server as its getting to be a pain to have to reboot every month and run checks on the sever as it getting bombarded.

Piwigo version: 2.6.2
PHP version: 5.4.27
MySQL version: 5.5.37
Graphics Library: External ImageMagick 6.8.9-0
Database: 144386 photos (first photo added on Wednesday 2 May 2012)
191 albums including 191 physical and 0 virtual (144386 associations)
947 tags (1220288 associations)
Piwigo URL: http://gallery.jackstringer.co.uk

flop25 · 2014-04-24 16:52:32

hello
check the slow mysql queries log

ViPeR_930 · 2014-04-24 22:26:59

I believe I have the same issue so I am tagging along with this if you don't mind. I was about to post a new thread but found this via search.

I will enable and check my slow queries log.

ViPeR_930 · 2014-04-25 02:57:43

What is an appropriate long_query_time setting to use? 5 seconds?

flop25 · 2014-04-25 07:52:08

It depends on the average response time of your queries... You should use server analytics services and or software. Any pro website should use them. But that's sys admin tasks nothing specific to piwigo

ViPeR_930 · 2014-04-27 11:36:29

After taking a peak at mysql log, I found I'm out of disk space which ould be causing the issues. I haven't added new images in a while. I'm in the processes of moving my site from Digital Ocean to Linode now. More space and CPU power for the same price!

mendip_discovery · 2014-04-27 22:29:04

OK, what info do you need. I have managed to work out how to turn on the slow log.

Its a bit scary to look at some of the times.

Code:

  ORDER BY file ASC, date_creation DESC;
# Time: 140427 21:13:47
# User@Host: piwigo_user[piwigo_user] @ localhost []
# Query_time: 67.406969  Lock_time: 4.571203 Rows_sent: 145013  Rows_examined: 580052
SET timestamp=1398629627;
SELECT DISTINCT(image_id)
  FROM piwigo_image_category
    INNER JOIN piwigo_images ON id = image_id
  WHERE
    1=1

  ORDER BY file ASC, date_creation DESC;
# Time: 140427 21:13:48
# User@Host: piwigo_user[piwigo_user] @ localhost []
# Query_time: 70.997976  Lock_time: 7.114748 Rows_sent: 145013  Rows_examined: 580052
SET timestamp=1398629628;
SELECT DISTINCT(image_id)
  FROM piwigo_image_category
    INNER JOIN piwigo_images ON id = image_id
  WHERE
    1=1

  ORDER BY file ASC, date_creation DESC;
# Time: 140427 21:13:49
# User@Host: piwigo_user[piwigo_user] @ localhost []
# Query_time: 64.738125  Lock_time: 0.000157 Rows_sent: 145013  Rows_examined: 580052
SET timestamp=1398629629;
SELECT DISTINCT(image_id)
  FROM piwigo_image_category
    INNER JOIN piwigo_images ON id = image_id
  WHERE
    1=1

  ORDER BY file ASC, date_creation DESC;

mendip_discovery · 2014-05-25 10:10:44

Well my site hasn't managed more than a few hours at a time before it crashes.

I get a message that 'piwigo_images' has crashed and needs a repair. I repair but soon after the server gets stressed again and gives up. I did try and re-sync the albums one at a time but I can only get so far before the system crashes.

Its annoying as the site was rather stable but since January its got worse.

I have used robots.txt to prevent baidu from scanning my site as I wondered if the it rate was too much.

Turned off some plugins so that its not loading anything it doesn't need.

I have turned off the tag cloud on gallery pages but with no effect.

I even cleared out most of the custom settings for MySQL but that doesn't seem to make a difference either. Its a VPS server from 1and1 and has Plesk installed on it. I use the remi repro and yum to run the occasional update to the software on the computer.

Please help, its getting me rather stressed now as I was always proud of my website etc.

ViPeR_930 · 2014-05-25 11:15:43

I am not seeing a whole lot of traffic to your site. How many visits do you get according to Google analytics? Are you sure your php and mysql configurations are set correctly? Post my.cnf please.

mendip_discovery · 2014-05-25 11:41:24

Traffic has died off in the past year as I haven't covered as many events.

MySQL = 5.5.37-1.el6.remi
PHP = 5.4.28-1.el6.remi
Plesk 11.30
Running 4GM Ram, with 2 cpu, 64bit on CentOS6.5
There is 10 website on the server and they don't even blip on the server usage stats.
https://www.dropbox.com/s/3w2xhs1p59kw1 … -graph.png

My old my.cnf but I changed it last night to see if it would make a difference.

Code:

[mysqld]
bind-address=127.0.0.1
local-infile=0
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
tmpdir=/var/tmp/

thread_cache_size = 4
query_cache_size = 12M
key_buffer_size = 32M
query_cache_limit = 1M
max_connections = 160
wait_timeout = 3600
interactive_timeout = 3600

sort_buffer_size = 16M
read_rnd_buffer_size = 3M
tmp_table_size = 80M
max_heap_table_size = 80M
table_cache = 5000
join_buffer_size = 256K

long_query_time = 5

[mysqld_safe]
pid-file=/var/run/mysqld/mysqld.pid
log_slow_queries = 1;
slow_query_log_file = /var/log/mysqldslow.log;

Changed it to,

Code:

[mysqld]
bind-address=127.0.0.1
local-infile=0
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
tmpdir=/var/tmp/

long_query_time = 5

[mysqld_safe]
pid-file=/var/run/mysqld/mysqld.pid
log_slow_queries = 1;
slow_query_log_file = /var/log/mysqldslow.log;

ViPeR_930 · 2014-05-26 02:45:36

I recommend you use mysqltuner to help you configure your my.cnf.

mendip_discovery · 2014-05-26 10:39:49

That is what I did in the first place to prevent it from crapping out at times when load was high. The site has now stayed up for 24hours now....beeeping computers.

I just don't have FTP access.

flop25 · 2014-05-26 11:25:30

you really have to use monitoring software! internal like monit or external like new relic ...

mendip_discovery · 2014-05-26 11:47:36

Plesk has a monitor system in place, and that is what I do to look at how much of a hit the site is getting. Though I am tempted to say sod it and move to Webmin or similar as I get a feeling Plesk is just bloating the server and messing it up.

I have run Piwigo on several systems and had nightmares with quite a few of them. Hence going for my own VPS as I had more control and the space to store all the pictures.

Piwigo.org

Announcement

#1 2014-04-24 12:06:28

High Server Load causing crashing of MySQL

#2 2014-04-24 16:52:32

Re: High Server Load causing crashing of MySQL

#3 2014-04-24 22:26:59

Re: High Server Load causing crashing of MySQL

#4 2014-04-25 02:57:43

Re: High Server Load causing crashing of MySQL

#5 2014-04-25 07:52:08

Re: High Server Load causing crashing of MySQL

#6 2014-04-27 11:36:29

Re: High Server Load causing crashing of MySQL

#7 2014-04-27 22:29:04

Re: High Server Load causing crashing of MySQL

Code:

#8 2014-05-25 10:10:44

Re: High Server Load causing crashing of MySQL

#9 2014-05-25 11:15:43

Re: High Server Load causing crashing of MySQL

#10 2014-05-25 11:41:24

Re: High Server Load causing crashing of MySQL

Code:

Code:

#11 2014-05-26 02:45:36

Re: High Server Load causing crashing of MySQL

#12 2014-05-26 10:39:49

Re: High Server Load causing crashing of MySQL

#13 2014-05-26 11:25:30

Re: High Server Load causing crashing of MySQL

#14 2014-05-26 11:47:36

Re: High Server Load causing crashing of MySQL

Board footer