Side-effect of mysqlhotcopy and LVM snapshots on active READ server

September 26th, 2009 ntavares Posted in en_US, monitorização, mysql, performance | No Comments »

Ouvir com webReader

I just came across a particular feature of MySQL while using inspecting a Query Cache being wiped out at backup times. Whenever you run FLUSH TABLES, the whole Query Cache gets flushed as well, even if you FLUSH TABLES a particular table. And guess what, mysqlhotcopy issues FLUSH TABLES so the tables get in sync on storage.

I actually noticed the problem with Query Cache on a server reporting the cache flush at a [too] round time (backup time).

flush_tables_affects_query_cache

First thought was «there’s something wrong about mysqlhotcopy. But actually this is expected behaviour:

When no tables are named, closes all open tables, forces all tables in use to be closed, and flushes the query cache. With one or more table names, flushes only the given tables. FLUSH TABLES also removes all query results from the query cache, like the RESET QUERY CACHE statement.

I got curious about why the heck closing a table should invalidate the cache - maybe the “close table” mechanism is overly cautious?

Anyway, it’s not mysqlhotcopy’s fault. And since you should issue FLUSH TABLES for LVM snapshost for consistentency as well, this method is also affected, which renders both methods pretty counter-perfomance in a single production server, comparing to mysqldump, unless you do post-backup warmup process. For that, it would be interesting to be able to dump the QC contents and reload them after the backup - which is not possible, at the moment… bummer…

AddThis Social Bookmark Button

Tamanho e character set de campos VARCHAR e consequências

September 24th, 2009 ntavares Posted in mysql, performance, pt_PT | 3 Comments »

Ouvir com webReader

Apesar do tamanho dos campos VARCHAR não influenciar o tamanho dos índices, este tipo de dados (e outros semelhantes) comportam uma série de características que podem afectar seriamente a performance. Vamos testar o impacto destes campos em dois cenários: com character sets diferentes e com tamanhos diferentes.

Tendo em conta que o storage engine MEMORY apenas trabalha com tamanhos de tuplos fixos (fixed-length rows), e que este engine é o utilizado para tabelas temporárias (uma coisa a evitar, embora nem sempre seja possível), as consequências podem ser desastrosas.

Para esta demonstração, vamos definir o tamanho máximo das tabelas MEMORY para um valor que possamos atingir em alguns segundos, o mínimo:

MySQL:
  1. mysql> SET max_heap_table_size = 1;
  2. Query OK, 0 rows affected, 1 warning (0.00 sec)
  3.  
  4. mysql> SHOW variables like '%heap%';
  5. +---------------------+-------+
  6. | Variable_name       | Value |
  7. +---------------------+-------+
  8. | max_heap_table_size | 16384 |
  9. +---------------------+-------+
  10. 1 row in SET (0.00 sec)

Observamos que o mínimo que conseguimos será 16KB. Vamos ver o que acontece com campos VARCHAR (ie, de comprimento [VAR]iável):

MySQL:
  1. mysql> CREATE TABLE `varchar_small` (
  2.     ->   `id` VARCHAR(36) NOT NULL
  3.     -> ) ENGINE=MEMORY DEFAULT CHARSET=utf8;
  4. Query OK, 0 rows affected (0.01 sec)
  5.  
  6. mysql> INSERT INTO varchar_small VALUES('abcd');
  7. Query OK, 1 row affected (0.00 sec)
  8.  
  9. mysql> -- Vamos enchendo ate nao dar mais....
  10. mysql> INSERT INTO varchar_small SELECT * FROM varchar_small;
  11. ERROR 1114 (HY000): The table 'varchar_small' is full
  12.  
  13. mysql> CREATE TABLE `var_char` (
  14.     ->   `id` VARCHAR(36) NOT NULL
  15.     -> ) ENGINE=MEMORY DEFAULT CHARSET=utf8;
  16. Query OK, 0 rows affected (0.00 sec)
  17.  
  18. mysql> INSERT INTO var_char VALUES('abcdefgh-ijkl-mnop-qrst-uvwxyzabcedf');
  19. Query OK, 1 row affected (0.00 sec)
  20.  
  21. mysql> -- O mesmo: vamos enchendo ate nao dar mais...
  22. mysql> INSERT INTO var_char SELECT * FROM var_char;
  23. ERROR 1114 (HY000): The table 'var_char' is full
  24.  
  25. mysql> SELECT count(1) FROM var_char;
  26. +----------+
  27. | count(1) |
  28. +----------+
  29. |      320 |
  30. +----------+
  31. 1 row in SET (0.00 sec)
  32.  
  33. mysql> SELECT count(1) FROM varchar_small;
  34. +----------+
  35. | count(1) |
  36. +----------+
  37. |      320 |
  38. +----------+
  39. 1 row in SET (0.00 sec)

Ora bem, o que fiz foi preencher a primeira tabela com conteúdo bem menor que 36 carácteres (apenas 4) e, na segunda, conteúdo que preenchesse o campo todo. O que podemos observar é que, neste storage engine, o espaço ocupado por um campo pouco cheio ou muito cheio é sempre o mesmo: é o tamanho total associado ao campo. É isso que significa fixed-length, e isto acarreta como consequência que o tamanho de um campo VARCHAR em tabelas do tipo MEMORY (leia-se também: tabelas temporárias) é invariavelmente o tamanho máximo do campo. Consequentemente, ainda que um tamanho máximo de 255 não influencie o tamanho de índice sobre esse campo, sempre que seja necessário transportar esses dados para tabelas temporárias pode verificar-se um desperdício enorme de espaço (multiplicar esse desperdício pelo número de linhas que terão que ser transportadas para a tabela temporária!).

Mas isto não fica por aqui: para além de cuidado e bom senso na definição do tamanho do campo, é necessário também ter em atenção o seu encoding. O segundo ponto é sobre a influência do charset. Tipicamente trabalhávamos em Latin1, mas com a disseminação da localização (i18n) passou-se praticamente a usar sempre UTF-8. Tudo muito bem, deixámos de ter uma série de problemas com a formatação das sequências de carácteres (vulgo strings). Mas nem tudo são rosas: o charset UTf-8 pode consumir até 4 carácteres (em MySQL, 3), ao passo que Latin1 usava apenas 1:

MySQL:
  1. mysql> CREATE TABLE `varchar_utf8` (
  2.     ->   `id` VARCHAR(36) NOT NULL
  3.     -> ) ENGINE=MEMORY DEFAULT CHARSET=utf8;
  4. Query OK, 0 rows affected (0.01 sec)
  5.  
  6. mysql> CREATE TABLE `varchar_latin1` (
  7.     ->   `id` VARCHAR(36) NOT NULL
  8.     -> ) ENGINE=MEMORY DEFAULT CHARSET=latin1;
  9. Query OK, 0 rows affected (0.00 sec)

A cláusula geral de encoding da tabela propaga-se como default para as colunas. De qualquer forma, poderia especificar o encoding para a coluna de forma individual. Para ter a certeza que não estamos a ser enganados pelo encoding corrente no cliente mysql, usei dois scripts em PHP para criar uma única linha para cada uma destas tabelas (o encoding no meu terminal é UTF-8, como já vai sendo comum):

PHP:
  1. mysql_connect('localhost','root','');
  2. mysql_query("SET NAMES UTF8");
  3. mysql_query("INSERT INTO varchar_utf8 VALUES('ãããããããã-ãããã-ãããã-ãããã-ãããããããããããã')");

