Why so many DB queries? (SQL optimization issues)

mkochano

Joined: 2008-12-05
Posts: 27
Posted: Thu, 2012-10-18 20:43

Hi

My G3 has always been slow. top indicates heavy usage of MySQL, so I've looked what it is doing. This is list of queries executed just to return single image thumbnail:

121018 22:32:53   716 Connect   gallery3@localhost on
                  716 Init DB   gallery3
                  716 Query     SET NAMES utf8
                  716 Query     SHOW TABLES LIKE '%'
                  716 Query     SELECT *
FROM `caches`
WHERE `key` IN ('var_cache')
                  716 Query     SHOW COLUMNS FROM `modules`
                  716 Query     SELECT `modules`.*
FROM `modules`
WHERE `name` = 'gallery'
ORDER BY `modules`.`id` asc
LIMIT 1
                  716 Query     SELECT `modules`.*
FROM `modules`
ORDER BY `weight`
                  716 Query     SELECT `data`
FROM `sessions`
WHERE `session_id` = '2an298q5b7kvr7pqc8565lb315'
LIMIT 1
                  716 Query     SHOW COLUMNS FROM `users`
                  716 Query     SELECT `users`.*
FROM `users`
WHERE `users`.`id` = '3'
ORDER BY `users`.`id` asc
LIMIT 1
                  716 Query     SHOW COLUMNS FROM `items`
                  716 Query     SELECT `items`.*
FROM `items`
WHERE `relative_path_cache` = '989_Rok-2012/292_Wycieczka-motocyklowa-Kampinos-Niepokalanow-Guzow-23.09.2012/DSC_0481_fx.JPG'
LIMIT 1
                  716 Query     SHOW COLUMNS FROM `groups`
                  716 Query     SELECT `group_id` AS `id`
FROM `groups_users`
WHERE `groups_users`.`user_id` = '3'
                  716 Query     SELECT `groups`.*
FROM `groups`
WHERE `groups`.`id` IN ('1', '2', '3', '5')
ORDER BY `groups`.`id` asc

It's a lot, especially considering that it's not first request in session.

Why is it reading list of tables ("SHOW TABLES LIKE '%'")?

Why is it reading columns from items, users, modules and groups ("SHOW COLUMNS FROM `items`" and similar)?

Why is it reading user info ("SELECT `users`.* FROM `users` WHERE `users`.`id` = '3'")?

Why is it reading groups info ("SELECT `group_id` AS `id` FROM `groups_users` WHERE `groups_users`.`user_id` = '3'; SELECT `groups`.* FROM `groups` WHERE `groups`.`id` IN ('1', '2', '3', '5') ORDER BY `groups`.`id` asc")?

It does all of this for each and every thumbnail on the album page. It's incredible waste of time and resources. All of this should be cached in session for example.

Is my installation misconfigured? It's 3.0.3, because 3.0.4 has some problem with names with multiple dots.

Regards
Michał
 
morganfeldon@gm...
morganfeldon@gmail.com's picture

Joined: 2009-06-22
Posts: 31
Posted: Sun, 2012-12-02 23:34

I understand some queries are to allow flexibility in Modules to add columns to different tables, but the query count does seem high. I just enabled Profiling in Kohana to see this just to confirm the OP's observations.

SHOW TABLES LIKE '%'

Why?

SHOW COLUMNS FROM `modules`
SHOW COLUMNS FROM `users`
SHOW COLUMNS FROM `items`
SHOW COLUMNS FROM `groups`
SHOW COLUMNS FROM `albumpassword_idcaches`
SHOW COLUMNS FROM `items_albumpasswords`
SHOW COLUMNS FROM `access_caches`
SHOW COLUMNS FROM `tags`

Can't columns be determined procedurally from the result set?

SELECT `modules`.* FROM `modules` WHERE `name` = 'gallery' ORDER BY `modules`.`id` asc LIMIT 1
SELECT `modules`.* FROM `modules` ORDER BY `weight`

