Acabo de configurar un servidor y luego de muchos días optimice mucho la base de datos. Tanto que ahora el consumo medio es del 27% de la memoria RAM disponible y el máximo es el 37%.
Mi duda es por donde empezar aumentar el consumo de RAM. ¿A que valores le debo dar prioridad?
Mi configuración es:
# MariaDB database server configuration file. # # You can copy this file to one of: # - "/etc/mysql/my.cnf" to set global options, # - "~/.my.cnf" to set user-specific options. # # One can use all long options that the program supports. # Run program with --help to get a list of available options and with # --print-defaults to see which it would actually understand and use. # # For explanations see # http://dev.mysql.com/doc/mysql/en/server-system-variables.html # This will be passed to all mysql clients # It has been reported that passwords should be enclosed with ticks/quotes # escpecially if they contain "#" chars... # Remember to edit /etc/mysql/debian.cnf when changing the socket location. [client] port = 3306 socket = /var/run/mysqld/mysqld.sock # Here is entries for some specific programs # The following values assume you have at least 32M ram # This was formally known as [safe_mysqld]. Both versions are currently parsed. [mysqld_safe] socket = /var/run/mysqld/mysqld.sock nice = 0 [mysqld] # # * Basic Settings # user = mysql pid-file = /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock port = 3306 basedir = /usr datadir = /var/lib/mysql tmpdir = /tmp lc_messages_dir = /usr/share/mysql lc_messages = en_US skip-external-locking # # Instead of skip-networking the default is now to listen only on # localhost which is more compatible and is not less secure. bind-address = 127.0.0.1 # # * Fine Tuning # max_user_connections=45 max_connections=100 max_connect_errors=20 #max_connections = 500 #ESTO LO CAMBIO SEGUN LA MEMORIA DEL SERVIDOR. POR CADA MEGA DE RAM LO MULTIPLICO POR 0,1 Y LO QUE ME DA ES EL VARLOR QUE TENGO QUE PONER. Con 1 GB de RAM que no puede manejar 200 conexiones, que puede bloquear el servidor connect_timeout=2 #connect_timeout = 600 wait_timeout=60 #wait_timeout = 86400 #Agregue esto a su my.cnf para reducir las conexiones inactivas comer hasta la memoria RAM max_allowed_packet=10M #max_allowed_packet = 32M thread_cache_size=128 #thread_cache_size = 50 #POR CADA MEGA DE RAM LO MULTIPLICO POR 0,05 Y LO QUE ME DA ES EL VARLOR QUE TENGO QUE PONER sort_buffer_size=256K #sort_buffer_size = 4M bulk_insert_buffer_size = 16M tmp_table_size=50M #tmp_table_size = 33M #ESTO LO CAMBIO SEGUN LA MEMORIA DEL SERVIDOR. POR CADA MEGA DE RAM LO MULTIPLICO POR 0,5 Y LO QUE ME DA ES EL VARLOR QUE TENGO QUE PONER max_heap_table_size=50M #max_heap_table_size = 33M #ESTO LO CAMBIO SEGUN LA MEMORIA DEL SERVIDOR. POR CADA MEGA DE RAM LO MULTIPLICO POR 0,5 Y LO QUE ME DA ES EL VARLOR QUE TENGO QUE PONER innodb_buffer_pool_instances = 2 join_buffer_size=2M table_cache=1000 # # * MyISAM # # This replaces the startup script and checks MyISAM tables if needed # the first time they are touched. On error, make copy and try a repair. myisam_recover_options = FORCE,BACKUP key_buffer=500M #key_buffer_size = 32M #POR CADA MEGA DE RAM LO MULTIPLICO POR 0,032 Y LO QUE ME DA ES EL VARLOR QUE TENGO QUE PONER open_files_limit = 65535 #open-files-limit = 2000 #POR CADA MEGA DE RAM LO MULTIPLICO POR 65,535 Y LO QUE ME DA ES EL VARLOR QUE TENGO QUE PONER table_definition_cache = 4096 table_open_cache = 4096 #POR CADA MEGA DE RAM LO MULTIPLICO POR 4,096 Y LO QUE ME DA ES EL VARLOR QUE TENGO QUE PONER myisam_sort_buffer_size=64M #myisam_sort_buffer_size = 512M concurrent_insert = 2 read_buffer_size=128K #read_buffer_size = 2M read_rnd_buffer_size=4M #read_rnd_buffer_size = 1M # # * Query Cache Configuration # # Cache only tiny result sets, so we can fit more in the query cache. query_cache_limit=20M #query_cache_limit = 128K #ESTO LO CAMBIO SEGUN LA MEMORIA DEL SERVIDOR. POR CADA MEGA DE RAM LO MULTIPLICO POR 0,002 Y LO QUE ME DA ES EL VARLOR QUE TENGO QUE PONER #query_cache_size = 64M #ESTO LO CAMBIO SEGUN LA MEMORIA DEL SERVIDOR. POR CADA MEGA DE RAM LO MULTIPLICO POR 0,256 Y LO QUE ME DA ES EL VARLOR QUE TENGO QUE PONER query_cache_size=20M #query_cache_size = 8M query_cache_type=1 #query_cache_type = 0 # for more write intensive setups, set to DEMAND or OFF #query_cache_type = DEMAND # # * Logging and Replication # # Both location gets rotated by the cronjob. # Be aware that this log type is a performance killer. # As of 5.1 you can enable the log at runtime! #general_log_file = /var/log/mysql/mysql.log #general_log = 1 # # Error logging goes to syslog due to /etc/mysql/conf.d/mysqld_safe_syslog.cnf. # # we do want to know about network errors and such log_warnings = 2 # # Enable the slow query log to see queries with especially long duration #slow_query_log[={0|1}] slow_query_log_file = /var/log/mysql/mariadb-slow.log long_query_time = 10 #log_slow_rate_limit = 1000 log_slow_verbosity = query_plan log_error = /var/log/mysql/mariadb-error.log log_queries_not_using_indexes = 1 #log-queries-not-using-indexes #log_slow_admin_statements # # The following can be used as easy to replay backup logs or for replication. # note: if you are setting up a replication slave, see README.Debian about # other settings you may need to change. #server-id = 1 #report_host = master1 #auto_increment_increment = 2 #auto_increment_offset = 1 log_bin = /var/log/mysql/mariadb-bin log_bin_index = /var/log/mysql/mariadb-bin.index # not fab for performance, but safer #sync_binlog = 1 expire_logs_days = 10 max_binlog_size = 100M # slaves #relay_log = /var/log/mysql/relay-bin #relay_log_index = /var/log/mysql/relay-bin.index #relay_log_info_file = /var/log/mysql/relay-bin.info #log_slave_updates #read_only # # If applications support it, this stricter sql_mode prevents some # mistakes like inserting invalid dates etc. #sql_mode = NO_ENGINE_SUBSTITUTION,TRADITIONAL # # * InnoDB # # InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/. # Read the manual for more InnoDB related options. There are many! default_storage_engine = InnoDB # you can't just change log file size, requires special procedure #innodb_log_file_size = 50M #POR CADA MEGA DE RAM LO MULTIPLICO POR 0,064 Y LO QUE ME DA ES EL VARLOR QUE TENGO QUE PONER innodb_buffer_pool_size = 100M #innodb_buffer_pool_size = 2G #ESTO INDICA LA CANTIDAD DE MEMORIA QUE USA LA BASE DE DATOS. POR CADA MEGA DE RAM LO MULTIPLICO POR 0,6 Y LO QUE ME DA ES EL VARLOR QUE TENGO QUE PONER innodb_log_buffer_size = 8M innodb_file_per_table = 1 innodb_open_files = 400 innodb_io_capacity = 400 innodb_flush_method = O_DIRECT innodb_log_files_in_group = 2 innodb_log_file_size = 128M # # * Security Features # # Read the manual, too, if you want chroot! # chroot = /var/lib/mysql/ # # For generating SSL certificates I recommend the OpenSSL GUI "tinyca". # # ssl-ca=/etc/mysql/cacert.pem # ssl-cert=/etc/mysql/server-cert.pem # ssl-key=/etc/mysql/server-key.pem # # * Galera-related settings # [galera] # Mandatory settings #wsrep_on=ON #wsrep_provider= #wsrep_cluster_address= #binlog_format=row #default_storage_engine=InnoDB #innodb_autoinc_lock_mode=2 # # Allow server to accept connections on all interfaces. # #bind-address=0.0.0.0 # # Optional setting #wsrep_slave_threads=1 #innodb_flush_log_at_trx_commit=0 innodb_flush_log_at_trx_commit = 1 [mysqldump] quick max_allowed_packet=32M #[mysqldump] #quick #quote-names #max_allowed_packet = 32M ##max_connect_errors = 100000000 #skip_name_resolve #sql_mode = STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ONLY_FULL_GROUP_BY #sysdate_is_now = 1 #innodb = FORCE #innodb_strict_mode = 1 [mysql] #no-auto-rehash # faster start of mysql but no tab completion #[isamchk] #key_buffer=500M #key_buffer = 16M # # * IMPORTANT: Additional settings that can override those from this file! # The files must end with '.cnf', otherwise they'll be ignored. # !includedir /etc/mysql/conf.d/
################################COMO SE COMPORTA ESTA CONFIGURACION EN EL SERVIDOR DE 4GB######################### [OK] Logged in using credentials from debian maintenance account. >> MySQLTuner 1.6.0 - Major Hayden <[email protected]> >> Bug reports, feature requests, and downloads at http://mysqltuner.com/ >> Run with '--help' for additional options and output filtering [--] Skipped version check for MySQLTuner script [OK] Currently running supported MySQL version 10.1.21-MariaDB-1~xenial [OK] Operating on 64-bit architecture -------- Storage Engine Statistics ------------------------------------------- [--] Status: +Aria +CSV +InnoDB +MRG_MyISAM +SEQUENCE [--] Data in InnoDB tables: 9G (Tables: 567) [--] Data in MEMORY tables: 0B (Tables: 1) [!!] Total fragmented tables: 18 -------- Security Recommendations ------------------------------------------- [OK] There is no anonymous account in all database users [OK] All database users have passwords assigned [!!] User 'wpoffline@%' hasn't specific host restriction. [--] There is 605 basic passwords in the list. -------- Performance Metrics ------------------------------------------------- [--] Up for: 22m 20s (32K q [24.193 qps], 2K conn, TX: 3B, RX: 10M) [--] Reads / Writes: 40% / 60% [--] Binary logging is enabled (GTID MODE: OFF) [--] Total buffers: 814.0M global + 6.7M per thread (100 max threads) [OK] Maximum reached memory usage: 860.6M (21.78% of installed RAM) [OK] Maximum possible memory usage: 1.4G (37.45% of installed RAM) [OK] Slow queries: 0% (0/32K) [OK] Highest usage of available connections: 7% (7/100) [OK] Aborted connections: 0.00% (0/2255) [!!] Query cache efficiency: 8.9% (1K cached / 11K selects) [OK] Query cache prunes per day: 0 [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 16 sorts) [!!] Joins performed without indexes: 173 [!!] Temporary tables created on disk: 42% (341 on disk / 811 total) [OK] Thread cache hit rate: 99% (7 created / 2K connections) [OK] Table cache hit rate: 99% (770 open / 776 opened) [OK] Open file limit used: 0% (60/16K) [OK] Table locks acquired immediately: 100% (4M immediate / 4M locks) [OK] Binlog cache memory access: 100.00% ( 17187 Memory / 17187 Total) -------- MyISAM Metrics ----------------------------------------------------- [!!] Key buffer used: 18.3% (95M used / 524M cache) [OK] Key buffer size / total MyISAM indexes: 500.0M/123.0K [!!] Read Key buffer hit rate: 80.0% (10 cached / 2 reads) -------- InnoDB Metrics ----------------------------------------------------- [--] InnoDB is enabled. [!!] InnoDB buffer pool / data size: 100.0M/9.6G [!!] InnoDB buffer pool <= 1G and innodb_buffer_pool_instances(!=1). [OK] InnoDB Used buffer: 84.00% (5375 used/ 6399 total) [OK] InnoDB Read buffer efficiency: 99.65% (23641888 hits/ 23725845 total) [!!] InnoDB Write buffer efficiency: 0.00% (0 hits/ 1 total) [OK] InnoDB log waits: 0.00% (0 waits / 18464 writes) -------- AriaDB Metrics ----------------------------------------------------- [--] AriaDB is disabled. -------- Replication Metrics ------------------------------------------------- [--] No replication slave(s) for this server. [--] This is a standalone server.. -------- Recommendations ----------------------------------------------------- General recommendations: Run OPTIMIZE TABLE to defragment tables for better performance Restrict Host for user@% to user@SpecificDNSorIp MySQL started within last 24 hours - recommendations may be inaccurate Adjust your join queries to always utilize indexes When making adjustments, make tmp_table_size/max_heap_table_size equal Reduce your SELECT DISTINCT queries which have no LIMIT clause Variables to adjust: query_cache_limit (> 20M, or use smaller result sets) join_buffer_size (> 2.0M, or always use indexes with joins) tmp_table_size (> 50M) max_heap_table_size (> 50M) innodb_buffer_pool_size (>= 9G) if possible. innodb_buffer_pool_instances (=1)
Respuestas
No entiendo muy bien la pregunta porque tu mismo parece que te la has respondido. Al ejecutar el script MySQLTuner puedes ver una lista de recomendaciones al final del todo. Además de las general recommendations, justo al final puedes ver la sección Variables to adjust. Esos son los valores que puedes cambiar para exprimir al máximo el rendimiento de tu base de datos.
@javiereguiluz
26 marzo 2017, 10:57