Pt53-Optimització

Guillem Solà i Boeck (host A10BPC22)

Fent servir scripts de monitorització per optimitzar el rendiment de l'SGBD

• Indicar quins scripts i eines de monitoratge hi ha de BBDD
• Identificar diferents tipus d'optimització: emmagatzematge, espai de disc i procediments de transferències i comunicacions. Indica els resultats obtinguts.
• Identifica tècniques d'optimització de consultes en un SGBD. Indica els resultats obtinguts.

Fer les modificacions al fitxer de configuració que es necessitin

Indicar quins scripts i eines de monitoratge hi ha de BBDD

• Un script escrit en Perl és molt conegut en el món MariaDB/MySQL. Es tracta de mysqltuner.
• Un altre script conegut com a mysql tuning primer. Aquest està escrit en bash i és molt semblant a l'anterior a nivell de funcionalitat.

Com a conclusió, crec que aquests 2 scripts hem de veure'ls com un conjunt ja que és difícil dir si un és millor que l'altre. Com a administradors de sistemes hem de ser capaços de poder treure bones conclusions a partir dels resultats de cadascun.

Identificar diferents tipus d'optimització

• Emmagatzematge

A nivell d'emmagatzematge es pot destacar el tipus de suport on tindrem instal·lat el sistema operatiu+SGBD i les dades de la Base de Dades en general. Com a norma general, descartarem discos durs mecànics perquè tenen temps d'accés molt lents en comparació als discos durs sòlids (SSD) basats en memòria flash. Encara que el SGBD carregarà les dades més accedides en RAM per a evitar lectures a disc (infinitament més lentes encara que es llegeixi d'un SSD), sempre serà millor un disc ràpid per si s'ha de fer una consulta sobre unes dades que no estiguin cachejades en memòria.

• Espai de disc

Generalment, com més espai disponible en disc, millor en termes d'escalabilitat del sistema que suporta el SGBD; de manera que no fem curt al cap d'un temps quan la BBDD creixi i haguem de migrar a discos durs més grans o, fins i tot, haver de canviar el servidor sencer. El que és interessant és trobar un balanç entre rendiment de velocitat i espai disponible; ja que com més gran sigui el disc, més car serà. Això potser no és problema per una empresa més o menys gran, però normalment, a les empreses, els sol costar bastant buidar la butxaca per a equipament informàtic i aquest tema serà més delicat. Per tant, tampoc podem donar una conclusió genèrica sinó que haurem d'estudiar cada cas en concret i prendre decisions basant-nos en aquella situació.

• Procediments de transferències i comunicacions

Per suposat que l'estàndard de comunicacions serà a velocitat de gigabit per qüestions òbvies. Llavors el servidor hauria de tenir, com a mínim, 2 targetes de xarxa en sistema failover per a evitar downtimes en cas que una interfície falli o tingui problemes.

Exemple de funcionament

MySQL Tuning Primer

Per a utilitzar aquest script necessitem tenir instal·lat el programari bc. L'instal·larem amb

yum install bc -y

Una vegada instal·lat el podem fer executable amb

chmod +x tuning-primer.sh

i executar-lo pròpiament amb

./tuning-primer.sh

El meu resultat d'execusió és el següent:

[root@pt5x ~]# ./tuning-primer.sh 

Using login values from ~/.my.cnf
- INITIAL LOGIN ATTEMPT FAILED -
Testing for stored webmin passwords:
 None Found
Could not auto detect login info!
Found potential sockets: /var/lib/mysql/mysql.sock
Using: /var/lib/mysql/mysql.sock
Would you like to provide a different socket?: [y/N] n
Do you have your login handy ? [y/N] : y
User: root
Password: asix
 
Would you like me to create a ~/.my.cnf file for you? [y/N] : y
 
        -- MYSQL PERFORMANCE TUNING PRIMER --
             - By: Matthew Montgomery -

MySQL Version 5.5.56-MariaDB x86_64

Uptime = 0 days 1 hrs 10 min 48 sec
Avg. qps = 4
Total Questions = 19165
Threads Connected = 1

Warning: Server has not been running for at least 48hrs.
It may not be safe to use these recommendations

To find out more information on how each of these
runtime variables effects performance visit:                                                                                                                            
http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html
Visit http://www.mysql.com/products/enterprise/advisors.html
for info about MySQL's Enterprise Monitoring and Advisory Service

SLOW QUERIES
The slow query log is NOT enabled.
Current long_query_time = 10.000000 sec.
You have 0 out of 19186 that take longer than 10.000000 sec. to complete
Your long_query_time seems to be fine

BINARY UPDATE LOG
The binary update log is NOT enabled.
You will not be able to do point in time recovery
See http://dev.mysql.com/doc/refman/5.5/en/point-in-time-recovery.html

WORKER THREADS
Current thread_cache_size = 0
Current threads_cached = 0
Current threads_per_sec = 1
Historic threads_per_sec = 0
Your thread_cache_size is fine

MAX CONNECTIONS
Current max_connections = 151
Current threads_connected = 1
Historic max_used_connections = 8
The number of used connections is 5% of the configured maximum.
You are using less than 10% of your configured max_connections.
Lowering max_connections could help to avoid an over-allocation of memory                                                                                               
See "MEMORY USAGE" section to make sure you are not over-allocating                                                                                                     

INNODB STATUS
Current InnoDB index space = 6 M
Current InnoDB data space = 17 M
Current InnoDB buffer pool free = 88 %
Current innodb_buffer_pool_size = 128 M
Depending on how much space your innodb indexes take up it may be safe
to increase this value to up to 2 / 3 of total system memory

MEMORY USAGE
Max Memory Ever Allocated : 294 M
Configured Max Per-thread Buffers : 419 M
Configured Max Global Buffers : 272 M
Configured Max Memory Limit : 691 M
Physical Memory : 487 M

Max memory limit exceeds 90% of physical memory

KEY BUFFER
Current MyISAM index space = 462 K
Current key_buffer_size = 128 M
Key cache miss rate is 1 : 81
Key buffer free ratio = 81 %
Your key_buffer_size seems to be fine

QUERY CACHE
Query cache is supported but not enabled
Perhaps you should set the query_cache_size                                                                                                                             

SORT OPERATIONS
Current sort_buffer_size = 2 M
Current read_rnd_buffer_size = 256 K
Sort buffer seems to be fine

JOINS
./tuning-primer.sh: line 402: export: `2097152': not a valid identifier
Current join_buffer_size = 132.00 K
You have had 0 queries where a join could not use an index properly
Your joins seem to be using indexes properly

OPEN FILES LIMIT
Current open_files_limit = 1024 files
The open_files_limit should typically be set to at least 2x-3x
that of table_cache if you have heavy MyISAM usage.                                                                                                                     
Your open_files_limit value seems to be fine

TABLE CACHE
Current table_open_cache = 400 tables
Current table_definition_cache = 400 tables
You have a total of 89 tables
You have 93 open tables.
The table_cache value seems to be fine

TEMP TABLES
Current max_heap_table_size = 16 M
Current tmp_table_size = 16 M
Of 559 temp tables, 6% were created on disk
Created disk tmp tables ratio seems fine

TABLE SCANS
Current read_buffer_size = 128 K
Current table scan ratio = 0 : 1
read_buffer_size seems to be fine

TABLE LOCKING
Current Lock Wait ratio = 0 : 19444
Your table locking seems to be fine

MySQL Tuner

Per al MySQL Tuner necessitem una llibreria de Perl que haurem d'instal·lar, sinó l'script no podrà córrer.

yum install -y perl-Switch

Una vegada instal·lat, executarem l'script especificant que volem veure recomanacions, ja que per defecte només se'ns llistaran els paràmetres i els seus valors actuals, només. També haurem d'especificar un arxiu de configuració d'execució; jo he triat un default que ja ve:

perl mysqltuner.pl --recommend --config tuner-default.cnf

Please enter your MySQL administrative login: root
Please enter your MySQL administrative password: 
Uptime in seconds: 1595.00
Uptime: 26m 35s
Questions: 722.00
% slow queries: 0.00
slow query rate: 0.00 per day
Long query time: 10.00
Slow query logging: OFF
% reads: 100.00
% writes: 0.00
qps: 0.45
reads per sec: 2.26 per hour
writes per sec: 0.00 per day
Queries: 27.16 per minute
Connections: 248.00
Bytes sent: 105 Thousand
Bytes received: 45 Thousand
Supported Version: 5
Release Series: 5.5
Minor Version: 56
Distribution: MariaDB Server
Distribution: MariaDB Server
MySQL Architecture: x86_64
Query cache efficiency (%): 0.00
Use of uninitialized value $num in pattern match (m//) at mysqltuner.pl line
        223 (#1)
    (W uninitialized) An undefined value was used as if it were already
    defined.  It was interpreted as a "" or a 0, but maybe it was a mistake.
    To suppress this warning assign a defined value to your variables.
    
    To help you figure out what was undefined, perl will try to tell you
    the name of the variable (if any) that was undefined.  In some cases
    it cannot do this, so it also tells you what operation you used the
    undefined value in.  Note, however, that perl optimizes your program
    anid the operation displayed in the warning may not necessarily appear
    literally in your program.  For example, "that $foo" is usually
    optimized into "that " . $foo, and the warning will refer to the
    concatenation (.) operator, even though there is no . in
    your program.
    
Use of uninitialized value $compval in concatenation (.) or string at
        mysqltuner.pl line 230 (#1)
Use of uninitialized value in string eq at mysqltuner.pl line 231 (#1)
% query cache used: 
Query cache low memory prunes: 0.00 per day
Query cache size: 0 bytes
Query cache min result size: 1.0 Mb
Total sorts: 0.00
% sorts that cause temporary tables: 
rate of sorts that cause temporary tables: 0.00 per day
sort_buffer_size: 2.0 Mb
read_rnd_buffer_size: 256.0 Kb
Sort rows: 0.00 per day
rate of joins without indexes: 8.28 per minute
rate of reading first index entry: 0.00 per day
rate of reading fixed position: 0.00 per day
rate of reading next table row: 2.85 per second
tmp_table_size-max_heap_table_size: 0.00
tmp_table_size: 16.0 Mb
max_heap_table_size: 16.0 Mb
% temp disk tables: 9.14
temp disk rate: 2.71 per minute
temp table rate: 26.93 per minute
MyISAM key buffer size: 128.0 Mb
max % MyISAM key buffer ever used: 0.00
% MyISAM key buffer used: 18.24
% index reads from memory: 
table open cache size (5.1+): 400.00
rate of table open: 14.07 per minute
% open files: 4.98
rate of open files: 1.92 per minute
Immediate table locks %: 100.00
Table lock wait rate: 0.00 per day
thread cache: 0.00
Total threads created: 247.00
thread cache hit rate %: 1.00
Threads that are slow to launch: 0.00
Slow launch time: 2.00
% connections used: 0.66
Max connections used: 1.00
Max connections limit: 151.00
% aborted connections: 1.61
rate of aborted connections: 9.03 per hour
% aborted clients: 0.00
rate of aborted clients: 0.00 per day
Is InnoDB enabled?: YES
% innoDB log size: 3.91

Number found where operator expected at (eval 140) line 1, near "AUTOeq 0" (#2)
    (S syntax) The Perl lexer knows whether to expect a term or an operator.
    If it sees what it knows to be a term when it was expecting to see an
    operator, it gives you this warning.  Usually it indicates that an
    operator or delimiter was omitted, such as a semicolon.
    
        (Do you need to predeclare AUTOeq?)
MyISAM concurrent inserts: 


RECOMMENDATIONS:
Uptime is less than 1 day, performance tuning may not be accurate
Run for a longer amount of time, fewer than 1,000 questions have been run against this server.  Variables may not be accurate.
long_query_time is set to 10 seconds or more.  This may not be appropriate for your environment.
Enable the slow query log to troubleshoot bad queries.
version less then 5.1, upgrade!
query cache not efficient, consider increasing query_cache_limit.
The query_cache is not turned on.  It may be useful to turn it on.
The max size of the result set in the query cache is the default of 1 Mb.  Changing this (usually by increasing) may increase efficiency.
There are too many joins without indexes -- this means that joins are doing full table scans.
The rate of reading the next table row is high; this indicates many queries are doing full table scans.
Too many temporary tables are being written to disk.  Increase max_heap_table_size and tmp_table_size.
Too many intermediate temporary tables are being created; consider increasing sort_buffer_size (sorting), read_rnd_buffer_size (random read buffer, ie, post-sort), read_buffer_size (sequential scan).
MyISAM key buffer (index cache) % used is low.  You may need to decrease the size of key_buffer_size, re-examine your tables to see if indexes have been removed, or examine queries and expectations about what indexes are being used.
MyISAM key buffer (index cache) % used is low.  You may need to decrease the size of key_buffer_size, re-examine your tables to see if indexes have been removed, or examine queries and expectations about what indexes are being used.
Size of the table cache
The rate of opening tables is high, increase table_open_cache to avoid this.
The rate of opening files is high.\nConsider increasing open_files_limit, and check the error log when restarting after changing open_files_limit.
Enable the thread cache by setting thread_cache_size>0 for less overhead from new connections to MySQL.
InnoDB log file size is not an appropriate size, in relation to the InnoDB buffer pool.  Consider changing either\ninnodb_log_file_size or innodb_buffer_pool_size