Archive for the 'MySQL' Category

30th Mar 2008

MySQL tip: why are my joins so slow?

Never! Never join tables on fields with different types. Say, you have one table with `zip` varchar(5) and another with `zip` int(5). As a result of …left join … on a.zip=b.zip you will get perfomance loss up to 1000 times.

Share This

Posted by Posted by Alexander Alfimov under Filed under MySQL Comments No Comments »

10th Nov 2007

Drupal, Wordpress and Joomla!: performance tests.

Hi!

Today I’d like to share some interesting results of performance tests for 3 most popular CMS/Blog systems: Drupal, Joomla! and WordPress.

Tests were done on dev debian machine using Apache ab utility.

MySQL settings:

| join_buffer_size | 131072 |
| key_buffer_size | 16777216 |
| key_cache_age_threshold | 300 |
| key_cache_block_size | 1024 |
| key_cache_division_limit | 100 |

| query_alloc_block_size | 8192 |
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 16777216 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |

Apache settings:

<IfModule prefork.c>
StartServers 5
MinSpareServers 5
MaxSpareServers 10
MaxClients 20
MaxRequestsPerChild 0
</IfModule>

All systems have about 20,000 articles and 200+ links on page.

Versions: drupal 5.1, wordpress 2.2, joomla! 1.0.12

Results for ab -n 200 -c 4:

drupal-sef-no-aliases:
Requests per second: 2.43 [#/sec] (mean)
drupal-sef-20000-aliases:
Requests per second: 1.21 [#/sec] (mean)

joomla1-no-sef:
Requests per second: 2.75 [#/sec] (mean)
joomla1-patched-opensef:
Requests per second: 1.87 [#/sec] (mean)

wordpress-numeric-permalinks:
Requests per second: 0.65 [#/sec] (mean)
wordpress-date-name-permalinks:
Requests per second: 0.64 [#/sec] (mean)

Larger number means better performance. Joomla rocks?

Share This

Posted by Posted by Alexander Alfimov under Filed under CMS, Joomla, MySQL, PHP Comments 4 Comments »

19th Apr 2007

Joomla’s non-optimized search in content.searchbot.php

Hi, All!

Recently I faced a MySQL replication lag time problem. From time to time it increased dramatically (up to 200 sec’s) on new boxes (there are 4 boxes in cluster beyond load balancer) with 4Gb of RAM and really-really nice CPUs. I had to enable slow queries logging to figure out what was causing the problem.
Okay, the slowest query was…. yeah, in search subsystem:

Count: 5 Time=0.80s (4s) Lock=0.00s (0s) Rows=37.2 (186), xxxxx@localhost
SELECT a.title AS title,
a.created AS created,
CONCAT(a.introtext, a.fulltext) AS text,
u.title AS section,
CONCAT( ‘S’, a.id ) AS href,
‘S’ AS browsernav,
‘S’ AS type
FROM jos_content AS a
INNER JOIN jos_categories AS b ON b.id=a.catid
INNER JOIN jos_sections AS u ON u.id = a.sectionid
WHERE ( (LOWER(a.title) LIKE ‘S’ OR LOWER(a.introtext) LIKE ‘S’ OR LOWER(a.fulltext) LIKE ‘S’ OR LOWER(a.metakey) LIKE ‘S’ OR LOWER(a.metadesc) LIKE ‘S’) )
AND a.state = N
AND u.published = N
AND b.published = N
AND a.access <= N
AND b.access <= N
AND u.access <= N
AND ( a.publish_up = 'S' OR a.publish_up <= 'S' )
AND ( a.publish_down = 'S' OR a.publish_down >= ‘S’ )
GROUP BY a.id
ORDER BY a.created DESC
LIMIT N, N

Does anybody have idea why do they use LOWER? MySQL states “By default, MySQL searches are not case sensitive”. So, why add additional load to server?

Anyway, after removing “LOWER” the queries became about 40% faster.

Hope this will help anybody else.

Thanks.

Share This

Posted by Posted by Alexander Alfimov under Filed under CMS, Joomla, MySQL Comments 2 Comments »

06th Mar 2007

OpenSEF RC5 SP2 Perfomance Improvement

Hi, all!Recently I’ve rewritten a pretty large site on Joomla (it has about 200 online users all the time, as Joomla backend stats shows, >17000 content items etc.) and noticed that the page generation time started to grow ftom 0.101 secs to 0.5 after the first day, 0.7 - 1 next day…). Not very good, right? First of all I went to the MySQL console at the time of high loads and… voila - there is a `opensef_sef` MySQL query present almost constantly. Guys, can anybody explain me why do they fetch into the memory almost all records in sef.php [Line #173]:

if ($sefSite->id == null) $sefSite->id = -1;
$query = “SELECT * FROM #__opensef_sef WHERE published = ‘1′” .
“\nAND valid = ‘1′” .
“\nAND (direction IS NULL OR direction = ‘’ OR direction = ‘o’)” .
“\nAND (site_id IS NULL ” .
($sefSite->id ? ‘ OR site_id = ‘ . $sefSite->id : ‘’) . ‘)’ .
“\nORDER BY site_id DESC, published DESC, valid DESC, LENGTH(direction) DESC”;
$database->setQuery( $query );
if ($database->loadResult()) {
$opensef_aliases = $database->loadObjectList();
}

A quick fix is to replace the query with this one:

if ($sefSite->id == null) $sefSite->id = -1;
$query = “SELECT * FROM #__opensef_sef WHERE published = ‘1′” .
“\nAND valid = ‘1′” .
“\nAND (direction IS NULL OR direction = ‘’ OR direction = ‘o’)” .
“\nAND (site_id IS NULL ” .
($sefSite->id ? ‘ OR site_id = ‘ . $sefSite->id : ‘’) . ‘)’ .
“\nAND (external=’{$_SERVER[’REQUEST_URI’]}’” .
“\nORDER BY site_id DESC, published DESC, valid DESC, LENGTH(direction) DESC”;
$database->setQuery( $query );
if ($database->loadResult()) {
$opensef_aliases = $database->loadObjectList();
}

After that, find the lookupOutgoing function  in administrator/components/com_sef/sef.class.php and replace it with this one:

function lookupOutgoing( $site_id, $url ) { // static method
global $opensef_aliases, $database;

$returnaliases = array();
/*
if ($site_id == null) $site_id = -1;
if (count($opensef_aliases) > 0) {
foreach($opensef_aliases as $aliases){
if ( $aliases->internal == $url ) {
$returnaliases[] = $aliases;
}
}
}
*/
if (!count($returnaliases))
{
$query = “SELECT * FROM #__opensef_sef WHERE published = ‘1′” .
“\nAND valid = ‘1′” .
“\nAND (direction IS NULL OR direction = ” OR direction = ‘o’)” .
“\nAND internal=’$url’”.
“\nORDER BY site_id DESC, published DESC, valid DESC, LENGTH(direction) DESC limit 1″;

$database->setQuery( $query );
if ($database->loadResult())
{
$aliases = $database->loadObjectList();
foreach($aliases as $k=>$val)
{
$returnaliases[] = $val;
}
}
}

if (is_array( $returnaliases ) && count( $returnaliases )) {
$returnaliases = xclCastObjectList( $returnaliases, ‘JosOpenSEFAlias’ );
return $returnaliases[0];
}
$null = null;
return $null;
}

Hurray! It works as a charm. Page generation time is now 0.07 seconds.

Hope this will help somebody else.

Thanks.

Share This

Posted by Posted by Alexander Alfimov under Filed under CMS, Joomla, MySQL, OpenSEF, PHP Comments 1 Comment »

Close
E-mail It