Как оптимизировать БД? - Joomla 2.5: Настройка и оптимизация

Есть Apache 2.2, php 5.3.2, MySQL 5.0.95 под freebsd 6.2 и Joomla 1.0 и 2.5 После взлома 1.0 (да и до него уже давно тоже) возникла необходимость обновления. Обновление делал через промежуточную J1.5 Однако возникла серьезная проблема. За время работы сайта накопилось большое кол-во материалов content - 103 МБ и при попытке запуска этого массива под 2.5 MySQL перестал справляться с нагрузкой, загрузка сервера превысила все возможные пределы.

Кэш включен, js и CSS жмутся средствами шаблона и jch optimize, оптимизация БД ни к чему не привела. Мой вопрос про индексы остался без ответа, сказали, что с ними все и так нормально.

В результате Joomla 1.0 невозможно пользоваться из-за взлома, да и она уже морально устарела, а J2.5 из-за повышения нагрузки.
Сайт стоит уже неделю

Может ли дать какие-либо результаты обновление php до последней версии и MySQL до 5.5?
Может быть все-же нужно что-то сделать с индексами?

Поможет ли перенос части статей в архив? Как это сделать автоматически (плагин, компонент) для такого объема данных?
Урл я в другой теме уже давал, но смысла в нем особого нет, т.к. сейчас там заглушка. При попытке запустить j 2.5. под нагрузкой, сервер падает.

Нужно писать хуки, анализировать индексы итд итп..
Общих советов нет.

За время работы сайта накопилось большое кол-во материалов content - 103 МБ и при попытке запуска этого массива под 2.5 MySQL перестал справляться с нагрузкой, загрузка сервера превысила все возможные пределы.

103мб это смешной объем данных для MySQL - у этого форума база раз в 10 больше, да и у меня есть дампы баз Joomla 1.5 и 2.5 размером более 700мб - и все просто летает. Мне кажется вам нужно разбираться с конфигурацией MySQL и доступными ресурсами сервера.

Да, но при тех же настройках J 1.0 работала относительно нормально.

Посещаемость по данным Google Analytics за сентябрь

2,545 people visited this site
Visits: 3,517
Unique Visitors: 2,545
Pageviews: 8,126
Pages / Visit: 2.31
Avg. Visit Duration: 00:02:10
Bounce Rate: 67.79%
% New Visits: 67.96%

Intel(R) Xeon(TM) CPU 3.60GHz
4 ядра,  4 ГБ ОЗУ


Через несколько минут после того, как я устанавливаю в качестве корневого каталога каталог с Joomla 2.5 - mytop
показывает вот такую картину


