Optimizando consultas de MySQL con explain

La performance de un servidor de bases de datos no siempre esta relacionada únicamente con la configuración, y la degradación de la misma muchas veces esta directamente ligada a las consultas que estamos recibiendo en el servidor, por eso vamos a hacer una breve guia de ocmo optimizar consultas de una forma sencilla.

Supongamos que tenemos una tabla con los equipos del mundial de rusia 2018 y una tabla con todos los equipos y sus grupos, donde queremos obtener los equipos por grupos.

MariaDB [worldcup]> desc teams;
+-----------+------------------+------+-----+---------+----------------+
| Field     | Type             | Null | Key | Default | Extra          |
+-----------+------------------+------+-----+---------+----------------+
| id        | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| teamName  | varchar(32)      | YES  |     | NULL    |                |
| groupname | char(1)          | YES  |     | NULL    |                |
+-----------+------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

MariaDB [worldcup]> select * from teams where groupname="C";
+----+-----------+-----------+
| id | teamName  | groupname |
+----+-----------+-----------+
|  9 | Francia   | C         |
| 10 | Perú      | C         |
| 11 | Dinamarca | C         |
| 12 | Australia | C         |
+----+-----------+-----------+
4 rows in set (0.00 sec)

MariaDB [worldcup]> 

MariaDB Audit Plugin con Percona Server 5.6.24-72.2

tar zxvf server_audit-1.2.0.tar.gz 
server_audit-1.2.0/
server_audit-1.2.0/linux-32_debug/
server_audit-1.2.0/linux-32_debug/server_audit.so
server_audit-1.2.0/linux-32/
server_audit-1.2.0/linux-32/server_audit.so
server_audit-1.2.0/linux-64_debug/
server_audit-1.2.0/linux-64_debug/server_audit.so
server_audit-1.2.0/windows-32/
server_audit-1.2.0/windows-32/server_audit.dll
server_audit-1.2.0/windows-64_debug/
server_audit-1.2.0/windows-64_debug/server_audit.dll
server_audit-1.2.0/linux-64/
server_audit-1.2.0/linux-64/server_audit.so
server_audit-1.2.0/windows-64/
server_audit-1.2.0/windows-64/server_audit.dll
server_audit-1.2.0/windows-32_debug/
server_audit-1.2.0/windows-32_debug/server_audit.dll
tomas@binlogic:~$ mysql -uroot -pbinlogic
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 46
Server version: 5.6.24-72.2 Percona Server (GPL), Release 72.2, Revision 8d0f85b

Copyright (c) 2009-2015 Percona LLC and/or its affiliates
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show global variables like 'plugin_dir';
+---------------+------------------------+
| Variable_name | Value                  |
+---------------+------------------------+
| plugin_dir    | /usr/lib/mysql/plugin/ |
+---------------+------------------------+
1 row in set (0.00 sec)

mysql> exit
Bye
tomas@binlogic:~$ ls
Desktop  Documents  Downloads  examples.desktop  Music  Pictures  Public  server_audit-1.2.0  server_audit-1.2.0.tar.gz  Templates  Videos
tomas@binlogic:~$ cd server_audit-1.2.0/
tomas@binlogic:~/server_audit-1.2.0$ ls
linux-32  linux-32_debug  linux-64  linux-64_debug  windows-32  windows-32_debug  windows-64  windows-64_debug
tomas@binlogic:~/server_audit-1.2.0$ cd linux-64
tomas@binlogic:~/server_audit-1.2.0/linux-64$ ls
server_audit.so
tomas@binlogic:~/server_audit-1.2.0/linux-64$ cp -R server_audit.so /usr/lib/mysql/plugin/
cp: cannot create regular file ‘/usr/lib/mysql/plugin/server_audit.so’: Permission denied
tomas@binlogic:~/server_audit-1.2.0/linux-64$ sudo cp -R server_audit.so /usr/lib/mysql/plugin/
tomas@binlogic:~/server_audit-1.2.0/linux-64$ mysql -uroot -pbinlogic
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 47
Server version: 5.6.24-72.2 Percona Server (GPL), Release 72.2, Revision 8d0f85b

