mysql replika db engine + teljesitmeny hangolas

Adatbázis: SQL, XML DB

van 3 mysql szerverem, 1 gep master + 2 gep replika. A master-en van egy 200k rekordot tartalmazo innodb tabla. A replikak gepein fut 1-1 alkalmazas, ami a localhost-on futo mysql szerverbol olvas adatokat (a gyorsasag fontos) read-only modon (minden iras termeszetesen a master-en tortenik).

A kerdes az, hogy kell-e/jo-e nekem a replikakon is innodb-t hasznalni, ha a cel a stabilitas es s teljesitmeny, vagy a replikakon myisam (vagy mas) legyen inkabb? (jelenleg minden gepen innodb-ben van az (n)agyon hasznalt tabla).

Az is szempont, hogy minel kevesebb memoriaval minel gyorsabb legyen a lekerdezesem

  • 1914 megtekintés

"mi?"

Innen indult a topic:

"A kerdes az, hogy kell-e/jo-e nekem a replikakon is innodb-t hasznalni, ha a cel a stabilitas es s teljesitmeny, vagy a replikakon myisam (vagy mas) legyen inkabb? (jelenleg minden gepen innodb-ben van az (n)agyon hasznalt tabla).Az is szempont, hogy minel kevesebb memoriaval minel gyorsabb legyen a lekerdezesem"

Erre irtam (bar nem ilyen szep mondattal, ilyent nem tudok), hogy leegyszerusiteni row level lockingra az erre adando valaszt trivialis.

fyi

drk

( cydrk | 2009. 10. 01., cs – 17:59 )

Hai,

Hmm igy kicsit tul egyszeru a kerdes. Rengeteg szempontbol erdekes, hogy hogyan hasznalod a tablat.

Elosszor is, ismerkedj meg ha meg nem ismerned a maatkit -el (www.maatkit.org). Ezzel tudod ujra szinkronizalni a tablakat ha baj lenne.

Ha a tabla tenyleg durvan hasznalt, innodb jobb megoldas es szerintem inkabb optimalizalasra erdemes rafekudni. Raadasul myisam 200k/(n)agyon hasznalt tablanal eloszeretettel crashel. Nincs sajat memory pool-ja hanem linux filecache-re bizza.

Ezert ami erdekesebb, hogy rengeteg opciot kitudszkapcsolni a replikan vagy attudsz allitani.
- ne legyen binlog a replikan, ez fontos.
- innodb buffer pool size-ba ferjenek bele a tablaid ha megoldhato (200k sor pici) mindenestol es jo ha marad hely.
- transaction-isolation level batran lehet read-uncommitted is akar.
- legalabb a slave-eken hasznalj file-per-table opciot
- emeld a log_buffer_size-t. Ne tul nagyra (nagyon sok irasnal (azaz kb replikalt queryk/sec) mondjuk 500 felett 4-8mb, ha nincsenek tul nagy insertek (mondjuk kep,mp3 ilyesmi) vagy tranzakciok)
- support_xa hacsak nem tudod mire jo, =0
- ha 5.1 a mysql vagy van xtradb akkor innodb_thread_concurrency -t erdemes emelni esetleg 0-ra rakni xtradb-nel.
- ezen opciok xtradb-vel vagy (aszem a verzio nem biztos) 1.0.2/4 innodb plugintol mukodnek
innodb_file_io_threads,innodb_io_capacity,innodb_write_io_threads,innodb_read_io_threads erdemes utanuk olvasni, folleg az olvasasra helyezd a hangsulyt imho.
- flush_log_at_trx_commit levenni 2-re
- ha bbu-s a raidvezerlod akkor flush_method = O_direct (es persze ha linux) ami sokat segithet a szegeny single thread replikanak.

Igazabol over-all ha innodb-t hasznalsz es elfer a tabla mindenestol a buffer_pool -ban (az innodb sajat memoriaja) akkor ezekkel az opciokkal eltudod erni azt, hogy jo queryknel (ertsd indexet okosan hasznaloknal) az innodb lenyomja boven myisam-ot vagy barmelyik masikat es az adatok is konzisztensek maradnak (ha megsem, ott a maatkit vagy ujra huzas). Viszont innodb nem csinal full table lockot minden aprosaghoz :)

