|
MySQL.RU - Webboard
 Вернуться
 выборка занимает слишком много времени (runner) 04/07/2007 - 14:29:00
 Re: выборка занимает слишком много времени (Po4emu4ka) 05/07/2007 - 10:02:23
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 |
+--------+---------+-----------+-----------------+---------+--------+----------- -------------------+------------------------------------------------------------ ------------------------------------------+
[Это сообщение - спам!]
Последние сообщения из форума
Уважаемые посетители форума MySQL.RU!
Убедительная просьба, прежде чем задавать свой вопрос в этом форуме, обратите внимание на разделы:
- ответы на наиболее часто задаваемые вопросы - FAQ
- раздел документация
- раздел поиск по сообщениям форума и документации
Также, старайтесь наиболее подробно указывать свою ситуацию (версию операционной системы, версию MySQL,
версию программного обеспечения, по которому возникает вопрос, текст возникающих ошибок, и др.)
Помните, чем конкретнее Вы опишете ситуацию, тем больше шансов получить реальную помощь.
31786
|
|