Copyright (c) 2009-2015 Percona LLC and/or its affiliates
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> install plugin server_audit SONAME 'server_audit.so';
Query OK, 0 rows affected (0.34 sec)

mysql> select * from information_schema.plugins;
+-----------------------------+----------------+---------------+--------------------+---------------------+-----------------+------------------------+--------------------------------------------+----------------------------------------------------------------------------+----------------+-------------+
| PLUGIN_NAME                 | PLUGIN_VERSION | PLUGIN_STATUS | PLUGIN_TYPE        | PLUGIN_TYPE_VERSION | PLUGIN_LIBRARY  | PLUGIN_LIBRARY_VERSION | PLUGIN_AUTHOR                              | PLUGIN_DESCRIPTION                                                         | PLUGIN_LICENSE | LOAD_OPTION |
+-----------------------------+----------------+---------------+--------------------+---------------------+-----------------+------------------------+--------------------------------------------+----------------------------------------------------------------------------+----------------+-------------+
| binlog                      | 1.0            | ACTIVE        | STORAGE ENGINE     | 50624.0             | NULL            | NULL                   | MySQL AB                                   | This is a pseudo storage engine to represent the binlog in a transaction   | GPL            | FORCE       |
| mysql_native_password       | 1.0            | ACTIVE        | AUTHENTICATION     | 1.0                 | NULL            | NULL                   | R.J.Silk, Sergei Golubchik                 | Native MySQL authentication                                                | GPL            | FORCE       |
| mysql_old_password          | 1.0            | ACTIVE        | AUTHENTICATION     | 1.0                 | NULL            | NULL                   | R.J.Silk, Sergei Golubchik                 | Old MySQL-4.0 authentication                                               | GPL            | FORCE       |
| sha256_password             | 1.0            | ACTIVE        | AUTHENTICATION     | 1.0                 | NULL            | NULL                   | Oracle                                     | SHA256 password authentication                                             | GPL            | FORCE       |
| MRG_MYISAM                  | 1.0            | ACTIVE        | STORAGE ENGINE     | 50624.0             | NULL            | NULL                   | MySQL AB                                   | Collection of identical MyISAM tables                                      | GPL            | FORCE       |
| CSV                         | 1.0            | ACTIVE        | STORAGE ENGINE     | 50624.0             | NULL            | NULL                   | Brian Aker, MySQL AB                       | CSV storage engine                                                         | GPL            | FORCE       |
| MyISAM                      | 1.0            | ACTIVE        | STORAGE ENGINE     | 50624.0             | NULL            | NULL                   | MySQL AB                                   | MyISAM storage engine                                                      | GPL            | FORCE       |
| MEMORY                      | 1.0            | ACTIVE        | STORAGE ENGINE     | 50624.0             | NULL            | NULL                   | MySQL AB                                   | Hash based, stored in memory, useful for temporary tables                  | GPL            | FORCE       |
| BLACKHOLE                   | 1.0            | ACTIVE        | STORAGE ENGINE     | 50624.0             | NULL            | NULL                   | MySQL AB                                   | /dev/null storage engine (anything you write to it disappears)             | GPL            | ON          |
| PERFORMANCE_SCHEMA          | 0.1            | ACTIVE        | STORAGE ENGINE     | 50624.0             | NULL            | NULL                   | Marc Alff, Oracle                          | Performance Schema                                                         | GPL            | FORCE       |
| FEDERATED                   | 1.0            | DISABLED      | STORAGE ENGINE     | 50624.0             | NULL            | NULL                   | Patrick Galbraith and Brian Aker, MySQL AB | Federated MySQL storage engine                                             | GPL            | OFF         |
| InnoDB                      | 5.6            | ACTIVE        | STORAGE ENGINE     | 50624.0             | NULL            | NULL                   | Oracle Corporation                         | Percona-XtraDB, Supports transactions, row-level locking, and foreign keys | GPL            | ON          |
| XTRADB_READ_VIEW            | 5.6            | ACTIVE        | INFORMATION SCHEMA | 50624.0             | NULL            | NULL                   | Percona Inc.                               | InnoDB Read View information                                               | GPL            | ON          |
| XTRADB_INTERNAL_HASH_TABLES | 5.6            | ACTIVE        | INFORMATION SCHEMA | 50624.0             | NULL            | NULL                   | Percona Inc.                               | InnoDB internal hash tables information                                    | GPL            | ON          |
| XTRADB_RSEG                 | 5.6            | ACTIVE        | INFORMATION SCHEMA | 50624.0             | NULL            | NULL                   | Percona Inc.                               | InnoDB rollback segment information                                        | GPL            | ON          |
| INNODB_TRX                  | 5.6            | ACTIVE        | INFORMATION SCHEMA | 50624.0             | NULL            | NULL                   | Oracle Corporation                         | InnoDB transactions                                                        | GPL            | ON          |
| INNODB_LOCKS                | 5.6            | ACTIVE        | INFORMATION SCHEMA | 50624.0             | NULL            | NULL                   | Oracle Corporation                         | InnoDB conflicting locks                                                   | GPL            | ON          |
| INNODB_LOCK_WAITS           | 5.6            | ACTIVE        | INFORMATION SCHEMA | 50624.0             | NULL            | NULL                   | Oracle Corporation                         | InnoDB which lock is blocking which                                        | GPL            | ON          |
| INNODB_CMP                  | 5.6            | ACTIVE        | INFORMATION SCHEMA | 50624.0             | NULL            | NULL                   | Oracle Corporation                         | Statistics for the InnoDB compression                                      | GPL            | ON          |
| INNODB_CMP_RESET            | 5.6            | ACTIVE        | INFORMATION SCHEMA | 50624.0             | NULL            | NULL                   | Oracle Corporation                         | Statistics for the InnoDB compression; reset cumulated counts              | GPL            | ON          |
| INNODB_CMPMEM               | 5.6            | ACTIVE        | INFORMATION SCHEMA | 50624.0             | NULL            | NULL                   | Oracle Corporation                         | Statistics for the InnoDB compressed buffer pool                           | GPL            | ON          |
| INNODB_CMPMEM_RESET         | 5.6            | ACTIVE        | INFORMATION SCHEMA | 50624.0             | NULL            | NULL                   | Oracle Corporation                         | Statistics for the InnoDB compressed buffer pool; reset cumulated counts   | GPL            | ON          |
| INNODB_CMP_PER_INDEX        | 5.6            | ACTIVE        | INFORMATION SCHEMA | 50624.0             | NULL            | NULL                   | Oracle Corporation                         | Statistics for the InnoDB compression (per index)                          | GPL            | ON          |
| INNODB_CMP_PER_INDEX_RESET  | 5.6            | ACTIVE        | INFORMATION SCHEMA | 50624.0             | NULL            | NULL                   | Oracle Corporation                         | Statistics for the InnoDB compression (per index); reset cumulated counts  | GPL            | ON          |
| INNODB_BUFFER_PAGE          | 5.6            | ACTIVE        | INFORMATION SCHEMA | 50624.0             | NULL            | NULL                   | Oracle Corporation                         | InnoDB Buffer Page Information                                             | GPL            | ON          |
| INNODB_BUFFER_PAGE_LRU      | 5.6            | ACTIVE        | INFORMATION SCHEMA | 50624.0             | NULL            | NULL                   | Oracle Corporation                         | InnoDB Buffer Page in LRU                                                  | GPL            | ON          |
| INNODB_BUFFER_POOL_STATS    | 5.6            | ACTIVE        | INFORMATION SCHEMA | 50624.0             | NULL            | NULL                   | Oracle Corporation                         | InnoDB Buffer Pool Statistics Information                                  | GPL            | ON          |
| INNODB_METRICS              | 5.6            | ACTIVE        | INFORMATION SCHEMA | 50624.0             | NULL            | NULL                   | Oracle Corporation                         | InnoDB Metrics Info                                                        | GPL            | ON          |
| INNODB_FT_DEFAULT_STOPWORD  | 5.6            | ACTIVE        | INFORMATION SCHEMA | 50624.0             | NULL            | NULL                   | Oracle Corporation                         | Default stopword list for InnDB Full Text Search                           | GPL            | ON          |
| INNODB_FT_DELETED           | 5.6            | ACTIVE        | INFORMATION SCHEMA | 50624.0             | NULL            | NULL                   | Oracle Corporation                         | INNODB AUXILIARY FTS DELETED TABLE                                         | GPL            | ON          |
| INNODB_FT_BEING_DELETED     | 5.6            | ACTIVE        | INFORMATION SCHEMA | 50624.0             | NULL            | NULL                   | Oracle Corporation                         | INNODB AUXILIARY FTS BEING DELETED TABLE                                   | GPL            | ON          |
| INNODB_FT_CONFIG            | 5.6            | ACTIVE        | INFORMATION SCHEMA | 50624.0             | NULL            | NULL                   | Oracle Corporation                         | INNODB AUXILIARY FTS CONFIG TABLE                                          | GPL            | ON          |
| INNODB_FT_INDEX_CACHE       | 5.6            | ACTIVE        | INFORMATION SCHEMA | 50624.0             | NULL            | NULL                   | Oracle Corporation                         | INNODB AUXILIARY FTS INDEX CACHED                                          | GPL            | ON          |
| INNODB_FT_INDEX_TABLE       | 5.6            | ACTIVE        | INFORMATION SCHEMA | 50624.0             | NULL            | NULL                   | Oracle Corporation                         | INNODB AUXILIARY FTS INDEX TABLE                                           | GPL            | ON          |
| INNODB_SYS_TABLES           | 5.6            | ACTIVE        | INFORMATION SCHEMA | 50624.0             | NULL            | NULL                   | Oracle Corporation                         | InnoDB SYS_TABLES                                                          | GPL            | ON          |
| INNODB_SYS_TABLESTATS       | 5.6            | ACTIVE        | INFORMATION SCHEMA | 50624.0             | NULL            | NULL                   | Oracle Corporation                         | InnoDB SYS_TABLESTATS                                                      | GPL            | ON          |
| INNODB_SYS_INDEXES          | 5.6            | ACTIVE        | INFORMATION SCHEMA | 50624.0             | NULL            | NULL                   | Oracle Corporation                         | InnoDB SYS_INDEXES                                                         | GPL            | ON          |
| INNODB_SYS_COLUMNS          | 5.6            | ACTIVE        | INFORMATION SCHEMA | 50624.0             | NULL            | NULL                   | Oracle Corporation                         | InnoDB SYS_COLUMNS                                                         | GPL            | ON          |
| INNODB_SYS_FIELDS           | 5.6            | ACTIVE        | INFORMATION SCHEMA | 50624.0             | NULL            | NULL                   | Oracle Corporation                         | InnoDB SYS_FIELDS                                                          | GPL            | ON          |
| INNODB_SYS_FOREIGN          | 5.6            | ACTIVE        | INFORMATION SCHEMA | 50624.0             | NULL            | NULL                   | Oracle Corporation                         | InnoDB SYS_FOREIGN                                                         | GPL            | ON          |
| INNODB_SYS_FOREIGN_COLS     | 5.6            | ACTIVE        | INFORMATION SCHEMA | 50624.0             | NULL            | NULL                   | Oracle Corporation                         | InnoDB SYS_FOREIGN_COLS                                                    | GPL            | ON          |
| INNODB_SYS_TABLESPACES      | 5.6            | ACTIVE        | INFORMATION SCHEMA | 50624.0             | NULL            | NULL                   | Oracle Corporation                         | InnoDB SYS_TABLESPACES                                                     | GPL            | ON          |
| INNODB_SYS_DATAFILES        | 5.6            | ACTIVE        | INFORMATION SCHEMA | 50624.0             | NULL            | NULL                   | Oracle Corporation                         | InnoDB SYS_DATAFILES                                                       | GPL            | ON          |
| INNODB_CHANGED_PAGES        | 1.0            | ACTIVE        | INFORMATION SCHEMA | 50624.0             | NULL            | NULL                   | Percona                                    | InnoDB CHANGED_PAGES table                                                 | GPL            | ON          |
| ARCHIVE                     | 3.0            | ACTIVE        | STORAGE ENGINE     | 50624.0             | NULL            | NULL                   | Brian Aker, MySQL AB                       | Archive storage engine                                                     | GPL            | ON          |
| partition                   | 1.0            | ACTIVE        | STORAGE ENGINE     | 50624.0             | NULL            | NULL                   | Mikael Ronstrom, MySQL AB                  | Partition Storage Engine Helper                                            | GPL            | ON          |
| SERVER_AUDIT                | 1.2            | ACTIVE        | AUDIT              | 3.2                 | server_audit.so | 1.3                    |  Alexey Botchkov (MariaDB Corporation)     | Audit the server activity                                                  | GPL            | ON          |
+-----------------------------+----------------+---------------+--------------------+---------------------+-----------------+------------------------+--------------------------------------------+----------------------------------------------------------------------------+----------------+-------------+
47 rows in set (0.10 sec)