PHP:
  1. mysql_connect('localhost','root','');
  2. mysql_query("SET NAMES Latin1");
  3. mysql_query("INSERT INTO varchar_latin1 VALUES('ãããããããã-ãããã-ãããã-ãããã-ãããããããããããã')");
  4. EOF

Gravei os dois scripts acima para varchar_utf8.php e varchar_latin1.php.temp, respectivamente. Por fim, alterei o encoding do script para em Latin1 (porque faça o que fizer, o encoding do meu terminal é UTF-8):

CODE:
  1. [root@speedy ~]# cat varchar_latin1.php.temp | iconv -f utf8 -t iso-8859-1 > varchar_latin1.php

E executei os dois:

CODE:
  1. [root@speedy ~]# php -f varchar_utf8.php
  2. [root@speedy ~]# php -f varchar_latin1.php

OK, agora tenho 1 registo de cada em cada tabela. Vamos usar esse único registo em cada para encher as tabelas até não ser possível adicionar mais registos:

MySQL:
  1. mysql> -- ...
  2. mysql> INSERT INTO varchar_utf8 SELECT * FROM varchar_utf8;
  3. ERROR 1114 (HY000): The table 'mem_utf8' is full
  4.  
  5. mysql> -- ...
  6. INSERT INTO varchar_latin1 SELECT * FROM varchar_latin1;
  7. ERROR 1114 (HY000): The table 'mem_latin1' is full
  8.  
  9. mysql> SELECT count(1) FROM mem_utf8;
  10. +----------+
  11. | count(1) |
  12. +----------+
  13. |      126 |
  14. +----------+
  15. 1 row in SET (0.00 sec)
  16.  
  17. mysql> SELECT count(1) FROM mem_latin1;
  18. +----------+
  19. | count(1) |
  20. +----------+
  21. |      320 |
  22. +----------+
  23. 1 row in SET (0.00 sec)

Pois é, verificámos o segundo problema: no engine MEMORY couberam muito menos linhas na primeira tabela (UTF-8) que na primeira (Latin1) porque que os campos são do seu tamanho máximo possível e, no caso dos VARCHAR em UTF8, cada carácter pode ocupar até 3 bytes. Ora, para caberem 36 carácteres, serão necessários, pelo menos, 36*3 = 108 bytes! Isto representa um consumo de 300% que pode não ter sido estimado no que respeita ao tamanho de memória permitido para tabelas temporárias.

Ambos cenários são bastante frequentes, especialmente em aplicações que usem frameworks - e também nestes casos, pela generalidade de backends que tentam suportar (MySQL, PostgreSQL, Oracle, etc), as queries envolvidas não costumam ser optimizadas para nenhum motor em particular; no caso do MySQL essas queries geram, muitas vezes, tabelas temporárias sem necessidade e optimizar este tipo de queries pode implicar alterações no núcleo da framework ou, no mínimo, criar excepções à regra.

A escolha do nome da coluna nos exemplos acima não foi ao acaso: com efeito, os campos UUID() costumam ser UTF-8 apenas porque toda a tabela é UTF-8 e, imagine-se, não há nenhum carácter num UUID() que não seja ASCII (na verdade, não há nenhum carácter que não seja [a-zA-Z-1-9]!):

MySQL:
  1. mysql> SELECT UUID();
  2. +--------------------------------------+
  3. | UUID()                               |
  4. +--------------------------------------+
  5. | 27d6a670-a64b-11de-866d-0017083bf00f |
  6. +--------------------------------------+
  7. 1 row in SET (0.00 sec)

Como o conjunto de caracteres de um UUID() é charset compatible entre os vários, esta é uma das optimizações que poderão fazer nas vossas aplicações facilmente, contribuindo para o aumento de performance do MySQL sem qualquer impacto na vossa aplicação.

AddThis Social Bookmark Button

Novidades na integração SugarCRM - IPBrick

September 15th, 2009 ntavares Posted in pt_PT, sugarcrm | No Comments »

Ouvir com webReader

Para a versão GA do SugarCRM 5.5, estamos a preparar algumas surpresas para a versão 5.1 da IPBrick:

  • Disponibilização do suporte integrado para qualquer das versões SugarCRM 5.2 e 5.5 (Community Edition, Professional ou Enterprise).
  • Novo método de sincronização/importação de contas e contactos. Este método reduz exponencialmente a velocidade de sincronização: quanto maiores forem os dados a importar, maior será a diferença na rapidez.
  • Graças a este novo método vai ser possível também algum nível de sincronização bidireccional. Na verdade trata-se de uma fusão - tanto quanto possível - de dois registos em alterados em ambos lados. O utilizador poderá controlar como pretende os resultados:
    • sincronização estrita com o IP Contactos
    • sincronização dos comuns (entre IPBrick e SugarCRM) mas preservando os dados isolados do SugarCRM (ie, que não existam no IPBrick), permitindo ao SugarCRM desenvolver autonomia
    • ou sincronização de apenas os dados novos do IP Contactos, preservando por completo os registos do SugarCRM.
  • Melhor integração com o SugarCRM: a nova versão está muito mais robusta no que toca a alterações upgrade safe e a DRI refez a integração para isso mesmo, o que significa que novas versões serão lançadas mais rapidamente.
  • A existência de uma camada de abstracção, possibilitando testar directa e instantaneamente sobre dados reais dos clientes. Ainda será desenvolvida uma funcionalidade de ofuscamento de dados para permitir manter a confidencialidade desses dados.
  • A possibilidade de realizar sobre o capítulo de sincronização uma bateria de testes de validação (unit testing) automatizados. Esta medida vai-nos possibilitar fazer controlo de qualidade antes de cada versão do módulo.
  • E claro, não menos importante, um footprint de memória muito reduzido (< 1MB na linha de comandos);
  • A nova interface de administração, com um assistente que vai explicando os passos a seguir, oferece agora a possibilidade de extrair relatórios da sincronização (ver abaixo) e permite a revisão do resultado final antes de ser fundido com o SugarCRM:

Quadro inicial:
sugar-ipbrick-uirevamp1

O primeiro passo é a importação dos dados do IP Contacts e cruzamento com os dados actuais do SugarCRM. No final do processo será possível rever as operações:
sugar-ipbrick-uirevamp2

Finalmente, o último passo é a fusão:
sugar-ipbrick-uirevamp3

De resto, estamos ainda a afinar os últimos detalhes do módulo de integração com as comunicações unificadas, que será também adaptado para as alterações da IPBrick 5.2.

AddThis Social Bookmark Button

Automatically cleaning up SPAM Wordpress comments

September 6th, 2009 ntavares Posted in dri, en_US, mysql | 2 Comments »

Ouvir com webReader

Doing the maintenance of our blogs (Wordpress), I bumped over one that had fallen on an active botnet. It was receiving like 5 or 6 spam comments per minute. It was nearly the only one in such an harassment, so I suspect the botnet loved it for being open on commenting.

Since I've activated reCaptcha I've been monitoring my "spam folder" and I'm really confident on his guesses, so I just wrote a STORED PROCEDURE to clean up these spam comments on a periodic basis, so I can do a sitewide cleanup:

MySQL:
  1. DELIMITER $$
  2.  
  3. DROP PROCEDURE IF EXISTS `our_blog_db`.`REMOVE_OLD_SPAM`$$
  4. CREATE PROCEDURE `our_blog_db`.`REMOVE_OLD_SPAM` ()
  5.     MODIFIES SQL DATA
  6.     COMMENT 'remove comentarios marcados como SPAM'
  7. BEGIN
  8.  
  9. DECLARE done BIT(1) DEFAULT FALSE;
  10. DECLARE commtbl VARCHAR(50);
  11. DECLARE comments_tbls CURSOR FOR SELECT TABLE_NAME
  12.     FROM information_schema.TABLES  
  13.     WHERE TABLE_SCHEMA = 'our_blog_db' AND TABLE_NAME LIKE '%comments';
  14. DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
  15.  
  16.  
  17. OPEN comments_tbls;
  18.  
  19. REPEAT
  20.     FETCH comments_tbls INTO commtbl;
  21.     SET @next_tbl = CONCAT('DELETE FROM our_blog_db.',commtbl,'
  22.         WHERE comment_approved = "spam"
  23.         AND comment_date_gmt < DATE_SUB(UTC_TIMESTAMP(), INTERVAL 15 DAYS)');
  24.     PREPARE get_next_tbl FROM @next_tbl;
  25.     EXECUTE get_next_tbl;
  26.  
  27. UNTIL done END REPEAT;
  28.  
  29. CLOSE comments_tbls;
  30.  
  31.  
  32. END$$
  33.  
  34. DELIMITER ;

It's very easy to stick it into an EVENT, if you have MySQL 5.1 or bigger, and which to do a daily clean up automatically:

MySQL:
  1. CREATE EVENT `EV_REMOVE_OLD_SPAM` ON SCHEDULE EVERY 1 DAY STARTS '2009-08-01 21:00:00' ON COMPLETION NOT PRESERVE ENABLE
  2. COMMENT 'remove comentarios marcados como SPAM' DO
  3. BEGIN
  4.  
  5. SELECT GET_LOCK('remove_spam',5) INTO @remove_spam_lock;
  6.  
  7. IF @remove_spam_lock THEN
  8.     CALL REMOVE_OLD_SPAM();
  9.  
  10. END IF;
  11.  
  12. END

Enjoy!

AddThis Social Bookmark Button

About cloud computing

September 2nd, 2009 ntavares Posted in en_US, linux driver, scaling | No Comments »

Ouvir com webReader

Last Sunday I commented about Pedro's opinion about cloud computing and thought I could give my blog a reversed trackback :) Here it goes:

I think Pedro's message is important. Cloud marketing and fuzzing seems to be targetted to business decision making personnel. However, no matter what they try to look like, that’s a technical decision and I really think that companies just following this marketing hype will eventually get caught on those small contract letters. As a technician, I agree with Pedro on the enterprise [not] moving its core to the cloud, and that the prices are [still] overrated.

However, for medium-to-large traffic platforms, such that they require a complex setup (meaning >4 machines) cloud can be a solution very similar to what could be called Hardware-as-a-Service. Unavoidabily, you have to move this kind of platforms outside the core, even if they are on a DMZ. More, you don’t usually want to mix corporate traffic with specific platforms (eg. a multinational’s CRM, the company’s website, etc.). In this context, cloud adds as much value as a regular hosting company would do, IMO. No more, no less.

Having said that, I still think it has lots of potential for intermediate companies (and again, this lives in technical scope) to provide HW solutions to costumers by clicking and adding “resources” to a [kind of] shop cart and then split them accordingly to their needs. That’s pretty much how Amazon seems to work – not some VPS/sliced hosting we are getting used to. Also, I see benefit for large hosting companies (now these could be those VPS/sliced ones :) ) because they can turn the income on periodic basis to match the periodic costs. From this intermediate’s perspective, one of the great features of this cloud thing is that they have setup quite heterogeneous provising systems, which a regular company can’t handle – that is to say you could setup a small/medium/full-blown pile of servers with a few clicks. Time also costs money.

Of course, this is all theoretical while the prices remain so high. It seemed even worst from my searches (although I confess I didn’t explore in depth): you will pay much more with cloud to have there available the same resources you can find on typical dedicated hosting servers – but it’s also true you rarely use them at 100%, so you may eventually get more cost/performance benefit in the near future (because when you buy or rent hardware it’s very difficult to recover the cost).

My conclusion is that the cloud is trying to attract customers on the hype, and that makes our technical advice more needed than ever: explain to the client how to plan, how to implement, and how to scale and where exactly the cloud fits in. To them, my recommendation is this: being on the cloud just because “it’s cool” or because it (seems) so simple you won’t need specialized IT staff, will eventually turn against you.

AddThis Social Bookmark Button

SugarCRM data generator

August 31st, 2009 ntavares Posted in performance, pt_PT, sugarcrm | No Comments »

Ouvir com webReader

O SugarCRM dispõe de um gerador de dados para popular a DB com accounts, contacts, etc de exemplo. No entanto, pode ser utilizado para gerar DB's para testes, nomeadamente para testes de carga. Segundo o que percebi, este data generator usa um seed fixo para que os dados gerados para uma DB sejam os mesmos para outra, para que se possam comparar entre si, por exemplo. Eis os passos necessários [usados na versão 5.5]:

Procurar large_scale_test no config.php (próx. à linha 200) e alterar para true:

CODE:
  1. 'large_scale_test' => false,
  2. 'large_scale_test' => true,

Colocar o script seguinte em install/dataGeneratorKit.php. Este script servirá de wrapper para o populateSeedData.php que já dispõe da lógica para popular a DB em termos propocionais:

PHP:
  1. define('sugarEntry',1);
  2. require_once('include/entryPoint.php');
  3. require_once('install/install_utils.php');
  4.  
  5. require_once('modules/TableDictionary.php');
  6.  
  7. require_once "include/database/DBManagerFactory.php";
  8. include "install/populateSeedData.php";
  9. ?>

Talvez seja necessário comentar algumas linhas dos ficheiros i18n, parecidas às que se mostram abaixo (fazem referência a SugarThemeRegistry). Pelo menos em 5.5beta foi:

PHP:
  1. ...
  2. //    'LBL_EMAIL_ADDRESS_BOOK_TITLE_ICON'     => 'getImageURL('icon_email_addressbook.gif').' align=absmiddle border=0> Address Book',
  3. //    'LBL_EMAIL_ADDRESS_BOOK_TITLE_ICON_SHORT'     => 'getImageURL('icon_email_addressbook.gif').' align=absmiddle border=0> Addr...',
  4. ...

Se necessário, alterar os valores no ficheiro install/populateSeedData.php:

PHP:
  1. if($large_scale_test) {
  2.         // increase the cuttoff time to 1 hour
  3.         ini_set("max_execution_time", "3600");
  4.         $number_contacts = 100000;
  5.         $number_companies = 15000;
  6.         $number_leads = 100000;
  7. }

Finalmente, executar:

CODE:
  1. php -f install/dataGeneratorKit.php

Notas

  • Não sei até que ponto o script é eficiente, pois já me crashou com ''memory exhausted'' nos 256MB de memory_limit.

Aproveitei e copiei este artigo para a Wiki da SugarCRM.

AddThis Social Bookmark Button

Multiple field index vs external primary key calculation

August 29th, 2009 ntavares Posted in en_US, mysql, performance | No Comments »

Ouvir com webReader

