W aplikacjach internetowych czasami musimy wykorzystać w jednym serwisie wiele baz danych, nieraz nawet wiele różnych baz danych. Mój wpis będzie prezentował proste przykłady użycia wielu baz z wykorzystaniem frameworka Symfony. Dla przykładu użyje bazy danych PostgreSQL oraz ORM Propela 1.3.

W przykładzie wykorzystam 2 tabele: “users” z bazy “db1″ (załóżmy, że to główna bazy serwisu z użytkownikami) i “requests” z bazy “db2″ (baza w której zapisywane są statystyki z żądań do aplikacji). Modele wygenerowane będą dla bazy PostgreSQL. W kolejnym etapie zaprezentuje w jaki sposób można załadować przykładowe dane pochodzące z data/fixtures.

Definiowanie baz w pliku databases.yml

Edytujemy config/databases.yml

all:
  propel:
    class:        sfPropelDatabase
    param:
      classname:  PropelPDO
      dsn:        pgsql:dbname=db1;host=panic user=db1 password=db1
      hostspec:   pgsql
      port:       5432
      encoding:   utf8

  stat:
    class:        sfPropelDatabase
    param:
      classname:  PropelPDO
      dsn:        pgsql:dbname=db2;host=panic user=db2 password=db2
      hostspec:   pgsql
      port:       5432
      encoding:   utf8

Pierwszym krokiem jest zdefiniowanie połączeń do baz danych. Główną nazwę połączenia będzie “propel” a drugą “stat”

Ustawienie pliku propel.ini

propel.database        = pgsql
propel.database.driver = pgsql
propel.database.url    = pgsql:dbname=db1;host=panic user=db1 password=db1

W pliku propel.ini ustawiamy połączenie z główną bazę danych. “propel.database” odpowiada za generowanie kodu SQL dla danych baz, np.: MySQL, PostgreSQL, Oracle. Jeśli chcemy generować SQL dla wybranego typu bazy, to musimy zmienić na odpowiednią wartość. Można to uzyskać z linii koment ustawiajac –phing-arg

Przykład dla MySQLa:

./symfony propel:build-all --no-confirmation --connection=stat --phing-arg="Dpropel.database=mysql"

Przykład dla Oracle:

./symfony propel:build-all --no-confirmation --connection=stat --phing-arg="Dpropel.database=oracle"

Definiowanie tabel w config/schema.yml

Jak w przykładzie z optymalizacją Propela dobrym nawykiem jest rozdzielenie różnego typu struktur na osobne pliki schema.yml. Główną definicja tabel można zapisać w pliku schema.yml a dodatkową strukturę bazy w osobnym pliku, np w pliku stat_schema.yml.

Plik config/schema.yml

propel:
  users:
    id:              { type: integer, required: true, primaryKey: true, autoincrement: true }
    created_at:      { type: timestamp }
    name:            { type: varchar, size: 255 }
    surname:         { type: varchar, size: 255 }

Plik config/stat_schema.yml

stat:
  _attributes:       { package: lib.stat_model }
  requests:
    id:              { type: integer, required: true, primaryKey: true, autoincrement: true }
    created_at:      { type: timestamp }
    module:          { type: varchar, size: 255 }
    action:          { type: varchar, size: 255 }

W pliku stat_schema.yml, użyłem package: lib.stat_model – czyli miejsce, w którym zostaną wygenerowane modele wykorzystujące bazę “db2″, lib.stat_model = lib/stat_model.

Po zdefiniowaniu struktur i baz przechodzimy do generowania modeli i stworzenia struktur tabel w bazach danych.

Generowanie modeli i struktur baz danych dla “db1″ i “db2″

./symfony propel:build-all --no-confirmation

Modele dla baz danych zostały wygenerowane. Struktura tabel została stworzona tylko w pierwszej bazie “db1″ - ponieważ to połączenie jest domyślne. Aby stworzyć strukturę tabel w bazie “db2″ musimy wymusić połączenie z bazą “db2″ (czyli połączenie o nazwie “stat”).

./symfony propel:build-all --no-confirmation –connection=stat

Przykład wykorzystania modeli

Przykłady:

$obj1 = UsersPeer::retrieveByPK(1);

$obj2 = RequestsPeer::retrieveByPK(1);

Wykorzystując model nie musimy podawać połączenia, chyba że chcemy aby dany model był wykorzystany jawnie do “innej” bazy, “innej” mam tutaj na myśli bazy danych o takiej samej strukturze tabeli jaka jest zdefiniowana w modelu.

