Тормозит сайт на linux ubuntu

Приведите что-нибудь из статистики. Хотя бы top дайте во время открытия.

xCache/eAccelerator шутстрость повышают.

вот когда открывается карточка товара (и происходит это очень медленно) в это время команда top выдает такое:

top - 18:46:55 up 44 min, 4 users, load average: 0,66, 0,39, 0,24

Tasks: 205 total, 1 running, 204 sleeping, 0 stopped, 0 zombie

%Cpu(s): 45,8 us, 5,0 sy, 0,0 ni, 48,8 id, 0,3 wa, 0,0 hi, 0,0 si, 0,0 st

п*п╦п▒ Mem: 3081640 total, 1898548 used, 1183092 free, 141756 buffers

п*п╦п▒ Swap: 2094076 total, 0 used, 2094076 free, 848728 cached

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND

8140 mysql 20 0 713m 57m 7188 S 100,0 1,9 0:29.96 mysqld

3386 andy 20 0 736m 117m 38m S 0,7 3,9 0:21.91 firefox

8046 root 20 0 26092 1588 1104 R 0,3 0,1 0:00.42 top

1 root 20 0 26936 2624 1340 S 0,0 0,1 0:01.65 init

2 root 20 0 0 0 0 S 0,0 0,0 0:00.00 kthreadd

3 root 20 0 0 0 0 S 0,0 0,0 0:00.70 ksoftirqd/0

5 root 0 -20 0 0 0 S 0,0 0,0 0:00.00 kworker/0:0H

7 root 0 -20 0 0 0 S 0,0 0,0 0:00.00 kworker/u:0H

8 root rt 0 0 0 0 S 0,0 0,0 0:00.02 migration/0

9 root 20 0 0 0 0 S 0,0 0,0 0:00.00 rcu_bh

10 root 20 0 0 0 0 S 0,0 0,0 0:01.22 rcu_sched

11 root rt 0 0 0 0 S 0,0 0,0 0:00.01 watchdog/0

12 root rt 0 0 0 0 S 0,0 0,0 0:00.01 watchdog/1

13 root 20 0 0 0 0 S 0,0 0,0 0:00.50 ksoftirqd/1

14 root rt 0 0 0 0 S 0,0 0,0 0:00.06 migration/1

15 root 20 0 0 0 0 S 0,0 0,0 0:00.57 kworker/1:0

16 root 0 -20 0 0 0 S 0,0 0,0 0:00.00 kworker/1:0H

Из этого видно что весь ресурс проца "выедает" база данных. Смотрите в сторону ее оптимизации. Кстати, статистику немного удобнее смотреть htop просто визуально нагляднее. В самой БД можно настроить логирование запросов, на которые уходит много времени, возможно это поможет найти причину.

Что бы понять почему "тормозит" сайт, рекомендую воспользоваться одним из профайлеров. Например, newrelic. В базе данных рекомендую включить slow logs. Вывод медленных кверей может подсказать дальнейшие шаги.

Спасибо за ответ

вот что в логе

подскажите пожалуйста что можно дальше сделать

# Time: 131017 20:36:10

# User@Host: root[root] @ localhost []

# Query_time: 0.000273 Lock_time: 0.000108 Rows_sent: 0 Rows_examined: 2

SET timestamp=1382027770;

delete from whos_online where time_last_click < '1382026870';

# User@Host: root[root] @ localhost []

# Query_time: 0.000198 Lock_time: 0.000059 Rows_sent: 1 Rows_examined: 2

SET timestamp=1382027770;

select count(*) as count from whos_online where session_id = 'l50v7c9ag0k1v39bju41r1uur7';

# User@Host: root[root] @ localhost []

# Query_time: 0.000208 Lock_time: 0.000055 Rows_sent: 0 Rows_examined: 2

SET timestamp=1382027770;

update whos_online set customer_id = '0', full_name = 'п÷п╬я│п╣я┌п╦я┌п╣п╩я▄', ip_address = '127.0.0.1', time_last_click = '1

382027770', last_page_url = '/index.php?cat=22' where session_id = 'l50v7c9ag0k1v39bju41r1uur7';

# Time: 131017 20:36:16

