Optimization of CpmFetch in large galleries

[jerx]

I did some further testing. I tried to do what you recommended and isolated the cpm code. My script looks like this:

Code Select Expand

<?php

$starttime = explode(' ', microtime());
$starttime = $starttime[1] + $starttime[0];

// cpmfetch
 include "./gallery/cpmfetch/cpmfetch.php";
 $objCpm = new cpm("./gallery/cpmfetch/cpmfetch_config.php");
 $objCpm->cpm_setReturnType('html');

print $objCpm->cpm_viewRandomMedia(1,2, array('alttag' => '%a, %f'));
print $objCpm->cpm_showAlbumsUpdatedOverLastDays(10, array('alttag' => '%a %w', 'subtitle' => '%a, %f', 'imagelink' => 'album'));
$objCpm->cpm_close();

$mtime = explode(' ', microtime());
$totaltime = $mtime[0] + $mtime[1] - $starttime;
printf('Page loaded in %.3f seconds.', $totaltime);

?>

I found the generation code by googling around. I don' t know if the returned time is comparable with the cpg info at the debug section. The above code parses in about 0.6 seconds the fastest. CPG gallery takes about 0.7 the fastest (140 queries in 0.267 seconds). This seems not to be much of a difference.

By the way, I compared the script to my main gallery page. The contents looks like this: breadcrumb/lastalb,3/lastcom,2/catlist/alblist/random,2/

[vuud]

I did some further testing. I tried to do what you recommended and isolated the cpm code. My script looks like this:

Code Select Expand

<?php

$starttime = explode(' ', microtime());
$starttime = $starttime[1] + $starttime[0];

// cpmfetch
 include "./gallery/cpmfetch/cpmfetch.php";
 $objCpm = new cpm("./gallery/cpmfetch/cpmfetch_config.php");
 $objCpm->cpm_setReturnType('html');

print $objCpm->cpm_viewRandomMedia(1,2, array('alttag' => '%a, %f'));
print $objCpm->cpm_showAlbumsUpdatedOverLastDays(10, array('alttag' => '%a %w', 'subtitle' => '%a, %f', 'imagelink' => 'album'));
$objCpm->cpm_close();

$mtime = explode(' ', microtime());
$totaltime = $mtime[0] + $mtime[1] - $starttime;
printf('Page loaded in %.3f seconds.', $totaltime);

?>

I found the generation code by googling around. I don' t know if the returned time is comparable with the cpg info at the debug section. The above code parses in about 0.6 seconds the fastest. CPG gallery takes about 0.7 the fastest (140 queries in 0.267 seconds). This seems not to be much of a difference.

By the way, I compared the script to my main gallery page. The contents looks like this: breadcrumb/lastalb,3/lastcom,2/catlist/alblist/random,2/

I am gonna have to play with this... I've not had a large enough data set to test against - till now

DB Connections are the largest delay, but I still imagined the time difference would be heavier.  Can you try running it with just view random media?  and not the UpdatedAlbums (that one does a monster SQL query with lots of grouping and stuff).

The benchmarking timing looks fine I think... I was thinking there was a float or somethng in there. but anyway...

Thanks much for the work on this!

[jerx]

Can you try running it with just view random media?  and not the UpdatedAlbums (that one does a monster SQL query with lots of grouping and stuff).

It is a little bit less now - about 0.5 s.

[vuud]

It is a little bit less now - about 0.5 s.

Thanks, can you do just the other one?  Sorry I did not think of it earlier...  if you are in there.  Most of that time is the construction of the db connection, so with the other one, the connection is already made (if both are run at the same time).  Don't worry if you can't - I can try it tonight hopefully.

Do you know offhand if your database is on the same machine or a different one?

My plan, and suggestions are welcome, is to have cpmfetch track three items...

- Construction time (time to start up and read the config)
- SQL time (time to execute SQL calls)
- Rendering time (put into tables and such).

Cool stuff... I'd love to try to optimize cpmfetch some more (not that it makes much of a difference)...

[jerx]

Do you know offhand if your database is on the same machine or a different one?

You mean the showAlbumsUpdatedOverLastDays code?! Look at this: 0.021 s was the best. On the majority of the page loads it was about 0.13 s.

[jerx]

Do you know offhand if your database is on the same machine or a different one?

Forgot to answer this question. The database is on the same machine.

[vuud]

You mean the showAlbumsUpdatedOverLastDays code?! Look at this: 0.021 s was the best. On the majority of the page loads it was about 0.13 s.