Ładowanie przykładowych danych (data/fixtures)

Podobnie jak w przypadku definicji struktur bazy danych rozdzielamy ładowanie danych. Dla bazy “db1” będzie to katalog data/fixtures a dla bazy “db2” data/stat_fixtures

Wywołanie tasków - dla domyślnej bazy danych “db1″ :

./symfony propel:data-load

Ładowanie danych do bazy “db2″ wymaga podania odpowiedniego połączenie i ustawienia katalogu z którego dane będą wczytywane.

./symfony propel:data-load --connection=stat --dir="data/stat_fixtures"

Można definiować n takich baz. Jeśli np. chcielibyśmy operować na takich samych tabelach w różnych bazach danych to możemy w modelach wymuszać konkretne połączenia, wszystko zależy od projektu i jego wymagań.

Aktualizując ostanio serwer miałem problem z upgrade na nowszą wersje PostgreSQLa więc opiszę swoje rozwiązanie na blogu.

Jeśli mamy już zainstalowaną wcześniejszą wersje PostgreSQLa to robimy backup.

pg_dumpall > postgres-backup.dump

Następnie wrzucamy do /etc/portage/package.keywords wpisy w celu odblokowania wersji niestabilnych.

echo "dev-db/postgresql-base ~amd64" >> /etc/portage/package.keywords
echo "dev-db/postgresql-server ~amd64" >> /etc/portage/package.keywords
echo "virtual/postgresql-server ~amd64" >> /etc/portage/package.keywords
echo "virtual/postgresql-base ~amd64" >> /etc/portage/package.keywords
echo "app-admin/eselect-postgresql ~amd64" >> /etc/portage/package.keywords

Kolejnym etapem jest usunięcie dev-db/postgresql dev-db/libpq

emerge --unmerge dev-db/postgresql dev-db/libpq

I możemy instalować nową wersję PostgreSQLa

emerge virtual/postgresql-base virtual/postgresql-server
emerge --config =dev-db/postgresql-server-8.3.5

Zostaje tylko wystartowanie bazy i dopisanie do skryptów startowych.

/etc/init.d/postgresql-8.3 start
rc-update add postgresql-8.3 default

Jeśli z jakiś przyczyn php wyrzuca, że nie chce działać to musimy przekompilować.

Szybkie serwisy, zoptymalizowane pod względem zużycia pamięci i ilości odwołań do bazy – czasami mogą być kluczowym elementem w powodzeniu naszego projektu. Chciałbym przedstawić jedną z takich możliwości, czyli użycie w Symfony widoków baz danych. Przykład można zastosować w bazach: PostgreSQL, MySQL, Oracle, itd. Najważniejszym elementem jest to, czy baza danych obsługuje widoki.

Dla przykładu użyję tabeli użytkownik i grupa.

schema.yml

propel:

  groups:
    id:              { type: integer, required: true, primaryKey: true, autoincrement: true }
    owner_id:        { type: integer, foreignTable: users, foreignReference: id, index: true }
    last_user_id:    { type: integer, foreignTable: users, foreignReference: id, index: true }
    name:            { type: varchar, size: 80, index: true }
    routing_name:    { type: varchar, size: 80, index: true, uniq: true }
    description:     { type: varchar, size: 400 }
    created_at:      { type: timestamp }
    user_count:      { type: integer, index: true }
    position_rank:   { type: integer }

  users:
    id:              { type: integer, required: true, primaryKey: true, autoincrement: true }
    login:           { type: varchar, size: 15, index: true, uniq: true }
    password:        { type: varchar, size: 255 }
    name:            { type: varchar, size: 255 }
    created_at:      { type: timestamp }
    updated_at:      { type: timestamp }
    last_login_at:   { type: timestamp }
    last_request_at: { type: timestamp }
    group_id:        { type: integer, foreignTable: groups, foreignReference: id, index: true }

Załóżmy, że mamy template w którym pokazujemy 20 grup według ilości użytkowników, w której wyświetlamy: nazwę grupy wraz z linkiem do profilu grupy, właściciela grupy, ostatniego dodanego użytkownika do tej grupy i ilość użytkowników w grupie. Stosując klasyczne wykorzystanie modelów Propela, dostaniemy modele grup z zależnymi 2 modelami użytkowników – właściciel i ostatnio dodany użytkownik. Od razu widać że większość danych jest zbędna, a co z tym idzie wykorzystanie pamięci będzie większe i czas zwracania danych z bazy będzie dłuższy.