MySQL on localhost (5.0.95-log)                                                                                                                          up 0+09:52:46 [08:41:25]
 Queries: 1.5M   qps:   44 Slow:   540.0         Se/In/Up/De(%):    78/00/17/00
             qps now:    7 Slow qps: 0.0  Threads:   54 (  48/   0) 58/05/00/02
 Cache Hits: 883.1k Hits/s: 25.4 Hits now:   2.6  Ratio: 74.6% Ratio now: 60.9%
 Key Efficiency: 100.0%  Bps in/out:  7.1k/99.3k   Now in/out:  2.0k/38.2k

       Id      User         Host/IP         DB      Time    Cmd Query or State
       --      ----         -------         --      ----    --- ----------
    22237      root       localhost       test         0  Query show full processlist
    22286       user       localhost       rpdb         1  Query SELECT a.id, a.title, a.alias, a.title_alias, a.introtext, a.checked_out, a.checked_out_time, a.catid, a.created,
    22285       user       localhost       rpdb         4  Query SELECT a.id, a.title, a.alias, a.title_alias, a.introtext, a.checked_out, a.checked_out_time, a.catid, a.created,
    22284       user       localhost       rpdb        14  Query SELECT a.id, a.title, a.alias, a.title_alias, a.introtext, a.checked_out, a.checked_out_time, a.catid, a.created,
    22283       user       localhost       rpdb        15  Sleep
    22281       user       localhost       rpdb        26  Query SELECT a.id, a.title, a.alias, a.title_alias, a.introtext, a.checked_out, a.checked_out_time, a.catid, a.created,
    22282       user       localhost       rpdb        26  Query SELECT a.id, a.title, a.alias, a.title_alias, a.introtext, a.checked_out, a.checked_out_time, a.catid, a.created,
    22280       user       localhost       rpdb        28  Query SELECT a.id, a.title, a.alias, a.title_alias, a.introtext, a.checked_out, a.checked_out_time, a.catid, a.created,
    22279       user       localhost       rpdb        33  Query SELECT a.id, a.title, a.alias, a.title_alias, a.introtext, a.checked_out, a.checked_out_time, a.catid, a.created,
    22278       user       localhost       rpdb        34  Sleep
    22277       user       localhost       rpdb        43  Query SELECT a.id, a.title, a.alias, a.title_alias, a.introtext, a.checked_out, a.checked_out_time, a.catid, a.created,
    22276       user       localhost       rpdb        44  Query SELECT a.id, a.title, a.alias, a.title_alias, a.introtext, a.checked_out, a.checked_out_time, a.catid, a.created,
    22275       user       localhost       rpdb        45  Sleep
    22274       user       localhost       rpdb        46  Query SELECT a.id, a.title, a.alias, a.title_alias, a.introtext, a.checked_out, a.checked_out_time, a.catid, a.created,
    22273       user       localhost       rpdb        56  Query SELECT a.id, a.title, a.alias, a.title_alias, a.introtext, a.checked_out, a.checked_out_time, a.catid, a.created,
    22272       user       localhost       rpdb        61  Query SELECT a.id, a.title, a.alias, a.title_alias, a.introtext, a.checked_out, a.checked_out_time, a.catid, a.created,
    22271       user       localhost       rpdb        62  Query SELECT a.id, a.title, a.alias, a.title_alias, a.introtext, a.checked_out, a.checked_out_time, a.catid, a.created,
    22270       user       localhost       rpdb        63  Sleep
    22269       user       localhost       rpdb        79  Query SELECT a.id, a.title, a.alias, a.title_alias, a.introtext, a.checked_out, a.checked_out_time, a.catid, a.created,
    22268       user       localhost       rpdb        85  Query SELECT a.id, a.title, a.alias, a.title_alias, a.introtext, a.checked_out, a.checked_out_time, a.catid, a.created,
    22267       user       localhost       rpdb        86  Sleep
    22266       user       localhost       rpdb        89  Query SELECT a.id, a.title, a.alias, a.title_alias, a.introtext, a.checked_out, a.checked_out_time, a.catid, a.created,
    22265       user       localhost       rpdb        96  Query SELECT a.id, a.title, a.alias, a.title_alias, a.introtext, a.checked_out, a.checked_out_time, a.catid, a.created,
    22264       user       localhost       rpdb       113  Query SELECT a.id, a.title, a.alias, a.title_alias, a.introtext, a.checked_out, a.checked_out_time, a.catid, a.created,
    22263       user       localhost       rpdb       117  Query SELECT a.id, a.title, a.alias, a.title_alias, a.introtext, a.checked_out, a.checked_out_time, a.catid, a.created,
    22262       user       localhost       rpdb       118  Query SELECT a.id, a.title, a.alias, a.title_alias, a.introtext, a.checked_out, a.checked_out_time, a.catid, a.created,
    22261       user       localhost       rpdb       119  Sleep
    22258       user       localhost       rpdb       129  Query SELECT a.id, a.title, a.alias, a.title_alias, a.introtext, a.checked_out, a.checked_out_time, a.catid, a.created,
    22257       user       localhost       rpdb       143  Query SELECT a.id, a.title, a.alias, a.title_alias, a.introtext, a.checked_out, a.checked_out_time, a.catid, a.created,
    22255       user       localhost       rpdb       144  Query SELECT a.id, a.title, a.alias, a.title_alias, a.introtext, a.checked_out, a.checked_out_time, a.catid, a.created,
    22256       user       localhost       rpdb       144  Query SELECT a.id, a.title, a.alias, a.title_alias, a.introtext, a.checked_out, a.checked_out_time, a.catid, a.created,
    22253       user       localhost       rpdb       154  Query SELECT a.id, a.title, a.alias, a.title_alias, a.introtext, a.checked_out, a.checked_out_time, a.catid, a.created,
    22252       user       localhost       rpdb       164  Query SELECT a.id, a.title, a.alias, a.title_alias, a.introtext, a.checked_out, a.checked_out_time, a.catid, a.created,
    22251       user       localhost       rpdb       178  Query SELECT a.id, a.title, a.alias, a.title_alias, a.introtext, a.checked_out, a.checked_out_time, a.catid, a.created,
    22250       user       localhost       rpdb       180  Query SELECT a.id, a.title, a.alias, a.title_alias, a.introtext, a.checked_out, a.checked_out_time, a.catid, a.created,
    22249       user       localhost       rpdb       193  Query SELECT a.id, a.title, a.alias, a.title_alias, a.introtext, a.checked_out, a.checked_out_time, a.catid, a.created,
    22248       user       localhost       rpdb       196  Query SELECT a.id, a.title, a.alias, a.title_alias, a.introtext, a.checked_out, a.checked_out_time, a.catid, a.created,
    22247       user       localhost       rpdb       197  Query SELECT a.id, a.title, a.alias, a.title_alias, a.introtext, a.checked_out, a.checked_out_time, a.catid, a.created,
    22246       user       localhost       rpdb       202  Query SELECT a.id, a.title, a.alias, a.title_alias, a.introtext, a.checked_out, a.checked_out_time, a.catid, a.created,
    22245       user       localhost       rpdb       208  Query SELECT a.id, a.title, a.alias, a.title_alias, a.introtext, a.checked_out, a.checked_out_time, a.catid, a.created,
    22243       user       localhost       rpdb       211  Query SELECT a.id, a.title, a.alias, a.title_alias, a.introtext, a.checked_out, a.checked_out_time, a.catid, a.created,
    22241       user       localhost       rpdb       222  Query SELECT a.id, a.title, a.alias, a.title_alias, a.introtext, a.checked_out, a.checked_out_time, a.catid, a.created

