Warning: Cannot use a scalar value as an array in /home/admin/public_html/forum/include/fm.class.php on line 757

Warning: Invalid argument supplied for foreach() in /home/admin/public_html/forum/include/fm.class.php on line 770

Warning: Invalid argument supplied for foreach() in /home/admin/public_html/forum/topic.php on line 737
Форумы портала PHP.SU :: Хостер требует оптимизовать запросы к БД

 PHP.SU

Программирование на PHP, MySQL и другие веб-технологии
PHP.SU Портал     На главную страницу форума Главная     Помощь Помощь     Поиск Поиск     Поиск Яндекс Поиск Яндекс     Вакансии  Пользователи Пользователи


 Страниц (1): [1]   

> Описание: База даных хостера потоянно блокируется
Айратиус
Отправлено: 29 Июля, 2013 - 08:58:27
Post Id



Частый гость


Покинул форум
Сообщений всего: 192
Дата рег-ции: Нояб. 2012  
Откуда: Россия


Помог: 0 раз(а)




День добрый! Проблема в следующем. Арендую хостинг у Макхоста. С недавних пор появилась проблема. База данных часто недоступна. Тех. поддержка хостинга объясняет это тем, что у меня слишком много запросов к базе данных. Я не силен в этих понятиях, могу лишь привести цитаты.
Цитата:
Вот такая ошибка

Цитата:
ERROR 1203 (42000): User 555_film already has more than 'max_user_connections' active connections