mysql> set global server_audit_events='CONNECT,QUERY,TABLE';
Query OK, 0 rows affected (0.03 sec)

mysql> SHOW GLOBAL VARIABLES LIKE 'server_audit%';
+-------------------------------+-----------------------+
| Variable_name                 | Value                 |
+-------------------------------+-----------------------+
| server_audit_events           | CONNECT,QUERY,TABLE   |
| server_audit_excl_users       |                       |
| server_audit_file_path        | server_audit.log      |
| server_audit_file_rotate_now  | OFF                   |
| server_audit_file_rotate_size | 1000000               |
| server_audit_file_rotations   | 9                     |
| server_audit_incl_users       |                       |
| server_audit_logging          | OFF                   |
| server_audit_mode             | 1                     |
| server_audit_output_type      | file                  |
| server_audit_syslog_facility  | LOG_USER              |
| server_audit_syslog_ident     | mysql-server_auditing |
| server_audit_syslog_info      |                       |
| server_audit_syslog_priority  | LOG_INFO              |
+-------------------------------+-----------------------+
14 rows in set (0.04 sec)

mysql> set global server_audit_logging=ON
    -> ;
Query OK, 0 rows affected (0.03 sec)

mysql> SHOW GLOBAL VARIABLES LIKE 'server_audit%';
+-------------------------------+-----------------------+
| Variable_name                 | Value                 |
+-------------------------------+-----------------------+
| server_audit_events           | CONNECT,QUERY,TABLE   |
| server_audit_excl_users       |                       |
| server_audit_file_path        | server_audit.log      |
| server_audit_file_rotate_now  | OFF                   |
| server_audit_file_rotate_size | 1000000               |
| server_audit_file_rotations   | 9                     |
| server_audit_incl_users       |                       |
| server_audit_logging          | ON                    |
| server_audit_mode             | 1                     |
| server_audit_output_type      | file                  |
| server_audit_syslog_facility  | LOG_USER              |
| server_audit_syslog_ident     | mysql-server_auditing |
| server_audit_syslog_info      |                       |
| server_audit_syslog_priority  | LOG_INFO              |
+-------------------------------+-----------------------+
14 rows in set (0.00 sec)

