Testing the Fastest Way to Import a Table into MySQL (and some interesting 5.7 performance results)

As I mentioned on my last post, where I compared the default configurations options in 5.6 and 5.7, I have been doing some testing for a particular load in several versions of MySQL. What I have been checking is different ways to load a CSV file (the same file I used for testing the compression tools) into MySQL. For those seasoned MySQL DBAs and programmers, you probably know the answer, so you can jump over to my 5.6 versus 5.7 results. However, the first part of this post is dedicated for developers and MySQL beginners that want to know the answer to the title question, in a step-by-step fashion. I must say I also learned something, as I under- and over-estimated some of the effects of certain configuration options for this workload.

Disclaimers: I do not intend to do proper benchmarks, most of the results obtained here were produced in a couple of runs, and many of them with a default configuration. This is intended, as I want to show “bad practices” for people that is just starting to work with MySQL, and what they should avoid doing. It is only the 5.6 to 5.7 comparison that have left me wondering. Additional disclaimer: I do not call myself a programmer, and much less a python programmer, so I apologize in advance for my code- after all, this is about MySQL. The download link for the scripts is at the bottom.

The Rules

I start with a CSV file (remember that it is actually a tab-separated values file) that is 3,700,635,579 bytes in size, has 46,741,126 rows and looks like this:

171773  38.6048402      -0.0489871      4       2012-08-25 00:37:46     12850816        472193  rubensd
171774  38.6061981      -0.0496867      2       2008-01-19 10:23:21     666916  9250    j3m
171775  38.6067166      -0.0498342      2       2008-01-19 10:23:21     666916  9250    j3m
171776  38.6028122      -0.0497136      5       2012-08-25 00:26:40     12850816        472193  rubensd
171933  40.4200658      -3.7016652      6       2011-11-29 05:37:42     9984625 33103   sergionaranja 

I want to load it into a table with the following structure:

CREATE TABLE `nodes` (
  `id` bigint PRIMARY KEY,
  `lat` decimal(9,7),
  `lon` decimal(10,7),
  `version` int,
  `timestamp` timestamp,
  `changeset` bigint,
  `uid` bigint,
  `user` varchar(255)

The import finish time will be defined as the moment the table is crash safe (even if there is some pending IO). That means that for InnoDB, the last COMMIT has to be successful and flush_log_at_trx_commit must be equal to 1, meaning that even if there is pending IO to be made, it is fully durable on disk (it is crash-resistant). For MyISAM, that means that I force a FLUSH TABLES before finishing the test. Those are, of course, not equivalent but it is at least a way to make sure that everything is more or less disk-synced. This is the ending part of all my scripts:

# finishing
if (options.engine == 'MyISAM'):
    cursor.execute('FLUSH TABLES')


For the hardware and OS, check the specs on this previous post– I used the same environment as the one mentioned there, with the exception of using CentOS7 instead of 6.5.

The naive method

Let’s say I am a developer being tasked with loading a file regularly into MySQL- how would I do that? I would probably be tempted to use a CSV parsing library, the mysql connector and link them together in a loop. That would work, wouldn’t it? The main parts of the code would look like this (load_data_01.py):

# import
insert_node = "INSERT INTO nodes (id, lat, lon, version, timestamp, changeset, uid, user) VALUES (%s, %s, %s, %s, %s, %s, %s, %s)"

with open(CSV_FILE, 'rb') as csv_file:
    csv_reader = csv.reader(csv_file, delimiter='\t')
    for node in csv_reader:
        cursor.execute(insert_node, node)

As I am playing the role of a developer without MySQL experience, I would also use the default configuration. Let’s see what we get (again, that is why I call these “tests”, and not benchmarks). Lower is better:

MySQL Version 5.1.72 5.5.39 5.6.20 5.7.4
Load time (seconds) 4708.594 6274.304 6499.033 6939.722

Wow, is that 5.1 being a 50% faster than the rest of versions? Absolutely not, remember that 5.5 was the first version to introduce InnoDB as the default engine, and InnoDB has additional transactional overhead and usually not good default configuration (unlike MyISAM, which is so simple that the default options can work in many cases). Let’s normalize our results by engine:

MySQL Version 5.1.72 5.5.39 5.6.20 5.7.4
MyISAM 4708.594 5010.655 5149.791 5365.005
InnoDB 6238.503 6274.304 6499.033 6939.722

This seems more reasonable, doesn’t it? However, in this case, it seems that there is a slight regression in single-thread performance as the versions go up, specially on MySQL 5.7. Of course, it is early to draw conclusions, because this method of importing a CSV file, row by row, is one of the slowest ones, and we are using very poor configuration options (the defaults), which vary from version to version and should not be taken into account to draw conclusions.

What we can say is that MyISAM seems to work better by default for this very particular scenario for the reasons I mentioned before, but it still takes 1-2 hours to load such a simple file.

The even more naive method

The next question is not: can we do it better, but, can we do it even slower? A particular text draw my attention when looking at the MySQL connector documentation:

Since by default Connector/Python does not autocommit, it is important to call this method after every transaction that modifies data for tables that use transactional storage engines.

-from the connector/python documentation
I though to myself- oh, so maybe we can speedup the import process by committing every single row to the database, one by one, don’t we? After all, we are inserting the table on a single huge transaction. Certainly, a huge number of small transactons will be better! 🙂 This is the slightly modified code (load_data_02.py):

insert_node = "INSERT INTO nodes (id, lat, lon, version, timestamp, changeset, uid, user) VALUES (%s, %s, %s, %s, %s, %s, %s, %s)"

with open('/tmp/nodes.csv', 'rb') as csv_file:
    csv_reader = csv.reader(csv_file, delimiter='\t')
    for node in csv_reader:
        cursor.execute(insert_node, node)

And I do not even have a fancy graphic to show you because after 2 hours, 19 minutes and 39.405 seconds, I canceled the import because only 111533 nodes had been inserted in MySQL 5.1.72 for InnoDB with the default configuration (innodb_flush_log_at_trx_commit = 1). Obviously, millions of fsyincs will not make our load faster, consider this a leason learned.

Going forward: multi-inserts

The next step I wanted to test is how effective grouping queries was in a multi-insert statement. This method is used by mysqldump, and supposedly minimizes the SQL overhead of handling every single query (parsing, permission checking, query planning, etc.). This is the main code (load_data_03.py):

concurrent_insertions = 100
with open(CSV_FILE, 'rb') as csv_file:
    csv_reader = csv.reader(csv_file, delimiter='\t')
    i = 0
    node_list = []
    for node in csv_reader:
        i += 1
	node_list += node
        if (i == concurrent_insertions):
            cursor.execute(insert_node, node_list)
            i = 0
            node_list = []

# insert the reminder nodes
if i > 0:
    insert_node = "INSERT INTO nodes (id, lat, lon, version, timestamp, changeset, uid, user) VALUES (%s, %s, %s, %s, %s, %s, %s, %s)"
    for j in xrange(i - 1):
        insert_node += ', (%s, %s, %s, %s, %s, %s, %s, %s)'
    cursor.execute(insert_node, node_list)

We tested it with a sample of 100 rows inserted with every query. What are the results? Lower is better:
load_data_03.py results

MySQL Version 5.1.72 5.5.39 5.6.20 5.7.4
MyISAM 1794.693 1822.081 1861.341 1888.283
InnoDB 3645.454 3455.800 2849.299 3032.496

With this method we observe an improvement of the import time of 262-284% from the original time for MyISAM and of 171-229% from the original time for InnoDB. Remember that this method will not scale indefinitely, as we will encounter the package size limit if we try to insert too many rows at the same time. However, it is a clear improvement over the one-row-at-a-time approach.

MyISAM times are essentially the same between versions while InnoDB shows an improvement over time (which may be due to code and optimization changes, but also to the defaults like the transaction log size changing, too), except again between 5.6 and 5.7.

The right method for importing data: Load Data

If you have a minimum of experience with MySQL, you know that there is a specialized keyword for data imports, and that is LOAD DATA. Let’s see how the code would end up looking like by using this option (load_data_04.py):

# data import
load_data = "LOAD DATA INFILE '" + CSV_FILE + "' INTO TABLE nodes"


Simple, isn’t it? With this we are minimizing the SQL overhead, and executing the loop in the compiled C MySQL code. Let’s have a look at the results (lower is better):
load_data_04.py results

MySQL Version 5.1.72 5.5.39 5.6.20 5.7.4
MyISAM 141.414 149.612 155.181 166.836
InnoDB 2091.617 1890.972 920.615 1041.702

In this case, MyISAM has a very dramatic improvement – LOAD DATA speeds up to 12x times the import. InnoDB, again still each one with the default parameters can improve the speed up to 3x times, and more significantly in the newer versions (5.6, 5.7) than the older ones (5.1, 5.5). I predict that this has to do much more with the different configuration of log files than with the code changes.

Trying to improve Load Data for MyISAM

Can we improve the load times for MyISAM? There are 2 things that I tried to do -augmenting the key_cache_size and disabling the Performance Schema for 5.6 and 5.7. I set up the key_cache_size to 600M (trying to fit the primary key on memory) and I set the performance_schema = 0, and I tested the 3 remaining combinations. Lower is better:
load_data_04.py results for myisam

MySQL Version 5.1.72 5.5.39 5.6.20 5.7.4
default 141.414 149.612 155.181 166.836
key_buffer_size=600M 136.649 170.622 182.698 191.228
key_buffer_size=600M, P_S = OFF 133.967 170.677 177.724 186.171
P_S = OFF 142.592 145.679 150.684 159.702

There are certain things to notice here:

  • P_S=ON and P_S=OFF should have no effect for MySQL 5.1 and 5.5, but it brings different results because of measuring errors. We must understand that only 2 significative figures should be taken into account.
  • key_buffer_cache does not in general improve performance, in fact I would say that it statistically worsens the performance. This is reasonable because after all, I am writing to filesystem cache, and a larger key cache might require costlier memory reservations, or more memory copys. This should be researched further to make a conclusion.
  • Performance_schema may worsen the performance on this workload, but I am not statistically sure.
  • MyISAM (or maybe the MySQL server) seems to have slightly worsen its performance for this specific workload (single threaded batch import).

There are more things that I would like to try with MyISAM, like seeing the impact of the several row formats (fixed), but I wanted to follow up for other engines.

Trying to improve Load Data for InnoDB

InnoDB is a much more interesting engine, as it is ACID by default, and more complex. Can we make it as fast as MyISAM for importing?

The first thing I wanted to do is to change the default values of the innodb_log_file_size and innodb_buffer_pool_size. The log is different by default before and after 5.6, and it is not suitable for a heavy write load. I set it for a first test to 2G, as it is the largest size that 5.1 and 5.5 can use (actually, I set it to 2,147,483,136 as it has to be less than 2G), meaning that we have logs of about 4G. I also set the buffer pool for a convenient size, 8GB, enough to hold the whole dataset. Remember that one of the problems why InnoDB is so slow for imports is because it writes the new pages (at least) twice on disk -on the log, and on the tablespace. However, with these parameters, the second write should be mostly buffered on memory. These are the new results (lower is better):
load_data_04.py innodb results

MySQL Version 5.1.72 5.5.39 5.6.20 5.7.4
default 1923.751 1797.220 850.636 1008.349
log_file_size=2G, buffer_pool=8G 1044.923 1012.488 743.818 850.868

Now this is a test that starts to be more reasonable. We can comment that:

  • Most of the improvements that we had before in 5.6 and 5.7 respect to 5.1 and 5.5 was due to the 10x size in logs.
  • Still, 5.6 and 5.7 are faster than 5.1 and 5.5 (reasonable, as 5.6 had quite some impresive InnoDB changes, both on code and on configuration)
  • InnoDB continues being at least 5x slower than MyISAM
  • Still, 5.7 is slower than 5.6! We are having consistently a 13-18% regression in 5.7 (now I am starting to worry)

I said before that the main overhead of InnoDB is writing the data twice (log and tables). This is actually wrong, as it may actually write it 3 times (on the double write area) and even 4 times, in the binary log. The binary log is not enabled by default, but the double write is, as it protects from corruption. While we never recommend disabling the latter on a production, the truth is that on an import, we do not care if the data ends up corrupted (we can delete it and import it again). There is also some options on certain filesystems to avoid setting it up.

Other features that are in InnoDB for security, not for performance are the InnoDB checksums- they even were the cause of bottlenecks on very fast storage devices like flash PCI cards. In those cases, the CPU was too slow to calculate it! I suspect that that will not be a problem because more modern versions of MySQL (5.6 and 5.7) have the option to change it to the hardware-sped up function CRC32 and, mainly, because I am using a magnetic disk, which is the real bottleneck here. But let’s not believe on what we’ve learned and let’s test it.

The other thing I can check is performance_schema overhead. I’ve found cases of workload where it produces significative overhead, while almost none in others. Let’s also test enabling and disabling it.

These are the results (lower is better):
load_data_04.py results for innodb optimized

MySQL Version 5.1.72 5.5.39 5.6.20 5.7.4
default security and monitoring enabled 1044.923 1012.488 743.818 850.868
doublewrite=off 896.423 848.110 483.542 468.943
doublewrite=off,checksums=none 889.827 846.552 488.311 476.916
doublewrite=off,checksums=none,P_S=off 488.273 467.716

There are several things to comment here, some of them I cannot even explain:

  • The doublewrite feature doesn’t halve the performance, but it impacts it significantly (between a 15-30%)
  • Without the doublewrite, most of the 5.7 regression goes away (why?)
  • The doublewrite is also more significative in 5.6 and 5.7 than previous versions of MySQL. I would dare to tell that most of the other bottleneck may have been eliminated (or maybe it is just something like the buffer pool partitions being active by default?)
  • The innodb checksum makes absolutely no difference for this workload and hardware.
  • Again, I cannot give statistical significance to the overhead of the performance schema. However, I have obtained very variables results in these tests, having results with a 10% higher latency than the central values of the ones with it disabled, so I am not a hundred percent sure on this.

In summary, with just a bit of tweaking, we can get results on InnoDB that are only 2x slower than MyISAM, instead of 5x or 12x.

Import in MyISAM, convert it to InnoDB

I’ve seem some people at some forums recommending importing a table as MyISAM, then convert it to InnoDB. Let’s see if we can bust or confirm this myth with the following code (load_data_06.py):

load_data = "LOAD DATA INFILE '/tmp/nodes.csv' INTO TABLE nodes"
alter_table = "ALTER TABLE nodes ENGINE=InnoDB"


These are the comparisons (lower is better):
load_data_06.py results

MySQL Version 5.1.72 5.5.39 5.6.20 5.7.4
LOAD DATA InnoDB 1923.751 1797.220 850.636 1008.349
LOAD DATA MyISAM; ALTER TABLE ENGINE=InnoDB 2075.445 2041.893 1537.775 1600.467

I can see how that could be almost true in 5.1, but it is definitely not true in supported versions of MySQL. It is actually faster than importing twice the table, once for MyISAM and another for InnoDB.

I leave as a homework as a reader to check it for other engines, like MEMORY or CSV [Hint: Maybe we could import to this latest engine in a different way].

Parallel loading

MyISAM writes to tables using a full table lock (although it can perform in some cases concurrent inserts), but InnoDB only requires row-level locks in many cases. Can we speed up the process by doing a parallel loading? This is what I tried to test with my last test. I do not trust my programming skills (or do not have time) to perform the file-seeking and chunking in a performant way, so I will start with a pre-sliced .csv file into 8 chunks. It should not consume much time, but the limited synchronization tools on the default threading library, together with my limited time made me opt for this plan. We only need to understand that we do not start with the exact same scenario in this case. This is the code (load_data_08.py):


def load_data(port, csv_file):
    db = mysql.connector.connect(host="localhost", port=port, user="msandbox", passwd="msandbox", database="test")
    cursor = db.cursor()
    load_data_sql = "LOAD DATA INFILE '" + csv_file + "' INTO TABLE nodes"

thread_list = []

for i in range(1, NUM_THREADS + 1):
   t = threading.Thread(target=load_data, args=(port, '/tmp/nodes.csv' + `i`))

for thread in thread_list:

for thread in thread_list:

And these are the results, with different parameters:
load_data_08.py results

MySQL Version 5.1.72 5.5.39 5.6.20 5.7.4
1 thread, log_file_size=2G, buffer_pool=8G 894.367 859.965 488.273 467.716
8 threads, log_file_size=2G, buffer_pool=8G 752.233 704.444 370.598 290.343
8 threads, log_file_size=5G, buffer_pool=20G 301.693 243.544
4 threads, log_file_size=5G, buffer_pool=20G 295.884 245.569

From this we can see that:

  • There is little performance changes between loading in parallel with 4 or 8 threads. This is a machine with 4 cores (8 HT)
  • Parallelization helps, although it doesn’t scale (4-8 threads gives around a 33% speed up)
  • This is where 5.6 and specially 5.7 shines
  • A larger transaction log and buffer pool (larger than 4G, only available in 5.6+) still helps with the load
  • Parallel load with 5.7 is the fastest way in which I can load this file into a table using InnoDB: 243 seconds. It is 1.8x times the fastest way I can load a MyISAM table (5.1, single-threaded): 134 seconds. That is almost 200K rows/s!

Summary and open questions

  • The fastest way you can import a table into MySQL without using raw files is the LOAD DATA syntax. Use parallelization for InnoDB for better results, and remember to tune basic parameters like your transaction log size and buffer pool. Careful programming and importing can make a >2-hour problem became a 2-minute process. You can disable temporarily some security features for extra performance
  • There seems to be an important regression in 5.7 for this particular single-threaded insert load for both MyISAM and InnoDB, with up to 15% worse performance than 5.6. I do not know yet why.
  • On the bright side, there is also an important improvement (up to 20%) in relation to 5.6 with parallel write-load.
  • Performance schema may have an impact on this particular workload, but I am unable to measure it reliably (it is closer to 0 than my measuring error). That is a good thing.

I would be grateful if you can tell me if I have made any mistakes on my assumptions here.

Here you can download the different scripts in Python tested for the MySQL data loading.

Remember that these were not “formal” benchmarks, and I have no longer access to the machine where I generated them. I have yet to analyze if the same problem exists on 5.7.5. There are other people pointing to regressions under low concurrency, like Mark Callaghan, maybe these are related? As usual, post a comment here or reach me on Twitter.

Hoy es el día en que MyISAM ha dejado de ser necesario

RIP MyISAMPor supuesto, esto sólo es un título para llamar la atención. Por lo que yo sé no todas las tablas de sistema se pueden convertir a InnoDB todavía (por ejemplo, las tablas de privilegios), lo cual convierte la cabecera en técnicamente falsa. MyISAM es un motor muy simple, y eso tiene algunas ventajas inherentes (no hay carga extra debido a las transacciones, más fácil de “editar” manualmente, normalmente ocupa menos espacio en disco), pero también algunas desventajas bastante importantes: no es seguro en el caso de un cuelgue general, no hay claves foráneas, sólo bloqueos a nivel de tabla, problemas de consistencia, bugs en tablas muy grandes,… La versión 5.7.5 “Milestone 15”, presentada hoy en el Oracle Open World tiene una impresionante lista de cambios, la cual necesitaré un tiempo para digerir, como una replicación multi-master (¿syncrona?) en fase de desarrollo o un completamente cambiado optimizador de consultas. Pero el cambio en particular que quiero destacar hoy es que la última de las “3 grandes” razones para usar MyISAM ha desaparecido finalmente. Para mí (y mis clientes) esas razones eran:

Espacio de tablas transportable

En MyISAM, mover una tabla en formato binario de una servidor a otro era (y sigue siendo) muy fácil- tirar el servidor y copiar los archivos .MYI, .MYD y .frm. Incluso se podría hacer en caliente con las debidas precauciones: se podía copiar los ficheros de tabla si se ejecutaba antes el infame “FLUSH TABLES WITH READ LOCK;” y usar eso como una backup.

innodb_file_per_table fue introducido tan pronto como MySQL 4.1, pero no fue puesto como por opción por defecto hasta 5.6.6 (con una breve indecisión en las versiones tempranas de 5.5). La verdadera funcionalidad de “espacio de trablas transportables” también fue añadida en 5.6.6, y proporcionó una manera dentro del servidor de preparar las tablas para la copia, bloqueándolas y exportando su porción del diccionario de datos de InnoDB (FLUSH TABLES ... FOR EXPORT).

Antes de 5.6, MySQL requería una parche para que esto funcionara de manera segura. Ahora, tablas individuales pueden exportarse e importarse sin problemas en formato binario, incluso entre diferentes servidores.

Índices Fulltext

La búsqueda de cadenas de texto fulltext nunca ha sido el punto fuerte de MySQL (y he ahí la razón por la que mucha gente lo combina con Sphinx o Apache Lucene/Solr). Pero muchos usuarios no requieren un clon de Google Search, sólo una manera rápida de buscar en una web pequeñita, o en una columna de descripción, y como sabemos, los índices BTREE no son útiles en expresiones como like '%palabra_buscada%'.

Los índices y búsquedas FULLTEXT han estado disponibles desde MySQL 3.23.23, pero sólo para MyISAM. Yo no sé vosotros, pero yo he encontrado un relativo alto número de clientes cuya única razón para seguir usando MyISAM era “necesitamos búsquedas fulltext”. A partir de MySQL 5.6.4, el soporte a fulltext fue añadido a InnoDB, eliminando la necesidad de decidir entre la transaccionalidad y la búsqueda rápida de cadenas. A pesar de que los comienzos no fueran precisamente geniales, (especialmente si los comparamos con otras soluciones externas y más complejas) y que fue publicado con algunos bugs importantes que afectaban a la estabilidad; los últimos cambios en el soporte de fulltext en innodb indican que se sigue trabajando para mejorar su rendimiento.

Soporte a GIS

Éste es cambio que los ingenieros de MySQL añadieron en MySQL 5.7.5. Por supuesto, los tipos de datos GIS estaban disponibles desde MySQL 4.1 para MyISAM, y en 5.0.16 para la mayoría de otros motores de primeras partes, incluyendo InnoDB. Sin embargo, esos tipos no son útiles si no se pueden utilizar de manera rápida en operaciones geográficas comunes como encontrar si dos polígonos se solapan o encontrar todos los puntos que están cerca de otro dado. La mayoría de estas operaciones requieren el indexado en 2 dimensiones, algo que no funciona muy bien con índices BTREE estándares. Para ello, necesitamos R-Trees o Quadtrees, estructuras que nos permitirán el indexado eficiente de valores multidimensionales. Hasta ahora, esos índices de tipo SPATIAL, como se les llama en sintaxis MySQL, sólo estaban disponibles en MyISAM- de forma que de nuevo teníamos que decidir entre transacciones y claves foráneas o operaciones GIS eficientes. Esta es una de las razones por las cuales proyectos como OpenStreetMap migraron a PostGIS, mientras que otros usaban las Oracle Spatial Extensions.

Para ser sinceros, la lista de cambios referentes a GIS parece bastante extensa, y he sido todavía incapaz de echarle un vistazo en detalles. Pero puedo ver que todavía no hay soporte a proyecciones (después de todo, eso requeriría probablemente rehacer por completo esta característica), y con ello, tampoco funciones nativas de distancia, lo cual hace que no sea una alternativa viable a PostGIS en muchos escenarios. Pero tengo que otorgar que el soporte a InnoDB, al menos a nivel de MyISAM y más allá es un gran paso adelante. De nuevo, a veces no necesitas un conjunto de características completo para la mayoría de la audiencia de MySQL, sino un conjunto de opciones mínimas para mostrar de manera eficiente un mapa en una página web.

MyISAM en un mundo post-myisam

En resumen, estos cambios, unidos con una lenta pero segura migración de las tablas de sistema a formato InnoDB, junto con los esfuerzos por reducir la carga transaccionar de las tablas temporales internas, permitirá a Oracle hacer MyISAM opcional en un futuro.

Yo mismo continuaré usando MyISAM en ciertos casos ya que a veces uno no necesita almacenamiento ACID, y funciona particularmente para conjuntos de datos pequeños y de solo lectura -incluso si tienes millones de esos (ey, les funciona a WordPress.com, así que ¿por qué no seguir usándolo también?).

Además, la gente tardará años en adoptar 5.7, que no está ni siquiera en GA release (versión considerada para uso general).

Contadme ¿planeáis migrar de motores cuando 5.7 llegue a tu producción? ¿Para qué sigues usando MyISAM? ¿Cuál es tu característica nueva favorita de 5.7.5? ¿Qué peros habéis encontrado en las nuevas características anunciadas? Dejadme un mensaje aquí o en Twitter.

Today is the day in which MyISAM is no longer needed

RIP MyISAMOf course, this is just a catchy title. As far as I know not all system tables can be converted to InnoDB yet (e.g. grant tables), which makes the header technically false. MyISAM is a very simple engine, and that has some inherent advantages (no transactional overhead, easier to “edit” manually, usually less space footprint on disk), but also some very ugly disadvantages: not crash safe, no foreign keys, only full-table locks, consistency problems, bugs in for large tables,… The 5.7.5 “Milestone 15” release, presented today at the Oracle Open World has an impressive list of changes, which I will need some time to digest, like an in-development (syncronous?) multi-master replication or a revamped query optimizer. But the one very change that I want to highlight today is how the last one of the “big 3” reasons to use MyISAM has finally vanished. For me (and my customers) those reasons were:

Transportable tablespaces

In MyISAM, moving a table in binary format from one server to another was very easy- shutdown the servers and copy the .MYI, .MYD and .frm files. You could even do it in a hot way with the due care: you could copy the table files if you executed the infamous “FLUSH TABLES WITH READ LOCK;” beforehand, and use that as a backup.

innodb_file_per_table was introduced as early as MySQL 4.1, but it wasn’t set as default until 5.6.6 (with a brief indecision on early versions of 5.5). The actual feature “Transportable tablespaces” was added also in 5.6.6, and provided a way inside the server to prepare InnoDB tables for copying, by locking them and exporting its portion of the InnoDB data dictionary (FLUSH TABLES ... FOR EXPORT).

Before 5.6, MySQL required a patch for this to work reliably. Now, single tables can be exported and imported without problem in binary format, even between servers.

Fulltext indexes

Fulltext search has never been the strong point of MySQL (and that is why many people combined it with Sphinx or Apache Lucene/Solr). But many users didn’t require a Google Search clone, only a quick way to search on a smallish website, or a description column, and as we know, BTREE indexes wouldn’t help with like '%term%' expressions.

FULLTEXT indexes and searches have been available since MySQL 3.23.23, but only on MyISAM. I do not know about you, but I have found a relatively high number of customers whose reason to continue using MyISAM was only “we need fulltext search”. Starting with MySQL 5.6.4, fulltext support was added to InnoDB, avoiding the need to decide between transactionality and fast string search. While the starts were not precisely great, (specially compared to other more complex, external solutions) and they were released with some important crashing bugs; the latest changes indicate that InnoDB fulltext support is still being worked on in order to increase its performance.

GIS support

This is the one that MySQL engineers added in MySQL 5.7.5. Of course, GIS datatypes were available since MySQL 4.1 for MyISAM, and in 5.0.16 for most other upstream engines, including InnoDB. However, those types are not useful if they cannot be used quickly in common geographical operations like finding if 2 polygons overlap or finding all points that are close to another. Most of those operations require indexing in 2 dimensions, something that doesn’t work very well with standard BTREE indexes. For that, we need R-Trees or Quadtrees, structures that can efficiently index multidimensional values. Up to now, those SPATIAL indexes, as they are called in MySQL syntax, were only available for MyISAM- meaning that you had to decide again between transactions and foreign keys or fast GIS operations. This was one of the reasons why projects like OpenStreetMap migrated to PostGIS, while others used Oracle Spatial Extensions.

To be fair, the list of changes regarding GIS seems quite extensive, and I have been yet unable to have a detailed look at it. But for I can see there is still no support for projections (after all, that would probably require a full overhaul of this feature), and with it, no native distance functions, which makes it not a viable alternative to PostGIS in many scenarios. But I can see how InnoDB support, at least at MyISAM level and beyond that, is a huge step forward. Again, sometimes you do not need a complete set of features for the main MySQL audience, but a set of minimum options to display efficiently something like a map on a website.

MyISAM in a post-myisam world

In summary, these changes, together with the slow but steady migration of system tables to InnoDB format, plus the efforts on reducing transactional overhead for internal temporary tables will allow Oracle to make MyISAM optional in the future.

I will continue to use MyISAM myself in certain cases because sometimes you do not need a fully ACID storage, and it works particularly well for small, read-only datasets -even if you have millions of those (hey, it works well for WordPress.com, so why should you not use it, too?).

Also, it will take years for all people to adopt 5.7, which is not even in GA release yet.

So tell me, are you planning to migrate engine when 5.7 arrives to your production? What are you still using MyISAM for? Which is your favorite 5.7.5 new feature? Which caveats have you found on the new announced features? Send me a message here or on Twitter.

Cambios en variables globales de configuración entre MySQL 5.6.20 y MySQL 5.7.4 “Milestone 14”

MySQL Upgrade from 5.6 to 5.7Mientras realizaba unos tests (que os enseñé posteriormente aquí) en el -todavía en desarrollo- MySQL 5.7 quise hacer un análisis de la configuración para ver si los cambios en el rendimiento eran debidos a los cambios en el código o simplemente a los parámetros por defecto de MySQL (algo que es muy común en una migración de 5.5 a 5.6 debido al tamaño por defecto del log de transacciones y otros parámetros por defecto). Éste es un post rápido con el objetivo de identificar las variables globales que se han modificado entre estas dos versiones.

Me podrían decir que con leer las notas de cambios de versiones (release notes) sería suficiente, pero mi experiencia me dice (y esta no es una excepción, como podréis comprobar) que compruebe los cambios por mí mismo.

No incluyo cambios en las tablas de performance_schema, ya que estaba ejecutando estos tests en particular con performance_schema = OFF. Tampoco incluyo “cambios administrativos”, mi nombre para las variables que no influyen el comportamiento o el rendimiento de mysql, tales como el server_uuid, que será diferente para instancias distintas y version e innodb_version, que obviamente han cambiado de 5.6.20 a 5.7.4-m14. Tenga en cuenta que alguno de los cambios han sido portados de vuelta a 5.6, por lo que no se mostrarán aquí, o ya estaban disponibles en versiones previas de 5.7.

Variables que han cambiado su valor

nombre de la variable valor en 5.6.20 valor en 5.7.4
eq_range_index_dive_limit 10 200
log_warnings 1 2
performance_schema_max_statement_classes 168 189

Nuevas variables

nombre de la variable/strong> valor en 5.7.4
default_authentication_plugin mysql_native_password
default_password_lifetime 360
have_statement_timeout YES
innodb_buffer_pool_dump_pct 100
innodb_log_write_ahead_size 8192
innodb_page_cleaners 1
innodb_temp_data_file_path ibtmp1:12M:autoextend
log_error_verbosity 3
log_timestamps UTC
max_statement_time 0
performance_schema_events_transactions_history_long_size -1
performance_schema_events_transactions_history_size -1
performance_schema_max_memory_classes 250
performance_schema_max_metadata_locks -1
performance_schema_max_prepared_statements_instances -1
performance_schema_max_program_instances 5000
performance_schema_max_statement_stack 10
rbr_exec_mode STRICT
session_track_schema ON
session_track_state_change OFF
session_track_system_variables time_zone,autocommit,
slave_parallel_type DATABASE

Variables hechas obsoletas

nombre de la variable valor en 5.6.20
binlogging_impossible_mode IGNORE_ERROR
innodb_additional_mem_pool_size 8388608
innodb_use_sys_malloc ON
thread_concurrency 10

Algunos comentarios:

  • Respecto a potenciales incompatibilidades, todas las variables obsoletas excepto una eran literalmente inútiles, y no las encontraba normalmente configuradas, excepto por innodb_additional_mem_pool_size, la cual era, en mi experiencia, siempre configurada por error, ya que no tenía absolutamente ningún efecto en versiones recientes de InnoDB. La excepción es binlogging_impossible_mode, que fue añadida en 5.6.20 y probablemente no mergeada a tiempo para esta milestone de 5.7. Probablemente sea añadida en el futuro con una funcionalidad equivalente. Una característica interesante, me gustaría añadir.
  • El cambio de eq_range_index_dive_limit de 10 a 200 es un cambio muy razonable, hecho a partir de una sugerencia de Facebook. Esta variable fue añadida en MySQL 5.6, y aunque solventaba el problema de obtener estadísticas fiables para expresiones IN con múltiples valores, Facebook tenía completa razón en que las cláusulas IN tienen frecuentemente más de 10 elementos (ya que es una características que muchos desarrolladores y frameworks utilizan).
  • max_statement_timeout y have_statement_timeout provienen del merge o la reimplementación de la funcionalidad de timeout de consultas de Twitter. Una buena adición en upstream.
  • default_authentication_plugin no es una nueva funcionalidad, tan sólo se ha movido de un parámetro de servidor a una variable global de pleno derecho que puede ser inspeccionada (pero no cambiada) en tiempo de ejecución. El verdadero cambio aquí es default_password_lifetime, que realmente se echaba de menos en 5.6- expiración automática de contraseñas (sin tener que ejecutar manualmente PASSWORD EXPIRE). Lo que encuentro interesante es el valor por defecto: 360 (las contraseñas expiran aproximadamente una vez al año). No estoy diciendo que sea un valor por defecto malo o bueno, pero predigo bastante controversia/confusión sobre eso. Hay más que hablar sobre cambios en la autenticación, pero no me expandiré aquí, ya que no concierne a las variables de configuración.
  • Cambiando slave_parallel_type a LOGICAL_CLOCK, mysql permite la replicación paralela de manera mucho más fina, mucho mejor que la limitada opción de 5.6 (sólo útil en infraestructuras multi-tenant)
  • Algunos añadidos a InnoDB, como por ejemplo la variable innodb_page_cleaners, permitiendo múltiples hilos de ejecución para el flusheo de páginas desde el buffer pool en paralelo, y el cual fue el sujeto de una reciente discusión sobre un cierto benchmark. Además, se ha añadido cierta flexibilidad extra respecto a la configuración del cacheo del log de transacciones y la configuración de la localización de las tablas temporales en formato InnoDB que considero cambios menores como para ir sobre cada uno de ellos en detalle.
  • log_warnings ha cambiado pero no ha sido documentado. Pero siendo sinceros, su funcionalidad es obsoleta ya que ha sido sustituida por log_error_verbosity, una nueva variable recientemente introducida que hace que por defecto se registren por defecto todos los errores, avisos y notas. He enviado el bug #73745 (arreglado) sobre esto.
  • Una nueva variable, rbr_exec_mode, parece haberse añadido en 5.7.1, pero no está documentada en ningún sitio de las sección de variables del servidor o en las release notes, tan sólo en el blog de ese desarrollador. Ésta permite iniciar a nivel de sesión un modo IDEMPOTENT cuando se replican eventos en modo filas, ignorando todos los conflictos encontrados. He creado un bug #73744 (arreglado) para esta incidencia.
  • Ha habido varios cambios en el performance_schema; no comentaré sobre ellos aquí. Tenga en cuenta que performance_schema_max_statement_classes no es un cambio real, ya que se calcula al inicio (no tiene un valor fijo).
  • Se han añadido variables session_track_* para la monitorización de cambios en la sesión para usarse en el conector de C

En resumen, cambios interesantes, tan sólo una cambio en la configuración por defecto que podría alterar el rendimiento (eq_range_index_dive_limit), y nada que podría crear problemas en una migración, con dos excepciones provenientes de pronósticos propios:

Instancis de la (por otro lado inútil desde hace tiempo, tal y como se mencionaba arriba) variable innodb_additional_mem_pool_size fallando con:

[ERROR] unknown variable 'innodb_additional_mem_pool_size=X'

, la cual simplemente debería borrarse del fichero de configuración.

Y el tiempo de expiración establecido por defecto a un año, que podría producir montones de:

ERROR 1862 (HY000): Your password has expired.

o incluso crear algunos problemas difíciles de identificar en conectores anticuados, tal y como hemos experimentado con esta funcionalidad en 5.6. Me gustaría conocer en particular vuestra opinión sobre la configuración por defecto para el expirado de contraseñas en el software, ya que no me considero un experto en seguridad. Como habitualmente, podéis dejarme comentarios aquí o en Twitter.

EDIT: Morgan Tocker, de Oracle, ha comentado via Twitter que “innodb_additional_mem_pool_size no ha tenido utilidad desde hace mucho tiempo (desde el plugin), y que la razón para el cambio ahora son los problemas adicionales de aceptar pero ignorar opciones“. No me quejo de esos cambios, de hecho, creo que deberían haberse mucho tiempo atrás para prevenir esos mismos errores, tan sólo estoy describiendo aquí una solución para lo que creo que serán problemas frecuentes en la migración. La incompatibilidad es a veces la manera correcta.

Changes in Configuration of Global Variables between MySQL 5.6.20 and MySQL 5.7.4 “Milestone 14”

MySQL Upgrade from 5.6 to 5.7While doing some testing (that I published later here) on the still-in-development MySQL 5.7 I wanted to do some analysis on the configuration to see if the changes in performance were due to the code changes or just to the new MySQL defaults (something that is very common in the migration from 5.5 to 5.6 due to the default transaction log size and other InnoDB parameters). This is a quick post aiming to identify the global variables changed between these two versions.

You could tell me that you could just read the release notes, but my experience (and this is not an exception, as you will see) tells me to check these changes by myself.

I do not include changes in the performance_schema tables, as I was running these particular tests with performance_schema = OFF. I also do not include “administrative changes”, my name for variables that do not influence the behaviour or performance of mysql, like server_uuid which will be unique for different instances and version and innodb_version, which obviously have been changed from 5.6.20 to 5.7.4-m14. Please note that some changes have also been back-ported to 5.6, so not being shown here, or were already available in previous releases of 5.7.

Variables that have changed its value

variable name 5.6.20 value 5.7.4 value
eq_range_index_dive_limit 10 200
log_warnings 1 2
performance_schema_max_statement_classes 168 189

New variables

variable name 5.7.4 value
default_authentication_plugin mysql_native_password
default_password_lifetime 360
have_statement_timeout YES
innodb_buffer_pool_dump_pct 100
innodb_log_write_ahead_size 8192
innodb_page_cleaners 1
innodb_temp_data_file_path ibtmp1:12M:autoextend
log_error_verbosity 3
log_timestamps UTC
max_statement_time 0
performance_schema_events_transactions_history_long_size -1
performance_schema_events_transactions_history_size -1
performance_schema_max_memory_classes 250
performance_schema_max_metadata_locks -1
performance_schema_max_prepared_statements_instances -1
performance_schema_max_program_instances 5000
performance_schema_max_statement_stack 10
rbr_exec_mode STRICT
session_track_schema ON
session_track_state_change OFF
session_track_system_variables time_zone,autocommit,
slave_parallel_type DATABASE

Deprecated variables

variable name 5.6.20 value
binlogging_impossible_mode IGNORE_ERROR
innodb_additional_mem_pool_size 8388608
innodb_use_sys_malloc ON
thread_concurrency 10

Some comments:

  • Regarding potential incompatibilities, all deprecated variables but one were literally useless, and I did not find them setup usually except for innodb_additional_mem_pool_size, which was, in my experience, always configured by mistake, as it had absolutely no effect in recent versions of InnoDB. The exception is binlogging_impossible_mode, which had been added in 5.6.20 and probably not merged in time for this 5.7 milestone. It will probably be added in the future with equivalent functionality. An interesting feature, I would add.
  • eq_range_index_dive_limit changed from 10 to 200 is a very reasonable change, made after a Facebook suggestion. This variable was added in MySQL 5.6, and while it solved the problem of getting more reliable statistics for IN expressions with multiple values, Facebook was completely right that IN clauses have commonly more than 10 items (as it is a feature that many developers/frameworks like).
  • max_statement_timeout and have_statement_timeout comes from the merge or reimplementation of the Twitter Statement Timeout functionality. Nice change to see upstream.
  • default_authentication_plugin is not a new feature, it just has been moved from a server parameter to a full global variable that can be inspected (but not changed) at runtime. The real change here is default_password_lifetime, which was really missing on the 5.6 release- automatic password expiration (without having to do manually PASSWORD EXPIRE). What I find amusing is the default value: 360 (passwords expire approximately once a year). I am not saying that that is a right or wrong default, but I predict a lot of controversy/confusion over that. There is more to talk about about authentication changes, but I will not expand it here, as it does not concern configuration variables.
  • By changing the slave_parallel_type to LOGICAL_CLOCK, mysql allows for more fine-grained parallel replication, much better than the limited 5.6 option (only useful in multi-tenant setups)
  • Some interesting additions to InnoDB, too, like the innodb_page_cleaners variable, allowing multiple threads for flushing pages from the buffer pool in parallel, and which was the subject of a recent discussion about a certain benchmark. Also we have additions like some extra flexibility regarding the transaction log caching configuration and the location of temporary tables in InnoDB format, but I consider those lesser changes to go over them in detail.
  • log_warnings has changed and it has not been documented. But to be honest, its functionality is being deprecated for log_error_verbosity, a newly introduced variable that makes by default all errors, warnings and notes to be logged by default. I have submitted bug #73745 (now fixed) about this.
  • A new variable, rbr_exec_mode, seems to have been added in 5.7.1, but it is not documented anywhere in the server variables section or on the release notes, only on that developer’s blog. It allows setting at session level an IDEMPOTENT mode when replicating events in row format, ignoring all conflicts found. I’ve created a bug #73744 for this issue (now fixed).
  • There has been several performance_schema changes; I will not go over each of them here. Please note that performance_schema_max_statement_classes is not a real change, as that is calculated at startup time, it does not have a fixed value.
  • Session tracking variables were added for notification of session changes when using the C connector

In summary, some interesting changes, only one default change that may alter the performance (eq_range_index_dive_limit), and nothing that will create problems for a migration, with only two own-predicted exceptions:

Instances of the (useless for a long time, as mentioned above) variable innodb_additional_mem_pool_size failing with:

[ERROR] unknown variable 'innodb_additional_mem_pool_size=X'

, which just should be deleted from the configuration file.

And the expiration time set by default to 1 year, that may create lots of:

ERROR 1862 (HY000): Your password has expired.

or even create some difficult-to-debug problems in older drivers, as we had experienced with this functionality in 5.6. I would like in particular your opinion about software defaults for password expiration, as I do not consider myself a security expert. As usual, you can comment here or on Twitter.

EDIT: Morgan Tocker, from Oracle, has commented via twitter that “innodb_additional_mem_pool_size had been useless for a long time (since the plugin), and that the reason for the change now is the additional problems of parsing but ignoring options“. I am not complaining about those changes, I actually think that they should have been done long time ago to prevent those very errors, I am just putting here a solution for what I think can be frequent mistakes on migration. Incompatibility is sometimes the way to go.

¿Qué herramienta de compresión debería usar para las copias de seguridad de mi base de datos? (Parte II: descompresión)

En mi entrada de la semana pasada, analizaba algunos de las herramientas y formatos de compresión más comunes, así como su velocidad y ratio de compresión. Aunque ello podría darnos una buena idea del rendimiento de estas herramientas, el análisis estaría incompleto sin investigar la descompresión. Esto es particularmente cierto para backups de base de datos ya que, en aquellos casos en los que el proceso de compresión se realice fuera de las máquinas de producción puede que no te importe tanto los tiempos de compresión. En tal caso, incluso si es relativamente lento, no afectará al rendimiento de tu servidor MySQL (o aquello que estés usando). El tiempo de descompresión, sin embargo, puede ser crítico, ya que podría influir en muchos casos en el MTTR (tiempo medio de recuperación) de todo tu sistema.

Entorno de pruebas

Utilicé el mismo dump MySQL de nodos de OpenStreetMap en formato CSV que mencioné en mi anterior post, y -dado que algunas de las herramientas usan el mismo formato (y deberían ser compatibles entre sí), pero resultaban en ratio de compresión distintos- elegí el fichero más pequeño de cada uno de ellos. He aquí una tabla con el tamaño comprimido por formato, como recordatorio:

formato tamaño (bytes)
.csv original (sin compresión) 3700635579
gzip 585756379
bzip2 508276130
bzip2 (pbzip2-compressed) 508782016
7z 354107250
lzip 354095395
lzo 782234410
lz4 816582329

Tenga en cuenta que aunque p7zip y lzip/plzip usan el mismo algoritmo, el formato de fichero es diferente. También tenga en cuenta que se han usando dos ficheros comprimidos distintos para bzip2: la razón de ello será aclarada más adelante.

El hardware era el mismo que en el último post: un Intel Quad Core i7-3770@3.40GHz con hyper threading y sin carga, exponiendo 8 cpus al kernel. 32 GB de ram. 2 discos duros clásicos (no SSD) de 3 TB en RAID 1. El sistema de archivos era ext4 con las opciones por defecto del sistema operativo. El sistema operativo ha cambiado, sin embargo, a CentOS 7.

La metodología fue similar a la utilizada anteriormente, para cada herramienta se ejecutaba el siguiente comando varias veces:

$ time [tool] -d -c < nodes.csv.[format] > nodes.csv

Excepto en el caso de dd y 7za, donde la sintaxis varía ligeramente.

El archivo final se almacenaba en una partición diferente del mismo RAID. De este fichero se comprobaba su corrección (que el archivo sin comprimir fuera exactamente igual al original) y borrada tras cada ejecución. No repetiré aquí mi disclaimer sobre el uso de la cache del sistema de archivos, pero he añadido los resultados de dd como referencia.

Resultados globales

Esta es mi tabla de resultados finales; el análisis y discusión de los mismo siguen a continuación:

método de                  tamaño        ratio de    mediana del tiempo  velocidad de   Uso %CPU
compresión                 comprimido    compresión  de descompresión 	 descompresión
                           (bytes)       (%)         (segundos)          (MB/s)
dd                         3700635579    100.00%       9.996              353.061       100 -  43
gzip                        585756379     15.83%      17.391              202.933        99 -  26
bzip2                       508276130     13.73%      55.616               63.457       100 -  45
pigz                        585756379     15.83%       7.115              496.023       172 -  62
pbzip2 (bzip2-compressed)   508276130     13.73%      50.760               69.527       170 -  64
pbzip2 (pbzip2-compressed)  508782016     13.75%       9.904              356.341       794 - 185
lzip                        354095395      9.57%      38.682               91.236       100 -  47
7za                         354107250      9.57%      28.070              125.729       157 -  95
plzip                       354095395      9.57%      19.791              178.324       345 - 177
lzop                        782234410     21.14%       6.094              579.127       136 -  47
lz4                         816582329     22.07%       3.176             1111.209       100 -  78

Estos datos pueden verse más fácilmente, como es habitual, en un gráfico bidimensional. En este caso, el eje X axis representa la mediana de la velocidad de descompresión en MB/s (más es mejor) y el eje Y representa el porcentaje del tamaño comprimido sobre el tamaño original (menos es mejor):

Análisis de las diferentes herramientas de compresión: ratio de compresión vs. velocidad de descompresión
(no está representado dd, ya que el aparecería con un ratio de compresión del 100%)

El uso de CPU se monitorizó cada segundo, así como el uso de memoria, que en ningún test para ninguna de las herramientas fue superior a 1MB.

En este caso he representado la función y = x*0.01+12 sobre los puntos y, aunque hay una clara tendencia de que mejores ratios de compresión requieren más tiempo de descompresión, la correlación es más débil que en el caso de la compresión.

La última cosa que me gustaría remarcar sobre los resultados globales es que no se han probado variaciones en los parámetros para la descompresión ya que en la mayoría de los casos hay pocas o ninguna opción para este proceso, y los algoritmos harán esencialmente los mismo para un archivo que fue creado con --fast que para otro que fue creado con --best.

Descomprimiendo los formatos gzip y bzip2

Sin ser precisamente sorpendente, el archivo gzip tardó menos tiempo en ser descomprimido que bzip con las herramientas GNU genéricas (56 segundos vs. 17). Utilicé GNU gzip 1.5 y bzip2 1.0.6. Ya comenté todo lo que quería comentar sobre las ventajas de y desventajas de usar las herramientas más estándar, así que no me volverá a repetir aquí, pero quería reiterar la idea de que gzip sigue siendo una buena utilidad para compresión rápida cuando no hay alternativa, ya que obtuvo un throughput de casi 203 MB/s al descomprimir nuestro fichero de pruebas.

Por supuesto, el siguiente paso era probar la descompresión en paralelo, y para ello disponía de pigz 2.3.1 y pbzip2 v1.1.6. Como nota al margen, me gustaría mencionar que, en el momento de escribir estas líneas, no había paquetes rpm para pbzip2 en CentOS 7 ni en la distribución base ni en EPEL (el cuál está actualmente en beta para la versión 7). Utilicé el paquete para RHEL6.

Sin embargo, cuando eché un vistazo a los resultados de pigz me pude dar cuenta de que, aunque ciertamente había una mejora en la velocidad (un poco más de 7 segundos), ésta no es tan dramática como la mejora 4x+ que observamos en la compresión. Además, si miramos al uso de la cpu, nos podemos dar cuenta de que el máximo uso de %CPU nunca supera el 170. Me dí cuenta que la razón de esto leyendo la documentación: aunque pigz usa varios hilos para la E/S de lectura y escritura, es incapaz de paralelizar el algoritmo básico de descompresión de gzip. Las mejora sobre el gzip estándar -en cualquier caso- están ahí, con casi 500 MB/s de ancho de banda de descompresión.

Cuando probé pbzip2, en mi primer intento, me di cuenta de que no había paralelización en absoluto, y que los tiempos eran esencialmente los mismos que el bzip2 normal. Buscando respuestas en la documentación encontré que la razón de ello era que la descompresión en paralelo era posible (al contrario que con gzip), pero sólo para archivos creados por el propio pbzip2.. En otras palabras, tanto bzip2 como pbzip2 crean archivos con un formato compatible (pueden ser descomprimidos por la otra utilidad), pero la paralelización sólo es posible si se crean y descomprimen con pbzip2. Para probar este segundo caso, utilicé el mejor archivo comprimido obtenido en mis resultados previos (que era ligeramente más grande que el creado con bzip2) y volví a probar los tests. Ésta es la razón por la cual hay dos filas diferentes en los resultados globales para pbzip2.

En ese segundo escenario, pbzip2 era una verdadera mejora sobre bzip2, obteniendo rates de descompresión de 356 MB/s, aproximadamente equivalentes a los resultados de una copia directa mediante el sistema de archivos.

Como era de esperar, el uso de múltiples hilos de descompresión es una clara ventaja en sistemas SMP, con los habituales avisos por los recursos extra consumidos y el hecho de que, en la realidad, como acabamos de ver, no siempre es posible para todos los formatos de archivo.

Descompresión Lzma

El siguiente grupo para poner a prueba son las herramientas basadas en lzma: Lzip 1.7, p7zip 9.20 y plzip 1.2-rc2. De nuevo, lzip no estaba disponible en EPEL-7, así que se usó la versión de RedHat6, y plzip fue compilado a partir del código fuente, tal y como hicimos anteriormente.

El algoritmo Lzma se había clasificado como lento pero con muy buena compresión en nuestros resultados anteriores. Se puede extrapolar un resultado similar para la descompresión: tanto lzip como 7za proporcionan tiempos de descompresión de unos 30 segundos, con anchos de banda cercanos a los 100 MB/s. Aunque p7zip parece un poco mejor paralelizado que lzip (con %cpu llegando a los 150), ambos proporcionan esencialmente un algoritmo de descompresión monohilo. Plzip proporciona una mejor paralelización, alcanzando un uso de la cpu del 290%, pero el throughput nunca supera los 200 MB/s.

La evaluación general es que son claramente mejores herramientas que los gzip y bzip2 monotarea, ya que proporcionan unos anchos de banda de descompresión similares pero con unos ratios de descompresión mucho mejores..

Herramientas “rápidas”: lzop y lz4

Finalmente nos quedan las herramientas de compresión y descompresión rápidas, en nuestros tests, lzop v1.03 y lz4 r121. En este caso podemos testificar las afirmaciones de que lz4, aunque proporcionan unas velocidades de compresión similares a lzop, es más rápido en la descompresión: casi dobla la velocidad (580 MB/s de lzop contra los 1111 MB/s de lz4). Obviamente, la única razón por la cual estos resultados son posibles es porque el sistema de archivos está siendo utilizado, así que tomad estos resultados con la debida precaución. Pero muestran la clase de ancho de banda de descompresión que se puede obtener cuando la latencia de disco no es el cuello de botella.

Cuando el tiempo de los tests es tan pequeño, recomendaría repetirlo con tamaños de archivo mayores o limitando los efectos de la caché del sistema de archivos. Dejaré esto como un ejercicio a realizar por el lector.


Aparte de las limitaciones encontradas en las distintas herramientas respecto a la paralelización en descompresión (pigz, pbzip2), no se han encontrado resultados muy extraños. Las herramientas de compresión rápida son rápidas en descompresión (me he vuelto un fan de lz4) y las herramientas de mejor compresión son más lentas (plzip parece funcionar muy bien si no se está restringido por el tiempo de ejecución y el uso de la CPU). Como siempre, mi recomendación es probar siempre en el propio entorno, con los archivos y máquinas propias, antes de sacar conclusiones prematuras.

¿Qué herramienta de compresión usas para MySQL (o cualquier otro sistema de base de datos)? Déjame un comentario aquí o en Twitter.

Which Compression Tool Should I Use for my Database Backups? (Part II: Decompression)

On my post last week, I analysed some of the most common compression tools and formats, and its compression speed and ratio. While that could give us a good idea of the performance of those tools, the analysis would be incomplete without researching the decompression. This is particularly true for database backups as, for those cases where the compression process is performed outside of the production boxes, you may not care too much about compression times. In that case, even if it is relatively slow, it will not affect the performance of your MySQL server (or whatever you are using). The decompression time, however, can be critical, as it may influence in many cases the MTTR of your whole system.

Testing environment

I used the same OpenStreetMap node MySQL dump in CSV format that I mentioned on my previous post, and -as some tools used the same format (and they should be compatible), but resulted on different compressed ratio- I chose the smallest resulting file for each of them. Here it is a table with the compressed size per format, as a reminder:

format size (bytes)
original .csv (no compression) 3700635579
gzip 585756379
bzip2 508276130
bzip2 (pbzip2-compressed) 508782016
7z 354107250
lzip 354095395
lzo 782234410
lz4 816582329

Please note that while p7zip and lzip/plzip used the same algorithm, the file format is different. Also please notice the usage of two different compressed files for bzip2: the reason for that will be clarified later.

The hardware specs were the same as for the last post: an almost-idle Intel Quad Core i7-3770@3.40GHz with hyper threading, exposing 8 cpus to the kernel. 32 GB of ram. 2 spinning disks of 3 TB in RAID 1. The filesystem type was ext4 with the default OS options. The operating system had changed to CentOS 7, however.

The methodology was similar as before, for each tool, the following command was executed several times:

$ time [tool] -d -c < nodes.csv.[format] > nodes.csv

Except for dd and 7za, where the syntax is slightly different.

The final file was stored on a different partition of the same RAID. The final file was checked for correction (the uncompressed file was exactly the same as the original one) and deleted after every execution. I will not repeat here my disclaimer about the filesystem cache usage, but I also added the dd results as a reference.

Global results

This is my table of final results and the analysis and discussion follows bellow:

method                     compressed    compression  median decompression  decompression  %CPU Usage
                           size (bytes)  ratio (%)    time (seconds)	    speed (MB/s)
dd                         3700635579    100.00%       9.996                 353.061       100 -  43
gzip                        585756379     15.83%      17.391                 202.933        99 -  26
bzip2                       508276130     13.73%      55.616                  63.457       100 -  45
pigz                        585756379     15.83%       7.115                 496.023       172 -  62
pbzip2 (bzip2-compressed)   508276130     13.73%      50.760                  69.527       170 -  64
pbzip2 (pbzip2-compressed)  508782016     13.75%       9.904                 356.341       794 - 185
lzip                        354095395      9.57%      38.682                  91.236       100 -  47
7za                         354107250      9.57%      28.070                 125.729       157 -  95
plzip                       354095395      9.57%      19.791                 178.324       345 - 177
lzop                        782234410     21.14%       6.094                 579.127       136 -  47
lz4                         816582329     22.07%       3.176                1111.209       100 -  78

These data can be seen more easily, as usual, on a bidimensional graph. Here the X axis represents the median speed of decompression in MB/s (more is better) and the Y axis represents the compressed ratio in percentage of the compressed size over the original size (less is better):

Analysis of different compression tools: Compression ratio vs decompression speed
(not plotted: dd, as it would appear with a 100% compression ratio).

CPU usage was polled every second, and so it was the memory usage, that in no test for any of the tools was over 1MB.

In this case I have plotted the function y = x*0.01+12 over the points and, while there is a clear tendency of better compression ratios requiring more time to decompress, the correlation is weaker than on the compression case.

The last thing I want to remark about the global results is that I have not tried variations in parameters for decompression, as in most cases there are little to no options for this process, and the algorithms will do essentially the same for a file that was created with --fast than another created with --best.

Decompressing gzip and bzip2 formats

Unsurprisingly, the gzip file took less time to decompress than bzip with the generic GNU tools (56 seconds vs. 17). I used GNU gzip 1.5 and bzip2 1.0.6. I said everything I had to say about the advantages and disadvantages of using the most standard tools, so I will not repeat myself, but I wanted to reiterate the idea that gzip is a great tool for fast compression processes when there is not an alternative, as it got a mean throughput of almost 203 MB/s when decompressing our test file.

Of course, the next step was testing decompressing in parallel, and for that I had pigz 2.3.1 and pbzip2 v1.1.6. As a side note, I would like to mention that, at the time of this writing, there were no rpm packages for pbzip2 for CentOS 7 in the base distribution nor on EPEL (which is currently in beta for version 7). I used the package for RHEL6.

However, when looking at the pigz results we can realise that, while there is certainly an improvement on speed (just over 7 seconds), it is not as dramatic as the 4x+ improvement that we had on compression. Also, if we look at the cpu usage, we can realise that the maximum %CPU usage is never over 170. I found the reason for that while looking at the documentation: while pigz uses several threads for read and write I/O, it is unable to parallelise the core gzip decompression algorithm. The improvement over standard gzip -however- is there, with almost 500 MB/s of decompression bandwidth.

When checking pbzip2, on my first try, I realised that there was no parallelization at all, and that the timing results were essentially the same as with regular bzip2. I searched for answers on the documentation and I found that and the reason for that was that decompression in parallel was possible (unlike gzip), but only for files created by pbzip2 itself. In other words, both bzip2 and pbzip2 create files with a compatible format (they can be decompressed with each other), but parallelization is only fully possible if they are created and decompressed with pbzip2. To test that second case, I got the best-compressed file that I got from my previous results (which was slightly larger than the one created with bzip2) and retried the tests. That is why there are two different rows on the global results for pbzip2.

In that second scenario, pbzip2 was a real improvement over bzip2, obtaining decompression rates of 356 MB/s, roughly equivalent to the results of a raw filesystem copy.

As it was expected, multiple threads of decompression is a clear advantage on SMP systems, with the usual disclaimers of extra resources consumed and the fact that, as just seen, it is not possible for all file formats.

Lzma decompression

The next group to test is lzma-based tools: Lzip 1.7, p7zip 9.20 and plzip 1.2-rc2. Again, lzip was not available on EPEL-7, and the RedHat6 version was used, and plzip was compiled from source, as we had to do previously.

Lzma algorithm was classified as a slow but good-compression algorithm on our previous results. A similar thing can be extrapolated for decompression: both lzip and 7za provide decompression times of around 30 seconds, with throughputs near the 100 MB/s. Although p7zip seems to be a bit better paralleled than lzip (with %cpu usage reaching 150), both provide essentially a mono-thread decompression algorithm. Plzip provides a better parallelization, reaching a maximum %cpu of 290, but the throughput never reaches the 200 MB/s.

The general evaluation is that they are clearly better tools than single-threaded gzip and bzip2, as they provide similar decompression bandwidths but with much better compression ratios.

Fast tools: lzop and lz4

Finally, we have left the fast compression and decompression tools, in our tests lzop v1.03 and lz4 r121. In this case we can testify the the claims that lz4, while providing similar compression speed than lzop, it is faster for decompression: almost doubling the rate (580 MB/s for lzop vs. 1111 MB/s for lz4). Obviously, the only reason those results are possible is because the filesystem cache is kicking in, so take this results with the due precaution. But it shows what kind of decompression bandwidth can be achieved when the disk latency is not the bottleneck.

When the time of the test is so small, I would recommend repeating it with larger filesizes and/or limiting the effect of the filesystem cache. I will leave that as a homework for the reader.


Aside from the found limitations of several of the tools regarding decompression parallelization (pigz, pbzip2), no highly surprising results have been found. Fast compression tools are fast to decompress (I have become a fan of lz4) and best-compression tools are slower (plzip seems to work very well if we are not constrained by time and CPU). As usual, I will leave you with my recommendation of always testing on your environment, with your own files and machines.

Which compression tool(s) do you use for MySQL (or any other database backups)? Leave me a comment here or on Twitter.

¿Qué herramienta de compresión debería usar para las copias de seguridad de mi base de datos? (Parte I: compresión)

Esta semana hablamos de tamaño, algo que debería preocuparle a cualquier administrador de sistemas a cargo del sistema de backups de cualquier proyecto, y en particular de los backups de una base de datos.

A menudo recibo preguntas sobre cuál es la mejor herramienta de compresión a aplicar en un sistema de copias de seguridad: ¿gzip? ¿bzip2? ¿algún otro?

El entorno de pruebas

Para poder probar diferentes formatos y herramientas, creé un archivo .csv (comma-separated values, valores separados por comas) de tamaño 3.700.635.579 bytes transformando un dump reciente de todos los nodos de la porción europea de España en OpenStreetMap. Tenía un total de 46.741.126 de filas y tenía la siguiente pinta:

171773  38.6048402      -0.0489871      4       2012-08-25 00:37:46     12850816        472193  rubensd
171774  38.6061981      -0.0496867      2       2008-01-19 10:23:21     666916  9250    j3m
171775  38.6067166      -0.0498342      2       2008-01-19 10:23:21     666916  9250    j3m
171776  38.6028122      -0.0497136      5       2012-08-25 00:26:40     12850816        472193  rubensd
171933  40.4200658      -3.7016652      6       2011-11-29 05:37:42     9984625 33103   sergionaranja 

De hecho, el fichero original es en realidad un tsv (tab-separated values, valores separados por tabuladores), y no un csv, pero sólo porque soy demasiado vago como para añadir el código extra FIELDS SEPARATED BY ',' cada vez que lo importo y exporto. Puede descargar este archivo en formato 7z, o crear el suyo propio desde los extractos de OpenStreetMap de Geofabrik.

Todos los tests se hicieron en un servidor casi inactivo con un Intel Quad Core i7-3770@3.40GHz con hyper threading, exponiendo 8 cpus al kernel. 32 GB de ram. 2 discos duros clásicos (no SSD) de 3 TB en RAID 1. Todo corriendo bajo CentOS 6.5 x86_64. El sistema de archivos era ext4 con las opciones por defecto del sistema operativo.

Tamaño en tabla

Para una importación a MySQL, propuse la siguiente estructura de tabla:

CREATE TABLE `nodes` (
  `id` bigint PRIMARY KEY,
  `lat` decimal(9,7),
  `lon` decimal(10,7),
  `version` int,
  `timestamp` timestamp,
  `changeset` bigint,
  `uid` bigint,
  `user` varchar(255)

Y estos fueron los tamaños en la base de datos (una vez que nos aseguramos de que no había operaciones de escritura pendientes):

  • Archivo de datos MySQL en MyISAM (.MYD): 2,755,256,596 bytes.(*)
  • Espacio de tablas MySQL de InnoDB (.ibd): 3,686,793,216 bytes.
  • Espacio de tablas MySQL de InnoDB con formato de filas compressed (.ibd): 1,736,441,856 bytes.

¿Por qué ocupa más espacio en texto plano que en la base de datos? Aunque las bases de datos están optimizadas para acceso rápido, y no en ocupación de disco, estamos usando un conjunto de tipos de datos muy compacto (enteros y timestamps en vez de cadenas de texto), ahorrando espacio en el proceso. ¡Esta es la razón por la que un diseño adecuado de base de datos es crítico para el rendimiento!

Podemos ver que una de las pocas razones por las que la gente sigue utilizando MyISAM es porque es un formato muy simple y compacto. (*)Sin embargo, para ser justos, no estamos teniendo en cuenta los 674.940.928 bytes extra de la clave primaria (.MYI), haciendo que la diferencia no sea tan grande. Por otro lado, no estamos teniendo en cuenta que el tamaño de los índices en InnoDB puede crecer de manera bastante significativa cuando estamos usando múltiples claves secundarias (debido al almacenamiento de la clave privada, si ésta es lo suficientemente grande) y las múltiples otras estructuras (tablespace 0, transaction logs) que son necesarios para que InnoDB funciones adecuadamente, compartido con otras tablas. En general, es imposible realizar una comparación justa entre MyISAM e InnoDB porque estamos comparando peras con manzanas.

Lo que está claro es que la compresión (en este caso estamos usando el algoritmo por defecto de InnoDB con el nivel por defecto de compresión-6) está ayudando a reducir el espacio en disco, introduciendo potenciales mejoras en escenarios específicos: más tablas que pueden caber en SSDs, o menos IOPS en una base de datos cuyo cuello de botella es el disco. Por otro lado, la carga inicial aumentó muy significativamente. No quiero mostrar las mediciones de tiempo de las importaciones en las distintas tablas porque no es trivial registrar el tiempo real a disco debido a todo el buffering que ocurre a nivel de base de datos, y simplemente proporcionar el tiempo de ejecución de las sentencias SQL sería injusto. Hablo más sobre tiempos de importación en este post.

Resultados globales

Los tamaños en tabla sólo se mostraron como referencias, nuestro principal objetivo es testear las herramientas disponibles para comprimir el archivo original nodes.csv file. Me limité a mí mismo a algunas de las más populares, y en la siguiente tabla se pueden ver los resultados finales (el análisis, explicación y discusión sigue a continuación):

Tamaño original  3700635579 bytes					
método         mediana del tiempo de  tamaño comprimido  ratio de compresión             uso de cpu de la
               compresión (seconds)   (bytes)            (tamaño_nuevo/tamaño_original)  compresión (unix %CPU)
dd               10.146               3700635579         100.00%                          97 -  68
gzip            113.796                614119104          16.59%                         100 -  89
gzip -1          43.219                729259339          19.71%                         100 -  67
gzip -9         266.991                585777285          15.83%                          97 -  77
bzip2           294.568                525839069          14.21%                          95 -  89
bzip2 -1        281.337                508276130          13.73%                         100 -  80
bzip2 -9        295.510                585777285          15.83%                         100 -  95
pigz             27.325                614093952          16.59%                         770 - 547
pigz -1          25.982                728206796          19.68%                         231 - 159
pigz -9          51.821                585756379          15.83%                         773 - 659
pbzip2           74.874                526311578          14.22%                         794 - 663
pbzip2 -1        60.487                508782016          13.75%                         800 - 495
pbzip2 -9*       76.597                526311578          14.22%                         773 - 394
lzip           2138.230                357788948           9.67%                         100 -  70
7za             833.611                380728938          10.29%                         172 - 145
7za "ultra"    1286.044                354107250           9.57%                         178 - 164
plzip           216.942                376484712          10.17%                         768 - 373
plzip -1         50.151                581552529          15.71%                         781 - 738
plzip "ultra"   426.486                354095395           9.57%                         785 - 159
lzop             15.505               1003908508          27.13%                          95 -  50
lzop -1          13.080               1000938935          27.05%                          90 -  63
lzop -9         487.850                782234410          21.14%                          99 -  89
lz4               8.537               1043868501          28.21%                          93 -  65
lz4 -1*           8.574               1043868501          28.21%                          94 -  65
lz4 -9           96.171                816582329          22.07%                          99 -  66

Como se puede ver, evalué varias herramientas en sus modos por defecto, así como un modo de “alta compresión” y un modo “rápido”. Para cada uno de ellos intenté evaluar 3 parámetros, importantes para la creación de archivos comprimidos: el tiempo de ejecución, el tamaño final y los recursos usados. Tened en cuenta que sólo evalué herramientas de compresión, y no de “archivado” (como tar o zip). Estas últimas herramientas pueden utilizar diversos algoritmos de compresión tanto para comprimir cada archivo individualmente como aplicarlo al archivado final.

La primera columna de datos muestra el número de segundos (wall-clock time) que tardó el proceso en escribir el archivo comprimido en una partición diferente del mismo conjunto de discos RAID. Se ejecutaron múltiples veces:

time [application] -c < nodes.csv > /output/nodes.csv.[format]

(excepto por 7z y dd, donde la sintaxis es diferente) y se tomó el valor de la mediana con el objetivo de minimizar errores debido a factores externos. Para cada ejecución, se comprobaba la corrección del archivo final (que el resultado era determinista y que podía extraerse de vuelta a su tamaño original sin errores ni diferencias) y después se borraba. Los resultados no son muy científicos, ya que se hacía uso del sistema de archivos de manera extensiva tanto para lecturas como para escrituras, pero mi objetivo era centrarme precisamente en ese escenario. Se muestra también una ejección de dd (copiado del archivo sin compresión) como valor de control.

Creo que la segunda y tercera columna de datos son autoexplicativas: el tamaño en bytes del archivo comprimido y cómo se compara respecto al archivo original.

La última columna intenta medir el uso de CPU máximo y mínimo, tal y como lo reporta el sistema operativo durante la compresión. Si embargo, debido al planficador de la CPU, y al hecho de que la mayoría de herramientas tienen un periodo de sincronización al principio o al final de la ejecución, unidos con el echo de que se obtuvo mediante polling en intervalos de tiempo, hace que no sea muy significativo excepto para la comprobación del uso del paralelismo en su algoritmo. Valores mayores que 100 indican que más de un core/hilo de ejecución se está usando para la compresión.

No registré el uso de memoria (el otro recurso importante) porque incluso en modos “ultra”, su uso no fue significativo para mi máquina con 32GB (menos de 1/2 GB en todos los casos, la mayoría mucho menos). Consideré que era algo que no debería preocuparnos mucho en un máquina que debería tener suficiente RAM como una base de datos. Lo que sí quizá debería tenerse en cuenta en un escenario real es la reserva de caché por el sistema de archivos, que podría impactar de manera directa al rendimiento de MySQL. Prevenir que las páginas leídas y escritas vayan a la caché del sistema de archivos es algo que se puede hacer jugando con el flag POSIX_FADV_DONTNEED. Me gustaría mencionar también que ciertas herramientas, como bzip, disponen de un modo de bajo consumo de recursos: bzip2 --small.

Los tiempos de descompresión los analizo en la segunda parte de este post.

Los resultados globales se pueden apreciar mucho más claramente dibujados en un grafo bidimensional. He representado los valores de tiempo de compresión en el eje X (menos es mejor) y el ratio de compresión en el eje Y (menos es mejor):

Comparativa de tiempo y ratio de compresión de gzip, bzip2, pigz, pbzip2, lzip, p7zip, plzip, lzop y lz4, con diferentes parámetros y niveles
Sin representación: dd (ratio de compresión del 100%), 7za “ultra” (21+ minutos para la compresión) y lzip (35+ minutos para la compresión).

En general, podemos ver que no hay herramientas mágicas, y que un mejor ratio de compresión requiere mas tiempo (el tamaño final es inversamente proporcional al tiempo de compresión). También he representado la función y = 200/x + 9. Ésta, o algo como y = 200/x+9.5 (es difícil encontrar una buena correlación con tan pocos resultados, la mayoría de ellos sin relación entre sí) parece indicarnos el límite inferior del ratio por unidad de tiempo, sugiriendo que un 9%-9.5% sería el máximo ratio de compresión obtenible para este archivo con las herramientas disponibles en este momento.

Analicemos los puntos fuertes y flacos de cada herramienta y formato de compresión.

Los famosos gzip y bzip2

Si lo que deseas es compatibilidad, gzip y bzip2 son los reyes. No sólo son formatos de compresión altamente reconocidos sino que las herramientas para compresión y descompresión están preinstaladas en la mayoría de sistemas operativos tipo unix. Probablemente Windows es el único sistema operativo que no soporta gzip por defecto. gzip y bzip2 son las únicas compresiones con su propia letra en tar (junto con compress en BSD y xz en GNU).

Si bien la compatibilidad y disponibilidad son los puntos fuertes de estas herramientas, mirando al grafo podemos apreciar que están lejos de la línea que mencionaba como ideal en ratio de tiempo/tamaño. bzip2 proporciona un mayor ratio de compresión que gzip a cambio de más ciclos de cpu, pero ambas herramientas son monohilo y no brillan en ningún aspecto en particular. Sorprendentemente, bzip2 -1 nos proporcionó un tiempo de compresión peor y un mejor ratio que la ejecución estándar de bzip2, y el manual de la versión gnu nos proporciona una explicación para ello:

The --fast and --best aliases are primarily for  GNU  gzip
compatibility.   In  particular,  --fast doesn't make things significantly faster.  And --best
merely selects the default behaviour.

(en español: los alias –fast y –best son primordialmente para compatibilidad con GNU gzip. En particular, –fast no hace las cosas significativamente más rápidas. Y –best simplemente selecciona el comportamiento por defecto)

Probablemente el mejor uso que recomendaría para estas herramientas sería gzip --fast (equivalente a gzip -1) que, aunque no proporcione un nivel de compresión espectacular, lo hace de manera relativamente rápida para una aplicación de un sólo hilo de ejecución. Por lo tanto, puede ser útil en aquellos casos en los que se desee maximizar la velocidad sin utilizar muchos recursos. En otros casos, donde la disponibilidad no es un problema, recomendaría utilizar otras herramientas con una mejor velocidad o nivel de compresión.

Para las pruebas utilicé las versiones GNU de gzip 1.3.12 y bzip2 1.0.6.

Herramientas de compresión paralela: pigz y pbzip2

Las cosas se ponen más interesantes si se usan alguna de las versiones paralelas de gzip o bzip2 en un sistema multi-core. Aunque hay más de una versión, elegí pigz 2.3.1 y pbzip2 1.1.6 para mis tests. Aunque no son parte oficial de los repositorios de Red Hat/CentOS, pueden encontrarse sin problemas en EPEL y Debian.

Ambas herramientas autodetectan el número de cores que dispongo y realizan la compresión en 8 hilos de ejecución, proporcionando ratios de compresión comparables pero en 4 veces menos de tiempo. El desventaja obvia es que en un entorno de alta demanda, como puede ser un servidor de MySQL bajo una carga considerable, puede que no desees/puedas otorgar los recursos completos de la CPU al proceso de backup. Pero si estás realizando la compresión en un servidor dedicado aparte, el paralelismo es algo de lo que deberías tomar ventaja, ya que en general, la CPU es el mayor cuello de botella en un algoritmo de compresión.

De nuevo, alguna cosa a destacar: pigz con los parámetros por defecto proporcionó un buen ratio de compresión (16,89%) en menos de 28 segundos- eso es comprimir a cerca de 130MB/s para mi modesto hardware (eso es más de un tercio de mi velocidad de copia, 350MB/s).

Como nota al margen, aunque pbzip2 acepta un nivel de compresión como parámetro, el nivel de compresión por defecto es -9.

Implementaciones de lzma: lzip, 7zip y plzip

Los siguientes test se realizados fueron distintas implementaciones de lzma, un algoritmo que tiene buena fama de proporcionar muy buenos ratios de compresión.

Comencé por lzip. No está en los repositorios oficiales, así que lo obtuve desde EPEL, instalando lzip 1.7. El ratio de compresión fue, efectivamente, el mejor de todos los algoritmos (cercano al 9.5%) pero tardó 35 minutos and 38 segundos en producir la salida. No sólo el algoritmo tenía la culpa en este caso: usaba un único hilo, de ahí el restraso.

Después de ello, utilizé p7zip 9.20, y en particular la herramienta unix 7za. Esta el la única aplicación de compresión probada que no es compatible con los parámetros de gzip. Tuve qe ejecutarla usando:

time 7za a /output/nodes.csv.7z nodes.csv

Tenga en cuenta que p7zip es una aplicación de archivado, pero hice una excepción para probar una implementación alternativa de lzma.

Los resultados fueron mejores: mientras que la herramienta proporcionó un ratio de compresión peor (10.29%), gracias a algún tipo de ejecución en mútiples hilos, el tiempo se redujo a menos de 14 minutos. También probé un sugerido modo “ultra” que encontré en el manual de 7za, con los siguientes parámetros:

-t7z -m0=lzma -mx=9 -mfb=64 -md=32m -ms=on

En resumen: maximizar el uso de memoria, nivel de compresión y tamaño del diccionario -aparte de forzar el formato de archivado y el algoritmo de compresión. Aunque esto proporcionó un tamaño de archivo más pequeño (pero sólo 25MB más pequeño, menos del 1% del tamaño original), el tiempo se incrementó hasta más de 21 minutos.

Quise también probar una implementación paralela de lzma, y plzip era exactamente eso. No pude encontrar un paquete rpm en ninguna parte, así que descargué e instalé desde código fuente Lzlib 1.5 y plzip 1.2-rc2. Los resultados fueron muy buenos, tal y como esperaba. plzip proporciona resultados comparables a “pigz -9” cuando se ejecuta en “modo rápido”; pero por defecto, en sólo 3m37s obtuve un archivo comprimido de 359MB, o 10.17% del archivo original. Después, intenté emular las opciones “ultra” de p7zip (con -9 -m 64 -s 33554432) y obtuve el ganador en ratio de compresión (9.57%) en sólo 7 minutos y 6.486 seconds.

Obviamente, las mismas restricciones que mencioné para las otras herramientas paralelas se aplican aquí: el uso completo de múltiples cores se desaconseja en un servidor muy ocupado, pero si estás almacenando los backups a largo plazo en un servidor separado, probablemenete quieras contemplar esta posibilidad. En cualquier caso, la mayoría de herramientas paralelas tienen una manera de limitar el número de hilos creados (por ejemplo, con la opción --threads en lzip).

Herramientas de compresión rápida: lzop and lz4

No quise terminar mis pruebas sin echar un vistazo a algunas de las herramientas de compresión de alto ancho de banda, así que elegí 2: lzop and lz4. Aunque tuve que instalar lz4 r119 desde EPEL, lzop v1.02rc1 es parte de los paquetes base de Red Hat/CentOS.

Ambos proporcionan lo que prometen: algoritmos de compresión muy rápidos (en algunos casos, incluso más rápidos que una simple copia de archivo, ya que el cuello de botella no está en la CPU y tienen que escribir menos cantidad de datos) a cambio de peores ratios de compresión (21-30%). Para el archivo de ejemplo, en mi máquina, obtuve un mejor rendimiento con lz4 que con lzop, ofreciendo ratios similares pero en menor tiempo (8.5 vs. 15.5 segundos). Así que, si tuviera que elegir, probablemente usaría lz4 antes que lzop en mi caso particular. Adicionalmente, aunque no se ha probado, lz4 presume de proporcionar mejores velocidades de descompresión.

Como resalte negativo, recomendaría no utilizar nunca lzop -9, ya que en ese caso dispondríamos de herramientas con un mejor ratio de compresión en la mitad de tiempo. lz4 no tuvo un buen rendimiento tampoco con un mayor nivel de compresión, así que recomendaría ceñirse a los parámetros por defecto o con un nivel menor de compresión para estas herramientas (de hecho, lz4 por defecto es equivalente a lz4 -1).


No he probado otras herramientas como compress (Lempel-Ziv), xz (lzma2) o QuickLZ, pero no espero demasiadas desviaciones de los patrones que hemos visto: el tiempo es inversamente proporcional al nivel de compresión. Si lo que quieres es compresión rápida, usa lz4. Si quieres un tamaño de archivo pequeño, utiliza una implementación de lzma, como p7zip. Los formatos bzip y gzip son buenas opciones si la compatibilidad es importante (ej. publicar un fichero), pero cuando sea posible, utiliza una implementación de compresión paralela para mejorar el rendimiento (plzip, pbzip2, pigz). Podemos incluso utilizar una combinación de herramientas para nuestros backups, por ejemplo, exportar nuestras tablas en formato binario usando lz4 para sacarlas del servidor mysql, y luego, en una máquina separada, convertirlos a lzma para almacenamiento a largo plazo.

También te aconsejaría probar los distintos métodos de compresión para tu conjunto de datos en particular y tu propio hardware ya que podrías obtener distintos ratios de compresión y medidas de tiempo, sobre todo dependiendo de la memoria disponible para cache del sistema de archivos, tu(s) CPU(s) y la velocidad de lectura y escritura de tu almacenamiento secundario. Lo que he intentado hacer aquí, sin embargo, es tener un punto de partida desde la cual cada uno pueda sacar sus propias conclusiones.

¿Estás de acuerdo conmigo? ¿Crees que he cometido algún error en algún punto? ¿Echas en falta algo? Escribe un comentario o mándame una respuesta a twitter o mediate email.

Echa una vistazo a la parte II de este artículo para mi análisis de estas herramientas para la descompresión.

Cómo instalar MySQL 5.6 en CentOS 7

CentOS 7 and MySQL 5.6

Un poco de historia

La última versión de Red Hat Enterprise Linux, una de las distribuciones de Linux más populares y respetadas en el mercado de servidores, se publicó en junio de 2014, seguido por los lanzamientos de CentOS 7 y Oracle Linux en julio del mismo año.

Hay cambios muy interesantes para administradores de bases de datos en estas nuevas versiones, de las cuales me gustaría destacar el hecho de que el instalador oficial ahora selecciona XFS como el sistema de archivos por defecto, sustituyendo a ext4 como el formato preferido para almacenamiento local. Red Hat EL7 también incluye Btrfs como tech preview.

Respecto a paquetería, el cambio con mayor impacto es, en mi opinión, la actualización de las versiones de MySQL y PostgreSQL, en verdad necesitadas de un upgrade, ya que la anterior versión de Red Hat, 6.5, todavía utilizava versiones con 5 años de edad de ambos SGBBDD, los cuales se encontraban fuera de su ciclo de vida de soporte. La mayor sorpresa es que Red Hat ha optado por elegir MariaDB 5.5, y no Oracle, como el proveedor por defecto de MySQL (o compatible). Esto tiene la hilarante consecuencia de que Oracle Linux está distribuyendo en realidad la versión de su competidor, MariaDB a través de sus repositories, con el objetivo de ser 100% compatible. La diferencia, claro está, es que Oracle ofrece la última versión de MySQL en sus repositorios de yum y, por lo tanto, está disponible para su instalación en todas las distribuciones compatibles con Red Hat.


En este tutorial os mostraremos cómo instalar MySQL 5.6 en CentOS 7, útil para aquellos que prefieran desplegar la última versión GA de MySQL. 5.6 introduce una gran cantidad de mejoras sobre MySQL 5.5, y dado que Red Hat EL7 tiene un ciclo de soporte de al menos 10 años, es posible que se vuelva muy anticuada en el futuro. Vamos a mostrar el proceso en CentOS 7, pero éste será idéntico en RHEL 7 y, hasta cierto punto, en otras distribuciones basadas en yum como las últimas versiones de Fedora y Amazon Linux.

Por favor, tenga en cuenta que el siguiente tutorial supone que no hay una versión previamente instalada de MySQL o MariaDB. Puede utilizar el siguiente comando: rpm -qa | grep -i mysql para comprobar paquentes de MySQL que estén instalados con anterioridad, y borrarlos con el comando yum remove.


El primer paso es configurar el repositori de MySQL de Oracle; para ello, nos dirigimos a la web de mysql.com, pulse en “Downloads“, luego en “Yum repository” y finalmente en “Red Hat Enterprise Linux 7”. En el momento de escribir estas líneas, está versión del respositorio se encuentra todavía en beta, pero no tuve problemas para instalarlo en diversas conbinaciones de software y hardware. Seleccione “Download” y le aparecerá la opción de acceder o crear una cuenta de Oracle. Podemos omitir este paso y simplemente copiar el enlace en la parte inferior que dice “No thanks , just start my download”. Esto nos proporcionará la dirección del rpm para auto-configurar los repositorios de la versión Community del servidor de MySQL.

Ahora, si ejecuta en un terminal:

$ sudo yum install http://dev.mysql.com/get/mysql-community-release-el7-5.noarch.rpm
[sudo] password for training:
Loaded plugins: fastestmirror
mysql-community-release-el7-5.noarch.rpm                                                               | 6.0 kB  00:00:00    
Examining /var/tmp/yum-root-uI5SBL/mysql-community-release-el7-5.noarch.rpm: mysql-community-release-el7-5.noarch
Marking /var/tmp/yum-root-uI5SBL/mysql-community-release-el7-5.noarch.rpm to be installed

Resolving Dependencies

--> Running transaction check
---> Package mysql-community-release.noarch 0:el7-5 will be installed
--> Finished Dependency Resolution

Dependencies Resolved
Package                            Arch              Version          Repository                                        Size
mysql-community-release            noarch            el7-5            /mysql-community-release-el7-5.noarch            4.3 k

Transaction Summary
Install  1 Package

Total size: 4.3 k
Installed size: 4.3 k
Is this ok [y/d/N]: y
Downloading packages:
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
 Installing : mysql-community-release-el7-5.noarch                                                                       1/1
 Verifying  : mysql-community-release-el7-5.noarch                                                                       1/1

 mysql-community-release.noarch 0:el7-5                                                                                      


Podemos comprobar que los respositories están efectivamente activos ejecutando:

$ sudo yum repolist enabled | grep "mysql.*-community.*"
[sudo] password for training:
mysql-connectors-community/x86_64       MySQL Connectors Community            9
mysql-tools-community/x86_64            MySQL Tools Community                 4
mysql56-community/x86_64                MySQL 5.6 Community Server           49

Acabamos de realizar la configuración (sólo es necesario hacerlo una vez) que nos permitirá instalar y mantener actualizada nuestra instalación de MySQL de manera fácil y sencilla.

El siguiente paso es instalar realmente los paquetes del servidor. Para ello, escribimos:

$ sudo yum install mysql-community-server
Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
 * base: centos.mirror.xtratelecom.es
 * extras: centos.mirror.xtratelecom.es
 * updates: centos.mirror.xtratelecom.es
Resolving Dependencies
--> Running transaction check
---> Package mysql-community-server.x86_64 0:5.6.19-2.el7 will be installed
--> Processing Dependency: mysql-community-common(x86-64) = 5.6.19-2.el7 for package: mysql-community-server-5.6.19-2.el7.x86_64
--> Processing Dependency: mysql-community-client(x86-64) = 5.6.19-2.el7 for package: mysql-community-server-5.6.19-2.el7.x86_64
--> Processing Dependency: perl(warnings) for package: mysql-community-server-5.6.19-2.el7.x86_64
--> Processing Dependency: perl(strict) for package: mysql-community-server-5.6.19-2.el7.x86_64
--> Processing Dependency: perl(if) for package: mysql-community-server-5.6.19-2.el7.x86_64
--> Processing Dependency: perl(Sys::Hostname) for package: mysql-community-server-5.6.19-2.el7.x86_64
--> Processing Dependency: perl(POSIX) for package: mysql-community-server-5.6.19-2.el7.x86_64
--> Processing Dependency: perl(Getopt::Long) for package: mysql-community-server-5.6.19-2.el7.x86_64
--> Processing Dependency: perl(File::Temp) for package: mysql-community-server-5.6.19-2.el7.x86_64
--> Processing Dependency: perl(File::Spec) for package: mysql-community-server-5.6.19-2.el7.x86_64
--> Processing Dependency: perl(File::Path) for package: mysql-community-server-5.6.19-2.el7.x86_64
--> Processing Dependency: perl(File::Copy) for package: mysql-community-server-5.6.19-2.el7.x86_64
--> Processing Dependency: perl(File::Basename) for package: mysql-community-server-5.6.19-2.el7.x86_64
--> Processing Dependency: perl(Fcntl) for package: mysql-community-server-5.6.19-2.el7.x86_64
--> Processing Dependency: perl(Data::Dumper) for package: mysql-community-server-5.6.19-2.el7.x86_64
--> Processing Dependency: perl(DBI) for package: mysql-community-server-5.6.19-2.el7.x86_64
--> Processing Dependency: net-tools for package: mysql-community-server-5.6.19-2.el7.x86_64
--> Processing Dependency: libaio.so.1(LIBAIO_0.4)(64bit) for package: mysql-community-server-5.6.19-2.el7.x86_64
--> Processing Dependency: libaio.so.1(LIBAIO_0.1)(64bit) for package: mysql-community-server-5.6.19-2.el7.x86_64
--> Processing Dependency: /usr/bin/perl for package: mysql-community-server-5.6.19-2.el7.x86_64
--> Processing Dependency: libaio.so.1()(64bit) for package: mysql-community-server-5.6.19-2.el7.x86_64
--> Running transaction check
---> Package libaio.x86_64 0:0.3.109-12.el7 will be installed
---> Package mysql-community-client.x86_64 0:5.6.19-2.el7 will be installed
--> Processing Dependency: mysql-community-libs(x86-64) = 5.6.19-2.el7 for package: mysql-community-client-5.6.19-2.el7.x86_64
--> Processing Dependency: perl(Exporter) for package: mysql-community-client-5.6.19-2.el7.x86_64
---> Package mysql-community-common.x86_64 0:5.6.19-2.el7 will be installed
---> Package net-tools.x86_64 0:2.0-0.17.20131004git.el7 will be installed
---> Package perl.x86_64 4:5.16.3-283.el7 will be installed
--> Processing Dependency: perl-libs = 4:5.16.3-283.el7 for package: 4:perl-5.16.3-283.el7.x86_64
--> Processing Dependency: perl(Socket) >= 1.3 for package: 4:perl-5.16.3-283.el7.x86_64
--> Processing Dependency: perl(Scalar::Util) >= 1.10 for package: 4:perl-5.16.3-283.el7.x86_64
--> Processing Dependency: perl-macros for package: 4:perl-5.16.3-283.el7.x86_64
--> Processing Dependency: perl-libs for package: 4:perl-5.16.3-283.el7.x86_64
--> Processing Dependency: perl(threads::shared) for package: 4:perl-5.16.3-283.el7.x86_64
--> Processing Dependency: perl(threads) for package: 4:perl-5.16.3-283.el7.x86_64
--> Processing Dependency: perl(constant) for package: 4:perl-5.16.3-283.el7.x86_64
--> Processing Dependency: perl(Time::Local) for package: 4:perl-5.16.3-283.el7.x86_64
--> Processing Dependency: perl(Storable) for package: 4:perl-5.16.3-283.el7.x86_64
--> Processing Dependency: perl(Socket) for package: 4:perl-5.16.3-283.el7.x86_64
--> Processing Dependency: perl(Scalar::Util) for package: 4:perl-5.16.3-283.el7.x86_64
--> Processing Dependency: perl(Pod::Simple::XHTML) for package: 4:perl-5.16.3-283.el7.x86_64
--> Processing Dependency: perl(Pod::Simple::Search) for package: 4:perl-5.16.3-283.el7.x86_64
--> Processing Dependency: perl(Filter::Util::Call) for package: 4:perl-5.16.3-283.el7.x86_64
--> Processing Dependency: perl(Carp) for package: 4:perl-5.16.3-283.el7.x86_64
--> Processing Dependency: libperl.so()(64bit) for package: 4:perl-5.16.3-283.el7.x86_64
---> Package perl-DBI.x86_64 0:1.627-4.el7 will be installed
--> Processing Dependency: perl(RPC::PlServer) >= 0.2001 for package: perl-DBI-1.627-4.el7.x86_64
--> Processing Dependency: perl(RPC::PlClient) >= 0.2000 for package: perl-DBI-1.627-4.el7.x86_64
---> Package perl-Data-Dumper.x86_64 0:2.145-3.el7 will be installed
---> Package perl-File-Path.noarch 0:2.09-2.el7 will be installed
---> Package perl-File-Temp.noarch 0:0.23.01-3.el7 will be installed
---> Package perl-Getopt-Long.noarch 0:2.40-2.el7 will be installed
--> Processing Dependency: perl(Pod::Usage) >= 1.14 for package: perl-Getopt-Long-2.40-2.el7.noarch
--> Processing Dependency: perl(Text::ParseWords) for package: perl-Getopt-Long-2.40-2.el7.noarch
---> Package perl-PathTools.x86_64 0:3.40-5.el7 will be installed
--> Running transaction check
---> Package mariadb-libs.x86_64 1:5.5.37-1.el7_0 will be obsoleted
---> Package mysql-community-libs.x86_64 0:5.6.19-2.el7 will be obsoleting
---> Package perl-Carp.noarch 0:1.26-244.el7 will be installed
---> Package perl-Exporter.noarch 0:5.68-3.el7 will be installed
---> Package perl-Filter.x86_64 0:1.49-3.el7 will be installed
---> Package perl-PlRPC.noarch 0:0.2020-14.el7 will be installed
--> Processing Dependency: perl(Net::Daemon) >= 0.13 for package: perl-PlRPC-0.2020-14.el7.noarch
--> Processing Dependency: perl(Net::Daemon::Test) for package: perl-PlRPC-0.2020-14.el7.noarch
--> Processing Dependency: perl(Net::Daemon::Log) for package: perl-PlRPC-0.2020-14.el7.noarch
--> Processing Dependency: perl(Compress::Zlib) for package: perl-PlRPC-0.2020-14.el7.noarch
---> Package perl-Pod-Simple.noarch 1:3.28-4.el7 will be installed
--> Processing Dependency: perl(Pod::Escapes) >= 1.04 for package: 1:perl-Pod-Simple-3.28-4.el7.noarch
--> Processing Dependency: perl(Encode) for package: 1:perl-Pod-Simple-3.28-4.el7.noarch
---> Package perl-Pod-Usage.noarch 0:1.63-3.el7 will be installed
--> Processing Dependency: perl(Pod::Text) >= 3.15 for package: perl-Pod-Usage-1.63-3.el7.noarch
--> Processing Dependency: perl-Pod-Perldoc for package: perl-Pod-Usage-1.63-3.el7.noarch
---> Package perl-Scalar-List-Utils.x86_64 0:1.27-248.el7 will be installed
---> Package perl-Socket.x86_64 0:2.010-3.el7 will be installed
---> Package perl-Storable.x86_64 0:2.45-3.el7 will be installed
---> Package perl-Text-ParseWords.noarch 0:3.29-4.el7 will be installed
---> Package perl-Time-Local.noarch 0:1.2300-2.el7 will be installed
---> Package perl-constant.noarch 0:1.27-2.el7 will be installed
---> Package perl-libs.x86_64 4:5.16.3-283.el7 will be installed
---> Package perl-macros.x86_64 4:5.16.3-283.el7 will be installed
---> Package perl-threads.x86_64 0:1.87-4.el7 will be installed
---> Package perl-threads-shared.x86_64 0:1.43-6.el7 will be installed
--> Running transaction check
---> Package perl-Encode.x86_64 0:2.51-7.el7 will be installed
---> Package perl-IO-Compress.noarch 0:2.061-2.el7 will be installed
--> Processing Dependency: perl(Compress::Raw::Zlib) >= 2.061 for package: perl-IO-Compress-2.061-2.el7.noarch
--> Processing Dependency: perl(Compress::Raw::Bzip2) >= 2.061 for package: perl-IO-Compress-2.061-2.el7.noarch
---> Package perl-Net-Daemon.noarch 0:0.48-5.el7 will be installed
---> Package perl-Pod-Escapes.noarch 1:1.04-283.el7 will be installed
---> Package perl-Pod-Perldoc.noarch 0:3.20-4.el7 will be installed
--> Processing Dependency: perl(parent) for package: perl-Pod-Perldoc-3.20-4.el7.noarch
--> Processing Dependency: perl(HTTP::Tiny) for package: perl-Pod-Perldoc-3.20-4.el7.noarch
---> Package perl-podlators.noarch 0:2.5.1-3.el7 will be installed
--> Running transaction check
---> Package perl-Compress-Raw-Bzip2.x86_64 0:2.061-3.el7 will be installed
---> Package perl-Compress-Raw-Zlib.x86_64 1:2.061-4.el7 will be installed
---> Package perl-HTTP-Tiny.noarch 0:0.033-3.el7 will be installed
---> Package perl-parent.noarch 1:0.225-244.el7 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

 Package                            Arch              Version                              Repository                    Size
 mysql-community-libs               x86_64            5.6.19-2.el7                         mysql56-community            2.0 M
     replacing  mariadb-libs.x86_64 1:5.5.37-1.el7_0
 mysql-community-server             x86_64            5.6.19-2.el7                         mysql56-community             57 M
Installing for dependencies:
 libaio                             x86_64            0.3.109-12.el7                       base                          24 k
 mysql-community-client             x86_64            5.6.19-2.el7                         mysql56-community             19 M
 mysql-community-common             x86_64            5.6.19-2.el7                         mysql56-community            247 k
 net-tools                          x86_64            2.0-0.17.20131004git.el7             base                         304 k
 perl                               x86_64            4:5.16.3-283.el7                     base                         8.0 M
 perl-Carp                          noarch            1.26-244.el7                         base                          19 k
 perl-Compress-Raw-Bzip2            x86_64            2.061-3.el7                          base                          32 k
 perl-Compress-Raw-Zlib             x86_64            1:2.061-4.el7                        base                          57 k
 perl-DBI                           x86_64            1.627-4.el7                          base                         802 k
 perl-Data-Dumper                   x86_64            2.145-3.el7                          base                          47 k
 perl-Encode                        x86_64            2.51-7.el7                           base                         1.5 M
 perl-Exporter                      noarch            5.68-3.el7                           base                          28 k
 perl-File-Path                     noarch            2.09-2.el7                           base                          26 k
 perl-File-Temp                     noarch            0.23.01-3.el7                        base                          56 k
 perl-Filter                        x86_64            1.49-3.el7                           base                          76 k
 perl-Getopt-Long                   noarch            2.40-2.el7                           base                          56 k
 perl-HTTP-Tiny                     noarch            0.033-3.el7                          base                          38 k
 perl-IO-Compress                   noarch            2.061-2.el7                          base                         260 k
 perl-Net-Daemon                    noarch            0.48-5.el7                           base                          51 k
 perl-PathTools                     x86_64            3.40-5.el7                           base                          82 k
 perl-PlRPC                         noarch            0.2020-14.el7                        base                          36 k
 perl-Pod-Escapes                   noarch            1:1.04-283.el7                       base                          49 k
 perl-Pod-Perldoc                   noarch            3.20-4.el7                           base                          87 k
 perl-Pod-Simple                    noarch            1:3.28-4.el7                         base                         216 k
 perl-Pod-Usage                     noarch            1.63-3.el7                           base                          27 k
 perl-Scalar-List-Utils             x86_64            1.27-248.el7                         base                          36 k
 perl-Socket                        x86_64            2.010-3.el7                          base                          49 k
 perl-Storable                      x86_64            2.45-3.el7                           base                          77 k
 perl-Text-ParseWords               noarch            3.29-4.el7                           base                          14 k
 perl-Time-Local                    noarch            1.2300-2.el7                         base                          24 k
 perl-constant                      noarch            1.27-2.el7                           base                          19 k
 perl-libs                          x86_64            4:5.16.3-283.el7                     base                         686 k
 perl-macros                        x86_64            4:5.16.3-283.el7                     base                          42 k
 perl-parent                        noarch            1:0.225-244.el7                      base                          12 k
 perl-podlators                     noarch            2.5.1-3.el7                          base                         112 k
 perl-threads                       x86_64            1.87-4.el7                           base                          49 k
 perl-threads-shared                x86_64            1.43-6.el7                           base                          39 k

Transaction Summary
Install  2 Packages (+37 Dependent packages)

Total download size: 91 M
Is this ok [y/d/N]: y
Downloading packages:
warning: /var/cache/yum/x86_64/7/mysql56-community/packages/mysql-community-common-5.6.19-2.el7.x86_64.rpm: V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Public key for mysql-community-common-5.6.19-2.el7.x86_64.rpm is not installed
(1/39): mysql-community-common-5.6.19-2.el7.x86_64.rpm                                                 | 247 kB  00:00:01     
(2/39): libaio-0.3.109-12.el7.x86_64.rpm                                                               |  24 kB  00:00:03     
(3/39): mysql-community-libs-5.6.19-2.el7.x86_64.rpm                                                   | 2.0 MB  00:00:05     
(4/39): perl-Carp-1.26-244.el7.noarch.rpm                                                              |  19 kB  00:00:02     
(5/39): perl-Compress-Raw-Bzip2-2.061-3.el7.x86_64.rpm                                                 |  32 kB  00:00:01     
(6/39): net-tools-2.0-0.17.20131004git.el7.x86_64.rpm                                                  | 304 kB  00:00:04     
(7/39): perl-Compress-Raw-Zlib-2.061-4.el7.x86_64.rpm                                                  |  57 kB  00:00:01     
(8/39): perl-Data-Dumper-2.145-3.el7.x86_64.rpm                                                        |  47 kB  00:00:01     
(9/39): perl-DBI-1.627-4.el7.x86_64.rpm                                                                | 802 kB  00:00:03     
(10/39): perl-Exporter-5.68-3.el7.noarch.rpm                                                           |  28 kB  00:00:00     
(11/39): perl-File-Path-2.09-2.el7.noarch.rpm                                                          |  26 kB  00:00:00     
(12/39): perl-File-Temp-0.23.01-3.el7.noarch.rpm                                                       |  56 kB  00:00:00     
(13/39): perl-Filter-1.49-3.el7.x86_64.rpm                                                             |  76 kB  00:00:00     
(14/39): perl-Getopt-Long-2.40-2.el7.noarch.rpm                                                        |  56 kB  00:00:00     
(15/39): perl-HTTP-Tiny-0.033-3.el7.noarch.rpm                                                         |  38 kB  00:00:00     
(16/39): perl-IO-Compress-2.061-2.el7.noarch.rpm                                                       | 260 kB  00:00:01     
(17/39): perl-Encode-2.51-7.el7.x86_64.rpm                                                             | 1.5 MB  00:00:08     
(18/39): perl-Net-Daemon-0.48-5.el7.noarch.rpm                                                         |  51 kB  00:00:03     
(19/39): perl-PlRPC-0.2020-14.el7.noarch.rpm                                                           |  36 kB  00:00:00     
(20/39): perl-Pod-Escapes-1.04-283.el7.noarch.rpm                                                      |  49 kB  00:00:00     
(21/39): perl-Pod-Perldoc-3.20-4.el7.noarch.rpm                                                        |  87 kB  00:00:01     
(22/39): perl-Pod-Simple-3.28-4.el7.noarch.rpm                                                         | 216 kB  00:00:02     
(23/39): perl-Pod-Usage-1.63-3.el7.noarch.rpm                                                          |  27 kB  00:00:00     
(24/39): perl-Scalar-List-Utils-1.27-248.el7.x86_64.rpm                                                |  36 kB  00:00:00     
(25/39): perl-Socket-2.010-3.el7.x86_64.rpm                                                            |  49 kB  00:00:00     
(26/39): perl-Storable-2.45-3.el7.x86_64.rpm                                                           |  77 kB  00:00:00     
(27/39): perl-Text-ParseWords-3.29-4.el7.noarch.rpm                                                    |  14 kB  00:00:00     
(28/39): perl-Time-Local-1.2300-2.el7.noarch.rpm                                                       |  24 kB  00:00:00     
(29/39): perl-constant-1.27-2.el7.noarch.rpm                                                           |  19 kB  00:00:00     
(30/39): perl-PathTools-3.40-5.el7.x86_64.rpm                                                          |  82 kB  00:00:12     
(31/39): perl-libs-5.16.3-283.el7.x86_64.rpm                                                           | 686 kB  00:00:03     
(32/39): perl-parent-0.225-244.el7.noarch.rpm                                                          |  12 kB  00:00:00     
(33/39): perl-podlators-2.5.1-3.el7.noarch.rpm                                                         | 112 kB  00:00:00     
(34/39): perl-threads-1.87-4.el7.x86_64.rpm                                                            |  49 kB  00:00:00     
(35/39): perl-threads-shared-1.43-6.el7.x86_64.rpm                                                     |  39 kB  00:00:00     
(36/39): perl-macros-5.16.3-283.el7.x86_64.rpm                                                         |  42 kB  00:00:05     
(37/39): mysql-community-client-5.6.19-2.el7.x86_64.rpm                                                |  19 MB  00:00:57     
(38/39): perl-5.16.3-283.el7.x86_64.rpm                                                                | 8.0 MB  00:01:13     
(39/39): mysql-community-server-5.6.19-2.el7.x86_64.rpm                                                |  57 MB  00:01:35     
Total                                                                                         915 kB/s |  91 MB  00:01:41     
Retrieving key from file:/etc/pki/rpm-gpg/RPM-GPG-KEY-mysql
Importing GPG key 0x5072E1F5:
 Userid     : "MySQL Release Engineering "
 Fingerprint: a4a9 4068 76fc bd3c 4567 70c8 8c71 8d3b 5072 e1f5
 Package    : mysql-community-release-el7-5.noarch (@/mysql-community-release-el7-5.noarch)
 From       : file:/etc/pki/rpm-gpg/RPM-GPG-KEY-mysql
Is this ok [y/N]: y
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Installing : mysql-community-common-5.6.19-2.el7.x86_64                                                                1/40 
  Installing : mysql-community-libs-5.6.19-2.el7.x86_64                                                                  2/40 
  Installing : 1:perl-parent-0.225-244.el7.noarch                                                                        3/40 
  Installing : perl-HTTP-Tiny-0.033-3.el7.noarch                                                                         4/40 
  Installing : perl-podlators-2.5.1-3.el7.noarch                                                                         5/40 
  Installing : perl-Pod-Perldoc-3.20-4.el7.noarch                                                                        6/40 
  Installing : 1:perl-Pod-Escapes-1.04-283.el7.noarch                                                                    7/40 
  Installing : perl-Text-ParseWords-3.29-4.el7.noarch                                                                    8/40 
  Installing : perl-Encode-2.51-7.el7.x86_64                                                                             9/40 
  Installing : perl-Pod-Usage-1.63-3.el7.noarch                                                                         10/40 
  Installing : 4:perl-libs-5.16.3-283.el7.x86_64                                                                        11/40 
  Installing : 4:perl-macros-5.16.3-283.el7.x86_64                                                                      12/40 
  Installing : perl-Storable-2.45-3.el7.x86_64                                                                          13/40 
  Installing : perl-Exporter-5.68-3.el7.noarch                                                                          14/40 
  Installing : perl-constant-1.27-2.el7.noarch                                                                          15/40 
  Installing : perl-Time-Local-1.2300-2.el7.noarch                                                                      16/40 
  Installing : perl-Socket-2.010-3.el7.x86_64                                                                           17/40 
  Installing : perl-Carp-1.26-244.el7.noarch                                                                            18/40 
  Installing : perl-PathTools-3.40-5.el7.x86_64                                                                         19/40 
  Installing : perl-Scalar-List-Utils-1.27-248.el7.x86_64                                                               20/40 
  Installing : perl-File-Temp-0.23.01-3.el7.noarch                                                                      21/40 
  Installing : perl-File-Path-2.09-2.el7.noarch                                                                         22/40 
  Installing : perl-threads-shared-1.43-6.el7.x86_64                                                                    23/40 
  Installing : perl-threads-1.87-4.el7.x86_64                                                                           24/40 
  Installing : perl-Filter-1.49-3.el7.x86_64                                                                            25/40 
  Installing : 1:perl-Pod-Simple-3.28-4.el7.noarch                                                                      26/40 
  Installing : perl-Getopt-Long-2.40-2.el7.noarch                                                                       27/40 
  Installing : 4:perl-5.16.3-283.el7.x86_64                                                                             28/40 
  Installing : perl-Data-Dumper-2.145-3.el7.x86_64                                                                      29/40 
  Installing : perl-Compress-Raw-Bzip2-2.061-3.el7.x86_64                                                               30/40 
  Installing : perl-Net-Daemon-0.48-5.el7.noarch                                                                        31/40 
  Installing : mysql-community-client-5.6.19-2.el7.x86_64                                                               32/40 
  Installing : 1:perl-Compress-Raw-Zlib-2.061-4.el7.x86_64                                                              33/40 
  Installing : perl-IO-Compress-2.061-2.el7.noarch                                                                      34/40 
  Installing : perl-PlRPC-0.2020-14.el7.noarch                                                                          35/40 
  Installing : perl-DBI-1.627-4.el7.x86_64                                                                              36/40 
  Installing : libaio-0.3.109-12.el7.x86_64                                                                             37/40 
  Installing : net-tools-2.0-0.17.20131004git.el7.x86_64                                                                38/40 
  Installing : mysql-community-server-5.6.19-2.el7.x86_64                                                               39/40 
  Erasing    : 1:mariadb-libs-5.5.37-1.el7_0.x86_64                                                                     40/40 
  Verifying  : perl-HTTP-Tiny-0.033-3.el7.noarch                                                                         1/40 
  Verifying  : perl-threads-shared-1.43-6.el7.x86_64                                                                     2/40 
  Verifying  : perl-Storable-2.45-3.el7.x86_64                                                                           3/40 
  Verifying  : net-tools-2.0-0.17.20131004git.el7.x86_64                                                                 4/40 
  Verifying  : perl-Exporter-5.68-3.el7.noarch                                                                           5/40 
  Verifying  : perl-constant-1.27-2.el7.noarch                                                                           6/40 
  Verifying  : perl-PathTools-3.40-5.el7.x86_64                                                                          7/40 
  Verifying  : 4:perl-5.16.3-283.el7.x86_64                                                                              8/40 
  Verifying  : libaio-0.3.109-12.el7.x86_64                                                                              9/40 
  Verifying  : mysql-community-server-5.6.19-2.el7.x86_64                                                               10/40 
  Verifying  : 1:perl-parent-0.225-244.el7.noarch                                                                       11/40 
  Verifying  : perl-Compress-Raw-Bzip2-2.061-3.el7.x86_64                                                               12/40 
  Verifying  : perl-Net-Daemon-0.48-5.el7.noarch                                                                        13/40 
  Verifying  : 1:perl-Pod-Simple-3.28-4.el7.noarch                                                                      14/40 
  Verifying  : perl-File-Temp-0.23.01-3.el7.noarch                                                                      15/40 
  Verifying  : 4:perl-libs-5.16.3-283.el7.x86_64                                                                        16/40 
  Verifying  : perl-Time-Local-1.2300-2.el7.noarch                                                                      17/40 
  Verifying  : mysql-community-libs-5.6.19-2.el7.x86_64                                                                 18/40 
  Verifying  : perl-Pod-Perldoc-3.20-4.el7.noarch                                                                       19/40 
  Verifying  : perl-DBI-1.627-4.el7.x86_64                                                                              20/40 
  Verifying  : perl-Socket-2.010-3.el7.x86_64                                                                           21/40 
  Verifying  : 4:perl-macros-5.16.3-283.el7.x86_64                                                                      22/40 
  Verifying  : mysql-community-client-5.6.19-2.el7.x86_64                                                               23/40 
  Verifying  : perl-Carp-1.26-244.el7.noarch                                                                            24/40 
  Verifying  : perl-Data-Dumper-2.145-3.el7.x86_64                                                                      25/40 
  Verifying  : perl-Scalar-List-Utils-1.27-248.el7.x86_64                                                               26/40 
  Verifying  : 1:perl-Compress-Raw-Zlib-2.061-4.el7.x86_64                                                              27/40 
  Verifying  : perl-IO-Compress-2.061-2.el7.noarch                                                                      28/40 
  Verifying  : perl-Pod-Usage-1.63-3.el7.noarch                                                                         29/40 
  Verifying  : perl-PlRPC-0.2020-14.el7.noarch                                                                          30/40 
  Verifying  : perl-Encode-2.51-7.el7.x86_64                                                                            31/40 
  Verifying  : perl-podlators-2.5.1-3.el7.noarch                                                                        32/40 
  Verifying  : perl-Getopt-Long-2.40-2.el7.noarch                                                                       33/40 
  Verifying  : perl-File-Path-2.09-2.el7.noarch                                                                         34/40 
  Verifying  : perl-threads-1.87-4.el7.x86_64                                                                           35/40 
  Verifying  : perl-Filter-1.49-3.el7.x86_64                                                                            36/40 
  Verifying  : 1:perl-Pod-Escapes-1.04-283.el7.noarch                                                                   37/40 
  Verifying  : perl-Text-ParseWords-3.29-4.el7.noarch                                                                   38/40 
  Verifying  : mysql-community-common-5.6.19-2.el7.x86_64                                                               39/40 
  Verifying  : 1:mariadb-libs-5.5.37-1.el7_0.x86_64                                                                     40/40 

  mysql-community-libs.x86_64 0:5.6.19-2.el7                   mysql-community-server.x86_64 0:5.6.19-2.el7                  

Dependency Installed:
  libaio.x86_64 0:0.3.109-12.el7                                mysql-community-client.x86_64 0:5.6.19-2.el7                 
  mysql-community-common.x86_64 0:5.6.19-2.el7                  net-tools.x86_64 0:2.0-0.17.20131004git.el7                  
  perl.x86_64 4:5.16.3-283.el7                                  perl-Carp.noarch 0:1.26-244.el7                              
  perl-Compress-Raw-Bzip2.x86_64 0:2.061-3.el7                  perl-Compress-Raw-Zlib.x86_64 1:2.061-4.el7                  
  perl-DBI.x86_64 0:1.627-4.el7                                 perl-Data-Dumper.x86_64 0:2.145-3.el7                        
  perl-Encode.x86_64 0:2.51-7.el7                               perl-Exporter.noarch 0:5.68-3.el7                            
  perl-File-Path.noarch 0:2.09-2.el7                            perl-File-Temp.noarch 0:0.23.01-3.el7                        
  perl-Filter.x86_64 0:1.49-3.el7                               perl-Getopt-Long.noarch 0:2.40-2.el7                         
  perl-HTTP-Tiny.noarch 0:0.033-3.el7                           perl-IO-Compress.noarch 0:2.061-2.el7                        
  perl-Net-Daemon.noarch 0:0.48-5.el7                           perl-PathTools.x86_64 0:3.40-5.el7                           
  perl-PlRPC.noarch 0:0.2020-14.el7                             perl-Pod-Escapes.noarch 1:1.04-283.el7                       
  perl-Pod-Perldoc.noarch 0:3.20-4.el7                          perl-Pod-Simple.noarch 1:3.28-4.el7                          
  perl-Pod-Usage.noarch 0:1.63-3.el7                            perl-Scalar-List-Utils.x86_64 0:1.27-248.el7                 
  perl-Socket.x86_64 0:2.010-3.el7                              perl-Storable.x86_64 0:2.45-3.el7                            
  perl-Text-ParseWords.noarch 0:3.29-4.el7                      perl-Time-Local.noarch 0:1.2300-2.el7                        
  perl-constant.noarch 0:1.27-2.el7                             perl-libs.x86_64 4:5.16.3-283.el7                            
  perl-macros.x86_64 4:5.16.3-283.el7                           perl-parent.noarch 1:0.225-244.el7                           
  perl-podlators.noarch 0:2.5.1-3.el7                           perl-threads.x86_64 0:1.87-4.el7                             
  perl-threads-shared.x86_64 0:1.43-6.el7                      

  mariadb-libs.x86_64 1:5.5.37-1.el7_0                                                                                        


Como podrá apreciar, los paquetes del servidor Community hacen referencia a la última versión de MySQL 5.6. Durante el proceso de instalación, sólo dos interrupciones se producirán (aparte de la petición de contraseña de sudo), una para la confirmación de los cambios, y otra para la importación de la clave de releases de los ingenieros de Oracle en su sistema, que podremos aceptar sin problemas si su fingerprint conincide con el siguiente: a4a9 4068 76fc bd3c 4567 70c8 8c71 8d3b 5072 e1f5. Recuerde que, en el caso de procesos automatizados, podemos añadir la opción -y (decir sí a todo) a yum, pero quería mostrar el proceso completo, así como realizar el proceso con buenas prácticas de seguridad.

La instalación ya se ha completado, ahora sólo tenemos que ejecutarlo y probarlo. Recuerde que Red Hat Enterprise Linux 7 reemplaza la gestión de servicios por systemd, por lo que la manera “correcta” de iniciar el servicio de mysql es:

$ sudo systemctl start mysqld

Puede comprobar que ha iniciado correctamente haciendo:

$ sudo systemctl status mysqld
mysqld.service - MySQL Community Server
  Loaded: loaded (/usr/lib/systemd/system/mysqld.service; disabled)
  Active: active (running) since Sun 2014-07-27 11:20:40 EDT; 10s ago
 Process: 10660 ExecStartPost=/usr/bin/mysql-systemd-start post (code=exited, status=0/SUCCESS)
 Process: 10599 ExecStartPre=/usr/bin/mysql-systemd-start pre (code=exited, status=0/SUCCESS)
Main PID: 10659 (mysqld_safe)
  CGroup: /system.slice/mysqld.service
          ├─10659 /bin/sh /usr/bin/mysqld_safe
          └─10801 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --log-erro...

Jul 27 11:20:39 localhost.localdomain mysql-systemd-start[10599]: Support MySQL by buying support/licenses at http://sho...com
Jul 27 11:20:39 localhost.localdomain mysql-systemd-start[10599]: Note: new default config file not created.
Jul 27 11:20:39 localhost.localdomain mysql-systemd-start[10599]: Please make sure your config file is current
Jul 27 11:20:39 localhost.localdomain mysql-systemd-start[10599]: WARNING: Default config file /etc/my.cnf exists on the...tem
Jul 27 11:20:39 localhost.localdomain mysql-systemd-start[10599]: This file will be read by default by the MySQL server
Jul 27 11:20:39 localhost.localdomain mysql-systemd-start[10599]: If you do not want to use this, either remove it, or use the
Jul 27 11:20:39 localhost.localdomain mysql-systemd-start[10599]: --defaults-file argument to mysqld_safe when starting ...ver
Jul 27 11:20:39 localhost.localdomain mysqld_safe[10659]: 140727 11:20:39 mysqld_safe Logging to '/var/log/mysqld.log'.
Jul 27 11:20:39 localhost.localdomain mysqld_safe[10659]: 140727 11:20:39 mysqld_safe Starting mysqld daemon with datab...ysql
Jul 27 11:20:40 localhost.localdomain systemd[1]: Started MySQL Community Server.
Hint: Some lines were ellipsized, use -l to show in full.

Y conectarse desde localhost haciendo:

$ mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.19 MySQL Community Server (GPL)

Copyright (c) 2000, 2014, 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

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


Recuerda también activar el autoinicio en el arranque, ya que es algo que querrás en la mayoría de los casos (esto también ha cambiado comparado con CentOS 6):

$ sudo systemctl enable mysqld.service
ln -s '/usr/lib/systemd/system/mysqls.service' '/etc/systemd/system/mysql.service'
ln -s '/usr/lib/systemd/system/mysqls.service' '/etc/systemd/system/multi-user.target.wants/mysqld.service'

Puede comprobar que se ha activado correctamente con el comando ‘status’ mostrado anteriormente; debería mostrarse ahora como “enabled” (activado).

Como buen administrador, los siguientes pasos son configurar las cuentas de usuario y securizar el servicio de mysql, pero eso está fuera de los objetivos de este tutorial.

Gracias a la instalación mediante respositorios, sus paquetes ahora pueden ser actualizados fácilmente tan sólo usando yum.

Para una documentación más detallada, puede revisar la documentación oficial. Los ingenieros de Oracle publicaron también una interestante historia sobr el proceso de pruebas de sus paquetes.

Espero que este tutorial haya sido de utilidad.

Which Compression Tool Should I Use for my Database Backups? (Part I: Compression)

This week we are talking about size, which is a subject that should matter to any system administrator in charge of the backup system of any project, and in particular database backups.

I sometimes get questions about what should be the best compression tool to apply during a particular backup system: gzip? bzip2? any other?

The testing environment

In order to test several formats and tools, I created a .csv file (comma-separated values) that was 3,700,635,579 bytes in size by transforming a recent dump of all the OpenStreetMap nodes of the European portion of Spain. It had a total of 46,741,126 rows and looked like this:

171773  38.6048402      -0.0489871      4       2012-08-25 00:37:46     12850816        472193  rubensd
171774  38.6061981      -0.0496867      2       2008-01-19 10:23:21     666916  9250    j3m
171775  38.6067166      -0.0498342      2       2008-01-19 10:23:21     666916  9250    j3m
171776  38.6028122      -0.0497136      5       2012-08-25 00:26:40     12850816        472193  rubensd
171933  40.4200658      -3.7016652      6       2011-11-29 05:37:42     9984625 33103   sergionaranja 

In fact, the original file is really a tsv (tab-separated values), not a csv, but only because I am lazy when importing/exporting to MySQL to add the extra FIELDS SEPARATED BY ','. You can download this file in 7z format, or create your own from the Geofabrik OpenStreetMap Data Extracts.

All tests were done on an almost-idle Intel Quad Core i7-3770@3.40GHz with hyper threading, exposing 8 cpus to the kernel. 32 GB of ram. 2 spinning disks of 3 TB in RAID 1. All running on CentOS 6.5 x86_64. The filesystem type was ext4 with the default OS options.

On-table sizes

For an import to MySQL, I proposed the following table structure:

CREATE TABLE `nodes` (
  `id` bigint PRIMARY KEY,
  `lat` decimal(9,7),
  `lon` decimal(10,7),
  `version` int,
  `timestamp` timestamp,
  `changeset` bigint,
  `uid` bigint,
  `user` varchar(255)

And these are the sizes on database (once we made sure there were no pending write operations):

  • MySQL MyISAM data file (.MYD): 2,755,256,596 bytes.(*)
  • MySQL InnoDB tablespace (.ibd): 3,686,793,216 bytes.
  • MySQL InnoDB tablespace using row_format=compressed (.ibd): 1,736,441,856 bytes.

Why is it taking more space on plain text than on the database? Well, despite databases being optimised for fast access and not for space, as we are using very compact set of datatypes (integers and timestamps instead of strings), actually saving disk space. This is why a proper database design is critical for performance!

We can see that one of the few reason why people are still using MyISAM is because it is a very simple and compact format. (*)However, to be fair, we are not having into account the extra 674,940,928 bytes for the primary key (.MYI), making the difference not so big. On the other side, we are not taking into account that InnoDB index size goes up quite significantly when using multiple secondary keys (due to the storage of the primary key, if it is large enough) and the many other structures (tablespace 0, transaction logs) that are needed for InnoDB to work properly, shared with other tables. In general, it is impossible to do a fair comparison between MyISAM and InnoDB because we are comparing apples and oranges.

What it is clear is that compression (in this case I used the default InnoDB zlib algorithm with the default level of compression-6) helps reduce on-disk size, potentially helping for some specific scenarios: more tables to fit in SSDs, or less IOPS for a disk-bound database. On the other side, the initial load from a file took significantly more. I do not want to show time measurements for the different table imports because it is not trivial to account the actual time to disk due to all the buffering occurring at database level, and giving the time of SQL execution would be unfair. I talk more about import times in this post.

Global results

The sizes in table are only showed as reference, our main goal was to test the several tools available for compressing the original nodes.csv file. I constrained myself to some of the most popular ones, and you can see the final results on the following table (analysis, explanation and discussion or results follows afterwards):

Original size  3700635579 bytes					
method         median compression  compressed size  compression ratio         compression cpu
               time (seconds)      (bytes)          (new_size/original_size)  usage (unix %CPU)
dd               10.146            3700635579       100.00%                    97 -  68
gzip            113.796             614119104        16.59%                   100 -  89
gzip -1          43.219             729259339        19.71%                   100 -  67
gzip -9         266.991             585777285        15.83%                    97 -  77
bzip2           294.568             525839069        14.21%                    95 -  89
bzip2 -1        281.337             508276130        13.73%                   100 -  80
bzip2 -9        295.510             585777285        15.83%                   100 -  95
pigz             27.325             614093952        16.59%                   770 - 547
pigz -1          25.982             728206796        19.68%                   231 - 159
pigz -9          51.821             585756379        15.83%                   773 - 659
pbzip2           74.874             526311578        14.22%                   794 - 663
pbzip2 -1        60.487             508782016        13.75%                   800 - 495
pbzip2 -9*       76.597             526311578        14.22%                   773 - 394
lzip           2138.230             357788948         9.67%                   100 -  70
7za             833.611             380728938        10.29%                   172 - 145
7za "ultra"    1286.044             354107250         9.57%                   178 - 164
plzip           216.942             376484712        10.17%                   768 - 373
plzip -1         50.151             581552529        15.71%                   781 - 738
plzip "ultra"   426.486             354095395         9.57%                   785 - 159
lzop             15.505            1003908508        27.13%                    95 -  50
lzop -1          13.080            1000938935        27.05%                    90 -  63
lzop -9         487.850             782234410        21.14%                    99 -  89
lz4               8.537            1043868501        28.21%                    93 -  65
lz4 -1*           8.574            1043868501        28.21%                    94 -  65
lz4 -9           96.171             816582329        22.07%                    99 -  66

As you can see, I evaluated several tools on their default modes, plus additionally “high-compression mode” and a “fast mode”. For them, I tried to evaluate 3 different parameters important for the creation of compressed files: time to completion, final file size and resources used. Please note that I only evaluated compression tools, and not archiving ones (like tar or zip). The latter tools can usually use different algorithms for compressing each file individually or the final full archive.

The first data column shows the number of seconds of wall clock time that took for the process to write the compressed file to a different partition on the same set of RAID disks. Several runs of:

time [application] -c < nodes.csv > /output/nodes.csv.[format]

were executed (except for 7z and dd, where the syntax is different) and the median vale was taken, in order to minimise measure errors due to external factors. For each run, the final file was checked for correctness (the compressed file is deterministic and it can be extracted into the original file without errors or differences) and then deleted. The results are not too scientific, as the filesystem cache can be heavily used for both reads and writes, but I tried to focus on that scenario in particular. An execution of dd (copying the file without compression) is also shown as a control value.

I think the second and third data columns are self-explanatory: the file size, in bytes, of the compressed file and how it compares with the original file.

The last column tries to measure the max and min CPU usage, as reported by the operating system during compression. However, due to the cpu scheduler, and the fact that most tools have a synchronisation period at the beginning and at end of the execution, together with the fact that is was obtained by polling its value at intervals, it is not very significative except for checking the parallelism of the algorithm used. Values greater than 100 means that more than core/thread is being used for compression.

I did not registered the memory usage (the other important resource) because even on ultra modes, its usage was not significative for my 32 GB machine (less than 1/2 GB every time, most of the times much less). I considered it was something one should not worry too much for a machine that should have enough free RAM like a database server. What you probably would like to have into account is the effects on the filesystem cache, as that could impact directly on the MySQL performance. Preventing backup page reads and writes going into the filesystem cache can be done playing around with the flag POSIX_FADV_DONTNEED. I want to mention also that there are tools, like bzip, that have a small footprint mode: bzip2 --small.

You can find the measures concerning decompression times on a followup post: Part II.

The global results may be appreciated much more clearly plotted on a bidimensional graph. I have plotted the obtained values with the time to compression on the X axis (lower is better) and the compression ratio on the Y axis (lower is better):

Time and ratio comparison of gzip, bzip2, pigz, pbzip2, lzip, p7zip, plzip, lzop and lz4 compression, with different levels and parameters
Not plotted: dd (100% compression ratio), 7za “ultra” (>21 minutes for compression) and lzip (>35 minutes for compression).

In general, we can see that there are no magical tools, and that a better compression ratio requires more time (size is inversely proportional to time). I have plotted also the function y = 200/x + 9. That, or something like y = 200/x+9.5(it is difficult to provide a good correlation with so little number of matches, most of them unrelated) seems to provide the lower limit of ratio per unit of time, suggesting that 9%-9.5% would be the maximum compress ration obtainable for that file with the available tools at the moment.

Let’s analyse what are the weaknesses and strong points of every compression format.

The well-known gzip and bzip2

If you want compatibility, gzip and bzip2 are the kings. Not only they are widely recognised compression formats, but the tools for compress and decompress are preinstalled in most unix-like operating systems. Probably Windows is the only operating system that doesn’t support gzip by default. gzip and bzip2 are the only compressions with its own letter on tar (with compress on BSD and xz on GNU).

Compatibility and availability are the strong points of these tools, however, if we look at the graph, we can see that they are relatively far from the line I mentioned as “ideal” in time/size ratio. bzip2 provides a better compression ratio than gzip in exchange of more cpu cycles, but both tools are single-threaded and they do not shine in any aspect. Surprisingly enough, bzip2 -1 provided me with a worse compression time and better rate than standard bzip2 execution, and the manual for the gnu version provides an explanation for that:

The --fast and --best aliases are primarily for  GNU  gzip
compatibility.   In  particular,  --fast doesn't make things significantly faster.  And --best
merely selects the default behaviour.

Probably the best use I would recommend for this tools is gzip --fast (equivalent to gzip -1) that, while not providing a great compression rate, it does it in a very fast way even for a single-thread application. So it can be useful in those cases where we want to maximise speed without taking many resources. In other cases, where tool availability is not a problem, I would recommend you trying other tools with either better speed or better compression ratio.

I used GNU versions gzip 1.3.12 and bzip2 1.0.6.

Parallel compression tools: pigz and pbzip2

Things get more interesting if you use the parallel versions of gzip and bzip2 on a multi-core system. While there are more than one version, I choose pigz 2.3.1 and pbzip2 1.1.6 for my tests. While they are not part of the official Red Hat/CentOS repositories, they can found on EPEL and Debian repositories.

Both tools auto-detect the number of cores I had and performed the compression in 8 threads, providing comparable compression ratios in about 4 times less time. The obvious downsize is that in a high-demanding environment, like a MySQL server under considerable load, you may not want/can’t provide full CPU resources to the backup process. But if you are doing the compression on a separated dedicated server, parallelization is something you should get advantage of, as in general CPU will be the main bottleneck on a compression algorithm.

Again, as a highlight, pigz with the default parameters provided me a good compression ration (16,89%) in less than 28 seconds- that is compressing at close to 130MB/s for my modest hardware (that is more than a third of my copy rate, 350MB/s).

As a side note, while pbzip2 accepts a compression level as a parameter, the default compression level is -9.

lzma implementations: lzip, 7zip and plzip

The next tests performed were simply different lzma implementations, an algorithm that has the fame of providing very good compression rates.

I started with lzip. It is not on the official repositories, so I got it from EPEL, installing lzip 1.7. The compression ratio was, effectively, the best of all other algorithms (close to 9.5%) but it took 35 minutes and 38 seconds to produce the output. Not only the algorithm was to blame: it used a single thread, hence the delay.

After that, I tried p7zip 9.20, in particular the unix tool 7za. This one the only tool tested that did not conformed to the gzip parameters. I had to execute it using:

time 7za a /output/nodes.csv.7z nodes.csv

Please note that p7zip is an archiver tool, but I made an exception in order to test an alternative implementation of lzma.

The results were better: while the tool provided a slightly worse compression ration (10.29%), thanks to some kind of execution in more than one thread, the execution time was reduced to just under 14 minutes. I also tested a suggested “ultra” mode found in the 7za manual, with the following parameters:

-t7z -m0=lzma -mx=9 -mfb=64 -md=32m -ms=on

In short: maximising the memory usage, compression level and dictionary size -aside from enforcing the archive format and compression algorithm. While this provided with a smaller file size (but only 25 MB smaller, less than a 1% of the original file), the time went up to more than 21 minutes.

I wanted to try a real parallel implementation of lzma, and plzip was exactly that. I could not find a rpm package anywhere, so I downloaded and installed from source code Lzlib 1.5 and plzip 1.2-rc2. The results were really good, as expected. plzip provided comparable results to “pigz -9” when running in “fast mode”; but by default, in only 3m37s I got a 359MB compressed file, or 10.17% of the original file. I then tried to emulate the ultra settings of p7zip (with -9 -m 64 -s 33554432) and got the winner tool in compression ratio (9.57%) in only 7 minutes and 6.486 seconds.

Obviously, the same restrictions that I mentioned for the other parallel tools apply here: usage of multiple cpus may be discuraged for a very busy server, but if you are storing the backups for long-term on a separate server, you may want to have a look at this possibility. In any case, most parallel tools have a way to limit the number of threads created (for example with the --threads option in lzip).

Fast compression tools: lzop and lz4

I didn’t want to finish my testing without having a look some of the high-bandwidth compression tools, and I choose 2: lzop and lz4. While I had to install lz4 r119 from EPEL, lzop v1.02rc1 is part of the base packages of Red Hat/CentOS.

They both provide what they promise: very fast compression algorithms (in some cases, faster than a plain copy of a file, as they are not CPU-bound but they have to write less amount of data) in exchange for worse compression ratios (21-30%). For the example file, on my machine, I got better performance for lz4 than lzop, offering similar ratios but in less time (8.5 vs. 15.5 seconds). So if I had to choose, I would probably would use lz4 over lzop in my particular case. Additionally, although it has not been tested, lz4 boasts of having better decompression speeds.

As a negative highlight, I would recommend against using lzop -9, as there are tools that could get you better compression ratios in half the time. lz4 did not perform well also with a higher compression level, so I recommend you to stick to the defaults or lower compression levels for these tools (in fact, lz4 defaults to -1).


I didn’t test other tools like compress (Lempel-Ziv), xz (lzma2) or QuickLZ, but I do not expect too many deviations from the patterns we have seen: time needed is inversely proportional to compression level. If you want fast compression times, go for lz4. If you want a small file size, go for an implementation of lzma, like p7zip. bzip and gzip formats are good options when compatibility is important (e.g. releasing a file), but when possible, use a parallel compress implementation to improve its performance (plzip, pbzip2, pigz). We can even use a combination of tools for our backups, for example, export our tables in binary format using lz4 to get them outside of the mysql server, and later, on a separate server, convert it to lzma for long-term storage.

I would also tell you to try the compression methods for your particular dataset and hardware, as you may get different compression ratios and timings, specially depending on the amount of memory available for filesystem caching, your cpu(s) and your read and write rate from secondary storage. What I have tried to do with this, however, is a starting point for you to get your own conclusions.

Do you agree with me? Do you think I am wrong at some point? Did you miss something? Write a comment or send me a replay on twitter or by email.

Check out Part II of this analysis for my research on decompression times.