Новости
Документация
Download
Webboard
Поиск
FAQ/ЧаВо
Обратная связь

MySQL.RU - Webboard



Вернуться
выборка занимает слишком много времени (runner) 04/07/2007 - 14:29:00
      Re: выборка занимает слишком много времени (Po4emu4ka) 05/07/2007 - 10:02:23

> Original message text:
> From: runner - 04/07/2007 - 14:29:00
> Subject:выборка занимает слишком много времени
> -----------------
> Доброго дня!
>
> Имеется база mysql (~200 M)
> Имеется сайт, работающий с ней. Каждая страница делает 6-10 запросов. Запросы в принципе небольшие, но систему грузят так, что сервер с 2М оперативки и SATA винтом 160Г почти ложится.
> Внизу пример списка процессов mysql
>
> Ещё иногда в консоли можно увидеть вот такое:
> bonton2web# /: write failed, filesystem is full
>
> Система: Mysql 4.1, FreeBSD 6.2
>
> Таблицы MyISAM, вложенных запросов нет, всё просто... Подскажите хотя бы в каком направлении копать!
>
> Спасибо всем кто откликнется :)
>
>
>
>
>
>
> bonton2web# mysqladmin processlist
> +--------+---------+-----------+-----------------+---------+--------+----------- -------------------+------------------------------------------------------------ ------------------------------------------+
> | Id | User | Host | db | Command | Time | State | Info |
> +--------+---------+-----------+-----------------+---------+--------+----------- -------------------+------------------------------------------------------------ ------------------------------------------+
> | 4798 | web2_u1 | localhost | blgdatabase_dbo | Query | 549130 | Sending da ta | SELECT RealtyUnit.HRealtyID, RealtyUnit.ImageCoID, RealtyUn it.TownCode, RealtyUnit.RegionCode, Realt |
> | 4801 | web2_u1 | localhost | blgdatabase_dbo | Query | 549072 | Sending da ta | SELECT RealtyUnit.HRealtyID, RealtyUnit.ImageCoID, RealtyUn it.TownCode, RealtyUnit.RegionCode, Realt |
> | 239057 | web2_u1 | localhost | blgdatabase_dbo | Query | 19390 | Sorting re sult | SELECT RealtyUnit.HRealtyID, RealtyUnit.ImageCoID, RealtyUn it.TownCode, RealtyUnit.RegionCode, Realt |
> | 239066 | web2_u1 | localhost | blgdatabase_dbo | Query | 19030 | Sorting re sult | SELECT RealtyUnit.HRealtyID, RealtyUnit.ImageCoID, RealtyUn it.TownCode, RealtyUnit.RegionCode, Realt |
> | 239589 | web2_u1 | localhost | blgdatabase_dbo | Query | 8989 | Sorting re sult | SELECT RealtyUnit.HRealtyID, RealtyUnit.ImageCoID, RealtyUn it.TownCode, RealtyUnit.RegionCode, Realt |
> | 239591 | web2_u1 | localhost | blgdatabase_dbo | Query | 8799 | Copying to tmp table on disk | SELECT RealtyUnit.HRealtyID, RealtyUnit.ImageCoID, RealtyUn it.TownCode, RealtyUnit.RegionCode, Realt |
> | 239593 | web2_u1 | localhost | blgdatabase_dbo | Query | 8626 | Copying to tmp table on disk | SELECT RealtyUnit.HRealtyID, RealtyUnit.ImageCoID, RealtyUn it.TownCode, RealtyUnit.RegionCode, Realt |
> | 239594 | web2_u1 | localhost | blgdatabase_dbo | Query | 8429 | Copying to tmp table on disk | SELECT RealtyUnit.HRealtyID, RealtyUnit.ImageCoID, RealtyUn it.TownCode, RealtyUnit.RegionCode, Realt |
> | 239605 | web2_u1 | localhost | blgdatabase_dbo | Query | 8207 | Copying to tmp table on disk | SELECT RealtyUnit.HRealtyID, RealtyUnit.ImageCoID, RealtyUn it.TownCode, RealtyUnit.RegionCode, Realt |
> | 239631 | web2_u1 | localhost | blgdatabase_dbo | Query | 7977 | Copying to tmp table on disk | SELECT RealtyUnit.HRealtyID, RealtyUnit.ImageCoID, RealtyUn it.TownCode, RealtyUnit.RegionCode, Realt |
> | 239645 | web2_u1 | localhost | blgdatabase_dbo | Query | 7797 | Copying to tmp table on disk | SELECT RealtyUnit.HRealtyID, RealtyUnit.ImageCoID, RealtyUn it.TownCode, RealtyUnit.RegionCode, Realt |
> | 239688 | web2_u1 | localhost | blgdatabase_dbo | Query | 7617 | Copying to tmp table on disk | SELECT RealtyUnit.HRealtyID, RealtyUnit.ImageCoID, RealtyUn it.TownCode, RealtyUnit.RegionCode, Realt |
> | 239713 | web2_u1 | localhost | blgdatabase_dbo | Query | 7547 | Copying to tmp table on disk | SELECT RealtyUnit.HRealtyID, RealtyUnit.ImageCoID, RealtyUn it.TownCode, RealtyUnit.RegionCode, Realt |
> | 239722 | web2_u1 | localhost | blgdatabase_dbo | Query | 7350 | Copying to tmp table on disk | SELECT RealtyUnit.HRealtyID, RealtyUnit.ImageCoID, RealtyUn it.TownCode, RealtyUnit.RegionCode, Realt |
> | 239725 | web2_u1 | localhost | blgdatabase_dbo | Query | 7150 | Copying to tmp table on disk | SELECT RealtyUnit.HRealtyID, RealtyUnit.ImageCoID, RealtyUn it.TownCode, RealtyUnit.RegionCode, Realt |
> | 239728 | web2_u1 | localhost | blgdatabase_dbo | Query | 6911 | Copying to tmp table on disk | SELECT RealtyUnit.HRealtyID, RealtyUnit.ImageCoID, RealtyUn it.TownCode, RealtyUnit.RegionCode, Realt |
> | 239739 | web2_u1 | localhost | blgdatabase_dbo | Query | 6729 | Sorting re sult | SELECT RealtyUnit.HRealtyID, RealtyUnit.ImageCoID, RealtyUn it.TownCode, RealtyUnit.RegionCode, Realt |
> | 239746 | web2_u1 | localhost | blgdatabase_dbo | Query | 6518 | Copying to tmp table on disk | SELECT RealtyUnit.HRealtyID, RealtyUnit.ImageCoID, RealtyUn it.TownCode, RealtyUnit.RegionCode, Realt |
> | 239756 | web2_u1 | localhost | blgdatabase_dbo | Query | 6314 | Copying to tmp table on disk | SELECT RealtyUnit.HRealtyID, RealtyUnit.ImageCoID, RealtyUn it.TownCode, RealtyUnit.RegionCode, Realt |
> | 239773 | web2_u1 | localhost | blgdatabase_dbo | Query | 6133 | Copying to tmp table on disk | SELECT RealtyUnit.HRealtyID, RealtyUnit.ImageCoID, RealtyUn it.TownCode, RealtyUnit.RegionCode, Realt |
> | 239812 | web2_u1 | localhost | blgdatabase_dbo | Query | 5935 | Copying to tmp table on disk | SELECT RealtyUnit.HRealtyID, RealtyUnit.ImageCoID, RealtyUn it.TownCode, RealtyUnit.RegionCode, Realt |
> | 239846 | web2_u1 | localhost | blgdatabase_dbo | Query | 5731 | Copying to tmp table on disk | SELECT RealtyUnit.HRealtyID, RealtyUnit.ImageCoID, RealtyUn it.TownCode, RealtyUnit.RegionCode, Realt |
> | 239857 | web2_u1 | localhost | blgdatabase_dbo | Query | 5551 | Copying to tmp table on disk | SELECT RealtyUnit.HRealtyID, RealtyUnit.ImageCoID, RealtyUn it.TownCode, RealtyUnit.RegionCode, Realt |
> | 239864 | web2_u1 | localhost | blgdatabase_dbo | Query | 5345 | Copying to tmp table on disk | SELECT RealtyUnit.HRealtyID, RealtyUnit.ImageCoID, RealtyUn it.TownCode, RealtyUnit.RegionCode, Realt |
> | 239888 | web2_u1 | localhost | blgdatabase_dbo | Query | 5142 | Copying to tmp table on disk | SELECT RealtyUnit.HRealtyID, RealtyUnit.ImageCoID, RealtyUn it.TownCode, RealtyUnit.RegionCode, Realt |
> | 239896 | web2_u1 | localhost | blgdatabase_dbo | Query | 4963 | Copying to tmp table on disk | SELECT RealtyUnit.HRealtyID, RealtyUnit.ImageCoID, RealtyUn it.TownCode, RealtyUnit.RegionCode, Realt |
> | 239916 | web2_u1 | localhost | blgdatabase_dbo | Query | 4783 | Copying to tmp table on disk | SELECT RealtyUnit.HRealtyID, RealtyUnit.ImageCoID, RealtyUn it.TownCode, RealtyUnit.RegionCode, Realt |
> | 239942 | web2_u1 | localhost | blgdatabase_dbo | Query | 4724 | Copying to tmp table on disk | SELECT RealtyUnit.HRealtyID, RealtyUnit.ImageCoID, RealtyUn it.TownCode, RealtyUnit.RegionCode, Realt |
> | 239992 | web2_u1 | localhost | blgdatabase_dbo | Query | 4584 | Copying to tmp table on disk | SELECT RealtyUnit.HRealtyID, RealtyUnit.ImageCoID, RealtyUn it.TownCode, RealtyUnit.RegionCode, Realt |
> | 239993 | web2_u1 | localhost | blgdatabase_dbo | Query | 4575 | Copying to tmp table on disk | SELECT RealtyUnit.HRealtyID, RealtyUnit.ImageCoID, RealtyUn it.TownCode, RealtyUnit.RegionCode, Realt |
> | 239999 | web2_u1 | localhost | blgdatabase_dbo | Query | 4416 | Copying to tmp table on disk | SELECT RealtyUnit.HRealtyID, RealtyUnit.ImageCoID, RealtyUn it.TownCode, RealtyUnit.RegionCode, Realt |
> | 240000 | web2_u1 | localhost | blgdatabase_dbo | Query | 4404 | Copying to tmp table on disk | SELECT RealtyUnit.HRealtyID, RealtyUnit.ImageCoID, RealtyUn it.TownCode, RealtyUnit.RegionCode, Realt |
> | 240021 | web2_u1 | localhost | blgdatabase_dbo | Query | 4221 | Copying to tmp table on disk | SELECT RealtyUnit.HRealtyID, RealtyUnit.ImageCoID, RealtyUn it.TownCode, RealtyUnit.RegionCode, Realt |
> | 240039 | web2_u1 | localhost | blgdatabase_dbo | Query | 4041 | Copying to tmp table on disk | SELECT RealtyUnit.HRealtyID, RealtyUnit.ImageCoID, RealtyUn it.TownCode, RealtyUnit.RegionCode, Realt |
> | 240059 | web2_u1 | localhost | blgdatabase_dbo | Query | 3864 | Copying to tmp table on disk | SELECT RealtyUnit.HRealtyID, RealtyUnit.ImageCoID, RealtyUn it.TownCode, RealtyUnit.RegionCode, Realt |
> | 240066 | web2_u1 | localhost | blgdatabase_dbo | Query | 3657 | Copying to tmp table on disk | SELECT RealtyUnit.HRealtyID, RealtyUnit.ImageCoID, RealtyUn it.TownCode, RealtyUnit.RegionCode, Realt |
> | 240070 | web2_u1 | localhost | blgdatabase_dbo | Query | 3456 | Copying to tmp table on disk | SELECT RealtyUnit.HRealtyID, RealtyUnit.ImageCoID, RealtyUn it.TownCode, RealtyUnit.RegionCode, Realt |
> | 240073 | web2_u1 | localhost | blgdatabase_dbo | Query | 3274 | Copying to tmp table on disk | SELECT RealtyUnit.HRealtyID, RealtyUnit.ImageCoID, RealtyUn it.TownCode, RealtyUnit.RegionCode, Realt |
> | 240076 | web2_u1 | localhost | blgdatabase_dbo | Query | 3031 | Copying to tmp table on disk | SELECT RealtyUnit.HRealtyID, RealtyUnit.ImageCoID, RealtyUn it.TownCode, RealtyUnit.RegionCode, Realt |
> | 240092 | web2_u1 | localhost | blgdatabase_dbo | Query | 2857 | Copying to tmp table on disk | SELECT RealtyUnit.HRealtyID, RealtyUnit.ImageCoID, RealtyUn it.TownCode, RealtyUnit.RegionCode, Realt |
> | 240099 | web2_u1 | localhost | blgdatabase_dbo | Query | 2842 | Copying to tmp table on disk | SELECT RealtyUnit.HRealtyID, RealtyUnit.ImageCoID, RealtyUn it.TownCode, RealtyUnit.RegionCode, Realt |
> | 240127 | web2_u1 | localhost | blgdatabase_dbo | Query | 2639 | Copying to tmp table on disk | SELECT RealtyUnit.HRealtyID, RealtyUnit.ImageCoID, RealtyUn it.TownCode, RealtyUnit.RegionCode, Realt |
> | 240140 | web2_u1 | localhost | blgdatabase_dbo | Query | 2486 | Copying to tmp table on disk | SELECT RealtyUnit.HRealtyID, RealtyUnit.ImageCoID, RealtyUn it.TownCode, RealtyUnit.RegionCode, Realt |
> | 240145 | web2_u1 | localhost | blgdatabase_dbo | Query | 2464 | Copying to tmp table on disk | SELECT RealtyUnit.HRealtyID, RealtyUnit.ImageCoID, RealtyUn it.TownCode, RealtyUnit.RegionCode, Realt |
> | 240175 | web2_u1 | localhost | blgdatabase_dbo | Query | 2280 | Copying to tmp table on disk | SELECT RealtyUnit.HRealtyID, RealtyUnit.ImageCoID, RealtyUn it.TownCode, RealtyUnit.RegionCode, Realt |
> | 240179 | web2_u1 | localhost | blgdatabase_dbo | Query | 2069 | Copying to tmp table on disk | SELECT RealtyUnit.HRealtyID, RealtyUnit.ImageCoID, RealtyUn it.TownCode, RealtyUnit.RegionCode, Realt |
> | 240184 | web2_u1 | localhost | blgdatabase_dbo | Query | 1889 | Copying to tmp table on disk | SELECT RealtyUnit.HRealtyID, RealtyUnit.ImageCoID, RealtyUn it.TownCode, RealtyUnit.RegionCode, Realt |
> | 240214 | web2_u1 | localhost | blgdatabase_dbo | Query | 1678 | Copying to tmp table on disk | SELECT RealtyUnit.HRealtyID, RealtyUnit.ImageCoID, RealtyUn it.TownCode, RealtyUnit.RegionCode, Realt |
> | 240236 | web2_u1 | localhost | blgdatabase_dbo | Query | 1505 | Copying to tmp table on disk | SELECT RealtyUnit.HRealtyID, RealtyUnit.ImageCoID, RealtyUn it.TownCode, RealtyUnit.RegionCode, Realt |
> | 240303 | web2_u1 | localhost | blgdatabase_dbo | Query | 1302 | Copying to tmp table on disk | SELECT RealtyUnit.HRealtyID, RealtyUnit.ImageCoID, RealtyUn it.TownCode, RealtyUnit.RegionCode, Realt |
> | 240321 | web2_u1 | localhost | blgdatabase_dbo | Query | 1118 | Copying to tmp table on disk | SELECT RealtyUnit.HRealtyID, RealtyUnit.ImageCoID, RealtyUn it.TownCode, RealtyUnit.RegionCode, Realt |
> | 240356 | web2_u1 | localhost | blgdatabase_dbo | Query | 914 | Copying to tmp table on disk | SELECT RealtyUnit.HRealtyID, RealtyUnit.ImageCoID, RealtyUn it.TownCode, RealtyUnit.RegionCode, Realt |
> | 240376 | web2_u1 | localhost | blgdatabase_dbo | Query | 736 | Copying to tmp table on disk | SELECT RealtyUnit.HRealtyID, RealtyUnit.ImageCoID, RealtyUn it.TownCode, RealtyUnit.RegionCode, Realt |
> | 240378 | web2_u1 | localhost | blgdatabase_dbo | Query | 551 | Copying to tmp table on disk | SELECT RealtyUnit.HRealtyID, RealtyUnit.ImageCoID, RealtyUn it.TownCode, RealtyUnit.RegionCode, Realt |
> | 240383 | web2_u1 | localhost | blgdatabase_dbo | Query | 374 | Copying to tmp table on disk | SELECT RealtyUnit.HRealtyID, RealtyUnit.ImageCoID, RealtyUn it.TownCode, RealtyUnit.RegionCode, Realt |
> | 240397 | web2_u1 | localhost | blgdatabase_dbo | Query | 192 | Copying to tmp table on disk | SELECT RealtyUnit.HRealtyID, RealtyUnit.ImageCoID, RealtyUn it.TownCode, RealtyUnit.RegionCode, Realt |
> | 240411 | web2_u1 | localhost | blgdatabase_dbo | Query | 61 | converting HEAP to MyISAM | SELECT RealtyUnit.HRealtyID, RealtyUnit.ImageCoID, RealtyUn it.TownCode, RealtyUnit.RegionCode, Realt |
> | 240413 | web2_u1 | localhost | blgdatabase_dbo | Query | 52 | converting HEAP to MyISAM | SELECT RealtyUnit.HRealtyID, RealtyUnit.ImageCoID, RealtyUn it.TownCode, RealtyUnit.RegionCode, Realt |
> | 240418 | web2_u1 | localhost | blgdatabase_dbo | Query | 19 | converting HEAP to MyISAM | SELECT RealtyUnit.HRealtyID, RealtyUnit.ImageCoID, RealtyUn it.TownCode, RealtyUnit.RegionCode, Realt |
> | 240426 | root | localhost | | Query | 0 | | show processlist |
> +--------+---------+-----------+-----------------+---------+--------+----------- -------------------+------------------------------------------------------------ ------------------------------------------+
>
>

From: Po4emu4ka - 05/07/2007 - 10:02:23
Subject:выборка занимает слишком много времени
-----------------
Копать не на уровне mysql, а на уровне диска. Проверить сколько файлов в tmp-дирректории. Пишет же, что файловая система загружена слишком, вот диск и не справляется.

Используй sar для снятия статистики и df


[Это сообщение - спам!]

Последние сообщения из форума

Уважаемые посетители форума MySQL.RU!
Убедительная просьба, прежде чем задавать свой вопрос в этом форуме, обратите внимание на разделы:
- ответы на наиболее часто задаваемые вопросы - FAQ
- раздел документация
- раздел поиск по сообщениям форума и документации
Также, старайтесь наиболее подробно указывать свою ситуацию (версию операционной системы, версию MySQL, версию программного обеспечения, по которому возникает вопрос, текст возникающих ошибок, и др.)
Помните, чем конкретнее Вы опишете ситуацию, тем больше шансов получить реальную помощь.
 Имя:
 E-mail:
 Тема:
 Текст:
Код подтверждения отправки: Code
31786


РЕКЛАМА НА САЙТЕ
  Создание сайтов | |