mysql> select version();
+-------------+
| version()   |
+-------------+
| 5.6.24-72.2 |
+-------------+
1 row in set (0.06 sec)

mysql> 

root@binlogic:/var/lib/mysql# ls
auto.cnf  debian-5.6.flag  ibdata1  ib_logfile0  ib_logfile1  mysql  mysql_upgrade_info  performance_schema  server_audit.log  test
root@binlogic:/var/lib/mysql# 

root@binlogic:/var/lib/mysql# tail -f server_audit.log 
20150601 14:44:29,binlogic,root,localhost,47,5,QUERY,,'set global server_audit_logging=ON',0
20150601 14:44:31,binlogic,root,localhost,47,6,QUERY,,'SHOW GLOBAL VARIABLES LIKE \'server_audit%\'',0
20150601 14:45:45,binlogic,root,localhost,47,7,QUERY,,'select version()',0
20150601 14:46:00,binlogic,root,localhost,47,0,DISCONNECT,,,0

Entendiendo el binlog

El archivo mysql binlog es uno de los logs que contiene todos los cambios que se realizan dentro de nuestro servidor de base de datos.
Estos cambios son, los insert, deletes, updates, drop, y create.
Este log es usado para la replicacion de mysql como tambien para realizar recovery de la base de datos.
Los logs estan guardados en un formato binario y se los puede ver unicamente con el programa cliente mysqlbinlog.