Which is faster, SQL sorting or PHP sorting?

SELECT `users`.* FROM `users` WHERE `users`.`id` = '1' ORDER BY `users`.`id` asc LIMIT 1
SELECT `users`.* FROM `users` WHERE `id` = 1 ORDER BY `users`.`id` asc LIMIT 1
SELECT `users`.* FROM `users` WHERE `id` = '3' ORDER BY `users`.`id` asc LIMIT 1

Why hit `users` table more than once?

SELECT `items`.* FROM `items` WHERE `id` = 1 LIMIT 1 0.000 1
SELECT `group_id` AS `id` FROM `groups_users` WHERE `groups_users`.`user_id` = '1' 0.000 1
SELECT `groups`.* FROM `groups` WHERE `groups`.`id` IN ('1') ORDER BY `groups`.`id` asc 0.000 1
SELECT `items`.* FROM `items` WHERE `items`.`id` = 1 LIMIT 1 0.000 1
SELECT `group_id` AS `id` FROM `groups_users` WHERE `groups_users`.`user_id` = '1' 0.000 1
SELECT `groups`.* FROM `groups` WHERE `groups`.`id` IN ('1') ORDER BY `groups`.`id` asc 0.000 1

The same 3 queries twice.

SELECT `access_caches`.* FROM `access_caches` WHERE `item_id` = '1' ORDER BY
`access_caches`.`id` asc LIMIT 1
SELECT `access_caches`.* FROM `access_caches` WHERE `item_id` = '3' ORDER BY
`access_caches`.`id` asc LIMIT 1
SELECT `access_caches`.* FROM `access_caches` WHERE `item_id` = '4' ORDER BY
`access_caches`.`id` asc LIMIT 1
SELECT `access_caches`.* FROM `access_caches` WHERE `item_id` = '5' ORDER BY
`access_caches`.`id` asc LIMIT 1
SELECT `access_caches`.* FROM `access_caches` WHERE `item_id` = '6' ORDER BY
`access_caches`.`id` asc LIMIT 1
SELECT `access_caches`.* FROM `access_caches` WHERE `item_id` = '7' ORDER BY
`access_caches`.`id` asc LIMIT 1
SELECT `access_caches`.* FROM `access_caches` WHERE `item_id` = '8' ORDER BY
`access_caches`.`id` asc LIMIT 1
SELECT `access_caches`.* FROM `access_caches` WHERE `item_id` = '9' ORDER BY
`access_caches`.`id` asc LIMIT 1
SELECT `access_caches`.* FROM `access_caches` WHERE `item_id` = '10' ORDER BY
`access_caches`.`id` asc LIMIT 1
SELECT `access_caches`.* FROM `access_caches` WHERE `item_id` = '11' ORDER BY
`access_caches`.`id` asc LIMIT 1
SELECT `access_caches`.* FROM `access_caches` WHERE `item_id` = '13' ORDER BY
`access_caches`.`id` asc LIMIT 1
SELECT `access_caches`.* FROM `access_caches` WHERE `item_id` = '14' ORDER BY
`access_caches`.`id` asc LIMIT 1

This is unbelievable. Why not:

SELECT `access_caches`.* FROM `access_caches` WHERE `item_id` IN(1,3,4,5,6,7,8,9,10,11,13,14) ORDER BY `access_caches`.`id`;

Gallery 3 seems very fast, but all MySQL queries have overhead, especially as the server gets bogged down. Looking forward to a response. :)
 
bozek

Joined: 2012-12-04
Posts: 4
Posted: Tue, 2012-12-04 14:46

Hello,
first of all I have to thank you all for great gallery My girlfriend has more then 6000 photos in gallery 3.0.4 and I have some performance problems with that. In
closer look I find that gallery makes over 3000 queris on one page click.

More then 2700 queries are from EXIF GPS module: "SELECT `exif_coordinates`.* FROM `exif_coordinates` WHERE `item_id` = ..."

