Optimiza las Consultas de MySQL en Ingles

Imagen de WarezFox

Tema: 

High loaded website can get slow to respond when a lot of different visitors visit sites querying the same mysql database server, making it slow to respond.

There is many ways you can improve mysql server response time: by modifying the cache size, stopping dns resolution ....

Let's see how to do that.

I remember, once at work, we were having troubles with our databases system. The mysql servers were slow to respond, but when we were logging into those machines, the load was fine, there were quite a few queries going on, but mysql didn't report it was overwhelmed.

1. Disable DNS Hostname Lookup
After seeking out the reason why the traffic wasn't going flawlessly, we determine that the mysql server was doing loads of name resolution queries!!!! What for? Why would that machine to a hostname resolution when only local network machines connect to it.

Seeking out in mysqld manual page, we found that this could be disabled by adding the --skip-name-resolve switch.

Under debian based system, such as ubuntu, knoppix ... and on most linux distribution, mysql configuration files are located in /etc/mysql/my.cnf.

In order to apply the --skip-name-resolve switch when you start mysqld, simply add:

[mysqld]
.....
......
skip-name-resolve

NOTA: When this option is activated, you can only use IP numbers in the MySQL Grant table.

Here is a tiny benchmark:

With DNS hostname resolution:

$ date; mysql -u root -h 192.168.1.4 ; date
Fri Jul 21 23:56:58 CEST 2006
ERROR 1130 (00000): Host '192.168.1.3' is not allowed to connect to this MySQL server
Fri Jul 21 23:57:00 CEST 2006

it take 2-3 seconds before the server reply that the client IP is not allowed to connect.

Once DNS hostname lookup is disabled:

$ date; mysql -u root -h 192.168.1.4 ; date
Fri Jul 21 23:56:37 CEST 2006
ERROR 1130 (00000): Host '192.168.1.3' is not allowed to connect to this MySQL server
Fri Jul 21 23:56:37 CEST 2006

The server is replying instantly.

2. Activate Query Cache

After we resolved that issue, we started seeing the database server load increasing, the response time was good after the previous change, but now, we had to lighten a bit the mysql database server's load.

By checking the Query cache memory:

mysql> SHOW STATUS LIKE 'Qcache%';

we could see that no query cache memory was left. It was neccessary to increase the query cache size.

To get an overview of your query_cache variables state, use the following syntax:

mysql> SHOW VARIABLES LIKE '%query_cache%';

You need to have the query cache enabled in the first place (have_query_cache | YES) and make sure that query_cache_type is set to ON. This is usually activated by default on most linux distribution.
Bueno esto esta en ingles pero es muy bueno para acelerar conexiones y querys de mysql

Now, you can increase the query cache size (let say you want 50M) using:

mysql> SET GLOBAL query_cache_size = 52428800;

If you want this setting to be kept when restarting mysql, add:

[mysqld]

...

...

query_cache_size = 52428800;

query_cache_type = 1

3. Conclusion:

After doing those changes, there were much more queries resolved from the cache, the effect was that the server was responding quickly without calculating too much has most of the queries where cached.

Comentarios

Pense que se trataba de spam,

Imagen de iknaxio

Pense que se trataba de spam, al estar el texto en idioma anglosajón!

Pense que ma había cambiado de sitio sin darme cuenta y estaba en pastebin.com, porque lo posteado es un CTRL+C y CTRL+V de:

No sé que pasa últimamente pero la gente como que no lee la leyenda que aparece al momento que se va a postear algo en la sección de blogs:

[quote]Por favor usa esta sección para contar alguna historia, o alguna solución de la instalación, configuración de alguna distribución o de alguna aplicación especifica relacionada a GNU/Linux o el software libre poniendo tu toque personal...
No uses esta sección para realizar una pregunta, para eso usa la sección de Foros....[/quote]

"Transporta un puñado de tierra todos los días y construirás una montaña" - Confucio
floss.iknaxio.net

Se agradecería al menos la

Imagen de deathUser

Se agradecería al menos la traducción, con las correspondientes notas del traductor y la referencia al artículo original :), pero bue...

Lástima que no pueda borrar POSTS enteros sino solo respuestas :'( ...

bye
;)