Announcement

#1 2013-10-01 00:57:08

jimaek
Member
2013-05-17
41

Huge amounts of queries to MySQL

Since my previous messages were kinda ignored here is a video I made to show you the problem.
At the start of this video I hit upload of 6 images 150kb each. My dedicated server then gets overloaded with 100% MySQL usage. The video shows what queries are executed.

https://www.youtube.com/watch?v=OMtrmkTQ6y4

Offline

 

#2 2013-10-01 09:51:35

mistic100
Former Piwigo Team
Lyon (FR)
2008-09-27
3277

Re: Huge amounts of queries to MySQL

[Forum, topic 22521] How scalable Piwigo really is?

why didn't you bump the other topic ?

as far as I know Piwigo is designed to have a constant number of queries, indenpandantly of the number of photos

as flop25 said, some people have > 300k photos without your kind of issues

you seems to have many redundant queries

do you have extensions installed ?
are your sure php and mysql are properly installed ? (I never managed a web server but I'm sure there are different types of installations)

Offline

 

#3 2013-10-01 10:57:54

mistic100
Former Piwigo Team
Lyon (FR)
2008-09-27
3277

Re: Huge amounts of queries to MySQL

since your concern is about upload the number of queries is obviously linear, but it shouldn't overload the server anyway

Offline

 

#4 2013-10-01 14:37:38

jimaek
Member
2013-05-17
41

Re: Huge amounts of queries to MySQL

I don't really care about the number of queries as long as everything is fast. But at the moment this huge number overloads everything and makes the website inaccessible while executing.
Note that all of those queries were 100% for the upload process. When users access the front-end there are no queries because of static caching.

Same thing happens when I try to create a new album and some other things.

I have plenty of experience in server administration so I am sure that everything is correctly installed.

Before posting I even switched from MySQL to Percona Server in case there was performance difference. Same problem.


Can you help me debug this issue? If you want we could even chat on IRC/Skype.

Offline

 

#5 2013-10-01 14:45:54

mistic100
Former Piwigo Team
Lyon (FR)
2008-09-27
3277

Re: Huge amounts of queries to MySQL

I am absolutely not a specially so I can't really help you

and you didn't answer to on of my questions

mistic100 wrote:

do you have extensions installed [piwigo extensions] ?

Offline

 

#6 2013-10-01 14:51:56

jimaek
Member
2013-05-17
41

Re: Huge amounts of queries to MySQL

This is what I have http://d.pr/i/IJl0 Can't imagine how they could cause this kind of issue.

Last edited by mistic100 (2013-10-01 14:55:50)

Offline

 

#7 2013-10-01 14:55:28

mistic100
Former Piwigo Team
Lyon (FR)
2008-09-27
3277

Re: Huge amounts of queries to MySQL

they can't

Offline

 

#8 2013-10-01 19:41:47

plg
Piwigo Team
Nantes, France, Europe
2002-04-05
13786

Re: Huge amounts of queries to MySQL

How do you add photos? (Web upload, pLoader, iPhoto....)

Can you also monitor the HTTP requests on your webserver and tell us what is called and how often?

Offline

 

#9 2013-10-01 19:43:20

plg
Piwigo Team
Nantes, France, Europe
2002-04-05
13786

Re: Huge amounts of queries to MySQL

Recently I have seen a super slow Piwigo and it was on a basic Amazon EC2 server. Is it what you have?

Offline

 

#10 2013-10-01 19:50:14

jimaek
Member
2013-05-17
41

Re: Huge amounts of queries to MySQL

In the video I added the picture via web uploader. But the issue is also there when I Scan for changes for FTP uploaded images. Also when I create a new album or update information on some album.

I have a dedicated server, no visualization.

The website has too many http requests to monitor them. Especially if I have no idea what you are looking for.

Offline

 

#11 2013-10-01 21:14:48

plg
Piwigo Team
Nantes, France, Europe
2002-04-05
13786

Re: Huge amounts of queries to MySQL

I'm looking for HTTP requests on Piwigo (lines in the access.log of Apache)

When you start to upload look at the new lines in access.log and tell us what they are. If there is no visitor, then you should only have one HTTP request for each file (on admin/include/uploadify/uploadify.php). Then for each call to uploadify.php Piwigo will need between 10 and 20 SQL queries.

Are you sure there is no visitor at the same time on your gallery (because of course it would be impossible to say that upload is the origin of the numerous SQL queries)

Offline

 

#12 2013-10-01 22:05:25

jimaek
Member
2013-05-17
41

Re: Huge amounts of queries to MySQL

The front-end is fully cached by Nginx so no queries are made when users access the website.
Also the website has ~300 users browsing at any given time, so access_logs are pretty hard to browse. I can get you the uploadify.php request but whats the point.

But I say again, the problem is NOT only when I upload images. I used that as an example.
MySQL overloads with queries when I create a new album, when I browse some pages in admin panel, apply settings and so on.  (admin panel is not cached by Nginx or anything else)

So I don't believe the problem is with uploadify.php.

Offline

 

#13 2013-10-02 10:17:48

AndyF
Guest

Re: Huge amounts of queries to MySQL

Not sure if this is related but here goes.

What's happening here?

"Warning: mysqli::mysqli() [mysqli.mysqli]: (42000/1203): User ********* already has more than 'max_user_connections' active connections in /home/*********/gallery/include/dblayer/functions_mysqli.inc.php on line 61

Warning: my_error() [function.my-error]: Couldn't fetch mysqli in /home/***********/gallery/include/dblayer/functions_mysqli.inc.php on line 826

Warning: my_error() [function.my-error]: Couldn't fetch mysqli in /home/*********/gallery/include/dblayer/functions_mysqli.inc.php on line 826
Piwigo encountered a non recoverable error

[mysql error ]
Can't connect to server

#1    my_error /home/*********/gallery/include/common.inc.php(119)
#2    include_once /home/*********/gallery/index.php(26)"

One day site was working then poof! gone.

URL is gallery.bromsgroverfc.co.uk
Help!!

 

#14 2013-10-02 10:21:33

plg
Piwigo Team
Nantes, France, Europe
2002-04-05
13786

Re: Huge amounts of queries to MySQL

Hi AndyF, please open another topic. I don't think it is related.

Offline

 

#15 2013-10-02 10:28:42

plg
Piwigo Team
Nantes, France, Europe
2002-04-05
13786

Re: Huge amounts of queries to MySQL

jimaek wrote:

The front-end is fully cached by Nginx so no queries are made when users access the website.

Are you 100% sure of that? Please monitor the web server log to know which HTTP request are performed on Piwigo. If you have 300 users browsing your gallery and your cache does not work as expected, the number of queries is maybe the right one (one page displayed = 15 to 30 SQL queries).

jimaek wrote:

MySQL overloads with queries when I create a new album, when I browse some pages in admin panel, apply settings and so on.  (admin panel is not cached by Nginx or anything else)

OK, let's make a debug test.

With plugin LocalFiles Editor, in the the "local config", add

<?php
$conf['show_gt'] = true;
$conf['show_queries'] = true;
?>

Then go to [Administration > Albums > Manage] and create a new album. After the list of albums, Piwigo displays all SQL queries. Copy them and paste them here. Then remove $conf['show_queries'] = true; from your local configuration.

Offline

 

Board footer

Powered by FluxBB

github twitter newsletter Donate Piwigo.org © 2002-2024 · Contact