divendres, 28 de juny de 2013

Getting performance over InnoDB

Hardware:


Per fer funcionar optimament una Base de Dades MySQL amb InnoDB, hem de parar especial atenció a: CPU, memòria i disc.

De aquesta 3 elements la CPU es l'element menys important ja que no serà el nostre bottleneck. Ha de ser de 64 bits per a poder utilitzar mes RAM.

La memoria te més importancia, necessitarem almenys tanta memoria com la nostra base de dades. Quan mes dades puguem tindre en memoria menys accesos a disc necessitarem i per tant millorarà molt el rendiment de la base de dades.

El disc és l'element més important. Serà el bottleneck de la base de dades per tant necessitem discos el mes rapids possible i configurats amb Raid 10. El controlador RAID hauria de tenir Write-Back catxe.

En el cas de que la màquina s'utilitze per configurar un slave, com la replicació es realitza amb un unic thread, llavors s'haurà de planificar la màquina amb un disc més rapid i una cpu mes rapida. En aquest tipus de configuració aquestes característiques importen més.

Sistemes de Fitxers:


Utilitzar sistemes de fitxers com ext4 o xfs en lloc de ext3 millorarà el rendiment general del sistema. També es útil utilitzar LVM. Facilita els backups.

Innodb tunning:


Aquestos son els elements a configurar quan volem obtenir mes rendiment d'una base de dades innodb.
  • Innodb_buffer_pool
  • Innodb_log_file_size
  • Innodb_log_buffer_size
  • Innodb_flush_log_at_trx_commit
  • Innodb_thread_concurrency 
  • Innodb_flush_method
  • Innodb_file_per_table

Innodb_buffer_pool:

Quan mes gran siga aquest valor més actuara la base de dades com una base de dades in-memory i mes rendiment tindra ja que minimitzarà els accessos a disc que son molt costosos.

Si la base de dades es xicoteta un valor acceptable es un 10% de la mida de la base de dades. Si la base de dades es gran llavors reservarem un 10% menys de la seua mida. El valor que reservem en la practica sempre serà mes gran. La base de dades sempre reserva memoria per a estructures auxiliars

Normalment reservant 256 MB de RAM per al sistema sera suficient en la majoria de maquines. En maquines amb mes recursos un 5% de la RAM la deixarem per al sistema. Tot aço depén del nombre de conexions i altres procesos que corren en el sistema.

Si no estem massa segurs un valor entre el 70% i el 80% de la memoria RAM es un valor per començar si la base de dades es suficientment gran.

Innodb_log_file_size:

Aquest parametre s'utilitza per a definir la mida dels logs de MySQL ib_logfile0 i ib_logfile1.
Aquestos logs son circulares i els utilitza MySQL per a minimitzar els accessos a disc i per a recuperar dades. Un valor de 64M sol ser suficient per a la majoria de necessitats.

mysql> \P grep 'Log sequence number'
PAGER set to 'grep 'Log sequence number''
mysql> SHOW ENGINE INNODB STATUS\G SELECT SLEEP(60); SHOW ENGINE INNODB \
    STATUS\G;
Log sequence number 14994414540947
1 row in set (0.00 sec)

1 row in set (59.99 sec)

Log sequence number 14994416309995
1 row in set (0.00 sec)

ERROR:
No query specified

mysql> \P
Default pager wasn't set, using stdout.

mysql> select ROUND((14994416309995 - 14994414540947) / 1024 / 1024) AS MB;
+------+
| MB   |
+------+
|    2 |
+------+
1 row in set (0.00 sec)

Per tant si en 1 minut es generen 2 MB en 60 minuts tindrem 120MB, dividit entre 2 fitxers, 60Mb cada fitxer.

En les versions de MySQL posteriors a 5.1 podem realitzar el càlcul de la següent manera.

mysql> SELECT @a1 := variable_value AS a1
    -> FROM information_schema.global_status
    -> WHERE variable_name = 'innodb_os_log_written'
    -> UNION ALL
    -> SELECT Sleep(60)
    -> UNION ALL
    -> SELECT @a2 := variable_value AS a2
    -> FROM information_schema.global_status
    -> WHERE variable_name = 'innodb_os_log_written';
+---------------+
| a1            |
+---------------+
| 1611291073024 |
| 0             |
| 1611295460864 |
+---------------+
3 rows in set (1 min 0.00 sec)