Anyone can correct me if I am wrong - I am not a database expert, but it would seem to me that the best time you get would be what cpmfetch is capable of as it is.  Slower times are due to the database or cpu being used elsewhere 

If you turn on debug, it will dump the SQL statements they are running.  I have it on my list now to see if there is anything I can do to improve them...

But I don't know if there is...  I can take out some joins maybe, but then we lose functionality.   If I split the code up to run faster then there is more to maintain and get bugs in.  Sigh.

[jerx]

I am not sure if the slower times are due to used resources. When I ran the last test (updated albums only), in the beginning the server load was even higher than on the random media test. Then the server load went down to the level I had on the random media test. I also checked the other scripts (both calls, random media only) at the same time and the updated albums calls seem to take less page generation time.

I am not a coder, but I took a closer look at the cpmfetch code anyway. I noticed that there are two commands in the random media function which are not present in the updated albums function. One is $this->sqlUserDataLink and the other is $this->getRandomSeed(). Does the first mean that it scans the user database? If so, this might be the problem. My user database is fairly big too (10.000 members) and it is not stored in coppermine (though on the same db).

And the second seems to deal with randomization. I think that the updated albums function scans the whole database. Still this is pretty fast. I think that the random media function scans the whole database, too. Moreover it seems to check all pictures whether they are private or public (the updated albums functions does this, too, but I think you need it). I don' t think that this is necessary. Wouldn' t it be faster to generate a random number and pick the picture with that id. You would only have to check one (or a couple of) picture(s) for privacy and existence. On my setup the first check is not even necessary, because I only have public pictures and do not allow personal galleries in coppermine.

[vuud]

I am not sure if the slower times are due to used resources. When I ran the last test (updated albums only), in the beginning the server load was even higher than on the random media test. Then the server load went down to the level I had on the random media test. I also checked the other scripts (both calls, random media only) at the same time and the updated albums calls seem to take less page generation time.

I am not a coder, but I took a closer look at the cpmfetch code anyway. I noticed that there are two commands in the random media function which are not present in the updated albums function. One is $this->sqlUserDataLink and the other is $this->getRandomSeed(). Does the first mean that it scans the user database? If so, this might be the problem. My user database is fairly big too (10.000 members) and it is not stored in coppermine (though on the same db).

And the second seems to deal with randomization. I think that the updated albums function scans the whole database. Still this is pretty fast. I think that the random media function scans the whole database, too. Moreover it seems to check all pictures whether they are private or public (the updated albums functions does this, too, but I think you need it). I don' t think that this is necessary. Wouldn' t it be faster to generate a random number and pick the picture with that id. You would only have to check one (or a couple of) picture(s) for privacy and existence. On my setup the first check is not even necessary, because I only have public pictures and do not allow personal galleries in coppermine.

The $this->sqlUserData is not a function, but a member variable that holds the sql to link to the user tables (varies by configuration), so that is just inserted.

$this->getRandomSeed() is just an object function that returns a random number.

Here is the SQL code for cpm_viewRandomMedia

(Note: This is from a dev version, but may not be the most recent version - its all I can access in debug from where I am now)

Code Select Expand



SELECT p.filepath AS pFilepath, p.filename AS pFilename, p.aid AS pAid, p.filesize AS pFilesize, p.title AS pTitle, p.caption AS pCaption, p.owner_name AS pOwner_name, p.owner_id as pOwnerId, p.ctime AS pCtime, p.hits AS pHits, p.pid AS pPid, p.pic_rating AS pPic_Rating, p.votes AS pVotes, p.pwidth AS pWidth, p.pheight AS pHeight, p.user1 as pUser1, p.user2 as pUser2, p.user3 as pUser3, p.user4 as pUser4, c.cid as cCid, c.name as cName, c.description as cDescription, c.pos as cPos, c.parent as cParent, c.thumb as cThumb, u.user_lastvisit AS uUser_lastvisit,u.user_regdate AS uUser_regdate,u.user_email AS uUser_email,u.user_profile1 AS uUser_profile1,u.user_profile2 AS uUser_profile2,u.user_profile3 AS uUser_profile3,u.user_profile4 AS uUser_profile4,u.user_profile5 AS uUser_profile5,u.user_profile6 AS uUser_profile6, a.aid AS aAid, a.title AS aTitle, a.description AS aDescription, a.visibility as aVisibility, a.pos as aPos, a.category as aCategory, a.thumb as aThumb, a.keyword as aKeyword