Esetleg konkretabb kerdesre tudnek okosat is mondani.

Udv,
drk

kossz a tippeket. A support_xa-t most mar tudom, es 0 :-)
A innodb_file_io_threads,innodb_io_capacity,innodb_write_io_threads,innodb_read_io_threads valtozok sajnos csak 5.4-tol ervenyesek, en meg 5.1.3x-et hasznalok.

A sztori roviden az, hogy egy spamszurot epitek, ami 2 mx-en fut, ezek a replikak. Az adatok nagyja a ~41 MB-os t_token.ibd file-ban van. Van meg egy user, policy, ill. email tabla, ezek jelenleg myisam-ok, es ezeken egyszeru 'select userdata from table where id=x' tipusu select-ek futnak, ahol az id-re meg van indexelve a myisam tabla.

A 2 mx-en futo replikakat az alkalmazas read-only modban hasznalja, csak lekerdez, az update-ek ki vannak kapcsolva. A master-en akkor van iras, ha valaki tanitja a szurot, vagy egy uj email cimet vesz fel, esetleg a feherlistajat modositja. Tehat a master felol nincs konstans query aradat a replikak fele. Ami fontos, hogy a master-en mindig elerheto legyen az adat, ne legyen korrupt(ta) egyik tabla sem.

A master-en default innodb beallitasok vannak, az egyik replikan (tesztre) ezek:

### 2009.10.01.
###log-bin = ferrum-bin

relay-log = ferrum-relay-log

replicate-do-db=clapf

server-id = 2

skip-networking
safe-show-database

##query-cache-size = 1M

###
### InnoDB variables
###
### http://dev.mysql.com/doc/refman/5.1/en/innodb-tuning.html

innodb_file_per_table
innodb_buffer_pool_size = 48M

## If you do not wish to use XA transactions, you can disable this variable
## to reduce the number of disk flushes and get better InnoDB performance.
innodb_support_xa=0

## These are from 5.4+ only
##innodb_file_io_threads,innodb_io_capacity,innodb_write_io_threads,innodb_read_io_threads

## With a value of 2, then only an operating system crash or a power outage
## can erase the last second of transactions.
innodb_flush_log_at_trx_commit=2

## A large log buffer allows large transactions to run without a need to write the log to disk before the transactions commit.
## Thus, if you have big transactions, making the log buffer larger saves disk I/O.
innodb_log_buffer_size=1M

transaction-isolation=READ-UNCOMMITTED

Meg azon tunodom, hogy ha a user, email es a policy tablak kicsik (a token tablahoz kepest), es ha mar agyontekertem az innodb valtozokat, akkor legyen-e minden tabla innodb-ben...

SPAMtelenul - POP3 spamszuro szolgaltatas

hmm nekem most epp 5.0.83 alatt vannak azok az opciok :))
Ime a titok http://www.percona.com/mysql/

Szoval xtradb 5.1 el tud olyan sebesseget mint az unstable 5.4. Nem tudom szukseged van-e ilyen tuningokra, de ha igazabol nincs akkor felesleges "kockaztatni".

Egyebkben ha van eleg memoriad, hogy innodb buffer_pool-ban elferjenek, akkor bizony erdemes illetve ugye a clustered indexek okan ha jol van indexelve es a queryk is jok (halistennek gondolom ilyen szolgaltatasnal nem is valtozik a query allandoan) akkor megeri igen. Innodb alapvetoen gyors, csak figyelembe kell venni, hogy az irasnal (es az isolation level miatt) az olvasasnal is sulyos penality van. Viszont ezek mind minimalizalhatoak :) A clustered indexek is nagyon jok, csak tudni kell hasznalni oket. Erdemes rafekodni queryknel a covering indexre, vagy pont ellenkezoleg, neha mindent indexelnek az essezruseg helyett (ez clustered miatt ismet lehet nemi penality a sebessegben es a foglalt helyben). Szoval innodb egy szornyetek csak tudni kell idomitani :)) MyISAM nem rosz, de ha gyakran irsz bele az full table lock afaik :P Akkor pedig innodb rogton leveri :D

