Optimize requests of CPG for next versions ? ...

[Rodinou]

Where do we change this ?

actually mysql on my server is configured like this

back log  50 
basedir  / 
binlog cache size  32768 
character set  latin1 
character sets  latin1 big5 czech euc_kr gb2312 gbk sjis tis620 ujis dec8 dos german1 hp8 koi8_ru latin2 swe7 usa7 cp1251 danish hebrew win1251 estonia hungarian koi8_ukr win1251ukr greek win1250 croat cp1257 latin5 
concurrent insert  ON 
connect timeout  20 
datadir  /var/lib/mysql/ 
delay key write  ON 
delayed insert limit  100 
delayed insert timeout  300 
delayed queue size  1000 
flush  OFF 
flush time  0 
have bdb  NO 
have gemini  NO 
have innodb  NO 
have isam  YES 
have raid  NO 
have openssl  NO 
init file   
interactive timeout  28800 
join buffer size  131072 
key buffer size  8388600 
language  /usr/share/mysql/english/ 
large files support  ON 
locked in memory  OFF 
log  OFF 
log update  OFF 
log bin  OFF 
log slave updates  OFF 
log long queries  ON 
long query time  4 
low priority updates  OFF 
lower case table names  0 
max allowed packet  1048576 
max binlog cache size  4294967295 
max binlog size  1073741824 
max connections  270 
max connect errors  10 
max delayed threads  20 
max heap table size  16777216 
max join size  4294967295 
max sort length  1024 
max user connections  0 
max tmp tables  32 
max write lock count  4294967295 
myisam max extra sort file size  256 
myisam max sort file size  2047 
myisam recover options  0 
myisam sort buffer size  8388608 
net buffer length  16384 
net read timeout  30 
net retry count  10 
net write timeout  60 
open files limit  0 
pid file  /var/lib/mysql/NAME OF MY SERVER
port  3306 
protocol version  10 
record buffer  131072 
record rnd buffer  131072 
query buffer size  0 
safe show database  OFF 
server id  0 
slave net timeout  3600 
skip locking  ON 
skip networking  OFF 
skip show database  OFF 
slow launch time  2 
socket  /var/lib/mysql/mysql.sock 
sort buffer  2097144 
sql mode  0 
table cache  64 
table type  MYISAM 
thread cache size  0 
thread stack  65536 
transaction isolation  READ-COMMITTED 
timezone  CEST 
tmp table size  33554432 
tmpdir  /tmp/ 
version  3.23.58-log 
wait timeout  28800 

[donnoman]

Mine is configured in my my.ini; it could be my.cnf depending on your configuration.

just add :

query_cache_size=16M
query_cache_type=on

to that whatever file you printed that config from,

another mysql query cache tuning doc

You can also change it in the MySQL Admin GUI Client under server configuration/performance.

You have to restart mysql after changing the config.

Other sql queries that will tell you the status of query caching:

Code Select Expand


SHOW VARIABLES LIKE '%query_cache%'
SHOW STATUS LIKE 'Qcache%'


[athlonkmf]

funny to see this thread, i see you've posted on my other thread too http://forum.coppermine-gallery.net/index.php?topic=14663.0

The changes I made there had reduced my gallery extremely already. But i've also made a security lock in it too, if the load reaches a certain degree, it'd temporarely disable usage of coppermine too.

If you are using linux you can add this code in init.inc.php. If the load goes above 25, it'll lock users out so that the server can handle things first. I find load 50 to be the limit, if you goes over that, you have a large chance that your server crashes.

If someone interested, i'll try to find what i've done to reduce load and let you guys mod it.

Code Select Expand