# User@Host: root[root] @ localhost []

# Query_time: 6.282628 Lock_time: 0.000224 Rows_sent: 10 Rows_examined: 622090

SET timestamp=1382027776;

select distinct

p.products_id,

pd.products_name,

p.products_image,

p.products_tax_class_id,

p.products_vpe,

p.products_vpe_status,

p.products_vpe_value,

p.products_price

from products p, products_description pd, products_to_categories p2c, c

ategories c

where p.products_status=1

and p.products_id = p2c.products_id

and pd.products_id = p.products_id

and p.products_id !='0'

and c.categories_id = p2c.categories_id

and c.categories_status=1 order by

p.products_date_added desc limit 10;

# Time: 131017 20:36:20

# User@Host: root[root] @ localhost []

# Query_time: 0.000281 Lock_time: 0.000111 Rows_sent: 0 Rows_examined: 2

SET timestamp=1382027780;

delete from whos_online where time_last_click < '1382026880';

# User@Host: root[root] @ localhost []

# Query_time: 0.000210 Lock_time: 0.000060 Rows_sent: 1 Rows_examined: 2

SET timestamp=1382027780;

select count(*) as count from whos_online where session_id = 'l50v7c9ag0k1v39bju41r1uur7';

# User@Host: root[root] @ localhost []

# Query_time: 0.000207 Lock_time: 0.000055 Rows_sent: 0 Rows_examined: 2

SET timestamp=1382027780;

update whos_online set customer_id = '0', full_name = 'п÷п╬я│п╣я┌п╦я┌п╣п╩я▄', ip_address = '127.0.0.1', time_last_click = '1

382027780', last_page_url = '/index.php?cat=22&page=5' where session_id = 'l50v7c9ag0k1v39bju41r1uur7';

# Time: 131017 20:36:21

# User@Host: root[root] @ localhost []

# Query_time: 0.891576 Lock_time: 0.000243 Rows_sent: 40 Rows_examined: 201352

SET timestamp=1382027781;

select p.products_fsk18,

p.products_shippingtime,

p.products_model,

p.products_ean,

pd.products_name,

m.manufacturers_name,

p.products_quantity,

p.products_image,

p.products_weight,

pd.products_short_description,

pd.products_description,

p.products_id,

p.manufacturers_id,

p.products_price,

p.products_vpe,

p.products_vpe_status,

p.products_vpe_value,

p.products_discount_allowed,

p.products_tax_class_id

from products_description pd, products_to_categories p2c, products p left join

manufacturers m on p.manufacturers_id = m.manufacturers_id

left join specials s on p.products_id = s.products_id

where p.products_status = '1'

and p.products_id = p2c.products_id

and pd.products_id = p2c.products_id

and pd.language_id = '1'

and p2c.categories_id = '22' ORDER BY p.products_price ASC LIMIT 160, 40;

# Time: 131017 20:36:25

# User@Host: root[root] @ localhost []

# Query_time: 0.000276 Lock_time: 0.000106 Rows_sent: 0 Rows_examined: 2

SET timestamp=1382027785;

delete from whos_online where time_last_click < '1382026885';

# User@Host: root[root] @ localhost []

# Query_time: 0.000203 Lock_time: 0.000062 Rows_sent: 1 Rows_examined: 2

SET timestamp=1382027785;

select count(*) as count from whos_online where session_id = 'l50v7c9ag0k1v39bju41r1uur7';

# User@Host: root[root] @ localhost []

# Query_time: 0.000201 Lock_time: 0.000055 Rows_sent: 0 Rows_examined: 2

SET timestamp=1382027785;

update whos_online set customer_id = '0', full_name = 'п÷п╬я│п╣я┌п╦я┌п╣п╩я▄', ip_address = '127.0.0.1', time_last_click = '1

382027785', last_page_url = '/product_info.php?products_id=3660' where session_id = 'l50v7c9ag0k1v39bju41r1uur7';

# Time: 131017 20:36:32

# User@Host: root[root] @ localhost []

# Query_time: 7.366894 Lock_time: 0.000197 Rows_sent: 10 Rows_examined: 622086

SET timestamp=1382027792;

select distinct