Цитата:
| 12586015 | 555_film | 100.0.1.10:3869 | 555_film | Query | 1317 | User sleep | SELECT COUNT(*) FROM video WHERE cat='' or sleep(7)#' | 0 | 0 | 0 |
| 12586980 | 555_film | 100.0.1.10:8733 | 555_film | Query | 1287 | Waiting for table level lock | SELECT COUNT(*) FROM video WHERE cat='' or sleep(7)#' | 0 | 0 | 0 |
| 12587171 | 555_film | 100.0.1.10:9597 | 555_film | Query | 1280 | Waiting for table level lock | SELECT COUNT(*) FROM video WHERE cat='' or sleep(7)#' | 0 | 0 | 0 |
| 12587819 | 555_film | 100.0.1.10:12648 | 555_film | Query | 1260 | Waiting for table level lock | SELECT COUNT(*) FROM video WHERE cat='' or sleep(7)#' | 0 | 0 | 0 |
| 12588010 | 555_film | 100.0.1.10:13757 | 555_film | Query | 1253 | Waiting for table level lock | SELECT COUNT(*) FROM video WHERE cat='' or sleep(7)#' | 0 | 0 | 0 |
| 12588206 | 555_film | 100.0.1.10:14782 | 555_film | Query | 1246 | Waiting for table level lock | SELECT COUNT(*) FROM video WHERE cat='' or sleep(7)#' | 0 | 0 | 0 |
| 12590100 | 555_film | 100.0.1.10:23951 | 555_film | Query | 1187 | Waiting for table level lock | SELECT COUNT(*) FROM video WHERE cat='' or sleep(7)#' | 0 | 0 | 0 |
| 12591205 | 555_film | 100.0.1.10:29236 | 555_film | Query | 1314 | Waiting for table level lock | UPDATE video SET view='4510' WHERE id='239' | 0 | 0 | 0 |
| 12591215 | 555_film | 100.0.1.10:29294 | 555_film | Query | 1313 | Waiting for table level lock | SELECT * FROM video WHERE id='282' | 0 | 0 | 0 |
| 12591269 | 555_film | 100.0.1.10:29567 | 555_film | Query | 1312 | Waiting for table level lock | SELECT * FROM video WHERE id='748' | 0 | 0 | 0 |
| 12591271 | 555_film | 100.0.1.10:29577 | 555_film | Query | 1312 | Waiting for table level lock | SELECT COUNT(*) FROM video | 0 | 0 | 0 |
| 12591394 | 555_film | 100.0.1.10:30219 | 555_film | Query | 1308 | Waiting for table level lock | SELECT * FROM video WHERE id='282' | 0 | 0 | 0 |
| 12591426 | 555_film | 100.0.1.10:30414 | 555_film | Query | 1307 | Waiting for table level lock | SELECT * FROM video WHERE id='325' | 0 | 0 | 0 |
| 12591485 | 555_film | 100.0.1.10:30707 | 555_film | Query | 1305 | Waiting for table level lock | UPDATE video SET view='4510' WHERE id='239' | 0 | 0 | 0 |
| 12591496 | 555_film | 100.0.1.10:30789 | 555_film | Query | 1305 | Waiting for table level lock | SELECT COUNT(*) FROM video WHERE cat='21' | 0 | 0 | 0 |
| 12591615 | 555_film | 100.0.1.10:31388 | 555_film | Query | 1300 | Waiting for table level lock | SELECT * FROM video WHERE id='23' | 0 | 0 | 0 |
| 12591628 | 555_film | 100.0.1.10:31449 | 555_film | Query | 1300 | Waiting for table level lock | SELECT COUNT(*) FROM video WHERE cat='sleep(7)#' | 0 | 0 | 0 |
| 12591692 | 555_film | 100.0.1.10:31806 | 555_film | Query | 1297 | Waiting for table level lock | SELECT * FROM video WHERE id='282' | 0 | 0 | 0 |
| 12591820 | 555_film | 100.0.1.10:32573 | 555_film | Query | 1292 | Waiting for table level lock | SELECT * FROM video WHERE id='23' | 0 | 0 | 0 |
| 12591867 | 555_film | 100.0.1.10:32814 | 555_film | Query | 1290 | Waiting for table level lock | SELECT * FROM video WHERE id='727' | 0 | 0 | 0 |
| 12591897 | 555_film | 100.0.1.10:32934 | 555_film | Query | 1289 | Waiting for table level lock | SELECT * FROM video WHERE id='208' | 0 | 0 | 0 |
| 12591924 | 555_film | 100.0.1.10:33029 | 555_film | Query | 1288 | Waiting for table level lock | SELECT * FROM video WHERE id='592' | 0 | 0 | 0 |
| 12591951 | 555_film | 100.0.1.10:33191 | 555_film | Query | 1287 | Waiting for table level lock | SELECT * FROM video WHERE id='240' | 0 | 0 | 0 |
| 12591972 | 555_film | 100.0.1.10:33285 | 555_film | Query | 1287 | Waiting for table level lock | SELECT COUNT(*) FROM video WHERE cat='sleep(7)#' | 0 | 0 | 0 |
| 12592040 | 555_film | 100.0.1.10:33604 | 555_film | Query | 1285 | Waiting for table level lock | SELECT * FROM video WHERE id='708' | 0 | 0 | 0 |
| 12592050 | 555_film | 100.0.1.10:33660 | 555_film | Query | 1284 | Waiting for table level lock | SELECT COUNT(*) FROM video WHERE cat='sleep(7)#' | 0 | 0 | 0 |
| 12592123 | 555_film | 100.0.1.10:33965 | 555_film | Query | 1282 | Waiting for table level lock | SELECT * FROM video WHERE id='282' | 0 | 0 | 0 |
| 12592159 | 555_film | 100.0.1.10:34089 | 555_film | Query | 1282 | Waiting for table level lock | SELECT COUNT(*) FROM video WHERE cat='19' | 0 | 0 | 0 |
| 12592160 | 555_film | 100.0.1.10:34093 | 555_film | Query | 1282 | Waiting for table level lock | SELECT COUNT(*) FROM video | 0 | 0 | 0 |
| 12592262 | 555_film | 100.0.1.10:34620 | 555_film | Query | 1279 | Waiting for table level lock | SELECT COUNT(*) FROM video WHERE cat='21' | 0 | 0 | 0 |
| 12592345 | 555_film | 100.0.1.10:35020 | 555_film | Query | 1276 | Waiting for table level lock | SELECT * FROM video WHERE id='112' | 0 | 0 | 0 |
| 12592376 | 555_film | 100.0.1.10:35121 | 555_film | Query | 1275 | Waiting for table level lock | SELECT COUNT(*) FROM video WHERE cat='2' | 0 | 0 | 0 |
| 12592453 | 555_film | 100.0.1.10:35612 | 555_film | Query | 1272 | Waiting for table level lock | SELECT COUNT(*) FROM video WHERE cat='7' | 0 | 0 | 0 |
| 12592491 | 555_film | 100.0.1.10:35803 | 555_film | Query | 1271 | Waiting for table level lock | SELECT * FROM video WHERE id='418' | 0 | 0 | 0 |
| 12592496 | 555_film | 100.0.1.10:35841 | 555_film | Query | 1271 | Waiting for table level lock | SELECT * FROM video WHERE id='112' | 0 | 0 | 0 |
| 12592497 | 555_film | 100.0.1.10:35846 | 555_film | Query | 1271 | Waiting for table level lock | SELECT COUNT(*) FROM video WHERE cat='26' | 0 | 0 | 0 |
| 12592562 | 555_film | 100.0.1.10:36161 | 555_film | Query | 1269 | Waiting for table level lock | SELECT COUNT(*) FROM video WHERE cat='26' | 0 | 0 | 0 |
| 12592569 | 555_film | 100.0.1.10:36183 | 555_film | Query | 1269 | Waiting for table level lock | SELECT * FROM video WHERE id='564' | 0 | 0 | 0 |
| 12592611 | 555_film | 100.0.1.10:36533 | 555_film | Query | 1267 | Waiting for table level lock | SELECT * FROM video WHERE id='394' | 0 | 0 | 0 |
| 12592623 | 555_film | 100.0.1.10:36599 | 555_film | Query | 1267 | Waiting for table level lock | SELECT * FROM video WHERE id='421' | 0 | 0 | 0 |
| 12592675 | 555_film | 100.0.1.10:36738 | 555_film | Query | 1266 | Waiting for table level lock | SELECT * FROM video WHERE id='569' | 0 | 0 | 0 |
| 12592678 | 555_film | 100.0.1.10:36743 | 555_film | Query | 1266 | Waiting for table level lock | SELECT * FROM video WHERE id='421' | 0 | 0 | 0 |
| 12592720 | 555_film | 100.0.1.10:36976 | 555_film | Query | 1264 | Waiting for table level lock | SELECT * FROM video WHERE id='101' | 0 | 0 | 0 |
| 12592745 | 555_film | 100.0.1.10:37108 | 555_film | Query | 1263 | Waiting for table level lock | SELECT * FROM video WHERE id='23' | 0 | 0 | 0 |
| 12592751 | 555_film | 100.0.1.10:37128 | 555_film | Query | 1263 | Waiting for table level lock | SELECT COUNT(*) FROM video WHERE cat='sleep(7)#' | 0 | 0 | 0 |
| 12592786 | 555_film | 100.0.1.10:37324 | 555_film | Query | 1262 | Waiting for table level lock | SELECT COUNT(*) FROM video | 0 | 0 | 0 |
| 12592850 | 555_film | 100.0.1.10:37674 | 555_film | Query | 1259 | Waiting for table level lock | SELECT COUNT(*) FROM video WHERE cat='2' | 0 | 0 | 0 |
| 12592876 | 555_film | 100.0.1.10:37807 | 555_film | Query | 1259 | Waiting for table level lock | SELECT * FROM video WHERE cat='23' ORDER BY id DESC LIMIT 12, 6 | 0 | 0 | 0 |
| 12592907 | 555_film | 100.0.1.10:37926 | 555_film | Query | 1258 | Waiting for table level lock | SELECT * FROM video WHERE id='34' | 0 | 0 | 0 |
| 12592919 | 555_film | 100.0.1.10:37998 | 555_film | Query | 1257 | Waiting for table level lock | SELECT COUNT(*) FROM video | 0 | 0 | 0 |
| 12592927 | 555_film | 100.0.1.10:38041 | 555_film | Query | 1257 | Waiting for table level lock | SELECT COUNT(*) FROM video WHERE cat='19' | 0 | 0 | 0 |
| 12592992 | 555_film | 100.0.1.10:38407 | 555_film | Query | 1255 | Waiting for table level lock | SELECT COUNT(*) FROM video WHERE cat='sleep(7)#' | 0 | 0 | 0 |
| 12593007 | 555_film | 100.0.1.10:38495 | 555_film | Query | 1254 | Waiting for table level lock | SELECT * FROM video WHERE id='241' | 0 | 0 | 0 |
| 12593082 | 555_film | 100.0.1.10:38895 | 555_film | Query | 1252 | Waiting for table level lock | SELECT COUNT(*) FROM video WHERE cat='2' | 0 | 0 | 0 |
| 12593090 | 555_film | 100.0.1.10:38978 | 555_film | Query | 1251 | Waiting for table level lock | SELECT * FROM video WHERE id='282' | 0 | 0 | 0 |
| 12593128 | 555_film | 100.0.1.10:39160 | 555_film | Query | 1250 | Waiting for table level lock | SELECT * FROM video WHERE id='664' | 0 | 0 | 0 |
| 12593140 | 555_film | 100.0.1.10:39233 | 555_film | Query | 1250 | Waiting for table level lock | SELECT COUNT(*) FROM video WHERE cat='2' | 0 | 0 | 0 |
| 12593180 | 555_film | 100.0.1.10:39418 | 555_film | Query | 1248 | Waiting for table level lock | SELECT * FROM video WHERE id='574' | 0 | 0 | 0 |
| 12593181 | 555_film | 100.0.1.10:39419 | 555_film | Query | 1248 | Waiting for table level lock | SELECT * FROM video WHERE id='421' | 0 | 0 | 0 |
| 12593183 | 555_film | 100.0.1.10:39423 | 555_film | Query | 1248 | Waiting for table level lock | SELECT COUNT(*) FROM video WHERE cat='sleep(7)#' | 0 | 0 | 0 |
| 12593195 | 555_film | 100.0.1.10:39449 | 555_film | Query | 1248 | Waiting for table level lock | SELECT * FROM video WHERE id='195' | 0 | 0 | 0 |
| 12593201 | 555_film | 100.0.1.10:39476 | 555_film | Query | 1248 | Waiting for table level lock | SELECT * FROM video WHERE id='422' | 0 | 0 | 0 |
| 12593203 | 555_film | 100.0.1.10:39483 | 555_film | Query | 1248 | Waiting for table level lock | SELECT COUNT(*) FROM video WHERE cat='29' | 0 | 0 | 0 |
| 12593235 | 555_film | 100.0.1.10:39700 | 555_film | Query | 1247 | Waiting for table level lock | SELECT * FROM video WHERE id='564' | 0 | 0 | 0 |
| 12593253 | 555_film | 100.0.1.10:39786 | 555_film | Query | 1246 | Waiting for table level lock | SELECT COUNT(*) FROM video WHERE cat='29' | 0 | 0 | 0 |
| 12593400 | 555_film | 100.0.1.10:40279 | 555_film | Query | 1243 | Waiting for table level lock | SELECT * FROM video WHERE cat='23' ORDER BY id DESC LIMIT 24, 6 | 0 | 0 | 0 |
| 12593440 | 555_film | 100.0.1.10:40467 | 555_film | Query | 1241 | Waiting for table level lock | SELECT * FROM video WHERE id='463' | 0 | 0 | 0 |
| 12593466 | 555_film | 100.0.1.10:40559 | 555_film | Query | 1241 | Waiting for table level lock | SELECT COUNT(*) FROM video WHERE cat='17' | 0 | 0 | 0 |
| 12593473 | 555_film | 100.0.1.10:40594 | 555_film | Query | 1241 | Waiting for table level lock | SELECT COUNT(*) FROM video | 0 | 0 | 0 |
| 12593534 | 555_film | 100.0.1.10:40880 | 555_film | Query | 1238 | Waiting for table level lock | SELECT * FROM video WHERE id='574' | 0 | 0 | 0 |
| 12593555 | 555_film | 100.0.1.10:40967 | 555_film | Query | 1238 | Waiting for table level lock | SELECT * FROM video WHERE id='101' | 0 | 0 | 0 |
| 12593601 | 555_film | 100.0.1.10:41241 | 555_film | Query | 1236 | Waiting for table level lock | SELECT * FROM video WHERE id='247' | 0 | 0 | 0 |
| 12593621 | 555_film | 100.0.1.10:41354 | 555_film | Query | 1236 | Waiting for table level lock | SELECT COUNT(*) FROM video | 0 | 0 | 0 |
| 12593650 | 555_film | 100.0.1.10:41497 | 555_film | Query | 1235 | Waiting for table level lock | SELECT * FROM video WHERE id='564' | 0 | 0 | 0 |
| 12593651 | 555_film | 100.0.1.10:41502 | 555_film | Query | 1234 | Waiting for table level lock | SELECT COUNT(*) FROM video | 0 | 0 | 0 |
| 12593657 | 555_film | 100.0.1.10:41546 | 555_film | Query | 1234 | Waiting for table level lock | SELECT COUNT(*) FROM video WHERE cat='24' | 0 | 0 | 0 |
| 12593658 | 555_film | 100.0.1.10:41548 | 555_film | Query | 1234 | Waiting for table level lock | SELECT * FROM video WHERE id='422' | 0 | 0 | 0 |
| 12593694 | 555_film | 100.0.1.10:41723 | 555_film | Query | 1233 | Waiting for table level lock | SELECT COUNT(*) FROM video | 0 | 0 | 0 |
| 12593707 | 555_film | 100.0.1.10:41831 | 555_film | Query | 1232 | Waiting for table level lock | SELECT * FROM video WHERE id='195' | 0 | 0 | 0 |
| 12593737 | 555_film | 100.0.1.10:41956 | 555_film | Query | 1231 | Waiting for table level lock | SELECT * FROM video WHERE id='416' | 0 | 0 | 0 |
| 12593811 | 555_film | 100.0.1.10:42373 | 555_film | Query | 1229 | Waiting for table level lock | SELECT COUNT(*) FROM video WHERE cat='26' | 0 | 0 | 0 |
| 12593817 | 555_film | 100.0.1.10:42392 | 555_film | Query | 1229 | Waiting for table level lock | SELECT COUNT(*) FROM video WHERE cat='2' | 0 | 0 | 0 |
| 12593857 | 555_film | 100.0.1.10:42597 | 555_film | Query | 1227 | Waiting for table level lock | SELECT COUNT(*) FROM video WHERE cat='29' | 0 | 0 | 0 |
| 12593869 | 555_film | 100.0.1.10:42642 | 555_film | Query | 1227 | Waiting for table level lock | SELECT * FROM video WHERE id='416' | 0 | 0 | 0 |
| 12593909 | 555_film | 100.0.1.10:42835 | 555_film | Query | 1225 | Waiting for table level lock | SELECT * FROM video WHERE id='200' | 0 | 0 | 0 |
| 12593940 | 555_film | 100.0.1.10:42999 | 555_film | Query | 1224 | Waiting for table level lock | SELECT * FROM video WHERE id='390' | 0 | 0 | 0 |
| 12593969 | 555_film | 100.0.1.10:43154 | 555_film | Query | 1223 | Waiting for table level lock | SELECT COUNT(*) FROM video WHERE cat='7' | 0 | 0 | 0 |
| 12593994 | 555_film | 100.0.1.10:43280 | 555_film | Query | 1223 | Waiting for table level lock | SELECT * FROM video WHERE id='569' | 0 | 0 | 0 |
| 12594025 | 555_film | 100.0.1.10:43436 | 555_film | Query | 1222 | Waiting for table level lock | SELECT * FROM video WHERE id='52' | 0 | 0 | 0 |
| 12594047 | 555_film | 100.0.1.10:43572 | 555_film | Query | 1221 | Waiting for table level lock | SELECT COUNT(*) FROM video WHERE cat='26' | 0 | 0 | 0 |
| 12594072 | 555_film | 100.0.1.10:43731 | 555_film | Query | 1220 | Waiting for table level lock | SELECT * FROM video WHERE id='569' | 0 | 0 | 0 |
| 12594149 | 555_film | 100.0.1.10:44186 | 555_film | Query | 1217 | Waiting for table level lock | SELECT * FROM video WHERE id='569' | 0 | 0 | 0 |
| 12594231 | 555_film | 100.0.1.10:44656 | 555_film | Query | 1214 | Waiting for table level lock | SELECT * FROM video WHERE id='664' | 0 | 0 | 0 |
| 12594244 | 555_film | 100.0.1.10:44727 | 555_film | Query | 1213 | Waiting for table level lock | SELECT COUNT(*) FROM video WHERE cat='2' | 0 | 0 | 0 |
| 12594283 | 555_film | 100.0.1.10:44971 | 555_film | Query | 1212 | Waiting for table level lock | SELECT COUNT(*) FROM video WHERE cat='26' | 0 | 0 | 0 |
| 12594305 | 555_film | 100.0.1.10:45066 | 555_film | Query | 1211 | Waiting for table level lock | SELECT * FROM video WHERE id='569' | 0 | 0 | 0 |
| 12594318 | 555_film | 100.0.1.10:45119 | 555_film | Query | 1211 | Waiting for table level lock | SELECT * FROM video WHERE id='12' | 0 | 0 | 0 |
| 12594352 | 555_film | 100.0.1.10:45307 | 555_film | Query | 1210 | Waiting for table level lock | SELECT * FROM video WHERE id='12' | 0 | 0 | 0 |
| 12594360 | 555_film | 100.0.1.10:45345 | 555_film | Query | 1209 | Waiting for table level lock | SELECT * FROM video WHERE id='569' | 0 | 0 | 0 |
| 12594365 | 555_film | 100.0.1.10:45364 | 555_film | Query | 1209 | Waiting for table level lock | SELECT * FROM video WHERE id='408'