//check for serverload
if ( file_exists('/proc/loadavg') )
        {
        if ( $fh = @fopen( '/proc/loadavg', 'r' ) )
        {
        $data = @fread( $fh, 6 );
        @fclose( $fh );
       
        $load_avg = explode( " ", $data );
      $server_load = trim($load_avg[0]);
        echo "Current serverload: ".$server_load ;
        if ($server_load > 25)
        {
pageheader("Server too busy, please try later");
msg_box("Server too busy, please try later", "Server too busy, please try later<br> current load $server_load ");
pagefooter();
exit;
        }
        }
        }




put this before

Code Select Expand




if (!USER_IS_ADMIN && $CONFIG['offline'] && !strstr($_SERVER["SCRIPT_NAME"],'login')) {
pageheader($lang_errors['offline_title']);
msg_box($lang_errors['offline_title'], $lang_errors['offline_text']);
pagefooter();
exit;
}



[RatKing]

Given a sample database and a explanation of how to get to the point where it crasses your server I would be willing to look into optimizing these sql statments for you.

It seems quite strange to me that anyone would need to make such a bige exclude list. It might just be inexperiance with SQL or with optimizing of SQL that caused these statments to be created in the first place but I am sure that these can be optimized with a little TLC. 

If you want me to have a go at me drop me a email or a PM and point me to a sample database so I can slap that on a test system and have a go at optimizing these quries for you.

[donnoman]

Ratking do you have a 1.4 development Gallery setup?

If you could post some of your ideas at optimizing SQL queries we can certainly discuss them. Once we have some workable queries that show significant perfomance gains without sacrificing features I can talk to the other devs and start fitting them into the roadmap.

If you just need to setup your dev gallery, search the boards for timer.php. Drag a few directories of pictures into your albums folder and run timer.php against it, you'll have a decent sized test gallery in no time. Complete with Categories, Albums, and Images.

[ulikoeln]

Regarding the Usage of the mysql query cache: I have looked at the 1.4 DB schema, and it still has a structure
that (partly) makes caching useless. One of the most-used tables of course is the "pictures" table, and this
table has counter values for "votes" and "views" for each picture. So if voting and/or counting is enabled ,
the content of the picture table changes frequently and the query cache can never be used.
This could only be circumvented with a kind of "queue" approach: collect counts and votes in a  "queue" table and insert the values into the picture table in batches, like once per hour or so (admin setting). This will
make it possible to benefit from the query cache.

[Sci7]

I would like to add my support for much optimising of the SQL queries made by CPG, I think the current 1.3.4 server load is excessive and detracts from an otherwise excellent gallery.

I would like to propose the option to turn off in the admin config the display of forum statistics (view counts, album counts etc.), checking for banned users and updating the banned list.

A preferable option would perhaps be for these counts, and maintainence actions not to occur with every page load but to be scheduled. I realise many users won't have access to cron and requiring a scheduled job to keep these things up to date as standard isn't a good idea, being able to turn them off in the admin control panel, and replacing the function by running a dedicated maintainance php file regularly using cron.

[donnoman]

Theres no reason maintenance routines have to be run by cron. Coppermine already has an example in it that uses a timestamp in the config to make sure some maintenance doesn't happen faster than once an hour.

This same method could be used in other schemes.

[TheGamer1701]

Mine is configured in my my.ini; it could be my.cnf depending on your configuration.

just add :

query_cache_size=16M
query_cache_type=on

to that whatever file you printed that config from,

another mysql query cache tuning doc

You can also change it in the MySQL Admin GUI Client under server configuration/performance.

You have to restart mysql after changing the config.

Other sql queries that will tell you the status of query caching:

Code Select Expand


SHOW VARIABLES LIKE '%query_cache%'
SHOW STATUS LIKE 'Qcache%'


Hey,
just found the topic.
I tried to change/add that to my "my.cnf", but after I did that my mysql rejected my login. I could not use mysql anymore.
So, where exactly do I have to add this?

[Joachim Müller]

this is not a support thread, butt out.

[fractalbit]

Hello all. At the site where i am an administrator we have a large user gallery based on coppermine. You can see it here if you want : http://www.dpgr.gr/usergalleries/index.php?cat=0
The gallery has more than 117 000 photos and more than 20 000 page views per day.

