Wpisy otagowane ‘MySQL’

Nowsze wpisy »

Analiza log-slow-queries (z życia wzięte…)

sobota, 26 Lipiec 2008

Jedna z tabel klienta wygląda tak:

CREATE TABLE `mix_stats` (
`id` int(11) NOT NULL auto_increment,
`id_produktu` decimal(20,0) NOT NULL default ’0′,
`data` date default NULL,
`count` decimal(1,0) NOT NULL default ’0′,
`session` varchar(255) NOT NULL default ”,
`ip` varchar(20) NOT NULL default ”,
`referer` varchar(255) NOT NULL default ”,
PRIMARY KEY  (`id`)
) TYPE=MyISAM

MySQL 3.23, w logach następujące wpisy:

# Time: 080726 10:40:51
# User@Host: xxx[xxx] @ localhost []
# Query_time: 48  Lock_time: 0  Rows_sent: 1  Rows_examined: 639552
SELECT count(id) FROM mix_stats WHERE id_produktu = ’84092324382e93e’ AND session = ”;

Jeżeli ktoś jest na tyle cierpliwy, by czekać 48 sekund na wynik, to musi mu bardzo zależeć…

Analizę bazy wykonywałem na MySQL 5.0.60, więc mam większe pole do popisu, np.:

mysql> SELECT count(id) FROM mix_stats WHERE id_produktu = '84092324382e93e' AND session = '';
+-----------+
| count(id) |
+-----------+
|         0 |
+-----------+
1 row in set, 65535 warnings (0.99 sec)

mysql> SHOW WARNINGS LIMIT 3;
+---------+------+-----------------------------------------------------+
| Level   | Code | Message                                             |
+---------+------+-----------------------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: '84092324382e93e' |
| Warning | 1292 | Truncated incorrect DOUBLE value: '84092324382e93e' |
| Warning | 1292 | Truncated incorrect DOUBLE value: '84092324382e93e' |
+---------+------+-----------------------------------------------------+
3 rows in set (0.00 sec)

Na początek polecam: DECIMAL. Choć pewnie skończy się przejściem do innego usługodawcy, gdzie mocniejsza maszyna bazodanowa przemieli to szybciej (ostrzeżenia i błędy i tak użytkowników nie interesują)…

Tagi:,
Zaszufladkowany do MySQL | 1 komentarz »

MySQL vs. PostgreSQL (adresy IP)

poniedziałek, 21 Lipiec 2008

Jeżeli kiedykolwiek zastanawialiśmy się jak przechowywać i łatwo przetwarzać adresy IP w bazie danych MySQL, odpowiedź brzmi – zmienić bazę na PostgreSQL (i nie tylko w tym przypadku).

MySQL oprócz funkcji INET_ATON() i INET_NTOA() nie oferuje nic więcej, ale to i tak lepsze niż przechowywanie adresów w CHAR(15) czy VARCHAR(15). Wskazany typ kolumny to UNSIGNED INT, a konwersja przy wstawianiu rekordów czy ich pobieraniu właśnie za pomocą wspomnianych funkcji.

PostgreSQL daje nam następujące typy danych: cidr, inet i dodatkowo macaddr (do przechowywania adresów MAC). Umożliwia także łatwe zarządzanie danymi za pomocą operatorów i funkcji.

Oczywiście sposób przechowywania adresu IP na pewno nie powinien być kryterium wyboru silnika baz danych, ale może być drobnym argumentem.

Do przeanalizowania kwestia szybkości przeszukiwania, indeksowania i wielkości danych.

Do poczytania:

Tagi:, , , , , ,
Zaszufladkowany do MySQL, PostgreSQL | Brak komentarzy »

Sprawdzanie aktywności użytkowników MySQL

piątek, 18 Lipiec 2008

Podłoże problemu: bazy zakładane na serwerze na żądanie użytkowników / klientów nie były usuwane. Po pewnym czasie ilość baz urosła, z czego spory odsetek była nieużywana. Nie obciąża to serwera, ale zabiera miejsce na dysku i ogólnie powoduje bałagan. Trzeba to jakoś posprzątać!

Prosty sposób – REVOKE i telefon. Mało elegancki! My szukamy czegoś na poziomie ;-)

Od pewnego czasu śledzę projekt mysql-proxy. Wprawdzie już jakiś czas projekt jest nieuaktualniany (At revision 511.), ale mam nadzieję, że Jan Kneschke cały czas pracuje…

Początkowo używałem mysql-proxy jako typowego pośrednika, co było pomocne przy przenoszeniu baz (sporo klientów łączyło się przez UNIX socket). Nie sprawdzał się za dobrze (ostatnia testowana wersja nie zawsze łączyła z PHP, wcześniejsze miały duże problemy ze stabilną pracą w ogóle). Na podobny projekt – pgpool-II (dla PostgreSQL) nie narzekałem nigdy. Niemniej z racji długości życia nie ma ich co porównywać.