top - такую.

last pid:   486;  load averages: 11.45,  7.11,  3.81    up 1+17:14:43  08:46:54
242 processes: 40 running, 202 sleeping
CPU states: 12.3% user,  0.0% nice, 14.9% system,  0.8% interrupt, 72.0% idle
Mem: 1584M Active, 5894M Inact, 302M Wired, 231M Cache, 112M Buf, 12M Free
Swap: 14G Total, 3956K Used, 14G Free

  PID USERNAME    THR PRI NICE   SIZE    RES STATE  C   TIME   WCPU COMMAND
73008 mysql       106  20    0   325M   146M kserel 0  85:43  1.46% mysqld
  449 www           1   4    0   158M 23908K sbwait 1   0:00  0.66% httpd
  448 www           1   4    0   158M 24776K sbwait 1   0:00  0.54% httpd
  466 www           1   4    0   156M 17404K sbwait 1   0:00  0.49% httpd
  454 www           1   4    0   156M 17468K sbwait 1   0:00  0.38% httpd
  450 www           1   4    0   156M 19516K sbwait 3   0:00  0.36% httpd
 7979 root          1   8    0   153M 13968K nanslp 0   0:16  0.00% httpd
45642 user           1  96    0  6796K  2980K select 2   0:15  0.00% sshd
 4991 mailman       1   8    0 10360K  8500K nanslp 2   0:15  0.00% python2.5
 4990 mailman       1   8    0 10352K  8484K nanslp 0   0:15  0.00% python2.5
 4992 mailman       1   8    0 10352K  8480K nanslp 0   0:14  0.00% python2.5
 4994 mailman       1   8    0 10372K  8532K nanslp 0   0:14  0.00% python2.5
 4993 mailman       1   8    0 11140K  9456K nanslp 0   0:14  0.00% python2.5
 4995 mailman       1   8    0 10400K  8576K nanslp 2   0:14  0.00% python2.5
 4996 mailman       1   8    0 10356K  8488K nanslp 2   0:14  0.00% python2.5
 3260 root          1  96    0  3484K  1216K select 0   0:12  0.00% sendmail
97563 root          1   8    0  5048K  4372K wait   0   0:12  0.00% mc
 3157 root          1  96    0  1376K   412K select 3   0:04  0.00% syslogd