FROM cpg132_pictures AS p LEFT JOIN cpg132_users AS u ON p.owner_id = u.user_id LEFT JOIN cpg132_albums AS a ON p.aid = a.aid LEFT JOIN cpg132_categories AS c ON a.category = c.cid

WHERE 1 AND p.approved='YES' AND ((a.visibility = 0 OR a.visibility IS NULL) AND (a.alb_password IS NULL OR a.alb_password = "")) ORDER BY rand(24326) LIMIT 1



If you turn debug on, it will spit this out for every call you make.

The random seed call just sticks the 24326 into the SQL rand function.  As you can see it does check public or private, etc...  Its hard to see in the code because of all the variable substitutions and making the SQL statement code on the fly.

The thing that is probably slowing things down is that I join to everything I can.  This is due to the subtitle abilities and such, where that information is needed.  You can eliminate one of these by setting:

cfBridgedTo in the config file to "blahblahblah" - or anything that is not false and not a bridge

That will omit user lookups and linking.  That will render anything that depends on user information not available. 

There may also be some caching... or something.  I just ran the sql manually twice in a row...

1 row in set (0.07 sec)
1 row in set (0.00 sec)
1 row in set (0.00 sec)
1 row in set (0.00 sec)

I have a *very* small test set here...

The Mysql explain directive produced this:

Code Select Expand


+----+-------------+-------+--------+---------------+---------+---------+----------------+------+----------------------------------------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref            | rows | Extra                                        |
+----+-------------+-------+--------+---------------+---------+---------+----------------+------+----------------------------------------------+
|  1 | SIMPLE      | p     | ALL    | NULL          | NULL    |    NULL | NULL           |    8 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | u     | eq_ref | PRIMARY       | PRIMARY |       4 | cpg.p.owner_id |    1 |                                              |
|  1 | SIMPLE      | a     | eq_ref | PRIMARY       | PRIMARY |       4 | cpg.p.aid      |    1 | Using where                                  |
|  1 | SIMPLE      | c     | eq_ref | PRIMARY       | PRIMARY |       4 | cpg.a.category |    1 |                                              |
+----+-------------+-------+--------+---------------+---------+---------+----------------+------+----------------------------------------------+


Which I need to sit down and figure out if its anything useful

[vuud]

I've moved this to its own thread, as it is important enough.

This discussion only applies to the 1.9.x series and better.  The 1.6.x and earlier will not be optimized

[jerx]

The $this->sqlUserData is not a function, but ...
If you turn debug on, it will spit this out for every call you make.

Sorry, I should not do wild guessing. I really need to get into coding. Right now it is like reading Chinese.
I turned debug on and the random media function seems to have less work to do. Still it takes much longer. 

In case you need the debug information:
cpm_showAlbumsUpdatedOverLastDays(10, array('alttag' => '%a %w', 'subtitle' => '%a, %f', 'imagelink' => 'album'));

Code Select Expand