Dla śledzenia użytkowników z tematu napisałem skrypt (LUA), który każde połączenie zapisuje do pliku dziennika (można przerobić na zapisywanie do bazy + jakiś licznik). Informacją wystarczającą jest IP klienta, nazwa użytkownika oraz baza, do której się podłączył. Do posprzątania nic więcej nas nie interesuje.
Sposób uruchomienia mysql-proxy opisany jest w dokumentacji, więc warto tam zajrzeć.
Skrypt wygląda tak:

local log_file = ‘/tmp/mysql.log’
local fh = io.open(log_file, „a+”)

function read_auth_result(auth)
if auth.packet:byte() == proxy.MYSQLD_PACKET_OK then
fh:write(
string.format(„%s %s\t%s\t%s\n”,
os.date(‘%Y-%m-%d %H:%M:%S’),
proxy.connection.client.address,
proxy.connection.client.username,
proxy.connection.client.default_db))
end
fh:flush()
end

Ze struktury proxy.connection.client przyda nam się address (adres IP klienta), username (nazwa uzytkownika) oraz default_db (baza, do której zostało wykonane połączenie). Jest jeszcze scrambled_password, ale logowanie tego podpada pod inwigilację. Logujemy tylko uwierzytelnionych użytkowników (MYSQLD_PACKET_OK – 0, ale jest jeszcze możliwość użycia MYSQLD_PACKET_ERR – 255). I w wyniku otrzymujemy:

2008-07-18 11:52:40 127.0.0.1:43228    root    test
2008-07-18 12:00:16 127.0.0.1:62133    exg    exg
2008-07-18 12:00:32 127.0.0.1:62901    root    mysql
2008-07-18 12:07:54 10.74.0.254:11243 guzik
2008-07-18 12:08:00 10.74.0.254:16369 guzik test

Log bez bazy, to połączenie z konsoli. \u czy USE nie loguje się w ogóle.
Po długoterminowym zbieraniu informacji (do miesiąca) mamy już listę wykorzystywanych baz. Pozostaje dump i DROP :>

Pamiętajmy też, że baza widzi połączenie z mysql-proxy, więc należy dać stosowne uprawnienia dla tego hosta.

Więcej do poczytania:

Tagi:, ,
Zaszufladkowany do DB, MySQL, Tech | Brak komentarzy »

MySQL: multiple tablespaces

środa, 16 Lipiec 2008

Wiadomo nie od dziś, że plik przestrzeni tabel InnoDB rośnie, ale nie maleje. Szczególnie dużym problemem jest to w momencie gdy tworzymy i usuwamy duże tabele. Sposobów radzenia sobie z tym jest kilka (nie wszystkie eleganckie, ale takie znalazłem – ja nie korzystam z żadnego z nich):

  • zmiana silnika wszystkich tabel z InnoDB na MyISAM, zatrzymanie bazy, usunięcie pliku ibdata1 i stworzenie go od nowa, zmiana silnika tabel z MyISAM na InnoDB – życzę powodzenia przy większej ilości tabel,
  • zrzut danych (dump), usunięcie pliku ibdata1 i utworzenie go od nowa, import danych.

Pewnie inne wariacje powyższych też zadziałają podobnie. Niemniej uważam, że downtime jest dość istotny, więc nie sprawdzą się w mocno obciążonym środowisku produkcyjnym.

Więcej o zmaganiach innych można poczytać tutaj:

Ja wybrałem tworzenie osobnego pliku dla każdej tabeli InnoDB. Na pierwszy rzut oka wydaje się, że jest to lek na całe zło InnoDB – usunięcie bazy / tabeli skutkuje usunięciem pliku, szybsze OPTIMIZE dla tabel, łatwiejsze tworzenie ‘zimnych kopii zapasowych’, obejście problemu systemu plików związanego z maksymalną wielkością pliku. Nic tylko używać.

Jeśli chodzi o przygotowanie serwera do tego, to sprowadza się do dodania w sekcji [mysqld] pliku my.cnf następującej linii:

[mysqld]
innodb_file_per_table

Oczywiście wymaga to restartu serwera. Wszystkie nowe tabele będą już miały osobny plik .ibd w katalogu bazy (dane i indeksy w jednym pliku w odróżnieniu od MyISAM – .MYD i MYI, plik z definicją tabel .frm tworzony jest dla obu silników). Dostęp do istniejących tabel zapisanych w ibdata1 jest po zmianie możliwy. Podobnie w sytuacji, gdybyśmy chcieli powrócić do poprzeniej konfiguracji – osobne pliki będą obsługiwane nadal.

Gdybyśmy chcieli dotychczasowe tabele utrzymywać w osobnych plikach, należy wykonać:

ALTER TABLE test ENGINE=InnoDB;

pamiętając, że nadal nie zmniejszy to głównego pliku.

Poza tym wspólna przestrzeń tabel (tablespace) dla InnoDB (plik ibdata1) zawsze będzie istnieć, nawet jak nową bazę uruchomimy z innodb_file_per_table, a wynika to z tego, że trzymane są tam np. undo logs.

