Hi
Im using Mediashare as a main gallery on my website. Mediashare is working as a user picture/avatar module as well.
My mysql server sometimes use ~100% CPU it seems that it is down to long queries most of them are from Mediashare - slow log
My website ~1500 uu/5000 registered
vps ram 768
Normally ~300M us used
Mediashare around 1000 albums and 1000media items
recently every day I have to restart mysql
looking into recent media&albums page in Mediashare make mysql goes to around 70% cpu - top command
my.cnf
[mysqld]
skip-innodb
default-character-set=latin2
default-collation=latin2_general_ci
character-sets-dir=/usr/share/mysql/charsets
slow_query_log=1
long_query_time=2
query_cache_size=32M
table_cache=512
key_buffer_size=128M
thread_cache_size=4
tmp_table_size=128M
max_heap_table_size=128M
[client]
default-character-set=latin2
What I should change?
--
rgfdgafgaf
Watch
GitHub Core
Show your support for Zikula! Sign up at Github account and watch the Core project!
GitHub Modules
Forum Activity
- craigh responded to »Using PageUtil::addVar() to load script code« 03:29 PM
- michiel responded to »password problem« 10:01 AM
- mazdev responded to »Hide "Register new account" and change template to 3 col« 07:50 AM
- mesteele101 created topic »Zikula 1.3.3 - Site Search 1.5.2 - Unable to turn off plug-ins« 07:48 AM
- mesteele101 responded to »ERR (3): E_USER_ERROR: Smarty error: [in pagesvar:pagesitem2en line XXX]…« 25. May
- mazdev responded to »Pages 2.5.0 and updating - Page not found« 25. May
- mesteele101 responded to »Zikula 1.3.3 - Selecting a category in Pages not working« 25. May
Zikula Blog
- Anatomy of Open Source Projects on Mar 07
- Continuous Review on Mar 01
- Not Invented Here on Feb 24
- How to Contribute Your Code at Github on Jan 13
- 10 Steps to Coding-Nirvana: Tips for Successful Module Writing on Nov 12
- Submitting Bug Report Tickets That Get Results on Aug 17
- Cozi Tricks #1: Syntax Highlighting on Aug 07
Login
Mediashare and long queries
-
- Rank: Freshman
- Registered: Dec 30, 2006
- Last visit: Apr 20, 2010
- Posts: 58
Posted: Nov 24, 2010 - 06:39 AM
-
- Rank: Developer
- Registered: Jun 16, 2003
- Last visit: May 29, 2010
- Posts: 1967
Posted: Nov 24, 2010 - 06:44 AM
does Mediashare create any index that would speed it up? -
- Rank: Freshman
- Registered: Dec 30, 2006
- Last visit: Apr 20, 2010
- Posts: 58
Posted: Nov 24, 2010 - 06:59 AM
don't think so I don't know much about it
how can I adopt it to create and use those indexes?
or even how to check if it is create them:/
This MySQL server has been running for 0 days, 1 hours, 53 minutes and 14 seconds.
Traffic 1 ø per hour
Received 215 MiB 114 MiB
Sent 963 MiB 510 MiB
Total 1,178 MiB 624 MiB
Connections ø per hour %
max. concurrent connections 42 --- ---
Failed attempts 0 0.00 0.00%
Aborted 0 0.00 0.00%
Total 2,914 1,544.07 100.00%
Red ones
Slow_queries 63
Handler_read_rnd 231 k
Handler_read_rnd_next 480 M
Created_tmp_disk_tables 2,945
Select_full_join 317
Table_locks_waited 26
--
rgfdgafgaf -
- Rank: Developer
- Registered: Jun 16, 2003
- Last visit: May 29, 2010
- Posts: 1967
Posted: Nov 24, 2010 - 07:55 AM
phpMyAdmin would show you if there are indexes on any tables. in my version, I have to click the 'details' link at the bottom while viewing the structure of the individual table. -
- Rank: Freshman
- Registered: Dec 30, 2006
- Last visit: Apr 20, 2010
- Posts: 58
Posted: Nov 24, 2010 - 08:16 AM
# Structure pn_mediashare_access
PRIMARY BTREE Yes No msa_id 2214 A
accessAlbumIdx BTREE No No msa_albumid 1107 A
# Structure pn_mediashare_albums
PRIMARY BTREE Yes No ms_id 1070 A
# Structure pn_mediashare_invitation
PRIMARY BTREE Yes No msinv_id 2 A
# Structure pn_mediashare_keywords
keywordsKeywordIdx BTREE No No ms_keyword 130 A
# Structure pn_mediashare_media
PRIMARY BTREE Yes No ms_id 1130 A
# Structure pn_mediashare_mediadb
PRIMARY BTREE Yes No mdb_id 0 A
# Structure pn_mediashare_mediahandlers
PRIMARY BTREE Yes No ms_id 22 A
# Structure pn_mediashare_mediastore
PRIMARY BTREE Yes No mss_id 3390 A
# Structure pn_mediashare_setup
PRIMARY BTREE Yes No ms_id 0 A
# Structure pn_mediashare_sources
PRIMARY BTREE Yes No ms_id 1 A
for me it seams that only two tables have indexes
--
rgfdgafgaf -
- Rank: Team Member
- Registered: Sep 06, 2006
- Last visit: May 09, 2010
- Posts: 2446
Posted: Nov 24, 2010 - 11:49 AM
SQL Queries of Mediashare are real complex,
it can be an issue of the module structure
and also the lack of pagination makes it a trouble.
I've done some work on its SVN but dunno if it's usable
we better begin to think a clean successor for Zikula 1.3
--
- Mateo T. -
Mis principios... son mis fines -
- Rank: Freshman
- Registered: Dec 30, 2006
- Last visit: Apr 20, 2010
- Posts: 58
Posted: Nov 24, 2010 - 12:45 PM
I don't know much about mysql performace one thing I know it is very hard and you need wide knowledge about what mysql likes what not making query. In my slow log most of them are like that:
Quote
# Query_time: 65.553587 Lock_time: 0.000236 Rows_sent: 1033 Rows_examined: 5631931
SET timestamp=1289768356;
SELECT DISTINCT ms_id
FROM pn_mediashare_albums
LEFT JOIN pn_mediashare_access
ON msa_albumid = ms_id
LEFT JOIN pn_group_membership
ON pn_gid = msa_groupid
AND pn_uid = 0
WHERE
( (msa_access & 31) != 0
AND (pn_gid IS NOT NULL OR msa_groupid = -1)
OR ms_ownerid = 0);
Because normally my server use around 250MB I have 500MB free is there any way to make server handle long queries using more memory instead off all my cpu :D
Other thing is if you thinking about gallery module for 1.3 Mediashare is cool but I had to do some changes like user have his own "main" album(created automatically) in witch he can add all the crap he want to add. Some of those albums may be interesting to promote on website (I have community website) so ability to promote them would be good as well. What im doing at the moment Im moving interesting albums to "special album" so at the top there is "specjal album" and "users albums" in witch there are "main" user albums.
If you need someone for testing or so let me know :)
--
rgfdgafgaf -
- Rank: Developer
- Registered: Aug 23, 2003
- Last visit: May 31, 2010
- Posts: 1428
Posted: Nov 24, 2010 - 08:39 PM
nestormateo
SQL Queries of Mediashare are real complex,
it can be an issue of the module structure
and also the lack of pagination makes it a trouble.
I've done some work on its SVN but dunno if it's usable
we better begin to think a clean successor for Zikula 1.3
There has been discussion with Carsten and Drak about making indeed a succesor, since making it Zikula Core 1.3 ready is a lot of hard work. The german community are discussing a successor as well and maybe even make a sponsoring like was done for Clip or Scribite. A module made with MOST might be a lot easier to start with and enhance then hacking in the Mediashare code.
--
campertoday.nl, Module development, Dutch Zikula Community -
- Rank: Freshman
- Registered: Dec 30, 2006
- Last visit: Apr 20, 2010
- Posts: 58
Posted: Nov 25, 2010 - 09:15 AM
Hi
I know hacking is no good but I need solution I have found log query part in Mediashare maybe you can tell me how can I speed up this it seams SQL in this function is making most of the problems
accessapi.php
Code
function getAccessibleAlbumsSql($albumId, $access, $field)
{
// Admin can do everything
if (pnSecAuthAction(0, 'mediashare::', '::', ACCESS_ADMIN))
return '1=1';
if (!pnSecAuthAction(0, 'mediashare::', '::', ACCESS_READ))
return '1=0';
$userId = (int)pnUserGetVar('uid');
pnModDBInfoLoad('Groups'); // Make sure groups database info is available
list($dbconn) = pnDBGetConn();
$pntable = pnDBGetTables();
$albumsTable = $pntable['mediashare_albums'];
$albumsColumn = $pntable['mediashare_albums_column'];
$accessTable = $pntable['mediashare_access'];
$accessColumn = $pntable['mediashare_access_column'];
$membershipTable = $pntable['group_membership'];
$membershipColumn = &$pntable['group_membership_column'];
$invitedAlbums = pnModAPIFunc('mediashare', 'invitation', 'getInvitedAlbums', array());
$invitedSql = '';
if (is_array($invitedAlbums) && ($access & mediashareAccessRequirementView) == mediashareAccessRequirementView)
{
foreach ($invitedAlbums as $invAlbumId => $ok)
{
if ($ok)
{
if (!empty($invitedSql))
$invitedSql .= ',';
$invitedSql .= (int)$invAlbumId;
}
}
}
$parentAlbumSql = '';
if ($albumId != null)
$parentAlbumSql = "$albumsColumn[parentAlbumId] = $albumId AND";
$sql = "SELECT DISTINCT $albumsColumn[id]
FROM $albumsTable
LEFT JOIN $accessTable
ON $accessColumn[albumId] = $albumsColumn[id]
LEFT JOIN $membershipTable
ON $membershipColumn[gid] = $accessColumn[groupId]
AND $membershipColumn[uid] = $userId
WHERE $parentAlbumSql
( ($accessColumn[access] & $access) != 0
AND ($membershipColumn[gid] IS NOT NULL OR $accessColumn[groupId] = -1)
OR $albumsColumn[ownerId] = $userId)";
//echo "<pre>$sql</pre><br/>\n";
$dbresult = $dbconn->execute($sql);
if ($dbconn->errorNo() != 0)
return mediashareErrorAPI(__file__, __line__, '"getAccessibleAlbumsSql" failed: ' . $dbconn->errorMsg() . " while executing: $sql");
$albumIds = $invitedSql;
for (; !$dbresult->EOF; $dbresult->MoveNext())
{
if ($albumIds != '')
$albumIds .= ',';
$albumIds .= $dbresult->fields[0];
}
$dbresult->close();
//echo "Acces bits = $access. Albums = ($albumIds). ";
return $albumIds == '' ? '1=0' : "$field IN ($albumIds)";
}
--
rgfdgafgaf
- Moderated by:
- Support