and I can not figure out from where comes next 200 queries "SELECT `items`.* FROM `items` WHERE `type` = 'album' AND `parent_id` = ..."

Does anyone have similar problems?
Is it possible to rewrite code to one query like "where item_id in (..., ..., ...)" ?

Best regards, Bozek
 
morganfeldon@gm...
morganfeldon@gmail.com's picture

Joined: 2009-06-22
Posts: 31
Posted: Tue, 2012-12-04 15:14

Hopefully this is not a Kohana limitation. And I'm hoping that optimization wouldn't break Modules and Themes.
 
bozek

Joined: 2012-12-04
Posts: 4
Posted: Thu, 2012-12-06 12:49

So... 2700 queries which I had on EXIF GPS module I rewrite to only one query, so if someone would the same, here is old modules/exif_gps/views/exif_gps_coordinates_xml.html.php:

Quote:
<?php defined("SYSPATH") or die("No direct script access.") ?>
<? print "<?xml version=\"1.0\" encoding=\"UTF-8\"?>\n"; ?>
<markers>
<? foreach ($items as $item) { ?>
<? $item_coordinates = ORM::factory("exif_coordinate")->where("item_id", "=", $item->id)->find(); ?>
<? $str_thumb_html = str_replace("&", "&amp;", $item->thumb_img(array("class" => "g-exif-gps-thumbnail"))); ?>
<? $str_thumb_html = str_replace("\'", "&apos;", $str_thumb_html); ?>
<? $str_thumb_html = str_replace("<", "&lt;", $str_thumb_html); ?>
<? $str_thumb_html = str_replace(">", "&gt;", $str_thumb_html); ?>
<? $str_thumb_html = str_replace("\"", "&quot;", $str_thumb_html); ?>
<marker lat="<?= $item_coordinates->latitude; ?>" lng="<?= $item_coordinates->longitude; ?>" url="<?= url::abs_site("exif_gps/item/{$item->id}"); ?>" thumb="<?=$str_thumb_html; ?>" />
<? } ?>
</markers>

and here is my new one:

Quote:
<?php defined("SYSPATH") or die("No direct script access.") ?>
<? print "<?xml version=\"1.0\" encoding=\"UTF-8\"?>\n"; ?>
<markers>
<?
$thumb_arr=array();
$items_id=array();;
foreach ($items as $item) {
array_push($items_id, $item->id);
$thumb_arr[$item->id] = $item->thumb_img(array("class" => "g-exif-gps-thumbnail"));
}
$item_coordinates_all = ORM::factory("exif_coordinate")->where("item_id", "IN", $items_id)->find_all();

foreach ($item_coordinates_all as $item_coordinates) {
$str_thumb_html = str_replace("&", "&amp;", $thumb_arr[$item_coordinates->item_id]);
$str_thumb_html = str_replace("\'", "&apos;", $str_thumb_html);
$str_thumb_html = str_replace("<", "&lt;", $str_thumb_html);
$str_thumb_html = str_replace(">", "&gt;", $str_thumb_html);
$str_thumb_html = str_replace("\"", "&quot;", $str_thumb_html);
?>
<marker lat="<?= $item_coordinates->latitude; ?>" lng="<?= $item_coordinates->longitude; ?>" url="<?= url::abs_site("exif_gps/item/$item_coordinates->item_id"); ?>" thumb="<?=$str_thumb_html; ?>" />
<? } ?>
</markers>

Another 200 queries comes from module Album Tree which I rewrite in future I hope :)

BTW: Im not programmer, so please excuse my syntax...
 
jnash
jnash's picture

Joined: 2004-08-02
Posts: 814
Posted: Thu, 2012-12-06 14:57

@bozek: what version of the exif_gps module are you using? v2 doesn't seem to include the code you posted above, or the massive query issues you've described. I haven't looked at what the latest ver is...

Also, you should post this in the author's thread as well, as there is a better chance for him to see it:
http://gallery.menalto.com/node/94762
 
