MySQL WTF
Mysql es una base de datos Open Source fiable y rápida. Ideal para la llamada “Web 2.0″ (o 3.0, no se por qué número vamos ya). Las grandes plataformas web no pueden depender de una base de datos relacional al estilo tradicional. Los datos se encuentran particionados en diferentes tablas, cada tabla casi en su propia granja de servidores. Los conceptos de Foreign Key, integridad referencial, normalización, etc. tan venerados por los diseñadores de bases de datos no tienen sentido. Ninguna base de datos ni máquina puede escalar a los niveles que necesitan las grandes plataformas web.
La responsabilidad sobre la lógica, la gestión de los datos, la gestión de la integridad se desplaza a los frontales, el código de la aplicación, instalado en cientos de servidores, y que por tanto escala casi linealmente frente al cuello de botella de las bases de datos.
Por ello mysql con su sencillez y orientación a la velocidad se posiciona como alternativa interesante, apoyada por sistemas de caché key-value (de nuevo nada relacionales) como Memcached. Incluso existen forks (por importantes divergencias en la forma de llevar el desarrollo) como Drizzle o Maria DB que incluso recortan aún más las funcionalidades para poder avanzar en el camino de la escalabilidad.
Pero MySQL también tiene cosas malas. Como este bug por el que hemos perdido horas y horas de investigación en tuenti. Al cambiar unas tablas de MyISAM a InnoDB empezamos a observar cómo el esclavo migrado se empezaba a retrasar respecto al maestro, totalmente saturado con las consultas y el thread de replicación consumiendo el 100% de uno de los cores.
Un ejemplo lo resume todo:
mysql> SELECT * FROM test WHERE int_field=1 and bigint_field=1; Empty set (0.00 sec)mysql> SELECT * FROM test WHERE (int_field,bigint_field) IN ((1,1)); Empty set (0.00 sec)
mysql> DELETE FROM test WHERE int_field=1 and bingint_field=1; Query OK, 0 rows affected (0.00 sec)
mysql> DELETE FROM test WHERE (int_field,bingint_field) IN ((1,1)); Query OK, 0 rows affected (1.19 sec) ^…………………………….. WTF!!!
El problema de rendimiento ocurre cuando se utiliza la sintaxis IN, sólo si hay dos o más campos en el IN, sólo si al menos uno de los campos es BIGINT. Un conjunto de condiciones cuanto menos curioso. Cambiando la sintaxis de las queries, problema resuelto…
El problema es especialmente notable cuando la tabla es InnoDB. Con MyISAM el cuatro query tarda 0.19sec, mejor, pero un tiempo todavía absurdo comparado con los tiempos de los otros queries. ¡Deberían comportarse todos igual! Definitivamente hay algo roto en los entresijos del servidor para tener un rendimiento tan horrible.
5 Comments »
The URI to TrackBack this entry is: http://blogs.eurielec.etsit.upm.es/freedreams/posts/2009/08/18/mysql-wtf/trackback/
RSS feed for comments on this post.
This design is irdcenible! You most certainly know how to keep a reader entertained. Between your wit and your videos, I was almost moved to start my own blog (well, almost HaHa!) Wonderful job. I really loved what you had to say, and more than that, how you presented it. Too cool!
Comment by Sandra — 27/04/2013 @ 03:58
Getting the right type of pantyhose is often quite irritating especially if you don’t have the greatest style of details about where by to have them from. This type of understanding might be important in producing guaranteed that you simply are aware of the way to possess a memorable getting working experience. Among the ways in which it is possible to detect fake pantyhose is thanks to the fact that it’s going to conveniently run right after you have got only worn it at the time. This can be an incredibly aggravating issue particularly if you had invested a good total of money getting it. Yet another way where it is possible to detect fake pantyhose is that if the casing has spelt the brand name wrongly. A lot of persons planning to get effortless revenue are likely to make use of using this method since it would be the easiest method of conning people today from their funds. The fact that your pantyhose will have dropped its elasticity just after just one or two occasions of the sporting it’s a further way in which you may convey to phony pantyhose.
Comment by auto insurance — 9/05/2013 @ 12:33
I’m assuming that you’re using PHP as the scripting language.It might be that your script needs write permissions in the folder using to create the dbase file. But if error reporting is turned off on your host’s configuration, you won’t really know what the problem is. You should temporarily enable error reporting so you can view the error that occurs when the script is run. You do this by placing the following code at the top of your script (remove when made publicly available):error_reporting(E_ALL);ini_set(“display_errors”, 1); Now, after error reporting is on, if it is not a permissions error, you might see an error like the following:Fatal error: Call to undefined function dbase_create()…If you do, dbase is not configured to work with the installation of PHP on your host’s server. In order for the dbase functions to work, PHP must be compiled with the –enable-dbase option. You will need to check with your host to see if it is enabled.
Comment by car insurance quotes — 11/05/2013 @ 10:39
I’m assuming that you’re using PHP as the scripting language.It might be that your script needs write permissions in the folder using to create the dbase file. But if error reporting is turned off on your host’s configuration, you won’t really know what the problem is. You should temporarily enable error reporting so you can view the error that occurs when the script is run. You do this by placing the following code at the top of your script (remove when made publicly available):error_reporting(E_ALL);ini_set(“display_errors”, 1); Now, after error reporting is on, if it is not a permissions error, you might see an error like the following:Fatal error: Call to undefined function dbase_create()…If you do, dbase is not configured to work with the installation of PHP on your host’s server. In order for the dbase functions to work, PHP must be compiled with the –enable-dbase option. You will need to check with your host to see if it is enabled.
Comment by car insurance in florida — 11/05/2013 @ 10:39
Thanks for your publish. My spouse and i have generally seen that most people are desirous to lose weight because they wish to look slim in addition to looking attractive. However, they do not constantly realize that there are more benefits for you to losing weight as well. Doctors say that over weight people are afflicted with a variety of conditions that can be instantly attributed to their own excess weight. The good news is that people who\’re overweight plus suffering from diverse diseases can reduce the severity of the illnesses by way of losing weight. It is easy to see a continuous but noticeable improvement in health as soon as even a slight amount of losing weight is reached.One thing I\’d like to reply to is that weightloss system fast can be achieved by the appropriate diet and exercise. Ones size not just affects the look, but also the complete quality of life. Self-esteem, despression symptoms, health risks, and also physical abilities are afflicted in an increase in weight. It is possible to do everything right whilst still having a gain. If this happens, a problem may be the offender. While a lot food rather than enough physical exercise are usually guilty, common health conditions and widespread prescriptions may greatly help to increase size. Thanks for your post right here.I realized more new things on this weight-loss issue. 1 issue is a good nutrition is tremendously vital if dieting. A tremendous reduction in fast foods, sugary foodstuff, fried foods, sugary foods, beef, and whitened flour products could possibly be necessary. Retaining wastes parasites, and harmful toxins may prevent targets for losing fat. While specified drugs momentarily solve the problem, the horrible side effects aren\’t worth it, and in addition they never give more than a short lived solution. It is a known undeniable fact that 95% of celebrity diets fail. Thank you for sharing your notions on this blog site.Thanks for the recommendations shared in your blog. Yet another thing I would like to mention is that weight reduction is not all about going on a dietary fad and trying to lose as much weight as possible in a few days. The most effective way to shed weight is by consuming it slowly and gradually and using some basic guidelines which can make it easier to make the most through your attempt to lose fat. You may learn and be following some of these tips, but reinforcing know-how never does any damage.I believe avoiding highly processed foods will be the first step to be able to lose weight. They can taste excellent, but processed foods currently have very little vitamins and minerals, making you take more only to have enough energy to get throughout the day. In case you are constantly consuming these foods, changing to cereals and other complex carbohydrates will let you have more energy while consuming less. Thanks a lot : ) for your blog post.
Comment by auto insurance rates — 13/05/2013 @ 08:39