cpm_dao. Setting cfFilesystemPathToAlbums to /gallery//albums/
SQL : SELECT COUNT(*) FROM cpg_pictures AS p, cpg_albums AS a WHERE p.aid = a.aid AND ctime > 1170836797 AND p.approved='YES'AND ((a.visibility = 0 OR a.visibility IS NULL) AND (a.alb_password IS NULL OR a.alb_password = "")) ROWS: 1
SQL : SELECT p.filepath AS pFilepath, p.filename AS pFilename, p.aid AS pAid, p.filesize AS pFilesize, p.title AS pTitle, p.caption AS pCaption, p.owner_name AS pOwner_name, p.owner_id as pOwnerId, p.ctime AS pCtime, p.hits AS pHits, p.pid AS pPid, p.pic_rating AS pPic_Rating, p.votes AS pVotes, p.pwidth AS pWidth, p.pheight AS pHeight, p.user1 as pUser1, p.user2 as pUser2, p.user3 as pUser3, p.user4 as pUser4, c.cid as cCid, c.name as cName, c.description as cDescription, c.pos as cPos, c.parent as cParent, c.thumb as cThumb, u.user_lastvisit AS uUser_lastvisit,u.user_regdate AS uUser_regdate,u.user_email AS uUser_email,u.user_profile1 AS uUser_profile1,u.user_profile2 AS uUser_profile2,u.user_profile3 AS uUser_profile3,u.user_profile4 AS uUser_profile4,u.user_profile5 AS uUser_profile5,u.user_profile6 AS uUser_profile6, a.aid AS aAid, a.title AS aTitle, a.description AS aDescription, a.visibility as aVisibility, a.pos as aPos, a.category as aCategory, a.thumb as aThumb, a.keyword as aKeyword, c.name AS cName, count( p.pid ) AS count FROM cpg_pictures AS p LEFT JOIN cpg_users AS u ON p.owner_id = u.user_id LEFT JOIN cpg_albums AS a ON p.aid = a.aid LEFT JOIN cpg_categories AS c ON a.category = c.cid WHERE a.category = c.cid AND p.approved='YES' AND ((a.visibility = 0 OR a.visibility IS NULL) AND (a.alb_password IS NULL OR a.alb_password = "")) AND p.ctime > 1170836797 GROUP BY a.category, a.aid ORDER BY c.name, p.mtime ROWS: 9
DEPRECIATED: Avoid using % style option array calls. Pls use {{ }} style
DEPRECIATED: Avoid using % style option array calls. Pls use {{ }} style
DEPRECIATED: Avoid using % style option array calls. Pls use {{ }} style
DEPRECIATED: Avoid using % style option array calls. Pls use {{ }} style
DEPRECIATED: Avoid using % style option array calls. Pls use {{ }} style
DEPRECIATED: Avoid using % style option array calls. Pls use {{ }} style
DEPRECIATED: Avoid using % style option array calls. Pls use {{ }} style
DEPRECIATED: Avoid using % style option array calls. Pls use {{ }} style
DEPRECIATED: Avoid using % style option array calls. Pls use {{ }} style
DEPRECIATED: Avoid using % style option array calls. Pls use {{ }} style
DEPRECIATED: Avoid using % style option array calls. Pls use {{ }} style
DEPRECIATED: Avoid using % style option array calls. Pls use {{ }} style
DEPRECIATED: Avoid using % style option array calls. Pls use {{ }} style
DEPRECIATED: Avoid using % style option array calls. Pls use {{ }} style
DEPRECIATED: Avoid using % style option array calls. Pls use {{ }} style
DEPRECIATED: Avoid using % style option array calls. Pls use {{ }} style
DEPRECIATED: Avoid using % style option array calls. Pls use {{ }} style
DEPRECIATED: Avoid using % style option array calls. Pls use {{ }} style
DEPRECIATED: Avoid using % style option array calls. Pls use {{ }} style
DEPRECIATED: Avoid using % style option array calls. Pls use {{ }} style
DEPRECIATED: Avoid using % style option array calls. Pls use {{ }} style
DEPRECIATED: Avoid using % style option array calls. Pls use {{ }} style
DEPRECIATED: Avoid using % style option array calls. Pls use {{ }} style
DEPRECIATED: Avoid using % style option array calls. Pls use {{ }} style
DEPRECIATED: Avoid using % style option array calls. Pls use {{ }} style
DEPRECIATED: Avoid using % style option array calls. Pls use {{ }} style
DEPRECIATED: Avoid using % style option array calls. Pls use {{ }} style
DEPRECIATED: Avoid using % style option array calls. Pls use {{ }} style
DEPRECIATED: Avoid using % style option array calls. Pls use {{ }} style
DEPRECIATED: Avoid using % style option array calls. Pls use {{ }} style
DEPRECIATED: Avoid using % style option array calls. Pls use {{ }} style
DEPRECIATED: Avoid using % style option array calls. Pls use {{ }} style
DEPRECIATED: Avoid using % style option array calls. Pls use {{ }} style
DEPRECIATED: Avoid using % style option array calls. Pls use {{ }} style
DEPRECIATED: Avoid using % style option array calls. Pls use {{ }} style
DEPRECIATED: Avoid using % style option array calls. Pls use {{ }} style

$objCpm->cpm_viewRandomMedia(1,5, array('alttag' => '%a, %f'));

Code Select Expand