Так же прислали ссылку на хелп http://mchost[dot]ru/help/33/#h104 . В итоге, понял что нужно как то оптимизировать запросы к базе данных. Посоветуйте хоть какое-нибудь решение проблемы. Выделенный сервер брать не хочу.

(Отредактировано автором: 29 Июля, 2013 - 09:25:07)

 
 Top
Мелкий Супермодератор
Отправлено: 29 Июля, 2013 - 09:44:17
Post Id



Активный участник


Покинул форум
Сообщений всего: 11926
Дата рег-ции: Июль 2009  
Откуда: Россия, Санкт-Петербург


Помог: 618 раз(а)




Айратиус пишет:
User 555_film already has more than 'max_user_connections' active connections

Не запросов, а соединений.

Айратиус пишет:
SELECT COUNT(*) FROM video WHERE cat='' or sleep(7)#

Если это не специально сделанная фича вашего сайта - у вас SQL-инъекция. И кто-то её эксплуатирует в таком вот безобидном виде.
В процитированном запросе - спать 7 секунд на каждую строку в таблице, что очень вряд ли запланированное поведение.


-----
PostgreSQL DBA
 
 Top
Айратиус
Отправлено: 29 Июля, 2013 - 09:47:46
Post Id



Частый гость


Покинул форум
Сообщений всего: 192
Дата рег-ции: Нояб. 2012  
Откуда: Россия


Помог: 0 раз(а)




а что посоветуете?

(Отредактировано автором: 29 Июля, 2013 - 09:48:37)

 
 Top
Мелкий Супермодератор
Отправлено: 29 Июля, 2013 - 09:50:16
Post Id



Активный участник


Покинул форум
Сообщений всего: 11926
Дата рег-ции: Июль 2009  
Откуда: Россия, Санкт-Петербург


Помог: 618 раз(а)




А что тут советовать? Устранить инъекцию.
Для этого - найти в коде, откуда создаётся этот запрос, и добавить корректное экранирование.


-----
PostgreSQL DBA
 
 Top
Страниц (1): [1]
Сейчас эту тему просматривают: 0 (гостей: 0, зарегистрированных: 0)
« Администрирование БД »


Все гости форума могут просматривать этот раздел.
Только зарегистрированные пользователи могут создавать новые темы в этом разделе.
Только зарегистрированные пользователи могут отвечать на сообщения в этом разделе.
 



Powered by PHP  Powered By MySQL  Powered by Nginx  Valid CSS  RSS

 
Powered by ExBB FM 1.0 RC1. InvisionExBB