Formato del log

Hay tres posibles formatoS:

Statement
Row Based
Mixed (Recomendado)

Este formato puede ser seteado desde el my.cnf con la variable “binlog_format=xxxxx ”

Analizado los campos y tipos de datos en MariaDB

Con la sintaxis SELECT PROCEDURE ANALYSE  podemos obtener la respuesta de un query con los datos optimos en base a la consulta que hicimos y los datos presentes en la tabla, aca a un ejemplo:

 MariaDB [training]> select * from soccer_teams procedure analyse ();
 +-----------------------------------+--------------+------------+------------+------------+------------------+-------+-------------------------+--------+--------------------------------
 ----------+
 | Field_name                        | Min_value    | Max_value  | Min_length | Max_length | Empties_or_zeros | Nulls | Avg_value_or_avg_length | Std    | Optimal_fieldtype
 |
 +-----------------------------------+--------------+------------+------------+------------+------------------+-------+-------------------------+--------+--------------------------------
 ----------+
 | training.soccer_teams.id          | 1            | 2          |          1 |          1 |                0 |     0 | 1.5000                  | 0.5000 | ENUM('1','2') NOT NULL
 |
 | training.soccer_teams.countryid   | 1            | 2          |          1 |          1 |                0 |     0 | 1.5000                  | 0.5000 | ENUM('1','2') NOT NULL
 |
 | training.soccer_teams.soccer_team | Boca Juniors | Flamengo   |          8 |         12 |                0 |     0 | 10.0000                 | NULL   | ENUM('Boca Juniors','Flamengo')
 NOT NULL |
 | training.soccer_teams.founded     | 1895-11-17   | 1905-04-03 |         10 |         10 |                0 |     0 | 10.0000                 | NULL   | ENUM('1895-11-17','1905-04-03')
 NOT NULL |
 +-----------------------------------+--------------+------------+------------+------------+------------------+-------+-------------------------+--------+--------------------------------
 ----------+
 4 rows in set (0.04 sec)