cpm_dao. Setting cfFilesystemPathToAlbums to /gallery//albums/
SQL : SELECT p.filepath AS pFilepath, p.filename AS pFilename, p.aid AS pAid, p.filesize AS pFilesize, p.title AS pTitle, p.caption AS pCaption, p.owner_name AS pOwner_name, p.owner_id as pOwnerId, p.ctime AS pCtime, p.hits AS pHits, p.pid AS pPid, p.pic_rating AS pPic_Rating, p.votes AS pVotes, p.pwidth AS pWidth, p.pheight AS pHeight, p.user1 as pUser1, p.user2 as pUser2, p.user3 as pUser3, p.user4 as pUser4, c.cid as cCid, c.name as cName, c.description as cDescription, c.pos as cPos, c.parent as cParent, c.thumb as cThumb, u.user_lastvisit AS uUser_lastvisit,u.user_regdate AS uUser_regdate,u.user_email AS uUser_email,u.user_profile1 AS uUser_profile1,u.user_profile2 AS uUser_profile2,u.user_profile3 AS uUser_profile3,u.user_profile4 AS uUser_profile4,u.user_profile5 AS uUser_profile5,u.user_profile6 AS uUser_profile6, a.aid AS aAid, a.title AS aTitle, a.description AS aDescription, a.visibility as aVisibility, a.pos as aPos, a.category as aCategory, a.thumb as aThumb, a.keyword as aKeyword FROM cpg_pictures AS p LEFT JOIN cpg_users AS u ON p.owner_id = u.user_id LEFT JOIN cpg_albums AS a ON p.aid = a.aid LEFT JOIN cpg_categories AS c ON a.category = c.cid WHERE 1 AND p.approved='YES' AND ((a.visibility = 0 OR a.visibility IS NULL) AND (a.alb_password IS NULL OR a.alb_password = "")) ORDER BY rand(724433) LIMIT 5 ROWS: 5
DEPRECIATED: Avoid using % style option array calls. Pls use {{ }} style
DEPRECIATED: Avoid using % style option array calls. Pls use {{ }} style
DEPRECIATED: Avoid using % style option array calls. Pls use {{ }} style
DEPRECIATED: Avoid using % style option array calls. Pls use {{ }} style
DEPRECIATED: Avoid using % style option array calls. Pls use {{ }} style
DEPRECIATED: Avoid using % style option array calls. Pls use {{ }} style
DEPRECIATED: Avoid using % style option array calls. Pls use {{ }} style
DEPRECIATED: Avoid using % style option array calls. Pls use {{ }} style
DEPRECIATED: Avoid using % style option array calls. Pls use {{ }} style
DEPRECIATED: Avoid using % style option array calls. Pls use {{ }} style


It doesn' t seem to make any difference how many random pictures you display.

The thing that is probably slowing things down is that I join to everything I can.  This is due to the subtitle abilities and such, where that information is needed.  You can eliminate one of these by setting:

cfBridgedTo in the config file to "blahblahblah" - or anything that is not false and not a bridge

That will omit user lookups and linking.  That will render anything that depends on user information not available. 

I changed that to true and to blahblahblah. Page generation times increases drastically on both functions. Updated albums was about 7 seconds and the other was 321 seconds. Server load was very high (about 5, it was below 1 before). I also checked with the unmodified cpm files, but it was the same behaviour. The debug output of random media did not change but the debug for the updated albums function was different. It had many more lines of "DEPRECIATED: Avoid using % style option array calls. Pls use {{ }} style". Instead of 36 it had a couple hundred of those lines.

The updated albums function displayed the same pictures, the random media function did not. It displayed the same picture over and over again, but every picture had a different name. I think the function displayed every single album of my gallery. As I said before, the unmodified files acted the same (same picture, different album name).

[vuud]

Sorry, I should not do wild guessing. I really need to get into coding. Right now it is like reading Chinese.
I turned debug on and the random media function seems to have less work to do. Still it takes much longer. 

In case you need the debug information:
cpm_showAlbumsUpdatedOverLastDays(10, array('alttag' => '%a %w', 'subtitle' => '%a, %f', 'imagelink' => 'album'));

Code Select Expand