p.products_id,

pd.products_name,

p.products_image,

p.products_tax_class_id,

p.products_vpe,

p.products_vpe_status,

p.products_vpe_value,

p.products_price

from products p, products_description pd, products_to_categories p2c, c

ategories c

where p.products_status=1

and p.products_id = p2c.products_id

and pd.products_id = p.products_id

and p.products_id !='3660'

and c.categories_id = p2c.categories_id

and c.categories_status=1 order by

p.products_date_added desc limit 10;

# User@Host: root[root] @ localhost []

# Query_time: 0.001179 Lock_time: 0.000180 Rows_sent: 0 Rows_examined: 3

SET timestamp=1382027792;

select

p.products_fsk18,

p.products_id,

p.products_price,

p.products_tax_class_id,

p.products_image,

pd.products_name,

p.products_vpe,

p.products_vpe_status,

p.products_vpe_value,

pd.products_short_description FROM orders_products opa, orders_products opb, order

s o, products p, products_description pd

where opa.products_id = '3660'

and opa.orders_id = opb.orders_id

and opb.products_id != '3660'

and opb.products_id = p.products_id

and opb.orders_id = o.orders_id

and p.products_status = '1'

and pd.language_id = '1'

and opb.products_id = pd.products_id

group by p.products_id order by o.date_purchased desc limit 6;

Я вижу в выводе медленных запросов квери, которые выполняются меньше секунды. Какой вы задали порог?

Давайте поставим 5 секунд и попробуем выловить самое "тяжелое ":

long_query_time = 4

Параллельно с этим, давайте прогоним скрипт для оптимизации MySQL. Вывод скрипта - в студию.

https://raw.github.com/major/MySQLTuner-perl/master/mysqltuner.pl

сделал как вы сказали в конфиге мускула прописал 5 секунд

вот лог

# Time: 131017 21:33:24

# User@Host: root[root] @ localhost []

# Query_time: 0.140222 Lock_time: 0.000101 Rows_sent: 0 Rows_examined: 124416

SET timestamp=1382031204;

select p.products_id,

pd.products_name,

products_date_available as date_expected from products p, products_description pd

where to_days(products_date_available) >= to_days(now())

and p.products_id = pd.products_id

and pd.language_id = '1'

order by date_expected desc

limit 10;

# User@Host: root[root] @ localhost []

# Query_time: 0.000230 Lock_time: 0.000096 Rows_sent: 0 Rows_examined: 3

SET timestamp=1382031204;

delete from whos_online where time_last_click < '1382030304';

# User@Host: root[root] @ localhost []

# Query_time: 0.000199 Lock_time: 0.000067 Rows_sent: 1 Rows_examined: 3

SET timestamp=1382031204;

select count(*) as count from whos_online where session_id = 'l50v7c9ag0k1v39bju41r1uur7';

# User@Host: root[root] @ localhost []

# Query_time: 0.000207 Lock_time: 0.000062 Rows_sent: 0 Rows_examined: 3

SET timestamp=1382031204;

update whos_online set customer_id = '0', full_name = 'Посетитель', ip_address = '127.0.0.1', time_last_click = '1382031204', last_pag

e_url = '/product_info.php?products_id=31783' where session_id = 'l50v7c9ag0k1v39bju41r1uur7';

# Time: 131017 21:33:30

# User@Host: root[root] @ localhost []

# Query_time: 6.773636 Lock_time: 0.000220 Rows_sent: 10 Rows_examined: 622086

SET timestamp=1382031210;

select distinct

p.products_id,

pd.products_name,

p.products_image,

p.products_tax_class_id,

p.products_vpe,

p.products_vpe_status,

p.products_vpe_value,

p.products_price

from products p, products_description pd, products_to_categories p2c, categories c

where p.products_status=1

and p.products_id = p2c.products_id

and pd.products_id = p.products_id

and p.products_id !='31783'

and c.categories_id = p2c.categories_id

and c.categories_status=1 order by

p.products_date_added desc limit 10;

# Time: 131017 21:33:31

# User@Host: root[root] @ localhost []

# Query_time: 0.001520 Lock_time: 0.000228 Rows_sent: 0 Rows_examined: 3