mysql> select concat('KILL ',id ,' ', Command,';') from information_schema.processlist where Command='Sleep';
+--------------------------------------+
| concat('KILL ',id ,' ', Command,';') |
+--------------------------------------+
| KILL 142374 Sleep;                   |
+--------------------------------------+
1 row in set (0.00 sec)

Calcular el tamaño de una base de datos y tablas con Information Schema

Aca les dejo na breve y sencilla solucion para poder calcular los tamaños de las DB en MySQL /MariaDB y forks con Information Schema

Realmente es muy sencillo:

MariaDB [information_schema]SELECT table_name, table_rows, data_length, index_length,
-> round(((data_length + index_length) / 1024 / 1024),2) "Size in MB"
-> FROM information_schema.TABLES WHERE table_schema = "training";
+--------------+------------+-------------+--------------+------------+
| table_name   | table_rows | data_length | index_length | Size in MB |
+--------------+------------+-------------+--------------+------------+
| by_year      |          4 |       65536 |            0 |       0.06 |
| country      |          0 |           0 |         1024 |       0.00 |
| part         |          0 |       16384 |            0 |       0.02 |
| soccer_teams |          2 |       16384 |            0 |       0.02 |
| table1       |          0 |           0 |         1024 |       0.00 |
| test_aria    |          0 |        8192 |         8192 |       0.02 |
+--------------+------------+-------------+--------------+------------+
6 rows in set (0.22 sec)

