SLEEP connection vyžírají procesy na hostingu a stránka končí na 503

kachna96

Ahoj,

včera jsem narazil na docela kritickej problém na našich webovkách, potažmo hostingu. Najednou z ničeho nic začal Process Count stoupat až do limitu, kdy php stránky vracely 503, statické soubory jako obrázky ale normálně fungovaly.
Nedělali jsme žádný změny v kódu, nastavení, traffic stejnej jako posledních 5 měsíců, prostě nic čeho se chytnout.
Řešil jsem to se supportem x hodin, jejich první řešení bylo zabít všechny webserver procesy, ale to zabralo jenom na chvíli, protože process count začal okamžitě zase stoupat a byli jsme zase na začátku.
Nakonec z nich vylezlo toto:

There was lots of SLEEP connections to the website databases which means that the SQL queries were not closing properly in the web application and this it hit the SQL connections and these open connections consume resources also. Please check the website scripts and close the SQL connections properly once the task is completed.

184156	teamaret_dbadmin	localhost:54620	teamaret_prod	Sleep	14911
198935	teamaret_dbadmin	localhost:39714	teamaret_prod	Sleep	12076
216969	teamaret_dbadmin	localhost:55942	teamaret_prod	Sleep	8578

Jde to nějak v Nette ošetřit? Myslel jsem, že si to Nette pohlídá samo. Připojuju se přes

/** @var Nette\Database\Context */
    private $database;

    public function __construct(Nette\Database\Context $database)
    {
        $this->database = $database;
    }

a pak přepoužívám $this->database variable
Takhle nějak to vypadadlo co se týče hw metrik

Nette 2.4, PHP 7.2.20

ipub/mobile-detect               dev-master cfdc3a9 Extension for detecting mobile devices, managing mobile view types, redirect to mobile version for Nette Framework
jaybizzle/crawler-detect         v1.2.78            CrawlerDetect is a PHP class for detecting bots/crawlers/spiders via the user agent
jenssegers/agent                 v2.6.3             Desktop/mobile user agent parser with support for Laravel, based on Mobiledetect
latte/latte                      v2.5.1             ☕ Latte: the intuitive and fast template engine for those who want the most secure PHP sites. Introduces context-sensitive escaping.
mobiledetect/mobiledetectlib     2.8.33             Mobile_Detect is a lightweight PHP class for detecting mobile devices. It uses the User-Agent string combined with specific HTTP headers to detect the mobile environment.
nette/application                v2.4.13            � Nette Application: a full-stack component-based MVC kernel for PHP that helps you write powerful and modern web applications. Write less, have cleaner code and your work will bring ...
nette/bootstrap                  v2.4.6             � Nette Bootstrap: the simple way to configure and bootstrap your Nette application.
nette/caching                    v2.5.8             ⏱ Nette Caching: library with easy-to-use API and many cache backends.
nette/component-model            v2.4.0             ⚛ Nette Component Model
nette/database                   v2.4.8             � Nette Database: layer with a familiar PDO-like API but much more powerful. Building queries, advanced joins, drivers for MySQL, PostgreSQL, SQLite, MS SQL Server and Oracle.
nette/di                         v2.4.15            � Nette Dependency Injection Container: Flexible, compiled and full-featured DIC with perfectly usable autowiring and support for all new PHP 7.1 features.
nette/finder                     v2.4.2             � Nette Finder: find files and directories with an intuitive API.
nette/forms                      v2.4.9             ?? Nette Forms: generating, validating and processing secure forms in PHP. Handy API, fully customizable, server & client side validation and mature design.
nette/http                       v2.4.10            � Nette Http: abstraction for HTTP request, response and session. Provides careful data sanitization and utility for URL and cookies manipulation.
nette/mail                       v2.4.6             � Nette Mail: handy email creation and transfer library for PHP with both text and MIME-compliant support.
nette/neon                       v3.0.0             � Nette NEON: encodes and decodes NEON file format.
nette/php-generator              v3.2.1             � Nette PHP Generator: generates neat PHP code for you. Supports new PHP 7.3 features.
nette/reflection                 v2.4.2             Nette Reflection: docblock annotations parser and common reflection classes
nette/robot-loader               v3.1.0             � Nette RobotLoader: high performance and comfortable autoloader that will search and autoload classes within your application.
nette/safe-stream                v2.4.0             Nette SafeStream: atomic and safe manipulation with files via native PHP functions.
nette/security                   v2.4.4             � Nette Security: provides authentication, authorization and a role-based access control management via ACL (Access Control List)
nette/tester                     v2.1.0             Nette Tester: enjoyable unit testing in PHP with code coverage reporter. ����
nette/utils                      v2.5.3             � Nette Utils: lightweight utilities for string & array manipulation, image handling, safe JSON en coding/decoding, validation, slug or strong password generating etc.
tracy/tracy                      v2.6.0             � Tracy: the addictive tool to ease debugging PHP code for cool developers. Friendly design, logging, profiler, advanced features like debugging AJAX calls or CLI support. You will lo...
vojtech-dobes/nette-ajax-history dev-master b5946b6 Adds History API support for Nette Framework.
webchemistry/forms-multiplier    v3.0.5             Multiplier for nette forms

