search

seenthis / hebergement

Un repo pour documenter et lister les bugs sur l'hébergement de seenthis
2 stars 3 forks source link

optimiser mysql #7

Closed tech-nova closed 8 years ago

tech-nova commented 8 years ago

Il consomme de swap Voici le résultat de mysqltunner.pl

# ./mysqltuner.pl
 >>  MySQLTuner 1.6.13 - Major Hayden <major@mhtx.net>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Run with '--help' for additional options and output filtering
Please enter your MySQL administrative login: root
Please enter your MySQL administrative password:
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.7.12-5-log
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MEMORY +MRG_MYISAM +MyISAM +PERFORMANCE_SCHEMA
[--] Data in InnoDB tables: 16K (Tables: 1)
[--] Data in MyISAM tables: 12G (Tables: 106)
[OK] Total fragmented tables: 0

-------- Security Recommendations ------------------------------------------------------------------
[OK] There are no anonymous accounts for any database users
[OK] All database users have passwords assigned
[--] There are 605 basic passwords in the list.

-------- CVE Security Recommendations --------------------------------------------------------------
[OK] NO SECURITY CVE FOUND FOR YOUR VERSION

-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 1d 17h 28m 1s (13M q [93.625 qps], 119K conn, TX: 12G, RX: 7G)
[--] Reads / Writes: 99% / 1%
[--] Binary logging is disabled
[--] Physical Memory     : 19.8G
[--] Max MySQL memory    : 1.5G
[--] Other process memory: 1.5G
[--] Total buffers: 1.3G global + 1.4M per thread (151 max threads)
[--] P_S Max memory usage: 72B
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 1.3G (6.53% of installed RAM)
[OK] Maximum possible memory usage: 1.5G (7.51% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (85/13M)
[OK] Highest usage of available connections: 4% (7/151)
[OK] Aborted connections: 0.01%  (6/119153)
[OK] Query cache is disabled by default due to mutex contention on multiprocessor machines.
[OK] Sorts requiring temporary tables: 0% (12K temp sorts / 1M sorts)
[OK] No joins without indexes
[!!] Temporary tables created on disk: 90% (972K on disk / 1M total)
[!!] Table cache hit rate: 13% (496 open / 3K opened)
[OK] Open file limit used: 26% (520/2K)
[OK] Table locks acquired immediately: 99% (15M immediate / 15M locks)

-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is enabled.
[--] Thread Pool Size: 24 thread(s).
[OK] thread_pool_size between 16 and 36 when using InnoDB storage engine.

-------- Performance schema ------------------------------------------------------------------------
[--] Performance schema is enabled.
[--] Memory used by P_S: 72B
[--] Sys schema is installed.

-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 44.3% (475M used / 1B cache)
[OK] Key buffer size / total MyISAM indexes: 1.0G/1.4G
[OK] Read Key buffer hit rate: 100.0% (1B cached / 268K reads)
[!!] Write Key buffer hit rate: 86.6% (3M cached / 474K writes)

-------- AriaDB Metrics ----------------------------------------------------------------------------
[--] AriaDB is disabled.

-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[OK] InnoDB buffer pool / data size: 128.0M/16.0K
[OK] InnoDB buffer pool instances: 1
[OK] InnoDB Used buffer: 87.43% (7161 used/ 8191 total)
[OK] InnoDB Read buffer efficiency: 99.22% (264167239 hits/ 266248267 total)
[!!] InnoDB Write Log efficiency: 0% (2 hits/ 0 total)
[OK] InnoDB log waits: 0.00% (0 waits / 2 writes)

-------- TokuDB Metrics ----------------------------------------------------------------------------
[--] TokuDB is disabled.

-------- Galera Metrics ----------------------------------------------------------------------------
[--] Galera is disabled.

-------- Replication Metrics -----------------------------------------------------------------------
[--] Galera Synchronous replication: NO
[--] No replication slave(s) for this server.
[--] This is a standalone server.

-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries which have no LIMIT clause
    Increase table_open_cache gradually to avoid file descriptor limits
    Read this before increasing table_open_cache over 64: http://bit.ly/1mi7c4C
    Beware that open_files_limit (2000) variable
    should be greater than table_open_cache ( 500)
Variables to adjust:
    tmp_table_size (> 128M)
    max_heap_table_size (> 128M)
    table_open_cache (> 500)
Fil commented 8 years ago

Sur alan : `show variables``

Variable_name   Value
auto_increment_increment        1
auto_increment_offset   1
autocommit      ON
automatic_sp_privileges ON
back_log        50
basedir /usr
big_tables      OFF
binlog_cache_size       32768
binlog_direct_non_transactional_updates OFF
binlog_format   STATEMENT
binlog_stmt_cache_size  32768
bulk_insert_buffer_size 8388608
character_set_client    utf8mb4
character_set_connection        utf8mb4
character_set_database  latin1
character_set_filesystem        binary
character_set_results   utf8mb4
character_set_server    latin1
character_set_system    utf8
character_sets_dir      /usr/share/mysql/charsets/
collation_connection    utf8mb4_general_ci
collation_database      latin1_swedish_ci
collation_server        latin1_swedish_ci
completion_type NO_CHAIN
concurrent_insert       AUTO
connect_timeout 10
datadir /var/lib/mysql/
date_format     %Y-%m-%d
datetime_format %Y-%m-%d %H:%i:%s
default_storage_engine  InnoDB
default_week_format     0
delay_key_write ON
delayed_insert_limit    100
delayed_insert_timeout  300
delayed_queue_size      1000
div_precision_increment 4
engine_condition_pushdown       ON
error_count     0
event_scheduler OFF
expire_logs_days        0
external_user
flush   OFF
flush_time      0
foreign_key_checks      ON
ft_boolean_syntax       + -><()~*:""&|
ft_max_word_len 84
ft_min_word_len 3
ft_query_expansion_limit        20
ft_stopword_file        (built-in)
general_log     OFF
general_log_file        /var/lib/mysql/alan.log
group_concat_max_len    1024
have_compress   YES
have_crypt      YES
have_csv        YES
have_dynamic_loading    YES
have_geometry   YES
have_innodb     YES
have_ndbcluster NO
have_openssl    DISABLED
have_partitioning       YES
have_profiling  YES
have_query_cache        YES
have_rtree_keys YES
have_ssl        DISABLED
have_symlink    YES
hostname        alan
identity        0
ignore_builtin_innodb   OFF
init_connect
init_file
init_slave
innodb_adaptive_flushing        ON
innodb_adaptive_hash_index      ON
innodb_additional_mem_pool_size 62914560
innodb_autoextend_increment     8
innodb_autoinc_lock_mode        1
innodb_buffer_pool_instances    1
innodb_buffer_pool_size 104857600
innodb_change_buffering all
innodb_checksums        ON
innodb_commit_concurrency       0
innodb_concurrency_tickets      500
innodb_data_file_path   ibdata1:10M:autoextend
innodb_data_home_dir
innodb_doublewrite      ON
innodb_fast_shutdown    1
innodb_file_format      Antelope
innodb_file_format_check        ON
innodb_file_format_max  Antelope
innodb_file_per_table   OFF
innodb_flush_log_at_trx_commit  0
innodb_flush_method
innodb_force_load_corrupted     OFF
innodb_force_recovery   0
innodb_io_capacity      200
innodb_large_prefix     OFF
innodb_lock_wait_timeout        30
innodb_locks_unsafe_for_binlog  OFF
innodb_log_buffer_size  8388608
innodb_log_file_size    104857600
innodb_log_files_in_group       2
innodb_log_group_home_dir       ./
innodb_max_dirty_pages_pct      75
innodb_max_purge_lag    0
innodb_mirrored_log_groups      1
innodb_old_blocks_pct   37
innodb_old_blocks_time  0
innodb_open_files       300
innodb_print_all_deadlocks      OFF
innodb_purge_batch_size 20
innodb_purge_threads    0
innodb_random_read_ahead        OFF
innodb_read_ahead_threshold     56
innodb_read_io_threads  4
innodb_replication_delay        0
innodb_rollback_on_timeout      OFF
innodb_rollback_segments        128
innodb_spin_wait_delay  6
innodb_stats_method     nulls_equal
innodb_stats_on_metadata        ON
innodb_stats_sample_pages       8
innodb_strict_mode      OFF
innodb_support_xa       ON
innodb_sync_spin_loops  30
innodb_table_locks      ON
innodb_thread_concurrency       0
innodb_thread_sleep_delay       10000
innodb_use_native_aio   ON
innodb_use_sys_malloc   ON
innodb_version  5.5.49
innodb_write_io_threads 4
insert_id       0
interactive_timeout     28800
join_buffer_size        8388608
keep_files_on_create    OFF
key_buffer_size 2147483648
key_cache_age_threshold 300
key_cache_block_size    1024
key_cache_division_limit        100
large_files_support     ON
large_page_size 0
large_pages     OFF
last_insert_id  0
lc_messages     en_US
lc_messages_dir /usr/share/mysql/english/
lc_time_names   en_US
license GPL
local_infile    ON
lock_wait_timeout       31536000
locked_in_memory        OFF
log     OFF
log_bin OFF
log_bin_trust_function_creators OFF
log_error       /var/lib/mysql/alan.err
log_output      FILE
log_queries_not_using_indexes   OFF
log_slave_updates       OFF
log_slow_queries        ON
log_warnings    1
long_query_time 2.000000
low_priority_updates    OFF
lower_case_file_system  OFF
lower_case_table_names  0
max_allowed_packet      5242880
max_binlog_cache_size   18446744073709547520
max_binlog_size 1073741824
max_binlog_stmt_cache_size      18446744073709547520
max_connect_errors      10
max_connections 300
max_delayed_threads     20
max_error_count 64
max_heap_table_size     16777216
max_insert_delayed_threads      20
max_join_size   18446744073709551615
max_length_for_sort_data        1024
max_long_data_size      5242880
max_prepared_stmt_count 16382
max_relay_log_size      0
max_seeks_for_key       4294967295
max_sort_length 1024
max_sp_recursion_depth  0
max_tmp_tables  32
max_user_connections    0
max_write_lock_count    4294967295
metadata_locks_cache_size       1024
min_examined_row_limit  0
multi_range_count       256
myisam_data_pointer_size        6
myisam_max_sort_file_size       2146435072
myisam_mmap_size        4294967295
myisam_recover_options  OFF
myisam_repair_threads   1
myisam_sort_buffer_size 8388608
myisam_stats_method     nulls_unequal
myisam_use_mmap OFF
net_buffer_length       16384
net_read_timeout        30
net_retry_count 10
net_write_timeout       60
new     OFF
old     OFF
old_alter_table OFF
old_passwords   OFF
open_files_limit        12000
optimizer_prune_level   1
optimizer_search_depth  62
optimizer_switch        index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on
performance_schema      OFF
performance_schema_events_waits_history_long_size       10000
performance_schema_events_waits_history_size    10
performance_schema_max_cond_classes     80
performance_schema_max_cond_instances   1000
performance_schema_max_file_classes     50
performance_schema_max_file_handles     32768
performance_schema_max_file_instances   10000
performance_schema_max_mutex_classes    200
performance_schema_max_mutex_instances  1000000
performance_schema_max_rwlock_classes   30
performance_schema_max_rwlock_instances 1000000
performance_schema_max_table_handles    100000
performance_schema_max_table_instances  50000
performance_schema_max_thread_classes   50
performance_schema_max_thread_instances 1000
pid_file        /var/run/mysqld/mysqld.pid
plugin_dir      /usr/lib/mysql/plugin/
port    3306
preload_buffer_size     32768
profiling       OFF
profiling_history_size  15
protocol_version        10
proxy_user
pseudo_slave_mode       OFF
pseudo_thread_id        23159076
query_alloc_block_size  8192
query_cache_limit       1048576
query_cache_min_res_unit        4096
query_cache_size        67108864
query_cache_type        ON
query_cache_wlock_invalidate    OFF
query_prealloc_size     8192
rand_seed1      0
rand_seed2      0
range_alloc_block_size  4096
read_buffer_size        131072
read_only       OFF
read_rnd_buffer_size    262144
relay_log
relay_log_index
relay_log_info_file     relay-log.info
relay_log_purge ON
relay_log_recovery      OFF
relay_log_space_limit   0
report_host
report_password
report_port     3306
report_user
rpl_recovery_rank       0
secure_auth     OFF
secure_file_priv
server_id       0
skip_external_locking   ON
skip_name_resolve       OFF
skip_networking OFF
skip_show_database      OFF
slave_compressed_protocol       OFF
slave_exec_mode STRICT
slave_load_tmpdir       /var/tmp
slave_max_allowed_packet        1073741824
slave_net_timeout       3600
slave_skip_errors       OFF
slave_transaction_retries       10
slave_type_conversions
slow_launch_time        2
slow_query_log  ON
slow_query_log_file     /var/log/mysql/mysql-slow.log
socket  /var/run/mysqld/mysqld.sock
sort_buffer_size        16777216
sql_auto_is_null        OFF
sql_big_selects ON
sql_big_tables  OFF
sql_buffer_result       OFF
sql_log_bin     ON
sql_log_off     OFF
sql_low_priority_updates        OFF
sql_max_join_size       18446744073709551615
sql_mode
sql_notes       ON
sql_quote_show_create   ON
sql_safe_updates        OFF
sql_select_limit        18446744073709551615
sql_slave_skip_counter  0
sql_warnings    OFF
ssl_ca
ssl_capath
ssl_cert
ssl_cipher
ssl_key
storage_engine  InnoDB
stored_program_cache    256
sync_binlog     0
sync_frm        ON
sync_master_info        0
sync_relay_log  0
sync_relay_log_info     0
system_time_zone        CEST
table_definition_cache  400
table_open_cache        4600
thread_cache_size       0
thread_concurrency      10
thread_handling one-thread-per-connection
thread_stack    262144
time_format     %H:%i:%s
time_zone       SYSTEM
timed_mutexes   OFF
timestamp       1467469305
tmp_table_size  16777216
tmpdir  /var/tmp
transaction_alloc_block_size    8192
transaction_prealloc_size       4096
tx_isolation    REPEATABLE-READ
unique_checks   ON
updatable_views_with_limit      YES
version 5.5.49-0+deb7u1-log
version_comment (Debian)
version_compile_machine i686
version_compile_os      debian-linux-gnu
wait_timeout    28800
warning_count   0
brunob commented 8 years ago

Un lien utile https://bugs.mysql.com/bug.php?id=69391

Les variables qui diffèrent entre les deux machines et qui semblent être une bonne piste : table_open_cache, et performance_schema qu'il faudrait peut-être passer à off.

brunob commented 8 years ago

In read-only case, Performance Schema with all consumers gives about 25% overhead, with “global instrumentation” only –10%, and with all disabled consumers – about 8%. For read-write case, Performance Schema with all consumers gives about 19% overhead, with “global instrumentation” only –11%, and it is about the same with all disabled consumers.

https://www.percona.com/blog/2011/04/25/performance-schema-overhead/

Je pense qu'on peut se passer du bouzin...

tech-nova commented 8 years ago

Je remets ce que j'avais remarqué sur les différences :

VARIABLE AVANT MAINTENANT
key_buffer_size 2147483648 1073741824
max_connect_errors 10 100
max_connections 300 151
max_heap_table_size 16777216 134217728
max_seeks_for_key 4294967295 18446744073709551615
max_write_lock_count 4294967295 18446744073709551615
open_files_limit 12000 2000
performance_schema OFF ON
query_cache_size 67108864 16777216
query_cache_type ON OFF
read_rnd_buffer_size 262144 393216
secure_auth OFF ON
skip_name_resolve OFF ON
sort_buffer_size 16777216 393216
table_definition_cache 400 650
table_open_cache 4600 500
thread_cache_size 0 9

Cela n'est pas exhaustif, mais peut constituer une bonne base de travail. Il faut vérifier les impacts en consommation mémoire puis se rapprocher de celle-ci, qu'en dites-vous ?

Fil commented 8 years ago

Là ça a l'air de vraiment bien tourner côté MySQL. Si on regarde les slow-logs il n'y a que le backup vers alan qui prend plus de 4s. (Il y avait aussi 1 requete sur les tags dans ?page=recherche, mais comme cette page est obsolète [nous on marche sur sphinx] c'est vraiment pas un souci).

brunob commented 8 years ago

Si tout baigne, on ferme le ticket ? Ou alors on tente de désactiver performance_schema et de jouer sur la valeur de table_open_cache ?

Fil commented 8 years ago

yep, à rouvrir si besoin