Najo az a baj, hogy az ilyen esetekben csak ilyesmiket tudok mondani. Allitsd fel a buffereket de ne tulsagos, optimalizalj a queryn es indexelj de ne feleslegesen szoval ilyen semmit mondo dolgokat. :) De azert remelem segitettem.

drk

kossz a 2 linket, feljegyzem magamnak, es maatkit-et be is fogom. A percona build-et most nem kockaztatnam egyelore. Visznot azt nem tudom, hogy ha az transaction-isolation read-uncommited, akkor is van read penalty? Mennyire sulyos?

Ami a query-ket illeti a tokenek lekerdezese valtozik. Van egy level, ami eppen bejon, abbol lesz egy halom token, es egy select-tel igy kerdezem le: select nham, nspam from t_token where token in (... itt vagy par szaz token, azaz par szaz unsigned long long szam ...) and (uid=0 or uid=x). Itt a token es uid oszlop van megindexelve.

De azert remelem segitettem.

Sokat, kossz.

SPAMtelenul - POP3 spamszuro szolgaltatas

Szira,

Lesz read-uncommittednel is nemi. Nem sok, de ez innodb hatranya sajnos. Esetleg ha erdekel a tema a kulcsszo MVCC.

Hmm erdemes lehet neked, ha a mysql geped csak ezt fogja csinalni ugynevezett covering indexet letrehozni.
Ebbe probalj belefoglalni _minden_ oszlopot amit a queryd vissza kell adjon. Igy nem fogja innodb a tablat nyuzni hanem indexbol jon vissza. Ezt erdemes kibenchmarkolni mert ez valszeg gyorslesz bar tobb helyet fog foglalni ami tobb memoria igeny, viszont mocskosul gyors lesz.

Esetleg a query explainjait es a tablat megmutatod talan meg konkret dolgokat is tudok mondani.

drk

ez a legrazosabb query, ahol egy nagy where in (...) van megadva:

mysql> explain select token, nham, nspam from t_token where token in (12959460, 14005231, 25655759, 13736174, ...) and (uid=0 or uid=100);

+----+-------------+---------+-------+-------------------+-------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+-------+-------------------+-------+---------+------+------+-------------+
| 1 | SIMPLE | t_token | range | token,t_token_idx | token | 11 | NULL | 8 | Using where |
+----+-------------+---------+-------+-------------------+-------+---------+------+------+-------------+
1 row in set (0.00 sec)