CZechBoY

Nemáš tam nějaký long running skripty? Klasický zpracování php requestu spočívá ve spawnutí procesu, samotného běhu aplikace a ukončení php procesu (čímž, v nejhorším případě, dojde k odpojení od db).

kachna96

Ne, máme tam jeden formulář schovaný za administrací, ale ten taky může trvat tam maximálně 5 minut a v logu ho nevidím
Naprostá většina requestů jsou jednoduché selecty do databáze buď při načítání stránky nebo přes ajax, občas nějáký update z administrace.

kachna96

Toto je process list, který na mašině běžel

root      6723  0.0  0.0 112708   968 pts/3    S+   00:08   0:00          \_ grep --color=auto teamaret
teamaret  5819  0.0  0.0 224232 25792 ?        S    00:07   0:00  \_ cpaneld - serving 84.242.85.237
teamaret  5820  0.0  0.0 219424 25416 ?        S    00:07   0:00  \_ cpaneld - serving 84.242.85.237
teamaret  5858  0.0  0.0 219424 25424 ?        S    00:07   0:00  \_ cpaneld - serving 84.242.85.237
teamaret  5826  0.3  0.0 474404 20548 ?        S    00:07   0:00  |   \_ lsphp
teamaret 21265  0.0  0.0 475852  9104 ?        Ss   Jul23   0:00 lsphp
teamaret 23095  0.0  0.0 475720  9100 ?        Ss   Jul23   0:00 lsphp
teamaret 30583  0.0  0.0 476512  9636 ?        Ss   Jul23   0:00 lsphp
teamaret 32138  0.0  0.0 477304 10424 ?        Ss   Jul23   0:00 lsphp
teamaret  3111  0.0  0.0 476512  9896 ?        Ss   Jul23   0:00 lsphp
teamaret  6394  0.0  0.0 475984  9372 ?        Ss   Jul23   0:00 lsphp
teamaret 11240  0.0  0.0 477040 10160 ?        Ss   Jul23   0:00 lsphp
teamaret 18288  0.0  0.0 477436 10688 ?        Ss   Jul23   0:00 lsphp
teamaret 32212  0.0  0.0 698960 16928 ?        Ssl  Jul23   0:01 lsphp:/home/teamaret/public_html/index.php
teamaret 19338  0.0  0.0 477436 10688 ?        Ss   Jul23   0:00 lsphp
teamaret 23882  0.0  0.0 477172 10424 ?        Ss   Jul23   0:00 lsphp
teamaret 24104  0.0  0.0 475984  9104 ?        Ss   Jul23   0:00 lsphp
teamaret 32614  0.0  0.0 475984  9108 ?        Ss   Jul23   0:00 lsphp
teamaret  6525  0.0  0.0 476380  9672 ?        Ss   Jul23   0:00 lsphp
teamaret  6674  0.0  0.0 477568 10992 ?        Ss   Jul23   0:00 lsphp
teamaret  9114  0.0  0.0 476512  9676 ?        Ss   Jul23   0:00 lsphp
teamaret  9175  0.0  0.0 475984  9148 ?        Ss   Jul23   0:00 lsphp
teamaret  9967  0.0  0.0 477436 10732 ?        Ss   Jul23   0:00 lsphp
teamaret 11899  0.0  0.0 477436 10728 ?        Ss   Jul23   0:00 lsphp
teamaret 14673  0.0  0.0 477172 10468 ?        Ss   Jul23   0:00 lsphp
teamaret 21941  0.0  0.0 476776 10204 ?        Ss   Jul23   0:00 lsphp
teamaret 25511  0.0  0.0 698960 16764 ?        Ssl  Jul23   0:01 lsphp:/home/teamaret/public_html/index.php
teamaret  4615  0.0  0.0 475852  9144 ?        Ss   Jul23   0:00 lsphp
teamaret  9056  0.0  0.0 477304 10468 ?        Ss   Jul23   0:00 lsphp
teamaret 10772  0.0  0.0 475852  9148 ?        Ss   Jul23   0:00 lsphp
teamaret 11449  0.0  0.0 477040 10468 ?        Ss   Jul23   0:00 lsphp
teamaret 17351  0.0  0.0 475720  9144 ?        Ss   Jul23   0:00 lsphp
teamaret 30954  0.0  0.0 477568 10720 ?        Ss   Jul23   0:00 lsphp
teamaret   790  0.0  0.0 476116  9408 ?        Ss   Jul23   0:00 lsphp
teamaret 11529  0.0  0.0 698960 13984 ?        Ssl  Jul23   0:00 lsphp:/home/teamaret/public_html/index.php
teamaret 22404  0.0  0.0 476776  9936 ?        Ss   Jul23   0:00 lsphp
teamaret 26910  0.0  0.0 698768 11956 ?        Ssl  Jul23   0:00 lsphp:/home/teamaret/public_html/index.php
teamaret 28320  0.0  0.0 476908 10200 ?        Ss   Jul23   0:00 lsphp
teamaret 28331  0.0  0.0 477172 10464 ?        Ss   Jul23   0:00 lsphp
teamaret 28655  0.0  0.0 477436 10728 ?        Ss   Jul23   0:00 lsphp
teamaret 31495  0.0  0.0 701272 18668 ?        Ssl  Jul23   0:00 lsphp:/home/teamaret/public_html/index.php
teamaret   897  0.0  0.0 475720  9148 ?        Ss   Jul23   0:00 lsphp
teamaret  8199  0.0  0.0 477436 10728 ?        Ss   Jul23   0:00 lsphp
teamaret  8633  0.0  0.0 474544  7772 ?        Ss   Jul23   0:00 lsphp
teamaret 19663  0.0  0.0 701272 18628 ?        Ssl  Jul23   0:00 lsphp:/home/teamaret/public_html/index.php
teamaret 21756  0.0  0.0 477436 10700 ?        Ss   Jul23   0:00 lsphp
teamaret  2873  0.0  0.0 477304 10740 ?        Ss   Jul23   0:00 lsphp
teamaret 25970  0.0  0.0 477304 10468 ?        Ss   Jul23   0:00 lsphp
teamaret  5751  0.0  0.0 476644  9940 ?        Ss   Jul23   0:00 lsphp
teamaret 10563  0.0  0.0 698960 16968 ?        Ssl  Jul23   0:00 lsphp:/home/teamaret/public_html/index.php
teamaret 11892  0.0  0.0 477304 10728 ?        Ss   Jul23   0:00 lsphp
teamaret 20887  0.0  0.0 477304 10464 ?        Ss   Jul23   0:00 lsphp
teamaret 21227  0.0  0.0 476116  9408 ?        Ss   Jul23   0:00 lsphp
teamaret 26220  0.0  0.0 476380  9672 ?        Ss   Jul23   0:00 lsphp
teamaret 31156  0.0  0.0 477436 10704 ?        Ss   Jul23   0:00 lsphp
teamaret 31712  0.0  0.0 701272 18928 ?        Ssl  Jul23   0:00 lsphp:/home/teamaret/public_html/index.php
teamaret 31985  0.0  0.0 698960 14384 ?        Ssl  Jul23   0:00 lsphp:/home/teamaret/public_html/index.php
teamaret  7195  0.0  0.0 462652 15744 ?        Ssl  Jul23   0:00 lsphp
teamaret  7199  0.0  0.0 315188 15804 ?        Ss   Jul23   0:00 lsphp
teamaret 15529  0.0  0.0 315188 15728 ?        Ss   Jul23   0:00 lsphp
teamaret 21664  0.0  0.0 477172 10428 ?        Ss   Jul23   0:00 lsphp
teamaret 23845  0.0  0.0 698768 11920 ?        Ssl  Jul23   0:00 lsphp:/home/teamaret/public_html/index.php
teamaret 24306  0.0  0.0 476380  9640 ?        Ss   Jul23   0:00 lsphp
teamaret 19792  0.0  0.0 475720  8888 ?        Ss   Jul23   0:00 lsphp
teamaret 19941  0.0  0.0 698960 15984 ?        Ssl  Jul23   0:00 lsphp:/home/teamaret/public_html/index.php
teamaret 25130  0.0  0.0 315188 15724 ?        Ss   Jul23   0:00 lsphp
teamaret 28332  0.0  0.0 477436 10724 ?        Ss   Jul23   0:00 lsphp
teamaret 28337  0.0  0.0 462652 15716 ?        Ssl  Jul23   0:00 lsphp

janpecha

Ještě je možné, že tam máš někde SQL injection a někdo ti tam přes GET/POST parametry posílá do databáze SLEEP().