Conociendo Drizzle

Para comenzar drizzle es una proyecto open source y fork de MySQL. Al codigo se le hizo una re ingenieria y se removio el codigo que no se consideraba primordial y mejorando el codigo de base en C++.

Es una base de datos orientada a:

  • Arquitectura de Cloud computing y aplicaciones web.
  • Diseñada para concurrencia masiva en arquitecturas de varios cpu.
  • Memoria optimizada para gran performance y paralelismo.
  • Diseño abierto, comunidad abierta y software abierto.
  • Transaccional por ACID.
  • Facil uso y administracion.
  • APIS para instalar plugins.

Descargar Drizzle:https://launchpad.net/drizzle

Problemas para iniciar MariaDB error 1067 en Windows 7/Vista/

Quienes intenten instalar MariaDB windows vista o seven pueden encontrar algunos problemas o el error 1067 a la hora de iniciar el servicio por eso aca van algunas recomendaciones para que no tengan ese problema.

Se descomprimer el zip de la version de mariadb-noinstall-5.2.4-win32.zip
Lo descomprimimos a c:\mariadb por ejemplo  y luego entramos al directorio \bin

C:\>cd mariadb524
C:\mariadb524>
cd bin   C:\mariadb524\bin>mysqld.exe –install MariaDB –defaults-file=C:\mariadb524\myconfig.ini

Cuando querramos iniciar el servicio si cuentan con una version mas vieja de MariaDB es necesario entrar a Panel de Control /Herramientas Administrativas / Servicios y buscar MariaDB , luego vemos las propiedades y PARAMETROS DE INICIO ES NECESARIO PONER –CONSOLE y luego Iniciar el servicio.

Eso es todo


MySQL es de Oracle!!! Y ahora que hago…?

Supongo que mucho ya han escuchado esta historia y no es nada nuevo que Oracle adquirio a Sun y por consiguiente a MySQL, ya que venia en el combo.
Esto quiere decir que  Oracle es el dueño de MySQL y para muchos este tema asusta mas despues de las noticias sobre Solaris. Entonces aqui muchos tienen algunos temores sobre que pasara con MySQL.

Por el momento Oracle va viento en popa con el MySQL Release Candidate 5.5 que nos incluye InnoDB 1.1 como esta en uno de los posts anteriores con varias mejoras asi que quienes quieran continuar de la mano de Oracle por ahora lo pueden hacer tranquilamente.

Igualmente cabe aclarar algo importante Oracle apunta a seguir ofreciendo MySQL con el doble licenciamiento y quienes tengan versiones anteriores open source tampoco tienen problemas al respecto.

Al margen de todo esto hay varias alternativas muy interesantes que vienen perfilando.
Una de ellas es MariaDB. MariaDB es la version gratuita de MySQL creada por el mismo fundador de MySQL años atras (Monty Widenius) que nos ofrece varios cambios en esta version con Storage Engines nuevos como Aria, XtraDB, QGraph, PBXT donde Aria es el sustito de MyISAM y XtraDB es el InnoDB plugin mejorado por Percona.

