Co może powodować dziwne limity czasu między zapytaniami między PHP a MySQL?

Jestem starszym programistą w aplikacji Software-as-a-Service, z której korzysta wielu różnych klientów. Nasze oprogramowanie działa na klastrze serwerów aplikacji Apache / PHP, wspieranych przez backend MySQL. W jednym konkretnym przypadku oprogramowania kod PHP, który wysyła zapytanie do listy nazw kategorii, wygasa, gdy klient ma więcej niż 29 kategorii . Wiem, że to nie ma sensu; nie ma nic specjalnego w liczbie 30, która by to zepsuła, a inni klienci mają o wiele więcej niż 30 kategorii, jednak problem jest w 100% odtwarzalny, gdy ta jedna instalacja ma 30 lub więcej kategorii i znika, gdy jest mniej niż 30 kategorii.

Tabela, o której mowa, to:

CREATE TABLE IF NOT EXISTS `categories` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `name` varchar(64) NOT NULL,
  `title` varchar(128) NOT NULL,
  `parent` int(10) unsigned NOT NULL,
  `keywords` varchar(255) NOT NULL,
  `description` text NOT NULL,
  `status` enum('Active','Inactive','_Deleted','_New') NOT NULL default 'Active',
  `style` enum('_Unknown') default NULL COMMENT 'Autoenum;',
  `order` smallint(5) unsigned NOT NULL,
  `created_at` datetime NOT NULL,
  `modified_at` datetime default NULL,
  PRIMARY KEY  (`id`),
  KEY `name` (`name`),
  KEY `parent` (`parent`),
  KEY `created_at` (`created_at`),
  KEY `modified_at` (`modified_at`),
  KEY `status` (`status`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COMMENT='R2' AUTO_INCREMENT=33 ;

Kod, o którym mowa, rekurencyjnie sprawdza tabelę w celu pobrania wszystkich kategorii. Wydaje a

SELECT * FROM `categories` WHERE `parent`=0 ORDER BY `order`,`name`

A następnie powtarza to zapytanie dla każdego zwróconego wiersza, ale za WHERE parent=$category_idkażdym razem. (Jestem pewien, że tę procedurę można ulepszyć, ale to prawdopodobnie kolejne pytanie)

O ile mi wiadomo, następujące zapytanie jest zawieszone na zawsze:

SELECT * FROM `categories` WHERE `parent`=22 ORDER BY `order`,`name`

Mogę wykonać tę kwerendę w kliencie mysql na serwerze idealnie dobrze, i mogę bez problemu wykonać ją w PHPMyAdmin.

Zauważ, że problem nie dotyczy konkretnego zapytania . Jeśli DELETE FROM categories WHERE id=22wtedy inne zapytanie podobne do powyższego zostanie zawieszone. Ponadto powyższe zapytanie zwraca zero wierszy, gdy uruchamiam go ręcznie .

Podejrzewałem, że tabela może być uszkodzony, a ja próbowałem REPAIR TABLEi OPTIMIZE TABLEtylko dolny z nich zgłaszało problemy ani rozwiązać ten problem. Upuściłem stół i odtworzyłem, ale problem powrócił. Jest to dokładnie taka sama struktura tabeli i kod PHP, z którego korzystają inni klienci, bez żadnych problemów dla kogokolwiek innego, w tym klientów, którzy mają więcej niż 30 kategorii.

Kod PHP nie powtarza się na zawsze. (To nie jest nieskończona pętla)

Serwer MySQL działa pod Linuksem CentOS z mysqld Ver 5.0.92-community na PC-linux-gnu na i686 (MySQL Community Edition (GPL))

Obciążenie serwera MySQL jest niskie: średnie obciążenie: 0,58, 0,75, 0,73, procesory: 4,6% us, 2,9% sy, 0,0% ni, 92,2% id, 0,0% wa, 0,0% hi, 0,3% si, 0,0% st. Używana jest nieznaczna zamiana (448k)

Jak mogę rozwiązać ten problem? Wszelkie sugestie dotyczące tego, co może się dziać?

UPDATE: I TRUNCEed tabeli i dodaje 30 wierszy danych fikcyjnych:

INSERT INTO `categories` (`id`, `name`, `title`, `parent`, `keywords`, `description`, `status`, `style`, `order`, `created_at`, `modified_at`) VALUES
(1, 'New Category', '', 0, '', '', 'Inactive', NULL, 1, '2011-10-25 12:06:30', '2011-10-25 12:06:34'),
(2, 'New Category', '', 0, '', '', 'Inactive', NULL, 2, '2011-10-25 12:06:39', '2011-10-25 12:06:40'),
(3, 'New Category', '', 0, '', '', 'Inactive', NULL, 3, '2011-10-25 12:06:41', '2011-10-25 12:06:42'),
(4, 'New Category', '', 0, '', '', 'Inactive', NULL, 4, '2011-10-25 12:06:46', '2011-10-25 12:06:47'),
(5, 'New Category', '', 0, '', '', 'Inactive', NULL, 5, '2011-10-25 12:06:49', NULL),
(6, 'New Category', '', 0, '', '', 'Inactive', NULL, 6, '2011-10-25 12:06:51', '2011-10-25 12:06:52'),
(7, 'New Category', '', 0, '', '', 'Inactive', NULL, 7, '2011-10-25 12:06:53', '2011-10-25 12:06:54'),
(8, 'New Category', '', 0, '', '', 'Inactive', NULL, 8, '2011-10-25 12:06:56', '2011-10-25 12:06:57'),
(9, 'New Category', '', 0, '', '', 'Inactive', NULL, 9, '2011-10-25 12:06:59', '2011-10-25 12:06:59'),
(10, 'New Category', '', 0, '', '', 'Inactive', NULL, 10, '2011-10-25 12:07:01', '2011-10-25 12:07:01'),
(11, 'New Category', '', 0, '', '', 'Inactive', NULL, 11, '2011-10-25 12:07:03', '2011-10-25 12:07:03'),
(12, 'New Category', '', 0, '', '', 'Inactive', NULL, 12, '2011-10-25 12:07:05', '2011-10-25 12:07:05'),
(13, 'New Category', '', 0, '', '', 'Inactive', NULL, 13, '2011-10-25 12:07:06', '2011-10-25 12:07:07'),
(14, 'New Category', '', 0, '', '', 'Inactive', NULL, 14, '2011-10-25 12:07:08', '2011-10-25 12:07:09'),
(15, 'New Category', '', 0, '', '', 'Inactive', NULL, 15, '2011-10-25 12:07:11', '2011-10-25 12:07:12'),
(16, 'New Category', '', 0, '', '', 'Inactive', NULL, 16, '2011-10-25 12:07:13', '2011-10-25 12:07:14'),
(17, 'New Category', '', 0, '', '', 'Inactive', NULL, 17, '2011-10-25 12:09:41', '2011-10-25 12:09:42'),
(18, 'New Category', '', 0, '', '', 'Inactive', NULL, 18, '2011-10-25 12:09:47', NULL),
(19, 'New Category', '', 0, '', '', 'Inactive', NULL, 19, '2011-10-25 12:09:48', NULL),
(20, 'New Category', '', 0, '', '', 'Inactive', NULL, 20, '2011-10-25 12:09:48', NULL),
(21, 'New Category', '', 0, '', '', 'Inactive', NULL, 21, '2011-10-25 12:09:49', NULL),
(22, 'New Category', '', 0, '', '', 'Inactive', NULL, 22, '2011-10-25 12:09:50', NULL),
(23, 'New Category', '', 0, '', '', 'Inactive', NULL, 23, '2011-10-25 12:09:51', NULL),
(24, 'New Category', '', 0, '', '', 'Inactive', NULL, 24, '2011-10-25 12:09:51', NULL),
(25, 'New Category', '', 0, '', '', 'Inactive', NULL, 25, '2011-10-25 12:09:52', NULL),
(26, 'New Category', '', 0, '', '', 'Inactive', NULL, 26, '2011-10-25 12:09:53', NULL),
(27, 'New Category', '', 0, '', '', 'Inactive', NULL, 27, '2011-10-25 12:09:54', NULL),
(28, 'New Category', '', 0, '', '', 'Inactive', NULL, 28, '2011-10-25 12:09:55', NULL),
(29, 'New Category', '', 0, '', '', 'Inactive', NULL, 29, '2011-10-25 12:09:56', NULL),
(30, 'New Category', '', 0, '', '', 'Inactive', NULL, 30, '2011-10-25 12:09:57', NULL);

W ogóle nie ma rodziców , wszystkie kategorie są na najwyższym poziomie. problem wciąż istnieje. Następujące zapytanie, wykonane przez PHP, kończy się niepowodzeniem:

SELECT * FROM `categories` WHERE `parent`=22 ORDER BY `order`,`name`

Oto EXPLAIN:

mysql> EXPLAIN SELECT * FROM `categories` WHERE `parent`=22 ORDER BY `order`,`name`;
+----+-------------+------------+------+---------------+--------+---------+-------+------+-----------------------------+
| id | select_type | table      | type | possible_keys | key    | key_len | ref   | rows | Extra                       |
+----+-------------+------------+------+---------------+--------+---------+-------+------+-----------------------------+
|  1 | SIMPLE      | categories | ref  | parent        | parent | 4       | const |    1 | Using where; Using filesort | 
+----+-------------+------------+------+---------------+--------+---------+-------+------+-----------------------------+
1 row in set (0.00 sec)

AKTUALIZACJA # 2: Próbowałem teraz wszystkich następujących czynności:

1. Skopiowałem tę tabelę i dane do innej witryny z tym samym oprogramowaniem. Problem nie dotarł do tabeli. Wydaje się, że ogranicza się do tej jednej bazy danych.
2. Zmieniłem indeks zgodnie z sugestią gbn. Problem pozostał.
3. Upuściłem tabelę i odtworzyłem jako InnoDBtabelę i wstawiłem te same 30 wierszy testowych powyżej. Problem pozostał.

Podejrzewam, że to musi być coś z tą bazą danych ...

AKTUALIZACJA # 3: Całkowicie usunąłem bazę danych i odtworzyłem ją pod nową nazwą, importując jej dane. Problem pozostaje.

Przekonałem się, że rzeczywiste polecenie PHP, które się zawiesza, jest wywołaniem mysql_query(). Oświadczenia po tym nigdy nie zostaną wykonane.

Podczas gdy to połączenie się zawiesza, MySQL wyświetla wątek jako uśpiony!

mysql> show full processlist;
+-------+------------------+-----------------------------+----------------------+---------+------+-------+-----------------------+
| Id    | User             | Host                        | db                   | Command | Time | State | Info                  |
+-------+------------------+-----------------------------+----------------------+---------+------+-------+-----------------------+
|  5560 | root             | localhost                   | problem_db           | Query   |    0 | NULL  | show full processlist |  
                          ----- many rows which have no relevancy; only rows from this customer's app are shown ------
| 16341 | shared_db        | oak01.sitepalette.com:53237 | shared_db            | Sleep   |  308 |       | NULL                  | 
| 16342 | problem_db       | oak01.sitepalette.com:60716 | problem_db           | Sleep   |  307 |       | NULL                  | 
| 16344 | shared_db        | oak01.sitepalette.com:53241 | shared_db            | Sleep   |  308 |       | NULL                  | 
| 16346 | problem_db       | oak01.sitepalette.com:60720 | problem_db           | Sleep   |  308 |       | NULL                  |  
+-------+------------------+-----------------------------+----------------------+---------+------+-------+-----------------------+

AKTUALIZACJA # 4: Zawęziłem ją do kombinacji dwóch tabel, categoriestabeli wyszczególnionej powyżej i media_imagestabeli z 556 wierszami. Jeśli media_imagestabela zawiera mniej niż 556 wierszy lub categoriestabela zawiera mniej niż 30 wierszy, problem zniknie. To jest jak jakiś limit MySQL, który tu uderzam ...

AKTUALIZACJA # 5: Właśnie próbowałem przenieść bazę danych na inny serwer MySQL i problem zniknął ... Więc jest to związane z moim produkcyjnym serwerem bazy danych ...

AKTUALIZACJA # 6: Oto odpowiedni kod PHP, który zawiesza się za każdym razem:

    public function find($type,$conditions='',$order='',$limit='')
    {
            if($this->_link == self::AUTO_LINK)
                    $this->_link = DFStdLib::database_connect();

            if(is_resource($this->_link))
            {
                    $q = "SELECT ".($type==_COUNT?'COUNT(*)':'*')." FROM `{$this->_table}`";
                    if($conditions)
                    {
                            $q .= " WHERE $conditions";
                    }
                    if($order)
                    {
                            $q .= " ORDER BY $order";
                    }
                    if($limit)
                    {
                            $q .= " LIMIT $limit";
                    }

                    switch($type)
                    {
                            case _ALL:
                                    DFSkel::log(DFSkel::LOG_DEBUG,"mysql_query($q,$this->_link);");
                                    $res = @mysql_query($q,$this->_link);
                                    DFSkel::log(DFSkel::LOG_DEBUG,"res = $res");

Ten kod jest produkowany i działa dobrze we wszystkich innych instalacjach. Tylko przy jednej instalacji wisi $res = @mysql_query($q,$this->_link);. Wiem, ponieważ widzę mysql_queryw dzienniku debugowania, a nie w res =, a kiedy ja straceproces PHP, jest zawieszonyread(

AKTUALIZACJA # cokolwiek-to-to-ja-nienawidzę-to- i (# ^ & -issue! To zaczęło się dziać z dwoma moimi klientami. Właśnie wystrzeliłem tcpdumpi wygląda na to, że odpowiedź z MySQL nigdy nie jest wysyłana całkowicie. Wydaje się, że strumień TCP zawiesza się, zanim można wysłać pełną odpowiedź MySQL. (Nadal jednak badam)

AKTUALIZACJA # Ja-poszedłem-całkowicie szalony-ale-to-działa-teraz-trochę: Ok, to nie ma sensu, ale znalazłem rozwiązanie. Jeśli przypiszę drugi adres IP do eth2interfejsu serwera MySQL i użyję jednego adresu IP dla ruchu NFS i drugiego adresu IP dla MySQL, problem zniknie. To tak, jakbym jakoś ... przeciążał adres IP, jeśli oba ruchy NFS + MySQL idą do tego adresu IP. Ale to nie ma sensu, ponieważ nie można „przeciążić” adresu IP. Nasycenie interfejsu jest pewne, ale jest to ten sam interfejs.

Masz pojęcie, co tu się do cholery dzieje? To jest prawdopodobnie pytanie unix.SE lub ServerFault w tym momencie ... (Przynajmniej działa teraz ...)

AKTUALIZACJA # dlaczego-och-dlaczego: ten problem nadal występuje. Zaczęło się dziać nawet przy użyciu dwóch różnych adresów IP. Mogę nadal tworzyć nowe prywatne adresy IP, ale najwyraźniej coś jest nie tak.

Aby uzyskać ogólne profilowanie tego, co dokładnie dzieje się w planie zapytań, możesz wypróbować PROFILOWANIE

Pomoże to zasadniczo ustalić, gdzie jest zawieszenie.

Oczywiście działa to tylko wtedy, gdy skompilowałeś MySQL enable-profiling.

Pomysły (nie jestem pewien, czy dotyczy MyISAM, współpracuję z InnoDB)

Zmień indeks „nadrzędny”, tak aby znajdował się w 3 kolumnach: nadrzędny, porządek, nazwa. Jest to zgodne z GDZIE .. ZAMÓWIENIE PRZEZ

Usuń SELECT *. Weź tylko potrzebne kolumny. Dodaj dowolne inne kolumny do indeksu „rodzic”

Umożliwi to optymalizatorowi korzystanie tylko z indeksu, ponieważ obejmuje on teraz. W tej chwili musisz przeczytać całą tabelę, ponieważ indeksy nie są przydatne dla tego zapytania

Chciałbym sprawdzić kilka rzeczy na Production DB Server

• Kwestia 1: Upewnij się, że wolumin danych, w którym zamontowano / var / lib / mysql, nie ma złych bloków. Może to wymagać przestoju w celu wykonania fsck (kontrola systemu plików)
• Kwestia 2: Upewnij się, że tabela nie jest obciążona DML (INSERT / UPDATE / DELETE) lub SELECTs
  • W MyISAM dla każdej instrukcji DML wydawana jest pełna blokada tabeli
  • W ramach InnoDB generowanych jest wiele danych MVCC do izolacji transakcji oraz niewielkie zagrożenie blokowaniem indeksu klastrów
• Kwestia 3: Upewnij się, że PHP poprawnie wydaje mysql_close (), a aplikacja nie polega na Apache w celu zamknięcia połączenia DB. W przeciwnym razie może wystąpić warunek wyścigu, gdy PHP może próbować użyć zasobów połączenia DB, które zostały skutecznie zamknięte przez MySQL.
• Kwestia 4: Upewnij się, że system operacyjny serwera DB nie ma zapasów TIME_WAIT na liście połączeń Netstat dla połączeń, które zostały zamknięte w oczach PHP i MySQL, ale system operacyjny nadal się utrzymuje. Możesz to zobaczyć za pomocąnetstat | grep -i mysql | grep TIME_WAIT
• Kwestia 5: Upewnij się, że nie używasz mysql_pconnect . Nadal jest otwarty raport o błędzie dotyczący trwałych połączeń, które nie zamykają się prawidłowo . Nienawidzę wyobrażać sobie, jak próbuję uzyskać dostęp do tych połączeń.
• Kwestia 6: Upewnij się, że przepustowość ruchu DB przez usługi równoważenia obciążenia, przełączniki, zapory ogniowe i serwery DNS są identyczne dla produkcyjnego serwera DB i innych serwerów zewnętrznych. Osobiście nienawidzę używać nazw DNS w kolumnie hosta mysql.user i mysql.db. Zwykle klienci usuwają je i zastępują twardymi adresami IP. Dodam również skip-host-cachei skip-name-resolvepomijam korzystanie z DNS przez mysqld. Mógłbym zatem odnieść się do odpowiedzi @ marcioAlmada jako punktu kontrolnego do przejrzenia.

Jeśli uważasz, że żadna z tych kontroli nie jest przydatna, skomentuj jak najszybciej i daj mi znać, abym mógł usunąć swoją odpowiedź.

a) Cześć Josh. Powiedziałeś, że zapytania działają normalnie w twoim kliencie MySQL i w PHPMyAdmin? spotyka się tylko aplikacja PHP?
b) @marcioAlmada tak, zgadza się

Powiedziałbym, że trafiłeś w schrödinbug . Możesz spróbować die()po zapytaniu lub przed nim i poszukać koduif statements co zdarza się bardzo rzadko. Trudno powiedzieć, co się zawiesza, gdy nie mamy Twojego kodu.

EDYCJA: Powiedziałbym, że może to być ta linia

$this->_link = DFStdLib::database_connect();

który (zakładam) tworzy połączenie przy każdym wywołaniu funkcji. To może być problem. Jakie są twoje max_connections w my.cnf?

Jestem prawie przekonany, że jest to kwestia PHP, a nie MySQL, ale dlaczego to działa, kiedy zmieniam serwery MySQL?

Niektóre próby:

• Zapory ogniowe? Czy firewall blokuje twoją aplikację i uniemożliwia jej wysyłanie jakichkolwiek żądań do twojego produkcyjnego serwera bazy danych lub odwrotnie?

• Czy używasz nazwy domeny w konfiguracji połączenia lub adresu IP? Użycie nazwy domeny może nieco spowolnić interakcję z bazą danych, a to w połączeniu z krótkim maksymalnym czasem wykonania skryptu w PHP spowoduje wieczne spotkanie

Ta ostatnia sugestia wydaje się wyjaśniać dziwne zachowanie zmiennych podczas przełączania serwerów baz danych.Jedna może odpowiadać znacznie szybciej niż druga, a ponieważ dla każdego znalezionego rekordu będziesz mieć dodatkowe zapytanie, ta hipoteza wyjaśniłaby, dlaczego aplikacja opóźnia się tylko z pewną ilością zapytanych wyników (> 30).

Przynajmniej doszliśmy do pierwotnego wniosku. Zdecydowanie problem nie dotyczy samego serwera MySQL. Przejrzałem dokumentację i wydaje się, że nie ma limitów funkcji, które pasowałyby do konkretnej sytuacji, a także nigdy nie miałem problemu z tabelami rekurencyjnymi i określoną liczbą wpisów.

Mam nadzieję, że to pomaga.

Czy próbowałeś zaktualizować komendę mysql_query (), aby była natywnym sterownikiem PHP5? mysqli :: query ()? Nie jestem pewien, czy to nic nie da, ale może być warte zastrzyku.