cpm_dao. Setting cfFilesystemPathToAlbums to /gallery//albums/
SQL : SELECT COUNT(*) FROM cpg_pictures AS p, cpg_albums AS a WHERE p.aid = a.aid AND ctime > 1170836797 AND p.approved='YES'AND ((a.visibility = 0 OR a.visibility IS NULL) AND (a.alb_password IS NULL OR a.alb_password = "")) ROWS: 1
SQL : SELECT p.filepath AS pFilepath, p.filename AS pFilename, p.aid AS pAid, p.filesize AS pFilesize, p.title AS pTitle, p.caption AS pCaption, p.owner_name AS pOwner_name, p.owner_id as pOwnerId, p.ctime AS pCtime, p.hits AS pHits, p.pid AS pPid, p.pic_rating AS pPic_Rating, p.votes AS pVotes, p.pwidth AS pWidth, p.pheight AS pHeight, p.user1 as pUser1, p.user2 as pUser2, p.user3 as pUser3, p.user4 as pUser4, c.cid as cCid, c.name as cName, c.description as cDescription, c.pos as cPos, c.parent as cParent, c.thumb as cThumb, u.user_lastvisit AS uUser_lastvisit,u.user_regdate AS uUser_regdate,u.user_email AS uUser_email,u.user_profile1 AS uUser_profile1,u.user_profile2 AS uUser_profile2,u.user_profile3 AS uUser_profile3,u.user_profile4 AS uUser_profile4,u.user_profile5 AS uUser_profile5,u.user_profile6 AS uUser_profile6, a.aid AS aAid, a.title AS aTitle, a.description AS aDescription, a.visibility as aVisibility, a.pos as aPos, a.category as aCategory, a.thumb as aThumb, a.keyword as aKeyword, c.name AS cName, count( p.pid ) AS count FROM cpg_pictures AS p LEFT JOIN cpg_users AS u ON p.owner_id = u.user_id LEFT JOIN cpg_albums AS a ON p.aid = a.aid LEFT JOIN cpg_categories AS c ON a.category = c.cid WHERE a.category = c.cid AND p.approved='YES' AND ((a.visibility = 0 OR a.visibility IS NULL) AND (a.alb_password IS NULL OR a.alb_password = "")) AND p.ctime > 1170836797 GROUP BY a.category, a.aid ORDER BY c.name, p.mtime ROWS: 9
DEPRECIATED: Avoid using % style option array calls. Pls use {{ }} style
DEPRECIATED: Avoid using % style option array calls. Pls use {{ }} style
DEPRECIATED: Avoid using % style option array calls. Pls use {{ }} style
DEPRECIATED: Avoid using % style option array calls. Pls use {{ }} style
DEPRECIATED: Avoid using % style option array calls. Pls use {{ }} style
DEPRECIATED: Avoid using % style option array calls. Pls use {{ }} style
DEPRECIATED: Avoid using % style option array calls. Pls use {{ }} style
DEPRECIATED: Avoid using % style option array calls. Pls use {{ }} style
DEPRECIATED: Avoid using % style option array calls. Pls use {{ }} style
DEPRECIATED: Avoid using % style option array calls. Pls use {{ }} style
DEPRECIATED: Avoid using % style option array calls. Pls use {{ }} style
DEPRECIATED: Avoid using % style option array calls. Pls use {{ }} style
DEPRECIATED: Avoid using % style option array calls. Pls use {{ }} style
DEPRECIATED: Avoid using % style option array calls. Pls use {{ }} style
DEPRECIATED: Avoid using % style option array calls. Pls use {{ }} style
DEPRECIATED: Avoid using % style option array calls. Pls use {{ }} style
DEPRECIATED: Avoid using % style option array calls. Pls use {{ }} style
DEPRECIATED: Avoid using % style option array calls. Pls use {{ }} style
DEPRECIATED: Avoid using % style option array calls. Pls use {{ }} style
DEPRECIATED: Avoid using % style option array calls. Pls use {{ }} style
DEPRECIATED: Avoid using % style option array calls. Pls use {{ }} style
DEPRECIATED: Avoid using % style option array calls. Pls use {{ }} style
DEPRECIATED: Avoid using % style option array calls. Pls use {{ }} style
DEPRECIATED: Avoid using % style option array calls. Pls use {{ }} style
DEPRECIATED: Avoid using % style option array calls. Pls use {{ }} style
DEPRECIATED: Avoid using % style option array calls. Pls use {{ }} style
DEPRECIATED: Avoid using % style option array calls. Pls use {{ }} style
DEPRECIATED: Avoid using % style option array calls. Pls use {{ }} style
DEPRECIATED: Avoid using % style option array calls. Pls use {{ }} style
DEPRECIATED: Avoid using % style option array calls. Pls use {{ }} style
DEPRECIATED: Avoid using % style option array calls. Pls use {{ }} style
DEPRECIATED: Avoid using % style option array calls. Pls use {{ }} style
DEPRECIATED: Avoid using % style option array calls. Pls use {{ }} style
DEPRECIATED: Avoid using % style option array calls. Pls use {{ }} style
DEPRECIATED: Avoid using % style option array calls. Pls use {{ }} style
DEPRECIATED: Avoid using % style option array calls. Pls use {{ }} style

$objCpm->cpm_viewRandomMedia(1,5, array('alttag' => '%a, %f'));

Code Select Expand