Dodatkowo, wg dokumentacji MySQL, plików .ibd nie można przenosić dowolnie pomiędzy katalogami baz tak jak w przypadku MyISAM (jeśli ktoś w ogóle robi coś takiego). Powodem jest przetrzymywanie we wspólnej przestrzeni nazw InnoDB nazwy bazy. Po szczegóły zainteresowanych odsyłam do dokumentacji.

Nie znalazłem jeszcze wad tego rozwiązania. Jak tylko się znajdą, uaktualnię artykuł.

Do poczytania:

Tagi:,
Zaszufladkowany do MySQL | Brak komentarzy »

Hasło w MySQL

wtorek, 15 Lipiec 2008

Znów pewnie zbyt słabo szukałem, żeby dowiedzieć się jak wyłączyć hasło w MySQL (np. dla root@localhost – jak tuż po instalacji). Jedyny sposób, to:

UPDATE mysql.user SET Password = NULL WHERE user = ‘root’ AND Host = ‘localhost’;
FLUSH PRIVILEGES;

Powyższe nie jest może zalecane, ale jak tylko my mamy dostęp do konsoli i nie ma tam niczego w stylu phpMyAdmin, to można się pokusić o taką akcję.

Ułatwić sobie życie przy łączeniu się lokalnie można także tworząc plik .my.cnf w katalogu domowym i dodając sekcję [mysql] (ew. [mysqladmin] czy [mysqldump]). Oprócz opcji ze standardowego pliku my.cnf można dodać:

user = <user>
password = <password>

Tagi:
Zaszufladkowany do MySQL | Brak komentarzy »

Replikacja baz MySQL

środa, 9 Lipiec 2008

Konfiguracja replikacji w skrócie:

  • MASTER

W sekcji [mysqld] w pliku konfiguracyjnym (my.cnf) dodać / zmienić następujące linie:

log-bin = mysql-bin
server-id = 1

Pierwsza to nazwa pliku, w którym zapisywane są binarne logi operacji na bazie (dopełniona sześcioma cyframi, np. mysqld-bin.000036), druga, to identyfikator serwer. Ważne, żeby był różny na obu serwerach i na serwerze MASTER wyższy.

Następnie za pomocą GRANT należy nadać odpowiednie uprawnienia:

SQL> GRANT REPLICATION SLAVE ON *.* TO <user>@<host> IDENTIFIED BY ‘<passwd>’;

Teraz trzeba działać szybko:

SQL> FLUSH TABLES WITH READ LOCK;
SQL> SHOW MASTER STATUS\G
*************************** 1. row ***************************
File: mysql-bin.000001
Position: 98
Binlog_Do_DB:
Binlog_Ignore_DB:

1 row in set (0.00 sec)

Pierwszą komendą zablokowaliśmy tablice przed zapisem, żeby nikt nie zmienił żadnych danych, druga pokazuje nam w jakim stanie jest MASTER – istotna jest nazwa pliku oraz pozycja (które zapytanie się wykonało).

Kolejny krok, to wyłączenie bazy, skopiowanie danych (katalogi w datadir i pliki InnoDB), uruchomienie bazy i wykonanie:

SQL> UNLOCK TABLES;

MASTER gotowy i może dalej pracować.

  • SLAVE

W pliku my.cnf, w sekcji [mysqld] wpisujemy:

server-id = 2

Kopiujemy dane z serwera MASTER, uruchamiamy bazę i włączamy w tryb SLAVE:

SQL> CHANGE MASTER TO MASTER_HOST=’<host>’, MASTER_USER=’<user>’, MASTER_PASSWORD=’<passwd>’, MASTER_LOG_FILE=’<filename>’, MASTER_LOG_POS=<position>;
SQL> START SLAVE;

W pierwszym poleceniu podajemy odpowiednie wartości, drugie zaczyna replikować dane i voilà!

Uwagi końcowe:

  1. Warto od czasu do czasu czyścić logi binarne za pomocą PURGE MASTER LOGS, np:

    2. PURGE MASTER LOGS BEFORE DATE_SUB(NOW(), INTERVAL 7 DAY);

    Jeżeli wierzymy, że replikacja zawsze będzie działać, ew. monitorujemy to wystarczająco często, to można posiłkować się zmienną expire_logs_days, by zautomatyzować proces czyszczenia logów.

  2. Zalecane jest, by monitorować stan serwera SLAVE za pomocą:

    3. SHOW SLAVE STATUS\G

    Interesujące dane to
    Seconds_Behind_Master – powinno zawsze wynosić 0, jeśli nie, to szukamy Last_Error.

  3. Replikację dobrze zaplanować we wczesnej fazie projektu. Zanim baza rozrośnie się do niebotycznych rozmiarów (długie kopiowanie i downtime) i będzie krytyczną (np. osiem milionów serwerów musi jednocześnie z niej korzystać).

Do replikacji nie trzeba jakoś specjalnie przekonywać: failover i load-balancing same nas przekonają. Więcej do poczytania na stronie MySQL AB (czy jak kto woli – Sun).

Tagi:
Zaszufladkowany do MySQL | Brak komentarzy »

Nowsze wpisy »