Definiowanie widoku (views) w schema

Aby rozdzielić pliki w którym są definicje tabel i widoki, warto utworzyć osobny plik w którym zapiszemy naszą definicje widoku.

config/views_schema.yml

propel:
  groups_view:
    _attributes:              { skipSQL: true, readOnly: true }
    id:                       { type: integer }
    name:                     { type: varchar }
    routing_name:             { type: varchar }
    user_count:               { type: integer }
    owner_name:               { type: varchar }
    last_user:                { type: varchar }

Kolejnym krokiem jest przygotowanie SQL z definicją naszego vidoku. Dobrym rozwiązaniem jest by każdy widok umieszczać w osobnym pliku i dodawać do sqldb.map, w celu zbudowania widoku z automatu (nie musimy już ręcznie wywoływać SQL).

Tworzymy plik sql

data/sql/views/groups_view.sql

CREATE OR REPLACE VIEW groups_view AS
  SELECT groups.id,
         groups.name ,
         groups.routing_name,
         groups.user_count,
         owner.login AS owner_name,
         last.login AS last_user
    FROM groups
    JOIN users owner ON owner.id = groups.owner_id
    JOIN users last ON last.id = groups.ast_user_id;

Dodajemy widok do sqldb.map

Edytujemy plik sqldb.map i dodajemy nową pozycję

data/sql/sdldb.map

views/groups_view.sql=propel

Kolejnym krokiem jest zbudowanie bazy danych, dla przykładu użyje polecenia, które tworzy modele, dodaje SQL do bazy i wczytuje przykładowe dane.

./symfony propel:build-all-load

Po wygenerowanie powstał model o nazwie GroupsView, teraz tylko nam zostaje dodanie metody, która będzie zwracać 20 grup z największą ilością użytkowników.

Edytujemy GroupsViewPeer.class.php

Dodajemy metodę

public static function getTopGroups( $limit = 20 )
{
  $c = new Criteria();
  $c->addDescendingOrderByColumn( GroupsViewPeer::USER_COUNT );
  $c->setLimit( $limit );

  return GroupsViewPeer::doSelect( $c );
}

Metoda ta zwraca 20 obiektów, w których mamy tylko i wyłącznie pola, które będą nam potrzebne do wyświetlenia.

Często na rożnych forach spotyka się opinie, że Symfony jest wolnym i zasobożernym frameworkiem, jeśli nie znamy możliwości, zasad jego działania jak i sposobów optymalizacji to niestety ale taka teoria dla pewnych osób jest prawdziwa. Wykorzystując możliwości baz, takie jak plsql, vidoki, triggery i funkcje można osiągnąć rewelacyjne efekty i obsługa milionowych tabel na “słabym” sprzęcie jest czymś realnym.

Przeprowadziłem kilka testów szybkości odczytu z pliku i tabel typu: MEMORY, MyISAM.

Jak był przeprowadzany test:

Test przeprowadzałem na Ubuntu Dapper (apache2, php5.1.x, MySQL 5.0.22) na laptopie, więc wiele rzeczy mogło wpłynąć na na nieprawidłowość testów.

Test podzieliłem na kilka etapów:

• z włączonym cache zapytań
• z wyłączonym cache zapytań
• z pomiarem połączenia z bazą danych

Odnośnie ilości wywołań testu, to był wykonywany 1000 razy, liczone były pojedyńcze czasy testu, a następnie sumowane i liczona z tego średnia testu. Jako danych testowych użyłem wygenerowanego ciągu znaków o długości około 65 KB.

Wyniki:

Odczyt z pliku:

Do odczytu z pliku użyłem funkcji: file_get_contents()
Średni czas odczytu jaki uzyskałem to: 0.000357507705688 (szczegółowe informacje)

Odczyt z bazy danych:

Do odczytu z bazy użyłem funkcji mysql_* (połączenie, wybór bazy, wykonanie zapytanie i zwrócenie rekordu). Tabela zawierała 1 rekord z id i polem w którym był zapisany taki sam ciąg znaków jak w przypadku pliku. Dlaczego nie więcej? Chciałem wyeliminować czas jaki będzie potrzebny na przeliczenie jaki rekord ma zostać zwrócony.

Wyłączony zapis cache w MySQL i czas połączenia z bazą pominięty:

Tabela MyISAM - średni czas to: 0.00516110825539 (szczegółowe informacje)
Tabela MEMORY - średni czas to: 0.00368802952766 (szczegółowe informacje)

Widać, że odczyt z pliku ma przewagę nad odczytem z bazy danych, a czasy odczytu z tabel typu MyISAM i MEMORY są bardzo do siebie zbliżone.

Włączony zapis cache w MySQL i czas połączenia z bazą pominięty:

Tabela MyISAM - średni czas to: 0.00179107260704 (szczegółowe informacje)

Tabela MEMORY - średni czas to: 0.000719551563263 (szczegółowe informacje)

Z włączonym cache zapytań widać, że czasy są zbliżone do odczytu z pliku.

Włączony zapis cache w MySQL + czas połączenia z bazą danych:

Tabela MyISAM - średni czas to: 0.00202487516403 (szczegółowe informacje)
Tabela MEMORY - średni czas to: 0.00205611944199 (szczegół?owe informacje)

Kluczowym znaczeniem jest czas połączenia z bazą danych a porównanie tych 2 testów zapytań do bazy danych można powiedzieć, że są prawie identyczne.

Różnice w wydajności pomiędzy MyISAM i MEMORY?

Typ tabeli MEMORY będzie lepszy zawsze, kiedy operujemy na pewnym zestawie rekordþw i nie musimy jak w przypadku MyISAM odczytywac z dysku, który jak wiadomo ma fizyczne ograniczenia, które są nie do przeskoczenia.

Polecam zapoznanie się z art. http://dev.mysql.com/tech-resources/articles/mysql_5.0_psea2.html, w którym autor porównał różne typy tabel.

Na forum php.pl pojawił się post z pytaniem czy zastosowanie prepared statements wpływa na wydajność zapytań.

Troszeczkę teorii:

Jak to wygląda od strony ORACLE:

Zanim dane zostaną zwrócone z bazy danych, po odebraniu odpowiedniego kodu baza danych Oracle musi wykonać określone czynności:

• tworzenie kursora
• proces parsowania zapytania jeśli nie istnieje w obszarze wspólnym. Na proces ten również wchodzi kilka etapów:
  • analiza zapytania - sprawdzenie czy zapytanie składniowo jest poprawne
  • sprawdzenie odwołań do obiektów
  • sprawdzenie uprawnień
  • modyfikacja zapytania, w celu uzyskania zapytania równoważnemu danemu, ale wykonywanemu efektywniej
  • przygotowanie planów wykonania na podstawie statystyk, wskazówek
  • wybranie najlepszego planu wykonania (najmniejszy koszt wykonania)
  • zapisanie do obszaru wspólnego

  Wykorzystanie wcześniejszego zapytania jest możliwe tylko wtedy, kiedy:

  • tekst zapytania jest taki sam (wchodzą w to również komentarze i białe znaki),
  • polecenie musi odwoływać sie do tych samych obiektów w bazie danych,
  • zmienne wiązane muszą się tak samo nazywać i być tego samego typu.

• przygotowanie zapytania
• podwiązanie zmiennych
• wykonanie zapytania
• zwrócenie rekordów do użytkownika

Jeśli do bazy danych jest wysyłane takie samo zapytanie proces parowania nie jest wykonywany, przez co wydajność wzrasta bo pewna czść operacji jest pomijana.

Czy zawsze stosować takie rozwiązanie?

Są przypadki zapytań dla których najlepszy plan pierwszego wykonania dla pewnych parametrów jest nieoptymalny i zaleca się nie stosowanie zmiennych wiązanych.

MySQL:

Jeśli chodzi o bazę danych MySQL mechanizmy nie są aż tak bardzo zaawansowane ale pewne rzeczy są zaimplementowane.

Więcej na stronie: http://dev.mysql.com/tech-resources/articles/4.1/prepared-statements.html

Jak PDO wpływa na wydajność

PDO udostępnia funkcje do tzw bind’a (w literaturze polskiej spotkałem się z określeniem zmienna wiązana), w którym możemy przekazać wartość jaka ma być podstawiona pod zmienną, która w późniejszym etapie zostanie wysłana do bazy. Możemy wysłać zapytanie tylko jeden raz, następnie podstawiać wielokrotnie a następnie wykonywać. Dodatkowo PDO chroni przed atakami SQL Injection.

Post z forum.php.pl: http://forum.php.pl/mysqli-prepared-statements-t54025.html