cpm_dao. Setting cfFilesystemPathToAlbums to /gallery//albums/
SQL : SELECT p.filepath AS pFilepath, p.filename AS pFilename, p.aid AS pAid, p.filesize AS pFilesize, p.title AS pTitle, p.caption AS pCaption, p.owner_name AS pOwner_name, p.owner_id as pOwnerId, p.ctime AS pCtime, p.hits AS pHits, p.pid AS pPid, p.pic_rating AS pPic_Rating, p.votes AS pVotes, p.pwidth AS pWidth, p.pheight AS pHeight, p.user1 as pUser1, p.user2 as pUser2, p.user3 as pUser3, p.user4 as pUser4, c.cid as cCid, c.name as cName, c.description as cDescription, c.pos as cPos, c.parent as cParent, c.thumb as cThumb, u.user_lastvisit AS uUser_lastvisit,u.user_regdate AS uUser_regdate,u.user_email AS uUser_email,u.user_profile1 AS uUser_profile1,u.user_profile2 AS uUser_profile2,u.user_profile3 AS uUser_profile3,u.user_profile4 AS uUser_profile4,u.user_profile5 AS uUser_profile5,u.user_profile6 AS uUser_profile6, a.aid AS aAid, a.title AS aTitle, a.description AS aDescription, a.visibility as aVisibility, a.pos as aPos, a.category as aCategory, a.thumb as aThumb, a.keyword as aKeyword FROM cpg_pictures AS p LEFT JOIN cpg_users AS u ON p.owner_id = u.user_id LEFT JOIN cpg_albums AS a ON p.aid = a.aid LEFT JOIN cpg_categories AS c ON a.category = c.cid WHERE 1 AND p.approved='YES' AND ((a.visibility = 0 OR a.visibility IS NULL) AND (a.alb_password IS NULL OR a.alb_password = "")) ORDER BY rand(724433) LIMIT 5 ROWS: 5
DEPRECIATED: Avoid using % style option array calls. Pls use {{ }} style
DEPRECIATED: Avoid using % style option array calls. Pls use {{ }} style
DEPRECIATED: Avoid using % style option array calls. Pls use {{ }} style
DEPRECIATED: Avoid using % style option array calls. Pls use {{ }} style
DEPRECIATED: Avoid using % style option array calls. Pls use {{ }} style
DEPRECIATED: Avoid using % style option array calls. Pls use {{ }} style
DEPRECIATED: Avoid using % style option array calls. Pls use {{ }} style
DEPRECIATED: Avoid using % style option array calls. Pls use {{ }} style
DEPRECIATED: Avoid using % style option array calls. Pls use {{ }} style
DEPRECIATED: Avoid using % style option array calls. Pls use {{ }} style


It doesn' t seem to make any difference how many random pictures you display.


I changed that to true and to blahblahblah. Page generation times increases drastically on both functions. Updated albums was about 7 seconds and the other was 321 seconds. Server load was very high (about 5, it was below 1 before). I also checked with the unmodified cpm files, but it was the same behaviour. The debug output of random media did not change but the debug for the updated albums function was different. It had many more lines of "DEPRECIATED: Avoid using % style option array calls. Pls use {{ }} style". Instead of 36 it had a couple hundred of those lines.

The updated albums function displayed the same pictures, the random media function did not. It displayed the same picture over and over again, but every picture had a different name. I think the function displayed every single album of my gallery. As I said before, the unmodified files acted the same (same picture, different album name).

Alot of this seems just wrong (not as in you are wrong, just as in it should not happen).  I will try to emulate it here today. 

[vuud]

Alot of this seems just wrong (not as in you are wrong, just as in it should not happen).  I will try to emulate it here today. 

Oh, and turning on DEBUG will definately not help performance   If anything will hurt it some...  Just gives you the actual sql output to browse and such.

[vuud]

Done with the fixes in 1.9.9

I've done some tests with your database and wrote up the following shell to test things out.  You can replace the call in there with whatever you want.   Your debug output also led me to some older code internally that could be better.

Then after I realized that SQL was caching my results, so the first number was the most accurate, you can ignore the mean and all that at the bottom.  First number is the most useful currenly.

I am going to work in an option to add SQL: SELECT SQL_NO_CACHE so that this can be tested pretty easily.  Then Mean and stuff will be MEANingful (get it?  sorry... could not resist it)

Regardless... the two that you mentioned:

For cpm_showAlbumsUpdatedOverLastDays(10, array('alttag' => '%a %w', 'subtitle' => '%a, %f', 'imagelink' => 'album')):
(You can see the difference after the first two with caching)
Not bridged:  0.272 0.219 0.010 0.003 0.004 0.004 0.003
Default bridged:  0.283 0.247 0.004 0.003 0.003 0.003
Default bridged with rendering: 0.235 0.006 0.470 0.201 0.051 0.029

I can live with this...


Now this one is a bit wierd...
For cpm_viewRandomMedia(1,5, array('alttag' => '%a, %f')):
Default bridged with rendering: 0.580 0.543 0.453 0.483 0.458 0.482
Not bridged: 0.828 0.678 0.667 0.725 0.671 0.667

Holy cow... that is the simplest thing I do.

If we replace the %f with the new way (saves an iteration) it does not seem to help.
cpm_viewRandomMedia(1,5, array('alttag' => '{{aTitle}}, {{pFilename}}')):
0.795 0.722 0.653 0.688 0.698 0.652
0.774 0.678 0.665 0.701 0.682 0.677

If I remove the order by rand(), the SQL response from the command line, drops from 0.64 to 0.0
Ordering by time lands us at 0.16

So I guess having SQL do the rand order by is not terribly good at large datasets. 

Narrowing the randomization down to just cat=27 (In which you have about 2000 photos) gives us:
0.117 0.157 0.098 0.086 0.070 0.107

So, yes - it is probably that whole ORDER BY rand() thing.

I will see what I can do, but you could always limit it to a category or something for now...

I am opting to not include benchmarking, since the microtime requires certain system functions to work - some of which may not be on windows machines.  So I am keeping it out of the core.

Here is my, somewhat flawed, benchmarking shell - I will be including it in the next and future downloads also...

Code Select Expand

<?php 
error_reporting(E_ALL);
ini_set('display_errors',1);  // 0 is off, 1 is on
include "./cpmfetch.php";
$objCpm = new cpm();

// You can comment this out to test rendering time also
$objCpm->cpm_setReturnType("resultset");
$objCpm->cpm_debugMode(true);
?>
<?php

$results = array();

// Leave this call in.  It forces cpmFetch to create a database connection.  Otherwise it will throw off the first result.
$objCpm->dbConnect();


for ($x = 0; $x < 101; $x++) {
$starttime = explode(' ', microtime());
$starttime = $starttime[1] + $starttime[0];
//$objCpm->cpm_viewRandomMedia(1,5, array('alttag' => '%a, %f'));

$objCpm->cpm_showAlbumsUpdatedOverLastDays(10, array('alttag' => '%a %w', 'subtitle' => '%a, %f', 'imagelink' => 'album'));
//$objCpm->cpm_getAlbumListFrom("",1,3);
$mtime = explode(' ', microtime());
$totaltime = $mtime[0] + $mtime[1] - $starttime;
array_push($results,$totaltime);
}

$x = 0;
foreach($results as $entry) {
//printf('Iteration ' . $x . ': Page loaded in %.3f seconds.<br>', $entry);
printf(' %.3f ', $entry);
$x++;
}


sort($results);

print "<p>Max is: {$results[100]}<br />";
print "Min is: {$results[0]}<br />";
print "Mean is: {$results[50]}<br />";
print "Average is: " . array_sum($results)/sizeof($results) . "</p>";

$objCpm->cpm_close(); ?>

- vuud

[jerx]

I think that the update already improved page generation time. It is about 0.15 s less (=> 0.35 s at best, time returned on my test script). The bridge setting is working better now. No matter which value it is set to, the page generation time doesn' t change.

Your test script returned these numbers:
Max is: 0.155085086823
Min is: 0.0033700466156
Mean is: 0.00373005867004
Average is: 0.0118527105539

[vuud]

I think that the update already improved page generation time. It is about 0.15 s less (=> 0.35 s at best, time returned on my test script). The bridge setting is working better now. No matter which value it is set to, the page generation time doesn' t change.

Your test script returned these numbers:
Max is: 0.155085086823
Min is: 0.0033700466156
Mean is: 0.00373005867004
Average is: 0.0118527105539

Good - yeah some stuff was a bit off.  There is also some functions that just do not work with that size database which I need to fix.  I posted last night about that in another thread.   The big database is fun to work with - thanks for sending it to me - its revealing a lot of issues that will probably benefit smaller galleries too once I fix them.

Oh, as I mentioned, in the above, only the max number matters until I get an option to turn SQL caching off...  Otherwise, MySQL already knows the answer after the first time and just spits it back.