99907 www           1   4    0   158M 26312K sbwait 1   0:03  0.00% httpd
99910 www           1   4    0   158M 26780K sbwait 1   0:03  0.00% httpd
99974 www           1   4    0   158M 26924K sbwait 3   0:03  0.00% httpd
99906 www           1   4    0   159M 25488K sbwait 2   0:01  0.00% httpd
99936 www           1   4    0   158M 26936K sbwait 1   0:01  0.00% httpd
99918 www           1   4    0   158M 26824K sbwait 1   0:01  0.00% httpd
  138 www           1   4    0   158M 26276K sbwait 2   0:00  0.00% httpd
 3270 root          1   8    0  1404K   372K nanslp 0   0:00  0.00% cron
99970 www           1   4    0   158M 25316K sbwait 2   0:00  0.00% httpd

Т.е. при переходе с Joomla 1.0 на 2.5 появилось много медленных запросов и плодящиеся процессы httpd.

memcached поставьте, через него запросы.
Еще можно проверить и увеличить кэши самого MySQL

Установлен Xcache, но ситуацию он не поменял.

Могу выложить настройки MySQL 5.0.59

[mysqld]
set-variable=long_query_time=3
log-slow-queries=/var/log/mysql_slow_query.log
log=/var/log/mysqld.log
max_allowed_packet=48M

skip-bdb
skip-innodb

thread_cache_size=4
table_cache=1024
query_cache_size=32M
query_cache_limit=32M

join_buffer_size=1M
key_buffer_size=128M

max_connections = 200

expire_logs_days=0


Memcached попробую.

Гм.

memcached поставьте, через него запросы.
Еще можно проверить и увеличить кэши самого MySQL

А как правильно подключать memcached для оптимизации MySQL?

Гм.
А как правильно подключать memcached для оптимизации MySQL?

Установить, в админке сайта настроить кэширование через него.
В 2.5 это из коробки работает.

Memcached поставить не удалось из-за проблем с зависимостями в портах freebsd.
Поставил чистую J2.5 - перенес данные с помощью SP Upgrade с J.1.0 через 1.5 на 2.5. Поставил шаблон полегче. Подкрутил настройки с my.cnf - практически никакого результата..
Что можно еще сделать?

smart - Сергей, не путай свой дедик с виртуалкой, на виртуалке естессно у него не будет летать база в 100 метров

У меня jail на Freebsd 6.2 на сервере 4 Ядра
Вот top c Joomla 2.5 c шаблоном Beez

last pid: 45942;  load averages:  1.82,  1.32,  1.06                                                             up 5+07:47:40  23:19:51
122 processes: 1 running, 121 sleeping
CPU states: 15.9% user,  0.0% nice, 15.5% system,  2.6% interrupt, 66.0% idle
Mem: 1417M Active, 5638M Inact, 304M Wired, 244K Cache, 112M Buf, 664M Free
Swap: 14G Total, 4336K Used, 14G Free

Вот этими настройками не удалось добится всего-лишь того, что load averages не достигает запредельных величин.

my.cnf
[mysqld]
set-variable=long_query_time=30
#~3
log-slow-queries=/var/log/mysql_slow_query.log
log=/var/log/mysqld.log
max_allowed_packet=48M
# wait_timeout = 600
skip-bdb
skip-innodb

thread_cache_size=64
#~4
table_cache=1024
# query_cache_type=1
query_cache_size=64M
query_cache_limit=64M
#~32

# low_priority_updates=1
join_buffer_size=1M

key_buffer_size=128M
# read_buffer_size=500k
# sort_buffer_size=8M

# read_rnd_buffer_size=8M

# myisam_sort_buffer_size=200M

max_connections = 200
# concurrent_insert=2
expire_logs_days=0
thread_concurrency=4
# tmp_table_size=832

#
key_buffer=512M
sort_buffer=128M
record_buffer=32M
max_join_size=1000000
max_sort_length=20
myisam_sort_buffer_size=512M
net_read_timeout=12
net_write_timeout=15
wait_timeout=30
interactive_timeout=600
thread_cache_size=64

Но сайт под нагрузкой постоянно жалуется на нехватку памяти. Что-то я "закрутил" видимо.

JDatabaseMySQLi::query: 5 - Out of memory (Needed 1676920 bytes) SQL=SHOW TABLES