We also have a dedicated dual Xeon server @ 3Ghz and 2Gb ram but the server was crashing almost 3 times per day. So i had to do something about it. I timed several parts of the code and finally located the most heavy queries. The good news is that i managed with some modifications to considerably decrease the page creation times. So lets start with the mainpage...

In our server the main page was created in something like 3 to 4 seconds. Very large time for a gallery with 20 000 page views per day. There were 2 main problems about it.

1) The stats display that say how many photos, albums, comments etc the gallery has took about 0,7 seconds. So i decided to remove the code that gathers the stats from the index.php and place it in a seperate .php file which you can either run manually when you want the stats to be updated or make it a cron job to automatically run every 1 or 4 hours or whatever period you want (but dont do it every minute!).

Here is the modifications you need to do:

1a) Open index.php and comment the following lines :

Code Select Expand

/*$result = cpg_db_query("SELECT count(*) FROM {$CONFIG['TABLE_ALBUMS']} as a WHERE 1" . $album_filter);
        $nbEnr = mysql_fetch_array($result);
        $album_count = $nbEnr[0];
     
......................................... More code here ..................................

        $nbEnr = mysql_fetch_array($result);
        $hit_count = (int)$nbEnr[0];
        mysql_free_result($result);*/

1b) Immediatly after that add the code:

Code Select Expand

$exec = mysql_query("SELECT * FROM gallery_stats LIMIT 0,1") or die(mysql_error());
        $data = mysql_fetch_assoc($exec);
        $album_count = $data['albums'];
        $picture_count = $data['pictures'];
        $comment_count = $data['comments'];
        $cat_count = $data['categories'];
        $hit_count = $data['views'];

Save and upload the file. 2 more steps remaining ...

1c) No go to your database with phpmyadmin and run the following SQL:

Code Select Expand

