Benchmarking de parâmetros InnoDB

July 15th, 2009 ntavares Posted in mysql, performance, pt_PT No Comments »

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:

AddThis Social Bookmark Button

MySQL DATETIME vs TIMESTAMP vs INT performance and benchmarking with InnoDB

July 6th, 2009 ntavares Posted in en_US, linux driver, mysql, performance, sugarcrm No Comments »

Ouvir com webReader

Following my tests with DATETIME vs vs TIMESTAMP vs INT performance and benchmarking with MyISAM storage engine, I've wondered about the performance impact using InnoDB, which is usually more peaky with I/O. Read the rest of this entry »

AddThis Social Bookmark Button

MySQL DATETIME vs TIMESTAMP vs INT performance and benchmarking with MyISAM

July 6th, 2009 ntavares Posted in en_US, mysql, performance 9 Comments »

Ouvir com webReader

Recently there was a discussion about DATETIME vs TIMESTAMP vs INT performance and storage requirements. If one setup was bound to disk usage, one would expect INT to perform better, since storage requirements are smaller. However, the amount of calculations to use it as a timestamp can be overwhelming as well. Read the rest of this entry »

AddThis Social Bookmark Button

Analysing MySQL slow queries

July 4th, 2009 ntavares Posted in en_US, mysql, performance No Comments »

Ouvir com webReader

While I'm engaged in my MySQL DBA mode, I usually come across the hard task of surfing around the slow query log.

Everybody trying to trace MySQL performance problems will hit the slow query log to keep track of those unperformant queries that seem to be hogging the system. The traditional tool to analyze the log is mysqldumpslow (provided with standard MySQL distribution) which aggregates the queries by pattern (fingerprint) and calculates some aggregated statistics. I personally find the tool very limited, and I don't seem to be the one (see below). Read the rest of this entry »

AddThis Social Bookmark Button

Dicas para performance Web

June 27th, 2009 ntavares Posted in performance, pt_PT, web No Comments »

Ouvir com webReader

Cruzei-me com [mais] uma ferramenta para análise de performance do rendering de páginas Web: YSlow, que é um plugin para o Firefox, que analisa as ditas páginas e sugere recomendações para aumentar a sua performance; uma delas é a integração da ferramenta Smush.it, que realiza optimizações na compressão de imagens por forma a diminuir o tempo de transferência. Este plugin segue as recomendações tecidas pela Yahoo Developer Network sobre as Best Practices for Speeding Up Your Web Site, que agrega algumas das recomendações típicas de uma forma bastante explicativa.

AddThis Social Bookmark Button

Linux on HP/Compaq Deskpro DC7700

April 10th, 2009 ntavares Posted in en_US, hardware, linux driver, performance No Comments »

Ouvir com webReader

Although "Linux" seems a little vague, I've seen people complaining about their problems with this HP/Compaq model on almost any distribution. These small-form factor desktops are one of those labeled with Windows-ready logo - and support for in can only be found on HP's forums. Actually, HP clearly states (somewhere) Linux is not supported. But... Read the rest of this entry »

AddThis Social Bookmark Button