Różnica wydajności między MySQL i PostgreSQL dla tego samego schematu / zapytań [zamknięte]

Jestem początkującym DBA i mam doświadczenie w Microsoft SQL Server, ale chcę przejść do FLOSS.

Zakładam firmę i tworzymy aplikację (PHP) z zapleczem Postgres, a także przeprowadziliśmy testy w porównaniu z MySQL. Zauważamy, że MySQL jest dwa razy szybszy niż PostgreSQL.

Zrobiłem namacalny test wydajności:

• Te same kolumny w tabeli z równoważnymi typami danych kolumn.
• Ta sama liczba rzędów.
• Te same indeksy w obu (zawiera klucz podstawowy).
• Obciążenie procesora jest bezczynne, a maszyna Postgres jest znacznie lepsza.
• I to samo zapytanie (oczywiście).

Co ja robię źle?

PS: Czytałem wiele „poradników” na temat dostrajania wydajności silników baz danych.
PS (2): Używamy InnoDB (jeden plik na tabelę) w bazie danych MySQL.

Cześć Mat!

Zrobiłem trzy najczęściej wybierane (i najtrudniejsze) zapytania.

Pytanie o dysk, z pewnością to nie to samo; W Postgres jest to dysk SSD (prawie trzykrotnie najszybszy).

Dane w pamięci podręcznej MySQL:

+------------------------------+----------------------+
| Variable_name                | Value                |
+------------------------------+----------------------+
| binlog_cache_size            | 32768                |
| have_query_cache             | YES                  |
| key_cache_age_threshold      | 300                  |
| key_cache_block_size         | 1024                 |
| key_cache_division_limit     | 100                  |
| max_binlog_cache_size        | 18446744073709547520 |
| query_cache_limit            | 1048576              |
| query_cache_min_res_unit     | 4096                 |
| query_cache_size             | 16777216             |
| query_cache_type             | ON                   |
| query_cache_wlock_invalidate | OFF                  |
| table_definition_cache       | 256                  |
| table_open_cache             | 64                   |
| thread_cache_size            | 8                    |
+------------------------------+----------------------+

Nie wiem, jak to zobaczyć w PostgreSQL.

Z góry dziękuję.

MySQL i PostgreSQL różnią się pod względem wydajności. Tabele InnoDB i PostgreSQL są zoptymalizowane pod kątem różnego rodzaju zapytań. Zrozumienie tych różnic jest ważne dla zrozumienia, w jaki sposób uzyskać dobrą wydajność w obu przypadkach.

Jako przykład przyjrzyjmy się najbardziej oczywistej różnicy.

Struktura tabeli PostgreSQL a MySQL / InnoDB i co to oznacza dla wydajności

Ogólnie przy złożonych obciążeniach PostgreSQL będzie szybszy, ale przy prostych przeglądach klucza podstawowego MySQL z InnoDB będzie szybszy.

Tabele PostgreSQL to tabele sterty. Nie ma opcji zbudowania tabeli, która nie jest tabelą sterty. clusterPolecenie po prostu przepisuje sterty uporządkowaną według określonego indeksu. Indeksy zapewniają lokalizacje sterty dla krotek o różnych wartościach. Indeksy nie mogą być przeglądane w fizycznym porządku, tylko w kolejności logicznej, dzięki czemu mają dużo losowych dyskowych I / O podczas czytania tabeli sekwencyjnie zwykle oznaczają wiele sekwencyjnych I / O dyskowych, ponieważ można odczytać tabelę w fizycznej kolejności. Sekwencyjne operacje we / wy dysku używają pamięci podręcznej odczytu z wyprzedzeniem i niektórych innych optymalizacji na poziomie systemu operacyjnego.

Oznacza to, że jeśli potrzebujesz znacznej części rekordów lub kilku stron, zwykle szybciej jest po prostu czytać strony z dysku. Z drugiej strony wyszukiwanie klucza podstawowego dla tabeli wymaga trafienia do indeksu, wyszukiwania lokalizacji w pliku, a następnie trafienia do tabeli stosu i pobrania rekordu. Oznacza to pewną liczbę losowych operacji we / wy dysku.

InnoDB stosuje inne podejście. W przypadku InnoDB tabela jest indeksem b-drzewa z rzeczywistymi danymi w ładunku indeksu. Oznacza to, że wyszukiwanie klucza podstawowego może już pobierać dane ze strony liści, a zatem wymagane jest mniej przypadkowe we / wy dysku. Jednocześnie skanowanie indeksów wymaga przejścia dwóch indeksów zamiast jednego, co oznacza, że ​​użycie dowolnego indeksu innego niż klucz główny jest wolniejsze, a skanowanie sekwencyjne jest jeszcze wolniejsze.

Uzyskiwanie diagnoz w PostgreSQL

Myślę, że chcesz użyć czegoś takiego:

 EXPLAIN (analyse, buffers, verbose)
 [query];

To da ci plan zapytań, wstępne szacunki, faktyczne czasy, użycie bufora i wiele więcej.