mysql> show create table t_token\G
*************************** 1. row ***************************
Table: t_token
Create Table: CREATE TABLE `t_token` (
`token` bigint(20) unsigned NOT NULL,
`uid` smallint(5) unsigned DEFAULT '0',
`nham` int(11) DEFAULT '0',
`nspam` int(11) DEFAULT '0',
`timestamp` int(10) unsigned DEFAULT '0',
UNIQUE KEY `token` (`token`,`uid`),
KEY `t_token_idx` (`token`,`uid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)


mysql> show indexes from t_token;
+---------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+---------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| t_token | 0 | token | 1 | token | A | 209289 | NULL | NULL | | BTREE | |
| t_token | 0 | token | 2 | uid | A | 209289 | NULL | NULL | YES | BTREE | |
| t_token | 1 | t_token_idx | 1 | token | A | 209289 | NULL | NULL | | BTREE | |
| t_token | 1 | t_token_idx | 2 | uid | A | 209289 | NULL | NULL | YES | BTREE | |
+---------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
4 rows in set (0.00 sec)

Ill. igy nez ki a user adatok kikeresese:

mysql> explain SELECT user.uid, user.username, user.domain, user.policy_group FROM user,t_email WHERE user.uid=t_email.uid AND t_email.email='xxx@aaa.fu';
+----+-------------+---------+--------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+--------+---------------+------+---------+------+------+-------+
| 1 | SIMPLE | user | system | uid,user_idx | NULL | NULL | NULL | 1 | |
| 1 | SIMPLE | t_email | system | PRIMARY | NULL | NULL | NULL | 1 | |
+----+-------------+---------+--------+---------------+------+---------+------+------+-------+
2 rows in set (0.00 sec)

mysql> show create table user\G
*************************** 1. row ***************************
Table: user
Create Table: CREATE TABLE `user` (
`uid` int(10) unsigned NOT NULL,
`username` char(32) NOT NULL,
`policy_group` int(4) DEFAULT '0',
`password` char(48) DEFAULT NULL,
`isadmin` tinyint(4) DEFAULT '0',
`dn` char(64) DEFAULT '*',
`domain` char(64) DEFAULT NULL,
UNIQUE KEY `uid` (`uid`),
KEY `user_idx` (`uid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> show create table t_email\G
*************************** 1. row ***************************
Table: t_email
Create Table: CREATE TABLE `t_email` (
`uid` int(10) unsigned NOT NULL,
`email` char(128) NOT NULL,
PRIMARY KEY (`email`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> show index from user;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| user | 0 | uid | 1 | uid | A | 1 | NULL | NULL | | BTREE | |
| user | 1 | user_idx | 1 | uid | A | 1 | NULL | NULL | | BTREE | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
2 rows in set (0.01 sec)

mysql> show index from t_email;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| t_email | 0 | PRIMARY | 1 | email | A | 1 | NULL | NULL | | BTREE | |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
1 row in set (0.01 sec)

SPAMtelenul - POP3 spamszuro szolgaltatas

Szoval elso korben kezdjuk rogton az elso queryvel. amibe belekotnek:

Felesleges a t_token_idx index, hiszen = a primary key-el. Igy most kb duplan tarolod le azt a ket oszlopot.
(alter table t_token drop key t_token_idx).

Annak ellenere hogy az explainben using-where van, ez az innodb priamry key miatt valoszinuleg jo lesz igy.

Az emailes mar viccesebb mivel join. Itt is az elso baj, hogy a suer tabla user_idx duplicated index. droppolhato a primary key megoldja.
Elsore az email tabla sem tunik problemasnak.

Szoval leszamitva a 2 duplikated indexet ugytunik rendben van. Van amugy vele gond?

drk

nincs gond, attol eltekintve, hogy a 41 MB-s t_token tabla 29 MB lett, miutan vegrehajtottam a 'alter table t_token drop key t_token_idx' utasitast.

Egyebkent az lehetett egy kepzavar a fejemben, hogy minden tabla letrehozasa utan csinaltam egy indexet is:

create table if not exists t_token (
token bigint unsigned not null,
uid smallint unsigned default 0,
nham int default 0,
nspam int default 0,
timestamp int unsigned default 0,
unique key(token, uid)
) Engine=InnoDB;

create index t_token_idx on token(token,uid);

Ezek szerint a 'create index...' mar nem kell, mert a unique key(...) hatasara mar csinal indexet?

SPAMtelenul - POP3 spamszuro szolgaltatas

Akár az s lehetne, de nem az, mert az egyik mezod nullable.
mysql> show create table t;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------+
| t | CREATE TABLE `t` (
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL,
`c` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> create unique index idx on t(a,b);
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> insert into t values (1, NULL, 1);
Query OK, 1 row affected (0.01 sec)

mysql> insert into t values (1, NULL, 2);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t;
+------+------+------+
| a | b | c |
+------+------+------+
| 1 | NULL | 1 |
| 1 | NULL | 2 |
+------+------+------+
2 rows in set (0.00 sec)

A sima unique constrant nem figyeli a nullt. Ha eldobom az indexet, es helyette primary key lesz az (a,b), akkor viszont mar ezt nem tudom megcsinalni.
mysql> delete from t;
Query OK, 2 rows affected (0.01 sec)

mysql> drop index idx on t;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> alter table t add primary key (a,b);
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> show create table t;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t | CREATE TABLE `t` (
`a` int(11) NOT NULL DEFAULT '0',
`b` int(11) NOT NULL DEFAULT '0',
`c` int(11) DEFAULT NULL,
PRIMARY KEY (`a`,`b`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Latszik, hogy ezutan mar sem az a, sem a b nem nullable. Ha ezutan probalom meg a sorokat beszurni a tablaba:
mysql> insert into t values (1, NULL ,1);
ERROR 1048 (23000): Column 'b' cannot be null

Akkor a not null-on elhasal. Az indexed tehat egy secondary index, es az innodb belul csinalt neki egy primary key-t, ami alapjan a datafile szervezodik. Minden secondary index lookup jar egy primary key lookup-pal is. Tehat ha az uid-ed is lehet nem null, akkor erdemes letrehoznod egy primary key-t erre, majd eldobni az indexed.

Ha az uid sem lenne nullable, akkor az innodb tud okos lenni, es latni, hogy az tud primary key lenni, de egeszsegesebb explicit megadni.

Akkor mar csak azt szeretnem megtudni, melyik (A vagy B vagy mas) a jobb sema definicio, hogy legyen egy korrekt modon indexelt tablam, ha sem a 'token', sem az 'uid' nem null, es a select-ek a 'where token=... and uid=...' -re vegzodnek ?

A)

create table if not exists t_token (
token bigint unsigned not null,
uid smallint unsigned default 0,
nham int default 0,
nspam int default 0,
timestamp int unsigned default 0,
unique key(token, uid)
) Engine=InnoDB;

B)

create table if not exists t_token (
token bigint unsigned not null,
uid smallint unsigned not null,
nham int default 0,
nspam int default 0,
timestamp int unsigned default 0,
unique key(token, uid)
) Engine=InnoDB;

SPAMtelenul - POP3 spamszuro szolgaltatas

Egyik sem:).

create table if not exists t_token (
token bigint unsigned not null,
uid smallint unsigned not null default 0,
nham int default 0,
nspam int default 0,
timestamp int unsigned default 0,
primary key(token, uid)
) Engine=InnoDB;

Ha itt adod meg a primary key-t, akkor a not null-ok a primary key mezoinel automatikusan odakerulnek egyebkent. A default ertek es a not null teljesen fuggetlenek egymastol.

kossz a felvilagositast. Viszont most egy ujabb dolgot nem ertek: 'not null default 0'. A 'default 0' ugye azt mondja, hogy ha nem adok erteket az oszlopnak, akkor a mysql 0-t ad neki. Viszont a 'not null' meg azt mondja, hogy nem adhatsz neki null-t, hanem meg kell adnod az oszlop erteket. Most akkor ezt hogy? Egyebkent amikor rekord kerul a t_token tablaba, mindig megadom az uid ertekere is: insert into t_token (token, uid, ...) values(12345, 1001, ...). Igy is kell a 'default 0'? Ill. mi tortenik akkor, ha 'uid smallint unsigned not null default 0' helyett csak 'uid smallint unsigned not null'-t irok? (Mert tolem ugyan maradhat, csak hogy ertsem is...)

SPAMtelenul - POP3 spamszuro szolgaltatas

janoszen mar megvalasztolta, de a null az ertek hianya. Tegyuk fel, hogy most a tablaban csak a token, uid ertekek vannak, egyik sem nullable. Az insert into table values (1) az 1,0 ertekeket fogja beszurni, mert az uid-nak van default 0 erteke, de egyik sem null. Ha mindig ugy insertelsz, hogy megadod explicite az oszlopokat, akkor mindegy mi a default ertek (egyebkent ezt igy is kell csinalni egy kulturalt alkalmazasnak:)). Ha mindig adsz neki erteket, es nincs ott a default 0, az is jo. Ha nem irod oda a not null-t, de odairod a primary key-t, majd megnezed a tablat show create table-lel, akkor benne lesz a not null, mert a primary key maga utan vonja, hogy a mezoinek nem lehet null erteke.