Desde ya hay libertad en el Mundo de MySQL ya que lo mas importante es poder acceder a soporte confiable y hay sobradas empresas que lo pueden brindar y nuevos binarios de diferentes empresas por lo tanto hay un sin fin de posibilidades en este gran momento de cambio para los usuarios de MySQL. Zend incluso esta recomendando instalar MariaDB y DRUPAL hace lo mismo demostrando que esta mas que probado que MariaDB cumple con todos los requerimientos.

Instalando MariaDB 5.1.49 en Fedora 13

En este post voy a explicar como instalar rapidamente MariaDB en Fedora 13
Primero vamos a descargar el ultimo release desde http://askmonty.org/wiki/MariaDB:Download#Binaries
En este caso yo descargue el binario para 64 bits (mariadb-5.1.49-Linux-x86_64.tar.gz)

Cuando lo descargamos  realizamos lo siguiente:

[root@host ~]#groupadd mysql
[root@host ~]# useradd -g mysql mysql
[root@host ~]#  cd /usr/local
[root@host ~]# gunzip < /home/usuario-test/Downloads/mariadb-5.1.49-Linux-x86_64.tar.gz | tar xvf -
[root@host ~]# ln -s la-ruta-a-mariadb-5.1.49-Linux-x86_64 mysql
[root@host ~]# cd mysql
[root@host ~]# chown -R mysql .
[root@host ~]# chgrp -R mysql .
[root@host ~]# scripts/mysql_install_db --user=mysql
[root@host ~]# chown -R root .
[root@host ~]# chown -R mysql data
[root@host ~]#  bin/mysqld_safe --user=mysql &

La instalacion es igual que instalar MySQL, RECUERDEN QUE DEBEREMOS
PONER UNA CONTRASENA A NUESTRO SERVIDOR DE MARIADB
Luego de ingresar por primera vez

MySQL 5.5 con InnoDB 1.1

Del brazo de Oracle llego la tan esperada version 5.5 de MySQL con InnoDB 1.1 que se muestra como la Default Storage Engine desplazando a MyISAM.

Dentro de los principales cambios tenemos

  • Bloqueo de Metadata con las transacciones
  • Particion por columnas (Antes solo lo teniamos por filas)
  • PERFORMANCE_SCHEMA para el analisis de la performance  y asi obtener mayor control a un bajo nivel.
  • Replicacion semi-sincronica.

Bloqueo de Metadata con las transacciones:

Supongamos que estamos realizando una transaccion, y alguien quisiera hacer algun DDL como un Drop de la tabla t1 donde nosotros estamos realizando una transaccion y otra de las sesiones quiere  realizar un drop. ¿Que pasaria? Bueno con  metadata locking evitariamos un problema de esta indole.

PERFORMANCE_SCHEMA

[mysql] show tables;
 +----------------------------------------------+
 | Tables_in_performance_schema                 |
 +----------------------------------------------+
 | COND_INSTANCES                               |
 | EVENTS_WAITS_CURRENT                         |
 | EVENTS_WAITS_HISTORY                         |
 | EVENTS_WAITS_HISTORY_LONG                    |
 | EVENTS_WAITS_SUMMARY_BY_EVENT_NAME           |
 | EVENTS_WAITS_SUMMARY_BY_INSTANCE             |
 | EVENTS_WAITS_SUMMARY_BY_THREAD_BY_EVENT_NAME |
 | FILE_INSTANCES                               |
 | FILE_SUMMARY_BY_EVENT_NAME                   |
 | FILE_SUMMARY_BY_INSTANCE                     |
 | MUTEX_INSTANCES                              |
 | PERFORMANCE_TIMERS                           |
 | PROCESSLIST                                  |
 | RWLOCK_INSTANCES                             |
 | SETUP_CONSUMERS                              |
 | SETUP_INSTRUMENTS                            |
 | SETUP_OBJECTS                                |
 | SETUP_TIMERS                                 |
 +----------------------------------------------+
 18 rows in set (0.00 sec)