I bumped over a client which was using a VARCHAR(200) as a MyISAM PRIMARY KEY key for detecting colisions which was externally (by the app) calculated and fed into MySQL. I supsected the reason for this was to [try to] speed up inserts or being fear that there were colisions and not wanting to have unnecessary data fields. The table was just a bunch of e-mail log rows.

The rows were to be quickly processed (selected between a DATETIME range) and archived. Obvious consequences are: big index size, poor performance. Let's see why:

  • The index was the concatenation of some fields, and an extra one: "Message-ID" - which could be not unique, AFAIWT. In the end, the key itself was randomly inserted, since it started with the 'sender' address (see below the maillogsmiorig schema). This obvious lead to poor key insertion (random order), of course, but also to duplicate content in the key itself, as the table engine was MyISAM.
  • More, after the loading, the operations were slower: based on MyISAM. An index would eventually be used, but the data access was unavoidable. That could also be circumvented by InnoDB, since the data is close to the index.

Due to various facts - including that DATETIME field - I suggested migrating to InnoDB with a PK composed of the necessary fields. Moreover, the first field would be the DATETIME which would impose some kind of chronological order for better key insertion - at the same time, the processing was being done for specific intervals of that field.

I've sampled 1M rows of the live data for a test: I wanted to compare one method to my overall solution, which you can check in maillogsinnonew below. I took the opportunity to isolate the storage engine benefit, by running the test for the both versions of both solutions (previous/orig and new):