bozek

Joined: 2012-12-04
Posts: 4
Posted: Fri, 2012-12-07 05:23
jnash wrote:
@bozek: what version of the exif_gps module are you using? v2 doesn't seem to include the code you posted above, or the massive query issues you've described. I haven't looked at what the latest ver is...

Also, you should post this in the author's thread as well, as there is a better chance for him to see it:
http://gallery.menalto.com/node/94762

I think that I have the newest version downloaded from here http://gallery.menalto.com/node/94762 The code above is in exif_gps.zip/uzip://exif_gps/views/exif_gps_coordinates_xml.html.php file from this link.

Number of queris depends on number of photos with gps data in exif. In my case I have 2731 photos so I had 2731 queris.
 
rWatcher
rWatcher's picture

Joined: 2005-09-06
Posts: 722
Posted: Wed, 2012-12-12 00:52
bozek wrote:
So... 2700 queries which I had on EXIF GPS module I rewrite to only one query, so if someone would the same, here is old modules/exif_gps/views/exif_gps_coordinates_xml.html.php:

Erm, sorry about that, I've sent your fix to github:
https://github.com/gallery/gallery3-contrib/pull/130
 
mkochano

Joined: 2008-12-05
Posts: 27
Posted: Sun, 2012-12-16 18:57

I've found the reason for the "SHOW TABLES LIKE '%'" query.

There is a bug(?) in "modules/gallery/libraries/MY_Database.php". The method "add_table_prefixes" reads list of tables, even if the "table_prefix" is not set.

Simplest solution:

--- /tmp/gallery-3.0.3/modules/gallery/libraries/MY_Database.php     2012-04-03 04:38:25.000000000 +0200
+++ MY_Database.php     2012-12-16 19:51:16.327288275 +0100
@@ -47,6 +47,8 @@

   public function add_table_prefixes($sql) {
     $prefix = $this->config["table_prefix"];
+    if($prefix == '')
+      return $sql;
     if (strpos($sql, "SHOW TABLES") === 0) {
       /*
        * Don't ignore "show tables", otherwise we could have a infinite
@@ -85,4 +87,4 @@
   static function set_default_instance($db) {
     self::$instances["default"] = $db;
   }
-}
\ Brak znaku nowej linii na końcu pliku
+}

The result of the "SHOW TABLES LIKE '%'" query is cached for duration of the request in $this->_table_names, but it should be cached at least for duration of session. Database structure is not going to change during the session. Not too often, at least. Note that without session-level cache this query is executed for every thumbnail on the page.

Regards
Michał
 
mkochano

Joined: 2008-12-05
Posts: 27
Posted: Sun, 2012-12-16 19:33

Hmmm.... the patch above doesn't always work, because original code changes some queries like this:

BEFORE: UPDATE {items} SET `view_count` = `view_count` + 1 WHERE `id` = 1
AFTER:  UPDATE items SET `view_count` = `view_count` + 1 WHERE `id` = 1

I don't know if these braces are Kohana's feature, but simple removal of braces seems to fix it:

--- /tmp/gallery-3.0.3/modules/gallery/libraries/MY_Database.php     2012-04-03 04:38:25.000000000 +0200
+++ MY_Database.php     2012-12-16 19:51:16.327288275 +0100
@@ -47,6 +47,8 @@

   public function add_table_prefixes($sql) {
     $prefix = $this->config["table_prefix"];
+    if($prefix == '')
+      return strtr($sql, array('{' => '', '}' => ''));
     if (strpos($sql, "SHOW TABLES") === 0) {
       /*
        * Don't ignore "show tables", otherwise we could have a infinite
@@ -85,4 +87,4 @@
   static function set_default_instance($db) {
     self::$instances["default"] = $db;
   }
-}
\ Brak znaku nowej linii na końcu pliku
+}

I know this is not a proper solution, but the original behaviour isn't proper either.

Regards
Michał