SET timestamp=1382031211;

select

p.products_fsk18,

p.products_id,

p.products_price,

p.products_tax_class_id,

p.products_image,

pd.products_name,

p.products_vpe,

p.products_vpe_status,

p.products_vpe_value,

pd.products_short_description FROM orders_products opa, orders_products opb, orders o, products p, products_description pd

where opa.products_id = '31783'

and opa.orders_id = opb.orders_id

and opb.products_id != '31783'

and opb.products_id = p.products_id

and opb.orders_id = o.orders_id

and p.products_status = '1'

and pd.language_id = '1'

and opb.products_id = pd.products_id

group by p.products_id order by o.date_purchased desc limit 6;

===============================================================

вот вывод перловского скрипта

>> MySQLTuner 1.2.0 - Major Hayden <major@mhtx.net>

>> Bug reports, feature requests, and downloads at http://mysqltuner.com/

>> Run with '--help' for additional options and output filtering

[OK] Logged in using credentials from debian maintenance account.

[--] Assuming 3000 MB of physical memory

[--] Assuming 200 MB of swap space

-------- General Statistics --------------------------------------------------

[--] Skipped version check for MySQLTuner script

[OK] Currently running supported MySQL version 5.5.32-0ubuntu0.13.04.1-log

[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------

[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster

[--] Data in MyISAM tables: 125M (Tables: 369)

[--] Data in InnoDB tables: 384K (Tables: 3)

[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)

[!!] Total fragmented tables: 4

-------- Security Recommendations -------------------------------------------

[!!] User '@andy-OEM' has no password set.

[!!] User '@localhost' has no password set.

-------- Performance Metrics -------------------------------------------------

[--] Up for: 51m 59s (7K q [2.267 qps], 134 conn, TX: 10M, RX: 911K)

[--] Reads / Writes: 95% / 5%

[--] Total buffers: 576.0M global + 12.4M per thread (151 max threads)

[OK] Maximum possible memory usage: 2.4G (81% of installed RAM)

[OK] Slow queries: 3% (225/7K)

[OK] Highest usage of available connections: 1% (3/151)

[OK] Key buffer size / total MyISAM indexes: 384.0M/15.1M

[OK] Key buffer hit rate: 100.0% (31M cached / 4K reads)

[OK] Query cache efficiency: 62.1% (3K cached / 6K selects)

[OK] Query cache prunes per day: 0

[!!] Sorts requiring temporary tables: 42% (27 temp sorts / 63 sorts)

[!!] Joins performed without indexes: 10

[OK] Temporary tables created on disk: 22% (41 on disk / 185 total)

[OK] Thread cache hit rate: 97% (3 created / 134 connections)

[OK] Table cache hit rate: 93% (156 open / 167 opened)

[OK] Open file limit used: 23% (280/1K)

[OK] Table locks acquired immediately: 100% (3K immediate / 3K locks)

[OK] InnoDB data size / buffer pool: 384.0K/128.0M

-------- Recommendations -----------------------------------------------------

General recommendations:

Run OPTIMIZE TABLE to defragment tables for better performance

MySQL started within last 24 hours - recommendations may be inaccurate

Adjust your join queries to always utilize indexes

Variables to adjust:

sort_buffer_size (> 2M)

read_rnd_buffer_size (> 8M)

join_buffer_size (> 128.0K, or always use indexes with joins)

PS спасибо, что до сих пор помогаете мне :)

Ещё скриптец для тестов http://www.day32.com/MySQL/tuning-primer.sh

Попробуйте запустить перловский скрипт повторно (нужно что бы база не перезагружалась как минимум часа 2-3, иначе он не может собрать статистику). После этого давайте попробуем выставить переменные в те значения, которые рекомендует скрипт. В данный момент (как вы видите из вывода скрипта), он дает такие рекомедации

Adjust your join queries to always utilize indexes

Variables to adjust:

sort_buffer_size (> 2M)

read_rnd_buffer_size (> 8M)

join_buffer_size (> 128.0K, or always use indexes with joins)

Как я написал выше, давайте подождем что бы база была запущена часа 2-3 и после этого выполним скрипт еще раз.