Call stack
#   Function   Location
1   JSite->initialise()   /var/pub/httpd/index.php:30
2   JApplication->initialise()   /var/pub/httpd/includes/application.php:116
3   JApplication->triggerEvent()   /var/pub/httpd/libraries/joomla/application/application.php:230
4   JDispatcher->trigger()   /var/pub/httpd/libraries/joomla/application/application.php:642
5   JEvent->update()   /var/pub/httpd/libraries/joomla/event/dispatcher.php:161
6   call_user_func_array()   /var/pub/httpd/libraries/joomla/event/event.php:71
7   plgSystemjdownloads->onAfterInitialise()   
8   JDatabaseMySQL->getTableList()   /var/pub/httpd/plugins/system/jdownloads/jdownloads.php:30
9   JDatabase->loadColumn()   /var/pub/httpd/libraries/joomla/database/database/mysql.php:423
10   JDatabaseMySQLi->execute()   /var/pub/httpd/libraries/joomla/database/database.php:965
11   JError::raiseError()   /var/pub/httpd/libraries/joomla/database/database/mysqli.php:396
12   JError::raise()   /var/pub/httpd/libraries/joomla/error/error.php:251

Люди, помогите! Может за деньги кто-нибудь поможет?
Я все мозги разбил на части! Сайт 2 недели не работает! Пипец 13 лет работы
Joomla 3 поставил, толку никакого ;(
Получается J 1.0 - самая быстрая?

Как можно оптимизировать запросы самой Joomla, если они в нее уже встроены?
В slow_queries логе вот такие запросы.

SELECT contact.user_id, MAX(contact.id) AS id, contact.language
FROM ddyqz_contact_details AS contact
WHERE contact.published = 1
GROUP BY contact.user_id, contact.language) AS contact ON contact.user_id = a.created_by
LEFT JOIN ddyqz_categories as parent ON parent.id = c.parent_id
LEFT JOIN ddyqz_content_rating AS v ON a.id = v.content_id
LEFT OUTER JOIN (SELECT cat.id as id FROM ddyqz_categories AS cat JOIN ddyqz_categories AS parent ON cat.lft BETWEEN parent.lft AND parent.rgt WHERE parent.extension = 'com_content' AND parent.published != 1 GROUP BY cat.id ) AS badcats ON badcats.id = c.id
WHERE a.access IN (1,1) AND c.access IN (1,1) AND CASE WHEN badcats.id is null THEN a.state ELSE 0 END = 1 AND (a.publish_up = '0000-00-00 00:00:00' OR a.publish_up <= '2012-11-06 18:39:30') AND (a.publish_down = '0000-00-00 00:00:00' OR a.publish_down >= '2012-11-06 18:39:30')
GROUP BY a.id, a.title, a.alias, a.introtext, a.checked_out, a.checked_out_time, a.catid, a.created, a.created_by, a.created_by_alias, a.created, a.modified, a.modified_by, uam.name, a.publish_up, a.attribs, a.metadata, a.metakey, a.metadesc, a.access, a.hits, a.xreference, a.featured, a.fulltext, a.state, a.publish_down, badcats.id, c.title, c.path, c.access, c.alias, uam.id, ua.name, ua.email, contact.id, parent.title, parent.id, parent.path, parent.alias, v.rating_sum, v.rating_count, c.published, c.lft, a.ordering, parent.lft, fp.ordering, c.id, a.images, a.urls
ORDER BY a.created DESC LIMIT 0, 3;

Как насчет MariaDB воткнуть?

Никогда с ним не работал. Как его связывать с php?

@Magi
А какие расширения у Вас стоят? Может попробуйте сделать тестовую копию сайта и начинать на ней эксперименты - выключайте плагины, удаляйте расширения и т.п.

Уже пробовал, например на J3 стоит только Jdownloads и blog calendar, их отключение не приводит к видимым результатам.

Уже пробовал, например на J3 стоит только Jdownloads и blog calendar, их отключение не приводит к видимым результатам.

Тогда проблема в железе. Вы конечно извините, но миллионы пользуются Joomla! 2.5 без проблем. Загвоздка не в ней.

Т.е. J1 быстрее J 2.5 ? Обновление php c 5.3.2 до 5.4.7 и MySQL 5.0.95 до  5.5. Может что-нибудь дать?

PHP не трогайте, а мускул - можно попробовать обновить.