Vamos aos testes. Especificações de hardware e SO:
- MySQL: 5.1.25-rc-log conforme consta no CoolStack
- Intel Quad core x 2800 MHz
- 16 GB RAM
- Solaris 10
As configurações de base eram estas:
[code]
transaction-isolation = READ-COMMITTED
innodb_file_per_table
innodb_buffer_pool_size = 3000M
innodb_additional_mem_pool_size = 20M
innodb_log_file_size = 256M
innodb_autoinc_lock_mode = 2
innodb_flush_log_at_trx_commit = 2
innodb_file_per_table
log-slow-queries=mysql-query-slow.log
slow_query_log = 1
long_query_time = 1
innodb_doublewrite = 0
[/code]
E todos os testes foram intercalados de DROP TABLE, CREATE TABLE e uma única alteração à configuração de base. Os INSERT's foram feitos com INSERT .... ON DUPLICATE KEY. O schema anda algures perdido aqui nos meus apontamentos (vou tentar encontrá-lo entranto) mas é de notar que havia um único índice, que era um UNIQUE KEY, e que foi transformado inicialmente para PK e deixado em alguns dos testes seguintes.
A tabela seguinte diz respeito ao único parâmetro que foi alterado em relação à base:
[code]
Serie A default
Serie B UNIQUE -> PRIMARY KEY
Serie C PK, Autocommit = 0 (1 comm/seg)
Serie D PK, innodb_flush_method = O_DIRECT
Serie F PK, innodb_flush_method = O_DSYNC
Serie G innodb_flush_log_at_trx_commit = 0
Serie H innodb_doublewrite = 0
Serie I innodb_log_file_size = 128M
Serie J zfs set recordsize=16k data/mysql
[/code]
Na Série B a única alteração foi a conversão da UNIQUE KEY para uma PRIMARY KEY, nada a assinalar. Na Série C, a alteração consistia em suster os COMMIT's durante aprox. 1 segundo, fazendo COMMIT a cada segundo. Mal seria se não tivéssemos um ganho, por mínimo que fosse, mas é preciso ter em conta que, apesar do ganho ser de 20%, são ~7600 potenciais candidatos a rollback caso alguma coisa corra mal nesse segundo..! Posso dizer que o rollback de aprox. metade demora bastante (para o que é um arranque normal do MySQL).
Queria também experimentar Direct I/O com InnoDB na Série D mesmo sabendo que estava em ZFS, e que a coisa não deveria ser tão fácil. O erro foi:
[code]
090721 18:18:10 InnoDB: Failed to set DIRECTIO_ON on file /opt/coolstack/mysql/data/ibdata1: OPEN: Inappropriate ioctl for device, continuing anyway
[/code]
Na série F, foi a vez de experimentar O_DSYNC, e nem acabei de terminar o teste, pois iria demorar demasiado tempo. Escusado será dizer que o I/O foi altíssimo durante esse momento. Comecei entretanto a procurar maneiras de afinar o ZFS, mas convenhamos que ao fim de uns minutos a ler, já estava a enveredar por um caminho muito muito distante.. :-) É incrível a quantidade de coisas que dá para fazer com ZFS e só por si vai merecer uma categoria própria, um dia...
De volta ao MySQL, a Série G inflinge um risco conhecido, desleixando o flush dos logs, por isso não é de estranhar o aumento de performance - esta opção deve ser analisada com cuidado, pois tem contrapartidas. A Série H desactivou o doublewrite do motor, resultando num aumento de performance de 3,5% (relativamente à Série A) conforme previsto pelo Peter Zaitsev. Como estamos perante ZFS, não vejo motivo nenhum para não desactivá-lo.
Reduzindo o tamanho dos logs na Série I resultou num aumento de performance de 2,8%. Isto é interessante, e é a prova viva de que, se por um lado precisamos deles grandes, por outro, a sua gestão pode infligir mais carga ou deteriorar a performance por serem grandes demais, isto para não falar na forma como afectam o recovery. Não cheguei a testar, mas com um tamanho ainda mais pequeno, o aumento podia ser maior...
A Série J foi dedicada ao ZFS, com a recomendação típica para filesystems dedicados a DBs, que é o alinhamento do tamanho dos blocos com o tamanho das páginas de InnoDB (16KB), e o ZFS, como [quase?] todos os sistemas de ficheiros, permite ajustar esse parâmetro. Traduzindo, a cada leitura do disco, o SO pede ao disco record size (omissão: 128KB) bytes de cada vez; o InnoDB, que tenta gerir os acessos I/O de forma inteligente minimizando-os ao máximo, faz pedidos de page size bytes de cada vez. Se o SO não estiver alinhado, e como os blocos pedidos pelo InnoDB são na maioria aleatórios, cada bloco de 16K solicitado pelo InnoDB traduzir-se-á em leituras de 128KB. Com um pedido de 10 páginas aleatórias (160KB), o SO terá de ler 1280KM, ou seja, quase 10x mais! Mas como eu estava à espera, o resultado não foi significativo (1%) já que este cenário era exclusivamente de INSERTs.
Em termos de sistemas de ficheiros, sejam ZFS ou outro qualquer, há ainda características determinantes a considerar, que são o efeito de prefetching e read-ahead.... mas lá está, já me estava a desviar demasiado do MySQL :P Aqui ficam algumas considerações interessantes sobre o uso de MySQL em ZFS.
Ficaram a faltar muitas opções por falta de tempo, mas aqui ficam sugestões para a quem quiser experimentar:
- skip-innodb-checksum, visto que estamos em ZFS, estaremos a fazer algo parecido com double checksumming;
- As opções características da versão 5.4, que já obrigavam a que os testes fossem multi-threaded:
- innodb_thread_concurrency_timer_based;
- innodb_io_capacity;
- innodb_read_io_threads, innodb_write_io_threads, e innodb_max_merged_io.
- E, por fim, repetir todos os testes com os melhores parâmetros encontrados!