MySQL:
  1. mysql> SELECT * FROM maillogs INTO OUTFILE '/export/home/gpshumano/teste/maillogsorig.sql';
  2. Query OK, 1000000 rows affected (6.47 sec)
  3.  
  4. mysql> SELECT ip,sender,receiver,date_time,SUBSTRING(chave,LENGTH(SUBSTRING_INDEX(chave,'|',2))+2) FROM maillogsminew INTO OUTFILE '/export/home/gpshumano/teste/maillogsnew.sql';
  5. Query OK, 1000000 rows affected (4.42 sec)
  6.  
  7.  
  8. mysql> CREATE TABLE `maillogsinnonew` (
  9.     ->   `ip` VARCHAR(16) NOT NULL,
  10.     ->   `sender` VARCHAR(320) NOT NULL,
  11.     ->   `receiver` VARCHAR(320) NOT NULL,
  12.     ->   `date_time` DATETIME NOT NULL,
  13.     ->   `message_id` VARCHAR(200) NOT NULL,
  14.     ->   PRIMARY KEY (`date_time`,`sender`,`receiver`,`message_id`)
  15.     -> ) ENGINE=INNODB DEFAULT CHARSET=latin1;
  16. Query OK, 0 rows affected (1.27 sec)
  17.  
  18.  
  19. mysql> CREATE TABLE `maillogsminew` (
  20.     ->   `ip` VARCHAR(16) NOT NULL,
  21.     ->   `sender` VARCHAR(320) NOT NULL,
  22.     ->   `receiver` VARCHAR(320) NOT NULL,
  23.     ->   `date_time` DATETIME NOT NULL,
  24.     ->   `message_id` VARCHAR(200) NOT NULL,
  25.     ->   PRIMARY KEY (`date_time`,`sender`,`receiver`,`message_id`)
  26.     -> ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
  27. Query OK, 0 rows affected (0.13 sec)
  28.  
  29. mysql> CREATE TABLE `maillogsmiorig` (
  30.     ->   `chave` VARCHAR(200) NOT NULL,
  31.     ->   `ip` VARCHAR(16) NOT NULL,
  32.     ->   `sender` VARCHAR(320) NOT NULL,
  33.     ->   `receiver` VARCHAR(320) NOT NULL,
  34.     ->   `date_time` DATETIME NOT NULL,
  35.     ->   PRIMARY KEY (`chave`),
  36.     ->   KEY `maillogs_date_time_idx` (`date_time`)
  37.     -> ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
  38. Query OK, 0 rows affected (0.14 sec)
  39.  
  40. mysql> CREATE TABLE `maillogsinnoorig` (
  41.     ->   `chave` VARCHAR(200) NOT NULL,
  42.     ->   `ip` VARCHAR(16) NOT NULL,
  43.     ->   `sender` VARCHAR(320) NOT NULL,
  44.     ->   `receiver` VARCHAR(320) NOT NULL,
  45.     ->   `date_time` DATETIME NOT NULL,
  46.     ->   PRIMARY KEY (`chave`),
  47.     ->   KEY `maillogs_date_time_idx` (`date_time`)
  48.     -> ) ENGINE=INNODB DEFAULT CHARSET=latin1;
  49. Query OK, 0 rows affected (2.81 sec)
  50.  
  51. mysql> LOAD DATA INFILE '/export/home/gpshumano/teste/maillogsnew.sql' INTO TABLE maillogsminew;
  52. Query OK, 1000000 rows affected (34.83 sec)
  53. Records: 1000000  Deleted: 0  Skipped: 0  WARNINGS: 0
  54.  
  55. mysql> LOAD DATA INFILE '/export/home/gpshumano/teste/maillogsnew.sql' INTO TABLE maillogsinnonew;
  56. Query OK, 1000000 rows affected (1 min 40.56 sec)
  57. Records: 1000000  Deleted: 0  Skipped: 0  WARNINGS: 0
  58.  
  59. mysql> LOAD DATA INFILE '/export/home/gpshumano/teste/maillogsorig.sql' INTO TABLE maillogsinnoorig;
  60. Query OK, 1000000 rows affected (6 min 54.14 sec)
  61. Records: 1000000  Deleted: 0  Skipped: 0  WARNINGS: 0
  62.  
  63. mysql> LOAD DATA INFILE '/export/home/gpshumano/teste/maillogsorig.sql' INTO TABLE maillogsmiorig;
  64. Query OK, 1000000 rows affected (1 min 17.06 sec)
  65. Records: 1000000  Deleted: 0  Skipped: 0  WARNINGS: 0

This was the aftermath. Comparing the results you'll see a 75% gain in speed for InnoDB, and a 55% in MyISAM. More, after the loadings just did an OPTIMIZE TABLE to get more precise table status:

MySQL:
  1. mysql> SHOW table status; 
  2. +----------------------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+
  3. | Name                 | Engine | Version | Row_format | Rows    | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | AUTO_INCREMENT | Create_time         | Update_time         | Check_time          | Collation         | Checksum | Create_options | Comment |
  4. +----------------------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+
  5. | maillogsinnonew      | INNODB |      10 | Compact    | 1048709 |            144 |   152043520 |               0 |            0 |      6144 |           NULL | 2009-08-24 20:00:44 | NULL                | NULL                | latin1_swedish_ci |     NULL |                |         |
  6. | maillogsinnoorig     | INNODB |      10 | Compact    | 1041388 |            392 |   408944640 |               0 |    216006656 |      4096 |           NULL | 2009-08-24 20:01:35 | NULL                | NULL                | latin1_swedish_ci |     NULL |                |         |
  7. | maillogsminew        | MyISAM |      10 | Dynamic    | 1000000 |            126 |   126120088 | 281474976710655 |    100151296 |         0 |           NULL | 2009-08-24 20:00:55 | 2009-08-24 20:06:08 | NULL                | latin1_swedish_ci |     NULL |                |         |
  8. | maillogsmiorig       | MyISAM |      10 | Dynamic    | 1000000 |            173 |   173720872 | 281474976710655 |    166667264 |         0 |           NULL | 2009-08-24 20:01:01 | 2009-08-24 20:18:24 | 2009-08-24 20:18:31 | latin1_swedish_ci |     NULL |                |         |
  9. +----------------------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+
  10. 4 rows in SET (0.03 sec)

CODE:
  1. bash-3.00# ls -lah /iscsipool/DATA/tavares/
  2. total 2288740
  3. drwx------   2 mysql    mysql         13 ago 24 20:37 .
  4. drwxr-xr-x  10 mysql    mysql         17 ago 24 19:45 ..
  5. -rw-rw----   1 mysql    mysql         65 ago 24 19:45 db.opt
  6. -rw-rw----   1 mysql    mysql       8,5K ago 24 20:29 maillogsinnonew.frm
  7. -rw-rw----   1 mysql    mysql       156M ago 24 20:32 maillogsinnonew.ibd
  8. -rw-rw----   1 mysql    mysql       8,5K ago 24 20:31 maillogsinnoorig.frm
  9. -rw-rw----   1 mysql    mysql       420M ago 24 20:40 maillogsinnoorig.ibd
  10. -rw-rw----   1 mysql    mysql       8,5K ago 24 20:00 maillogsminew.frm
  11. -rw-rw----   1 mysql    mysql       120M ago 24 20:06 maillogsminew.MYD
  12. -rw-rw----   1 mysql    mysql        96M ago 24 20:37 maillogsminew.MYI
  13. -rw-rw----   1 mysql    mysql       8,5K ago 24 20:01 maillogsmiorig.frm
  14. -rw-rw----   1 mysql    mysql       166M ago 24 20:18 maillogsmiorig.MYD
  15. -rw-rw----   1 mysql    mysql       159M ago 24 20:37 maillogsmiorig.MYI

As you seen, you not only gain in speed, but also in smaller data files size - 63% for InnoDB and 33% for MyISAM. This smaller sizes also contribute for a longer stay in the buffer pool and reduced I/O. In any case, since the rows were processed immediatelly after the loading, they would probably be all in InnoDB's buffer pool, avoiding the extra I/O to refetch the data from the MyISAM data files.

Also notice as maillogsinnonew considers it's index (PK) size as 0 - that's because in InnoDB the fields in the PK are actually part of the data node itself. In practical terms, this index is just a matter of data ordering, ie, no extra space is required to store the index itself!

AddThis Social Bookmark Button

Update on mysql-query-browser “affected rows”

August 27th, 2009 ntavares Posted in en_US, mysql | No Comments »

Ouvir com webReader

Yesterday I tried massaging mysql-gui-tools a bit to see if I could make affected rows show up on modification DMLs, sucha as INSERT, DELETE and UPDATE. Here is a briefing about it, along with all the patches I used, some taken from the current Fedora 10 source RPM, along a rude one by myself to show affected rows.

Update

Fixing the problem on the root will take a bit more time. This kind of protocol information, such as affected_rows is lost because the guys at MySQL considered that DML changing data never return usefull results - but actually they do: the response protocol packet comes with logs of info, like if you used an index, if you are in a transaction, etc. It could have been due to the protocol changed over time and the Query Browser didn't catchup.

This translates to a lot of fixes: adapting the methods discarding results only when variable result is NULL for not doing that, and find a way to leave affected_rows set somewhere.

So, for the moment, here is a list of patches I used, the bold are my own. The RPM will have to wait, since I wanted to release mysql-gui-tools-5.0r14 (which are the versions you should apply these patches against) instead of the r12 currently available. In the meantime, if I find more patches to submit, may be I have a real look at it. Here are the patches:

AddThis Social Bookmark Button

Compiling mysql-gui-tools

August 27th, 2009 ntavares Posted in en_US, mysql | 1 Comment »

Ouvir com webReader

Me and a colleague were missing some features in mysql-query-browser and am trying to have a look at them, since no one at MySQL AB is very interested in supporting it. So I thought I could have a look at it. System is Fedora 10 (still), and I use it mainly because it's small, simple to use, and it's GTK!

Setting up the building (compile) environment

Got the sources from the notes at MySQL Forge: Building MySQL GUI Tools on Linux:

CODE:
  1. svn co http://svn.mysql.com/svnpublic/mysql-gui-common/trunk mysql-gui-common
  2. svn co http://svn.mysql.com/svnpublic/mysql-query-browser/trunk mysql-query-browser

You'll need a patch from Oden Eriksson attached to Bug #32184, or you can use the one from the RPM - otherwise you'll get the error error: ‘SigC’ has not been declared found on that bug report. I had to cut it for building from the SVN tree, and patched mysql-gui-common and mysql-query-browser independently (split the patch).

Building mysql-gui-common is straightforward:

CODE:
  1. ./autogen.sh
  2. ./configure --prefix=/home/nmct/mysql-query-browser/fake 
  3. make -j 2
  4. make install

Building mysql-query-browser seems to need explicit pointing to the libgtkhtml besides the packages it mentions on error:

CODE:
  1. [root@speedy ~]# rpm -qa | grep gtkhtml | grep devel
  2. gtkhtml3-devel-3.24.5-1.fc10.i386
  3. [root@speedy ~]# rpm -ql gtkhtml3-devel | grep pc
  4. /usr/lib/pkgconfig/gtkhtml-editor.pc
  5. /usr/lib/pkgconfig/libgtkhtml-3.14.pc

So it's easy to spot the needed --with switch. I had to apply several other patches that I just took source RPM. Most of them were applied with -p2.

CODE:
  1. [nmct@speedy mysql-query-browser]$ patch -p 2 < mysql-gui-tools-5.0_p12-libsigc++-2.2.patch
  2. patching file source/linux/MQResultTab.h
  3. [nmct@speedy mysql-query-browser]$ patch -p2 < mysql-gui-tools-gtksourceview-cflags.patch
  4. patching file source/linux/Makefile.in
  5. Hunk #1 succeeded at 119 (offset 17 lines).
  6. [nmct@speedy mysql-query-browser]$ patch -p2 < gtk_deprecated_typedefs.patch
  7. patching file source/linux/gtksourceview/gtksourceview/Makefile.in
  8. ...
  9.  
  10. ...
  11. ./configure --with-gtkhtml=libgtkhtml-3.14 --prefix=/home/nmct/mysql-query-browser/fake
  12. make -j 2
  13. make install

And that should be it - actually there was a path concatenation issue (looking for ...fake/usr/local/share...) which I quickly fixed with symlinks. After that, we should be ready to rock.

First patch: mysql_affected_rows

One of the features I miss most is the number of affected rows of some DML commands, such as UPDATE and INSERT. This was not easy to do in five minutes because of the UI split: mysql_affected_rows() doesn't seem to reach the GUI. So I've made a simple test, and succeeded.

mysql-query-browser-affected-rows1

This looks promising. I just set a global var, which will do for now. I still have to check for potential race conditions, but expect the polished patch, along with a new RPM for Fedora 10, at least, in the near future.

AddThis Social Bookmark Button

Percorrer uma tabela MyISAM de forma controlada

August 26th, 2009 ntavares Posted in mysql, pt_PT | No Comments »

Ouvir com webReader

Lembrei-me de partilhar um teste que fiz há tempos. O que se pretendia era demonstrar que podemos percorrer uma tabela MyISAM (e só neste caso específico) de forma controlada (ie, prever a ordem pela qual os registos são devolvidos) e se esse percurso se mantém em outras operações para além do SELECT, como no caso do DELETE. É fácil pensar numa aplicação: se eu quiser, por exemplo, transladar blocos de registos de uma tabela para outra, torna-se fundamental que a operação DELETE também obedeça ao expectável já que, como sabemos, o MyISAM não é transaccional e, se alguma coisa falhar, queremos poder ter acesso aos registos desta forma determinística para saber o que reverter.

Para alguns pode parecer óbvio, mas sem olhar para o código nunca vamos ter a certeza. Para além disso, o código pode mudar, por isso mais vale termos a certeza :-) Não vamos sequer tentar extrapolar as conclusões para InnoDB porque internamente trabalha de forma completamente diferente. Aliás, um único aspecto da sua arquitectura - o famoso clustered index, que pode ou não ser interno, mas existe sempre! - dá logo para desconfiar que o comportamento seja completamente diferente.

Portanto, na prática, o que se pretende mesmo é ter a certeza que sabemos que registos vão surgindo em várias iterações, e se essa certeza se extrapola para DELETEs (e, eventualmente, para UPDATEs) - ie, tornar o nosso processo determinístico.

Vamos começar com uma tabela simples:

MySQL:
  1. CREATE TABLE `teste` (
  2.   `_int` INT(11) NOT NULL DEFAULT '0',
  3.   `_char` VARCHAR(5) NOT NULL DEFAULT '',
  4.   KEY `idx_int` (`_int`)
  5. ) ENGINE=MyISAM DEFAULT CHARSET=latin1

E inserimos alguns dados:

MySQL:
  1. mysql> INSERT INTO teste VALUES (2,'c'), (1,'e'), (1,'b'), (1,'z'), (2,'b'), (2,'d'),(3,'m');
  2. Query OK, 6 rows affected (0.00 sec)
  3. Records: 6  Duplicates: 0  WARNINGS: 0
  4.  
  5. mysql> SELECT SQL_NO_CACHE * FROM teste;
  6. +------+-------+
  7. | _int | _char |
  8. +------+-------+
  9. |    2 | c     |
  10. |    1 | e     |
  11. |    1 | b     |
  12. |    1 | z     |
  13. |    2 | b     |
  14. |    2 | d     |
  15. |    3 | m     |
  16. +------+-------+
  17. 7 rows in SET (0.00 sec)

A ordem pela qual foram inseridos os registos é fundamental. Podemos observar que este table scan é feito de forma natural, também segundo o Query Optimizer:

MySQL:
  1. mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM teste;
  2. +----+-------------+-------+------+---------------+------+---------+------+------+-------+
  3. | id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra |
  4. +----+-------------+-------+------+---------------+------+---------+------+------+-------+
  5. 1 | SIMPLE      | teste | ALL  | NULL          | NULL | NULL    | NULL |    7 |       |
  6. +----+-------------+-------+------+---------------+------+---------+------+------+-------+
  7. 1 row in SET (0.00 sec)

Perfeito. Estou a pedir os campos todos de cada linha, onde se inclui _char, que não é indexado, e o Optimizer comporta-se como suposto. Mas agora vejamos uma query ligeiramente diferente:

MySQL:
  1. mysql> EXPLAIN SELECT SQL_NO_CACHE _int FROM teste;
  2. +----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
  3. | id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
  4. +----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
  5. 1 | SIMPLE      | teste | index | NULL          | idx_int | 4       | NULL |    7 | USING index |
  6. +----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
  7. 1 row in SET (0.00 sec)

É interessante como o Optimizer reconheceu que, se eu só quero um campo que por acaso até está indexado, então posso obtê-lo directamente do índice (e é isso que significa Using index no campo Extra) evitando ir aos datafiles. Porém isto significa que iremos obter os registos pela ordem do índice:

MySQL:
  1. mysql> SELECT SQL_NO_CACHE _int FROM teste;
  2. +------+
  3. | _int |
  4. +------+
  5. |    1 |
  6. |    1 |
  7. |    1 |
  8. |    2 |
  9. |    2 |
  10. |    2 |
  11. |    3 |
  12. +------+
  13. 7 rows in SET (0.00 sec)

Isto é mais importante que o que possa parecer para este teste. Se eu for obrigado a requisitar mais campos do que esse, o Optimizer vai voltar ao table scan. E não basta colocar um ORDER BY...

MySQL:
  1. mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM teste ORDER BY _int;
  2. +----+-------------+-------+------+---------------+------+---------+------+------+----------------+
  3. | id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra          |
  4. +----+-------------+-------+------+---------------+------+---------+------+------+----------------+
  5. 1 | SIMPLE      | teste | ALL  | NULL          | NULL | NULL    | NULL |    7 | USING filesort |
  6. +----+-------------+-------+------+---------------+------+---------+------+------+----------------+
  7. 1 row in SET (0.00 sec)

... porque o Optimizer pode querer usar uma tabela temporária para fazer a ordenação, que é que nos diz Using filesort no campo Extra. Isto pode parecer uma falha do Optimizer, mas a verdade é que o Optimizer é inteligente o suficiente para determinar que um ''full table scan'' pode ser mais eficiente que percorrer o índice por uma ordem e ir buscar os restantes dados aos data files com localização (nos discos) completamente desordenada, provavelmente aleatória (o I/O manifestar-se-ia imediatamente) - claro que nesta tabela talvez não seja o caso, mas para tabelas muito grandes pode ser desastroso. Assim sendo, teríamos que forçar explicitamente a utilização do índice, já que, pelo menos no meu caso, nem a pista USE INDEX ajudava:

MySQL:
  1. mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM teste FORCE INDEX(idx_int) ORDER BY _int;
  2. +----+-------------+-------+-------+---------------+---------+---------+------+------+-------+
  3. | id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra |
  4. +----+-------------+-------+-------+---------------+---------+---------+------+------+-------+
  5. 1 | SIMPLE      | teste | index | NULL          | idx_int | 4       | NULL |    7 |       |
  6. +----+-------------+-------+-------+---------------+---------+---------+------+------+-------+
  7. 1 row in SET (0.00 sec)

De facto, o Optimizer é tão teimoso que mesmo forçando a utilização do índice ele descarta-o se não usarmos o ORDER BY, pois sabe que, para um table scan a ordem dos registos é indiferente e, como tal, não precisa do índice para nada. Deve haver uma explicação para este comportamento - que vou ter que pesquisar - mas este comportamento interessa-nos e muito: se o Optimizer pegasse ao acaso um índice que lhe parecesse bem, seria difícil obter os registos pela ordem natural sem testar com um EXPLAIN primeiro. Parece-me interessante salientar o seguinte:

MySQL:
  1. mysql> SELECT SQL_NO_CACHE * FROM teste FORCE INDEX(idx_int) ORDER BY _int;
  2. +------+-------+
  3. | _int | _char |
  4. +------+-------+
  5. |    1 | e     |
  6. |    1 | b     |
  7. |    1 | z     |
  8. |    2 | c     |
  9. |    2 | b     |
  10. |    2 | d     |
  11. |    3 | m     |
  12. +------+-------+
  13. 7 rows in SET (0.00 sec)

Ou seja, dentro do índice, quando há colisões, elas são simplesmente adicionadas no fim. Isto significa que, após a ordenação, a ordem pela qual obtemos os registos é... a ordem natural.

Mas pronto, agora sim, podemos assumir, para já, que se percorrermos a tabela com SELECT ... LIMIT 1, podemos ir obtendo registo a registo quer pela ordem natural, quer pela ordem do índice que quisermos. Mas o meu grande problema era na remoção, pois não temos EXPLAIN para o DELETE. Qual dos dois métodos o DELETE utiliza?

MySQL:
  1. mysql> DELETE FROM teste LIMIT 1;
  2. Query OK, 1 row affected (0.00 sec)
  3.  
  4. mysql> SELECT SQL_NO_CACHE * FROM teste;
  5. +------+-------+
  6. | _int | _char |
  7. +------+-------+
  8. |    1 | e     |
  9. |    1 | b     |
  10. |    1 | z     |
  11. |    2 | b     |
  12. |    2 | d     |
  13. |    3 | m     |
  14. +------+-------+
  15. 6 rows in SET (0.00 sec)

Bem, para já, parece ser a ordem natural. Claro que se eu especificar um ORDER BY _int o próximo registo a apagar deveria ser (1,e) - porque é o primeiro no índice idx_int, e sabemos nós que o valor no campo _char será o da ordem natural - resta saber se o Optimizer não pensa que precisa duma tabela temporária para essa ordenação. Eu estou convencido que não, pois como não há selecção de nenhum campo específico, não há porque não utilizar o índice idx_int;. Vamos só confirmar:

MySQL:
  1. mysql> DELETE FROM teste ORDER BY _int LIMIT 1;
  2. Query OK, 1 row affected (0.00 sec)
  3.  
  4. mysql> SELECT SQL_NO_CACHE * FROM teste;
  5. +------+-------+
  6. | _int | _char |
  7. +------+-------+
  8. |    1 | b     |
  9. |    1 | z     |
  10. |    2 | b     |
  11. |    2 | d     |
  12. |    3 | m     |
  13. +------+-------+
  14. 5 rows in SET (0.00 sec)

Tudo bem, conforme previsto. Mas há mais. Raramente há tabelas só com um índice e pode acontecer que o nosso campo _char fosse apanhado por um índice, o que tornaria as coisas um pouco diferentes:

MySQL:
  1. mysql> ALTER TABLE teste ADD KEY idx_char(_char);
  2. Query OK, 5 rows affected (0.00 sec)
  3. Records: 5  Duplicates: 0  WARNINGS: 0
  4.  
  5. mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM teste;
  6. +----+-------------+-------+------+---------------+------+---------+------+------+-------+
  7. | id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra |
  8. +----+-------------+-------+------+---------------+------+---------+------+------+-------+
  9. 1 | SIMPLE      | teste | ALL  | NULL          | NULL | NULL    | NULL |    5 |       |
  10. +----+-------------+-------+------+---------------+------+---------+------+------+-------+
  11. 1 row in SET (0.00 sec)

Acho interessante porque é como se o Optimizer «em caso de dúvida, optasse por nenhum», ou seja, como desconhece um critério para escolher um ou outro índice, não usa nenhum.

Na verdade, o Optimizer não usa nenhum índice porque não tem nenhuma pista sobre qual agarrar. Por norma irá utilizar o de maior cardinalidade, para as pistas que tiver disponíveis:

MySQL:
  1. mysql> OPTIMIZE TABLE teste;
  2. +------------+----------+----------+----------+
  3. | Table   | Op      | Msg_type | Msg_text
  4. +------------+----------+----------+----------+
  5. | test.teste | OPTIMIZE | status   | OK    
  6. +------------+----------+----------+----------+
  7. 1 row in SET (0.00 sec)
  8.  
  9. mysql> SHOW INDEXES FROM teste;
  10. +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
  11. | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | NULL | Index_type | Comment
  12. +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
  13. | teste |        1 | idx_int  |         1 | _int      | A         |       2 |     NULL | NULL   |      | BTREE     |        
  14. | teste |        1 | idx_char |         1 | _char     | A         |       5 |     NULL | NULL   |      | BTREE     |        
  15. +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
  16. 2 rows in SET (0.00 sec)
  17.  
  18. mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM teste WHERE _int = 1 AND _char = 'e';
  19. +----+-------------+-------+------+------------------+----------+---------+-------+------+-------------+
  20. | id | select_type | table | type | possible_keys   | key    | key_len | ref   | rows | Extra      
  21. +----+-------------+-------+------+------------------+----------+---------+-------+------+-------------+
  22. 1 | SIMPLE     | teste | ref  | idx_int,idx_char | idx_char | 7    | const | 1 | USING WHERE
  23. +----+-------------+-------+------+------------------+----------+---------+-------+------+-------------+
  24. 1 row in SET (0.00 sec)

Ou seja, idx_char é utilizado para a filtragem e, como tinha potencial para filtrar mais registos, é esse índice o escolhido pelo Optimizer, que nos diz ainda que vai precisar de percorrer os datafiles para filtrar o campo idx_int (Using where).

Eu sei que o * é expandido para os campos todos pelo Optimizer; então e se houvesse um covering index?

MySQL:
  1. mysql> ALTER TABLE teste ADD KEY idx_int_char(_int,_char);
  2. Query OK, 5 rows affected (0.01 sec)
  3. Records: 5  Duplicates: 0  WARNINGS: 0
  4.  
  5. mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM teste;
  6. +----+-------------+-------+-------+---------------+--------------+---------+------+------+-------------+
  7. | id | select_type | table | type  | possible_keys | key          | key_len | ref  | rows | Extra      
  8. +----+-------------+-------+-------+---------------+--------------+---------+------+------+-------------+
  9. 1 | SIMPLE     | teste | index | NULL        | idx_int_char | 11    | NULL |    5 | USING index
  10. +----+-------------+-------+-------+---------------+--------------+---------+------+------+-------------+
  11. 1 row in SET (0.00 sec)

Como era de esperar, o índice será usado. Vamos ver o que acontece com o DELETE. Para testar, forçamos os registos a mudar a posição no índice (mantendo a ordem natural) e vamos criar outra tabela similar, com os mesmos registos, porque vai-nos interessar esta ordem mais abaixo:

MySQL:
  1. mysql> UPDATE teste SET _int = 4 WHERE _int = 1 AND _char = 'b'; UPDATE teste SET _int = 5, _char = 'f' WHERE _int = 2 AND _char = 'b';
  2.  
  3. mysql> CREATE TABLE teste3 like teste;
  4. Query OK, 0 rows affected (0.00 sec)
  5.  
  6. mysql> INSERT INTO teste3 SELECT * FROM teste IGNORE INDEX(idx_int_char);
  7. Query OK, 5 rows affected (0.00 sec)
  8. Records: 5  Duplicates: 0  WARNINGS: 0
  9.  
  10. mysql> SELECT * FROM teste3 IGNORE INDEX(idx_int_char);
  11. +------+-------+
  12. | _int | _char |
  13. +------+-------+
  14. |    4 | b     |
  15. |    1 | z     |
  16. |    5 | f     |
  17. |    2 | d     |
  18. |    3 | m     |
  19. +------+-------+
  20. 5 rows in SET (0.00 sec)
  21.  
  22. mysql> DELETE FROM teste3 LIMIT 1;
  23. Query OK, 1 row affected (0.00 sec)
  24.  
  25. mysql> SELECT * FROM teste3 IGNORE INDEX(idx_int_char);
  26. +------+-------+
  27. | _int | _char |
  28. +------+-------+
  29. |    1 | z     |
  30. |    5 | f     |
  31. |    2 | d     |
  32. |    3 | m     |
  33. +------+-------+
  34. 4 rows in SET (0.00 sec)

Ou seja, com o SELECT antes e depois temos oportunidade de comprovar que o DELETE não pegou nenhum índice, nem mesmo o covering index.

Mas voltando atrás, à tabela teste, será que o facto de se usar uma PRIMARY KEY (que é única e não nula) influencia os resultados? Esta pergunta provavelmente só será pertinente para quem conheça o InnoDB.

MySQL:
  1. mysql> ALTER TABLE teste DROP KEY idx_int;
  2. Query OK, 5 rows affected (0.01 sec)
  3. Records: 5  Duplicates: 0  WARNINGS: 0
  4.  
  5. mysql> ALTER TABLE teste ADD PRIMARY KEY(_int);
  6. Query OK, 5 rows affected (0.01 sec)
  7. Records: 5  Duplicates: 0  WARNINGS: 0
  8.  
  9. mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM teste;
  10. +----+-------------+-------+------+---------------+------+---------+------+------+-------+
  11. | id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra |
  12. +----+-------------+-------+------+---------------+------+---------+------+------+-------+
  13. 1 | SIMPLE      | teste | ALL  | NULL          | NULL | NULL    | NULL |    5 |       |
  14. +----+-------------+-------+------+---------------+------+---------+------+------+-------+
  15. 1 row in SET (0.00 sec)

Muito bem, nada a assinalar.

Mas nós também sabemos o que acontece com o MyISAM: após os DELETEs começam a surgir buracos nos data files (que, consequentemente, acabam por interferir com as inserções em concorrência). Vejamos. Recriei a tabela teste com o dataset original, e fiz os dois DELETE que fizémos até aqui. A seguir:

MySQL:
  1. mysql> SELECT @@concurrent_insert;
  2. +---------------------+
  3. | @@concurrent_insert
  4. +---------------------+
  5. |               1
  6. +---------------------+
  7. 1 row in SET (0.00 sec)
  8.  
  9. mysql> INSERT INTO teste VALUES (6,'e'),(5,'f'),(4,'g');
  10. Query OK, 3 rows affected (0.00 sec)
  11. Records: 3  Duplicates: 0  WARNINGS: 0
  12.  
  13. mysql> SELECT SQL_NO_CACHE * FROM teste;
  14. +------+-------+
  15. | _int | _char |
  16. +------+-------+
  17. |   5 | f     |
  18. |   6 | e     |
  19. |   1 | b     |
  20. |   1 | z     |
  21. |   2 | b     |
  22. |   2 | d     |
  23. |   3 | m     |
  24. |   4 | g     |
  25. +------+-------+
  26. 8 rows in SET (0.00 sec)

Repare-se que os dois primeiros registos ficaram fora da ordem natural pois o MyISAM conseguiu reciclar o espaço livre, exactamente nos mesmos sítios. O terceiro elemento calhou no único sítio onde cabia: no fim. Esta também é uma observação importante porque se houverem escritas na tabela durante as operações, então é preciso um cuidado especial. Este cuidado agrava-se pelo facto de nem conseguirmos desactivar este comportamento. Recrie-se novamente a tabela teste, com o INSERT inicial, depois os dois DELETEs feitos até agora, e depois o INSERT do teste anterior:

MySQL:
  1. mysql> SET global concurrent_insert = 0;
  2. Query OK, 0 rows affected (0.00 sec)
  3.  
  4. mysql> INSERT INTO teste VALUES (6,'e'),(5,'f'),(4,'g');
  5. Query OK, 3 rows affected (0.00 sec)
  6. Records: 3  Duplicates: 0  WARNINGS: 0
  7.  
  8. mysql> SELECT SQL_NO_CACHE * FROM teste;
  9. +------+-------+
  10. | _int | _char |
  11. +------+-------+
  12. |   5 | f     |
  13. |   6 | e     |
  14. |   1 | b     |
  15. |   1 | z     |
  16. |   2 | b     |
  17. |   2 | d     |
  18. |   3 | m     |
  19. |   4 | g     |
  20. +------+-------+
  21. 8 rows in SET (0.00 sec)

Exactamente o mesmo. Perde-se, portanto, a ordem natural.

Nota sobre o InnoDB

A título de nota, deixa-se uma breve explicação do porquê de não analisarmos o caso do InnoDB:

MySQL:
  1. mysql> CREATE TABLE teste2 like teste;
  2. Query OK, 0 rows affected (0.00 sec)
  3.  
  4. mysql> INSERT INTO teste2 SELECT * FROM teste;
  5. Query OK, 5 rows affected (0.01 sec)
  6. Records: 5  Duplicates: 0  WARNINGS: 0
  7.  
  8. mysql> UPDATE teste2 SET _char = 'k' WHERE _int = 1 and _char = 'b';
  9. Query OK, 1 row affected (0.03 sec)
  10. Rows matched: 1  Changed: 1  WARNINGS: 0
  11.  
  12. mysql> ALTER TABLE teste2 engine=INNODB, DROP INDEX idx_int, DROP INDEX idx_char, DROP INDEX idx_int_char;
  13. Query OK, 5 rows affected (0.01 sec)
  14. Records: 5  Duplicates: 0  WARNINGS: 0
  15.  
  16. mysql> ALTER TABLE teste2 add PRIMARY KEY (_char);
  17. Query OK, 5 rows affected (0.02 sec)
  18. Records: 5  Duplicates: 0  WARNINGS: 0
  19.  
  20. mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM teste2;
  21. +----+-------------+--------+------+---------------+------+---------+------+------+-------+
  22. | id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows | Extra
  23. +----+-------------+--------+------+---------------+------+---------+------+------+-------+
  24. 1 | SIMPLE     | teste2 | ALL  | NULL        | NULL | NULL    | NULL |    5 |    
  25. +----+-------------+--------+------+---------------+------+---------+------+------+-------+
  26. 1 row in SET (0.00 sec)
  27.  
  28. mysql> SELECT * FROM teste2;
  29. +------+-------+
  30. | _int | _char |
  31. +------+-------+
  32. |   2 | b     |
  33. |   2 | d     |
  34. |   1 | k     |
  35. |   3 | m     |
  36. |   1 | z     |
  37. +------+-------+
  38. 5 rows in SET (0.00 sec)

O que queria que reparassem é que não há indicação de estar a ser utilizada nenhuma chave e, no entanto, os registos vêm ordenados pela PRIMARY KEY. Como disse logo no início, com InnoDB os resultados seriam diferentes, e isto deve-se ao clustered index deste storage engine, que armazena os registos (fiscamente nos datafiles) pela ordem da chave (a famosa primary key order). Como consequência, não é possível ter acesso aos registos na forma natural (ie, pela ordem em que foram inseridos) - e, de forma consistente com o que tínhamos visto para MyISAM, o Optimizer prefere não usar nenhum índice, passando para o storage engine a mesma query expandida.

Penso que consegui demonstrar com alguma segurança que podemos obter registos com SELECT de uma forma controlada. Dependendo do cenário, nem sempre se torna possível usar um ou outro método (também pode não ser possível criar um novo índice, por exemplo) pelo que antes de tirar conclusões precipitadas, o melhor é usar o EXPLAIN para explorar as possibilidades!

AddThis Social Bookmark Button