mysql> SELECT ROUND((@a2-@a1) * 60 / 1024 / 1024 /  @@innodb_log_files_in_group) as MB;
+------+
| MB   |
+------+
|  126 |
+------+
1 row in set (0.00 sec)
Innodb_log_buffer_size:

Aquest parametre s'utilitza per definir la mida del buffer que InnoDB utilitza per a escriure els logs al disk. Si aumentem aquest valor estem permeten que les grans transaccions puguen correr sense xafar disc escriure a disc fins que arribe una transacció de commit. Normalment 4MB son suficient si no tenim un gran volum de BLOBS.

Aquesta comanda mostra el valor del log:

mysql> SHOW GLOBAL VARIABLES LIKE 'innodb_log_buffer_size';
+------------------------+----------+
| Variable_name          | Value    |
+------------------------+----------+
| innodb_log_buffer_size | 33554432 |
+------------------------+----------+
1 row in set (0.00 sec)
Si el següent valor es zero o proper a zero el parametre esta ben configurat, si aquest valor es alt i va creixent necessitarem incrementar el valor del buffer. 

 mysql> SHOW GLOBAL STATUS LIKE 'innodb_log_waits';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| Innodb_log_waits | 0     |
+------------------+-------+
1 row in set (0.00 sec)

Innodb_flush_log_at_trx_commit:

 El valor per defecte es 1. Aquest valor es l'únic que garanteix que les transaccions respecten ACID.
Qualsevol valor diferent d'1 millorarà el rendiment però pots perdre fins a 1 segon de transaccions.

  • Valor 1: Respected ACID.
  • Valor 0: Millor rendiment, podem borrar fins a 1 segon de transaccions si mysql es cau.
  • Valor 2: Millor rendiment, en aquest cas nomes una perdua de corrent o un error en el sistema operatiu pot causar aquesta perdua de transaccions d'1 segon.
Alguns sistemes operatius i discs durs enganyen a l'hora de realitzar les operacions de FLUSH. Per tant la durabilitat de les transaccions no està garantida, ni amb 1 com a opció. Es convenient utilitzar catxés d'escritura a disc amb bateria. Açò aporta seguretat i velocitat a les transaccions.


mysql> show variables like '%innodb_flush_log_at_trx%'\G;
*************************** 1. row ***************************
Variable_name: innodb_flush_log_at_trx_commit
        Value: 1
1 row in set (0.00 sec)

mysql> set global innodb_flush_log_at_trx_commit=0;
Es pot agilitzar la velocitat a la que sincronitza un slave utilitzant aquest valor, però en el cas que es produeixca un error no podrem confiar en l'esclau i l'haurem de resincronitzar o comprobar d'alguna manera que les dues bases de dades son identiques.

Innodb_thread_concurrency:

Si tenim 1-2 CPU i una versió de MySQL posterior a 5.0.19, el millor es que configurem el valor a 0. Açò significa que el nombre de threads es il·limitat. Per a altres configuracions podem utilitzar aquesta formula 2*(NumCPUs+NumDisks), de forma que tinguem 2 threads per disc i per cpu.
Algunes voltes aquests valors no seran optims per tant millor sempre fer profiling per a veure quin impacte te en el rendiment qualscvol canvi en la configuració.


Innodb_flush_method:

Es  recomanable utilitzar O_Direct, així les transaccions es fan directament entre el MySQL i la controladora de disc sense passar per la catxe del Sistema Operatiu. Es important disposar un disc amb catxé d'escritura a disc amb bateria.

Cambiar el innodb_flush_method no es pot realitzar amb la base de dades funcionant, requereix reiniciar la màquina.

Innodb_file_per_table:

Si no tenim massa tables es recomanable configurar Innodb per a que cree un fitxer per taula. Així podem gestionar millor el tablespace.

Enllaços:


http://www.mysqlperformanceblog.com/2007/11/03/choosing-innodb_buffer_pool_size/
http://www.dbasquare.com/2012/04/10/what-is-the-proper-size-of-innodb-logs/
http://useranswer.com/answer/how-to-know-the-current-log-sequence-number-in-5-0-x/
http://www.mysqlab.net/knowledge/kb/detail/topic/innodb/id/6553
http://www.mysqlperformanceblog.com/2006/06/05/innodb-thread-concurrency/
http://www.dbasquare.com/kb/direct-io-in-innodb-o_direct/