CREATE TABLE `gallery_stats` (
  `stat_id` tinyint(3) unsigned NOT NULL auto_increment,
  `albums` bigint(20) NOT NULL default '0',
  `pictures` bigint(20) NOT NULL default '0',
  `categories` bigint(20) NOT NULL default '0',
  `comments` bigint(20) NOT NULL default '0',
  `views` bigint(20) NOT NULL default '0',
  PRIMARY KEY  (`stat_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;

--
-- Dumping data for table `gallery_stats`
--

INSERT INTO `gallery_stats` VALUES (1, 0, 0, 0, 0, 0);


This will create a table that will hold the stats that index.php will retrieve and display and that the following file (gallery_stats.php) will generate...

1d) Create a php file gallery_stats.php and put the following code in it

Code Select Expand

<?php

$db_name = "yourDB";
$db_user = "yourUser";
$db_passwd = "yourPass";
$db_server = "localhost";

mysql_connect("localhost", $db_user, $db_passwd);
mysql_select_db($db_name);

$CONFIG['TABLE_ALBUMS'] = "usercpg11d_albums";
$CONFIG['TABLE_PICTURES'] = "usercpg11d_pictures";
$CONFIG['TABLE_COMMENTS'] = "usercpg11d_comments";
$CONFIG['TABLE_CATEGORIES'] = "usercpg11d_categories";

$result = mysql_query("SELECT count(*) FROM {$CONFIG['TABLE_ALBUMS']} as a WHERE 1" . $album_filter);
        $nbEnr = mysql_fetch_array($result);
        $album_count = $nbEnr[0];
        mysql_free_result($result);

        $sql = "SELECT count(*) FROM {$CONFIG['TABLE_PICTURES']} as p " . 'LEFT JOIN ' . $CONFIG['TABLE_ALBUMS'] . ' as a ' . 'ON a.aid=p.aid ' . 'WHERE 1' . $pic_filter . ' AND approved=\'YES\'';
        $result = mysql_query($sql);
        $nbEnr = mysql_fetch_array($result);
        $picture_count = $nbEnr[0];
        mysql_free_result($result);

        $sql = "SELECT count(*) FROM {$CONFIG['TABLE_COMMENTS']} as c " . 'LEFT JOIN ' . $CONFIG['TABLE_PICTURES'] . ' as p ' . 'ON c.pid=p.pid ' . 'LEFT JOIN ' . $CONFIG['TABLE_ALBUMS'] . ' as a ' . 'ON a.aid=p.aid ' . 'WHERE 1' . $pic_filter;
        $result = mysql_query($sql);
        $nbEnr = mysql_fetch_array($result);
        $comment_count = $nbEnr[0];
        mysql_free_result($result);

        $sql = "SELECT count(*) FROM {$CONFIG['TABLE_CATEGORIES']} WHERE 1";
        $result = mysql_query($sql);
        $nbEnr = mysql_fetch_array($result);
        $cat_count = $nbEnr[0] - $HIDE_USER_CAT;
        mysql_free_result($result);

        $sql = "SELECT sum(hits) FROM {$CONFIG['TABLE_PICTURES']} as p " . 'LEFT JOIN ' . $CONFIG['TABLE_ALBUMS'] . ' as a ' . 'ON p.aid=a.aid ' . 'WHERE 1' . $pic_filter;
        $result = mysql_query($sql);
        $nbEnr = mysql_fetch_array($result);
        $hit_count = (int)$nbEnr[0];
        mysql_free_result($result);
        
mysql_query("UPDATE gallery_stats SET albums=$album_count, categories=$cat_count, comments=$comment_count, views=$hit_count, pictures=$picture_count WHERE stat_id=1") or die(mysql_error());

mysql_close();
?>
Just replace the lines with th db connection data with your settings as well the table names. Now save the file and upload it. As i said you can either run it manually ormake it a cron job.



Ok. This is maybe too much for some so lets see another easier modification you can do.

If you have lots of photos and you have enabled the random photos box it will greatly increase your page creation time. So you can either disable it from the admin panel or make the following modification that will decrease the query time from 1,4 secs to about 0,3 :

2a) Open /include/functions.inc.php and find the line :

Code Select Expand

$query = "SELECT $select_columns FROM {$CONFIG['TABLE_PICTURES']} WHERE approved = 'YES' $META_ALBUM_SET ORDER BY RAND() LIMIT $limit2";

Replace it with the following lines :

Code Select Expand

$q = "SELECT pid FROM {$CONFIG['TABLE_PICTURES']} WHERE approved = 'YES' $META_ALBUM_SET ORDER BY RAND() LIMIT $limit2";
                $ex = mysql_query($q) or die(mysql_error());
                $pid_array = array();
                while($d = mysql_fetch_assoc($ex)){
                    $pid_array[] = $d['pid'];
                }
                $pid_string = implode(", ", $pid_array);
               
                $query = "SELECT $select_columns FROM {$CONFIG['TABLE_PICTURES']} WHERE pid IN ($pid_string)";

Save and upload the file. We are done for the mainpage. With these modifications i was able to drop the page creation time of index.php from 3 seconds to 1.

Now another thing that really slows down coppermine on large galleries is top viewed photos. When you visit the top views album and from there you click to see a photo the page will be created in 4 to 5 seconds. The reason for this is the way the filmstrip is generated trying to figure out what is the previous and next photos. So lets drop this time to around 0,5 seconds doing the following modifications:

3a) find the line :

Code Select Expand

$query ="SELECT COUNT(*) from {$CONFIG['TABLE_PICTURES']} WHERE approved = 'YES' AND hits > 0  $META_ALBUM_SET $keyword";

and replace with the following:

Code Select Expand


$cat >= 10000 ? $hit_limit = 0 : $hit_limit = 310;               
$query ="SELECT COUNT(*) from {$CONFIG['TABLE_PICTURES']} WHERE approved = 'YES' AND hits > $hit_limit  $META_ALBUM_SET $keyword";

3b) find the line:

Code Select Expand

$query = "SELECT $select_columns FROM {$CONFIG['TABLE_PICTURES']} WHERE approved = 'YES'AND hits > 0 $META_ALBUM_SET $keyword ORDER BY hits DESC, filename  $limit";

replace with the following :

Code Select Expand

$query = "SELECT $select_columns FROM {$CONFIG['TABLE_PICTURES']} WHERE approved = 'YES'AND hits > $hit_limit $META_ALBUM_SET $keyword ORDER BY hits DESC, filename  $limit";

The only change you need to do is replace 303 with your own number. To find what the number for your gallery should be do this: Go to the 10th page of the top views album and see how much views the last photo of this page has. Now this is the number you should use instead of 303.

The downside of this modification is that you limit the top views album to the first 10 pages. But when you consider that the page creation time drops to 1/10th of original i think its a fair trade. After all, top views is about top, not bottom


So finishing up, i would like to make some things clear.
1) Those solutions may not be the best ones ... but they work.
2) I am not trying to say that the coppermine gallery is not good. I am just trying to help people that have similar proplems with me and maybe offer some ideas for future versions of coppermine.

Hope this helped, try the modifications and tell me what you think.

[Joachim Müller]

Thanks for sharing your thoughts and code tweaks. Will be considered for future versions.

[dke]

Now another thing that really slows down coppermine on large galleries is top viewed photos. When you visit the top views album and from there you click to see a photo the page will be created in 4 to 5 seconds. The reason for this is the way the filmstrip is generated trying to figure out what is the previous and next photos. So lets drop this time to around 0,5 seconds doing the following modifications:

That was one awesome hack, thank you so much. I've used the config tool to make my own button called "Last Viewed" , is there a way to apply this hack to that to? i use string "thumbnails.php?album=lasthits&cat=0" to call the page.

[Nibbler]

No support in feature requests.

If anyone out there has a gallery causing high load on a dedicated server and would consider moving it to my server for analysis please contact me via PM.

[dke]

I actually figured this out my self, if anyone wants to apply this my sloppy way:

go to functions.inc.php

find code:

Code Select Expand

        case 'lasthits': // Last viewed pictures
                if ($META_ALBUM_SET && $CURRENT_CAT_NAME) {
                        $album_name = $lang_meta_album_names['lasthits'].' - '. $CURRENT_CAT_NAME;
                } else {
                        $album_name = $lang_meta_album_names['lasthits'];
                }
                $query = "SELECT COUNT(*) from {$CONFIG['TABLE_PICTURES']} WHERE approved = 'YES' and hits > 0 $META_ALBUM_SET";
                $result = cpg_db_query($query);
                $nbEnr = mysql_fetch_array($result);
                $count = $nbEnr[0];
                mysql_free_result($result);

                //if($select_columns != '*') $select_columns .= ', UNIX_TIMESTAMP(mtime) as mtime, aid, hits, lasthit_ip, owner_id, owner_name';
                $select_columns = '*, UNIX_TIMESTAMP(mtime) as mtime'; //allows building any data into any thumbnail caption

                $query = "SELECT $select_columns FROM {$CONFIG['TABLE_PICTURES']} WHERE approved = 'YES' and hits > 0 $META_ALBUM_SET ORDER BY mtime DESC $limit";
                $result = cpg_db_query($query);
                $rowset = cpg_db_fetch_rowset($result);
                mysql_free_result($result);

                if ($set_caption) build_caption($rowset,array('mtime','hits'));

                $rowset = CPGPluginAPI::filter('thumb_caption_lasthits',$rowset);

                return $rowset;
                break;

change the number within the part " $count = $nbEnr[0]; " to the number of images you want to list on your "Last Viewed" page.

[Nibbler]

No, that is rubbish. Please don't force me to lock the thread just to stop you making invalid posts.