Benchmarking de parâmetros InnoDB

Ouvir com webReader

Aqui vão uns benchmarks que fiz uma vez com InnoDB para uma tabela quase exclusivamente de escrita. Com quase exclusivamente quero dizer que 99.99% das operações eram INSERTs. O filesystem, desta vez, era ZFS, e era o mesmo para os logs e datafiles.

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:
  1. transaction-isolation = READ-COMMITTED
  2. innodb_file_per_table
  3. innodb_buffer_pool_size = 3000M
  4. innodb_additional_mem_pool_size = 20M
  5. innodb_log_file_size = 256M
  6. innodb_autoinc_lock_mode = 2
  7. innodb_flush_log_at_trx_commit = 2
  8. innodb_file_per_table
  9. log-slow-queries=mysql-query-slow.log
  10. slow_query_log = 1
  11. long_query_time = 1
  12. innodb_doublewrite = 0

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:
  1. Serie A default
  2. Serie B UNIQUE -> PRIMARY KEY
  3. Serie C PK, Autocommit = 0 (1 comm/seg)
  4. Serie D PK, innodb_flush_method = O_DIRECT
  5. Serie F PK, innodb_flush_method = O_DSYNC
  6. Serie G innodb_flush_log_at_trx_commit = 0
  7. Serie H innodb_doublewrite = 0
  8. Serie I innodb_log_file_size = 128M
  9. Serie J zfs set recordsize=16k data/mysql

innodb-parameters-1

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:
  1. 090721 18:18:10  InnoDB: Failed to set DIRECTIO_ON on file /opt/coolstack/mysql/data/ibdata1: OPEN: Inappropriate ioctl for device, continuing anyway

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:


You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

AddThis Social Bookmark Button

Leave a Reply