30th Mar 2008
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 Alexander Alfimov under
MySQL
No Comments »
02nd Mar 2008
Did you know PHP 4.x cannot take reference from $this? So, code below won’t work:
$reference = &$this;
It took me about 30 minutes to figure out what exactly was wrong with my code. So, I hope this post will help somebody else to avoid the problem.
What I did to fix it. Just added another property named “reference” to class and initialized it right after creating class instance. If anybody knows another way - please let me know.
Thank you.
Share This
Posted by Alexander Alfimov under
PHP
No Comments »
10th Nov 2007
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 Alexander Alfimov under
CMS, Joomla, MySQL, PHP
4 Comments »
09th Jun 2007
Что ж, семинар состоялся. И, как мне кажется, прошел довольно таки успешно
Well, we passed over the first intown programmers conference. Pretty nice, what else can I say?
Team Meating #1
Share This
Posted by Alexander Alfimov under
Team meAting
No Comments »
06th Jun 2007
Мы считаем себя разработчиками и каждый день боремся каждый со своми задачами. Мы постоянно заняты и часто красивые решения проблем или интересные находки не добираются даже до наших блогов. Все мы владеем кое-чем уникальным — своим опытом. Предлагаем его обменять по курсу один к одному на опыт коллег и неформальное общение с единомышленниками.
Примерная программа:
- Очное знакомство, каждый представится и обозначит круг своих интересов в разработке
- Неформальные доклады
Место и время проведения: Украина, г. Сумы, Центр Европейской информации (библиотека им. Крупской, 3-й этаж), 9 июня (суббота), 15:00. Всех встретят на входе.
Ориентировочные темы докладов:
1. Краткий обзор CMS Joomla, Drupal, Typo3 и WordPress. Используемые подходы, плюсы и минусы. Результаты crash-тестов. Докладчики: alfim(Joomla, WordPress, Drupal), ArtVolk(Typo3, критерии выбора CMS: субъективный взгляд)
2. Организация рабочего процесса небольшой группы разрабочиков (системы контроля версий, баг-трекеры, работа с командой). Основной докладчик - ArtVolk
Организаторы - alfim, ArtVolk.
Заявку на участие можно оставить в комментариях. Задать вопросы можно там же или в ICQ: 142806591
Share This
Posted by Alexander Alfimov under
Team meAting
No Comments »
04th May 2007
It seems like we’ve finished one more project, 1c-uso.ru. It’s pretty simple Joomla-based website with downloads section for registered users and Mantis integration.
Share This
Posted by Alexander Alfimov under
CMS, Projects
1 Comment »
19th Apr 2007
I’ve googled today for such a thing but without any luck. The main requirement was the ability to convert ISBNs in batch mode. Fortunately I’ve found small JS function here and decided to write the tool myself. Please don’t hesitate to leave comments in case you found it useful or have suggestions on it’s functionality.
Simple online ISBN10 to ISBN13 batch converter.
Share This
Posted by Alexander Alfimov under
Other
10 Comments »
19th Apr 2007
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 Alexander Alfimov under
CMS, Joomla, MySQL
2 Comments »
06th Mar 2007
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 Alexander Alfimov under
CMS, Joomla, MySQL, OpenSEF, PHP
1 Comment »
20th Nov 2006
Have you ever thought about web without advertisement? Don’t you think it’s possible? May be yes, may be no. But we have to give it a try…
Few years ago I was surprised seeing somebodies project with 3 blocks of google-like ads. “What’s that?”, - I thought, “Where is the content?”. Affiliate links and text ads made up to 80% of the site. It was unusual for 2001-2002 years. But now thousands of web-projects turned from good nice looking source of interesting news etc. into ugly monsters where one should search for the info he came for. And there is a tendence: as more popular is website as more banners it has. Ads on the top, ads on the left, right, bottom and even inside the content! That’s ugly! Pretty project becomes to look like a dirty chicken.
Yes, I understand that is a way to earn some money. But guys, why don’t you try to sell your content itself instead of selling your luck? “May be somebody will click the links and I’ll get a cent… or even dollar!”. People, please try to forget about that. I see more and more adsense-fishing sites without any sort of own content. With such a tendency our children have good chance to get web without something outdated earlier know as “good content”. Even now advertising of advertising is not astonishing.
Why am I writing this? I don’t expect wellknown bloggers and webmasters to remove superfluous. But I hope somebody (even if he’ll be one of a thousand) will read the post and decide to leave only one adsense block on the site instead of putting it everywhere.
Thanks.
Share This
Posted by Alexander Alfimov under
Advertising
No Comments »