Advanced search  

News:

cpg1.5.48 Security release - upgrade mandatory!
The Coppermine development team is releasing a security update for Coppermine in order to counter a recently discovered vulnerability. It is important that all users who run version cpg1.5.46 or older update to this latest version as soon as possible.
[more]

Pages: 1 [2]   Go Down

Author Topic: Hosting problem - Slow database queries lot of time  (Read 9602 times)

0 Members and 1 Guest are viewing this topic.

Αndré

  • Administrator
  • Coppermine addict
  • *****
  • Country: de
  • Offline Offline
  • Gender: Male
  • Posts: 15760
Re: Hosting problem - Slow database queries lot of time
« Reply #20 on: July 11, 2016, 04:56:02 pm »

Find
Code: [Select]
    // Meta albums
    switch($album) {
and above, add
Code: [Select]
return;to disable all meta albums.


This is a problem of my ISP MySQL DB?

I think so. It seems the MySQL server has a very bad performance, for whatever reason.
Logged

ikar

  • Coppermine novice
  • *
  • Offline Offline
  • Posts: 21
Re: Re: Hosting problem - Slow database queries lot of time
« Reply #21 on: July 11, 2016, 06:12:45 pm »

Find
Code: [Select]
    // Meta albums
    switch($album) {
and above, add
Code: [Select]
return;to disable all meta albums.

Above? Or below?

switch($album) {
return;
...
}

Logged

ikar

  • Coppermine novice
  • *
  • Offline Offline
  • Posts: 21
Re: Re: Hosting problem - Slow database queries lot of time
« Reply #22 on: July 11, 2016, 06:17:24 pm »

Well :)


return;
switch($album) {

...
}
Logged

ikar

  • Coppermine novice
  • *
  • Offline Offline
  • Posts: 21
Re: Hosting problem - Slow database queries lot of time
« Reply #23 on: July 13, 2016, 02:32:26 pm »

Hi. Last day - 0 slow queries
Sorry - I don't understand anything in SQL. I try to send your answer to my ISP.
This is his answer (sorry foe automatically Google translated)
-----------------------------
In this case, particularly the performance of your site galleries are not so important, you need to analyze and optimize database queries it.
Optimization of queries to the database should be limited to the following results:
- In all correspondence indexes are used to retrieve data,
- Minimize the use of temporary files and filesort operations.

All these data for each of the queries can be obtained using EXPLAIN: http://dev.mysql.com/doc/refman/5.6/en/explain.html

Previously, we have provided you slow query log. Analyze requests from any given one of the log files, for example:

SELECT r.pid, r.aid, filepath, filename, url_prefix, pwidth, pheight, filesize, ctime, r.title
                FROM cpg14x_pictures AS r
                INNER JOIN cpg14x_albums AS a ON a.aid = r.aid
                WHERE (1)
                AND approved = 'YES'
                AND hits> 0
                ORDER BY hits DESC, pid ASC
                 LIMIT 4505, 5;


Using EXPLAIN, execute this query:
mysql> explain
SELECT r.pid, r.aid, filepath, filename, url_prefix, pwidth, pheight, filesize, ctime, r.title
    -> FROM cpg14x_pictures AS r
    -> INNER JOIN cpg14x_albums AS a ON a.aid = r.aid
    -> WHERE (1)
    -> AND approved = 'YES'
    -> AND hits> 0
                 LIMIT 4505, 5 -> ORDER BY hits DESC, pid ASC
    -> LIMIT 4505, 5;
+----+-------------+-------+--------+-------------------------------+----------+---------+-------------------------+-------+- ---------------------------- +
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+-------------------------------+----------+---------+-------------------------+-------+- ---------------------------- +
| 1 | SIMPLE | r | range | pic_hits, aid_approved, pic_aid | pic_hits | 4 | NULL | 19381 | Using where; Using filesort |
| 1 | SIMPLE | a | eq_ref | PRIMARY | PRIMARY | 4 | wwwikarin_gallery.r.aid | 1 | Using index |
+----+-------------+-------+--------+-------------------------------+----------+---------+-------------------------+-------+- ---------------------------- +
2 rows in set (0.02 sec)


As can be seen, the sample is derived from a table with 19,381 lines. Yes, indexes are used, but the sample data from the 19381 using Using filesort type of request in any case will move all the lines and creating temporary files (which in turn can cause a strain on the drive). Using LIMIT in this case does not bring much good, so going on a first sampling of all the rows, and then cut off in accordance with LIMIT.

And such requests, according to the logs, there is a very large number. This and similar indicators of resources.
Logged

Αndré

  • Administrator
  • Coppermine addict
  • *****
  • Country: de
  • Offline Offline
  • Gender: Male
  • Posts: 15760
Re: Hosting problem - Slow database queries lot of time
« Reply #24 on: July 13, 2016, 10:36:43 pm »

They suggest to use (additional) indexes in the pictures table. May be worth a try. If I have some spare time, I'll make some tests with "EXPLAIN" on my local testbed.

If some database expert reads this, feel free to reply with some useful tips :)
Logged
Pages: 1 [2]   Go Up
 

Page created in 0.02 seconds with 20 queries.