Configuración óptima de un Mysql Replication Slave con 10GB de Ram, 8 cores y 25GB de SSD
- abril 12, 2018
- Categoría: clúster linux mysql virtualización
Importante: Esta información es para uso lúdico personal. En ningún caso se pretende dar conocimientos, consejos, formaciones, herramientas, ni soluciones técnicas de ámbito comercial ni profesional. Simplemente es información que se ha redactado en finalidad de recordatorio de ciertos proyectos realizados. Si usted no está seguro de la fiabilidad de la información, no cumple con sus expectativas o no es de su agrado, le ruego que abandone este sitio web. Para ver todas las exclusiones garantía y de responsabilidad acceda a la sección Aviso Legal.
Archivo de configuración para un Mysql 5.6 configurado cómo Slave de Mysql Replication. El servidor tiene 10 GB de RAM, usa 8 Cores Intel y tiene una capacidad de disco SSD de 25GB.
La base de datos contiene tablas en formato MyIsam y InnoDB, con que se han ajustado las dos cache’s apropiadamente.
vim /etc/mysql/mysql.conf.d/mysqld.cnf
Configuración
# Copyright (c) 2014, 2016, Oracle and/or its affiliates. All rights reserved. # # This program is free software; you can redistribute it and/or modify # it under the terms of the GNU General Public License as published by # the Free Software Foundation; version 2 of the License. # # This program is distributed in the hope that it will be useful, # but WITHOUT ANY WARRANTY; without even the implied warranty of # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the # GNU General Public License for more details. # # You should have received a copy of the GNU General Public License # along with this program; if not, write to the Free Software # Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA # # The MySQL Server configuration file. # # For explanations see # http://dev.mysql.com/doc/mysql/en/server-system-variables.html [mysqld] pid-file = /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock datadir = /var/lib/mysql log-error = /var/log/mysql/error.log # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 performance_schema = OFF skip-name-resolve innodb_buffer_pool_dump_at_shutdown = ON innodb_buffer_pool_load_at_startup = ON # INNODB # innodb-flush-method = O_DIRECT innodb-log-files-in-group = 2 innodb-log-file-size = 256M innodb-flush-log-at-trx-commit = 1 innodb-file-per-table = 1 innodb-buffer-pool-size = 6G # CACHES AND LIMITS # tmp-table-size = 32M max-heap-table-size = 32M max-connections = 500 thread-cache-size = 50 open-files-limit = 65535 table-definition-cache = 4096 table-open-cache = 1G query_cache_type = 1 query_cache_limit = 3M query_cache_size = 256M # LOGGING # log-error = /var/lib/mysql/mysql-error.log log-queries-not-using-indexes = 1 #long_query_time = 0.5 slow-query-log = 1 slow-query-log-file = /var/lib/mysql/mysql-slow.log # MyISAM # key-buffer-size = 32M myisam-recover-options = FORCE,BACKUP # * Replication server-id = 2 replicate-do-db = dbeninsoft replicate-ignore-db = test relay-log = /var/lib/mysql/mysql-relay-bin relay-log-index = /var/lib/mysql/mysql-relay-bin.index log-error = /var/lib/mysql/mysql.err master-info-file = /var/lib/mysql/mysql-master.info relay-log-info-file = /var/lib/mysql/mysql-relay-log.info log-bin = /var/lib/mysql/mysql-bin expire-logs-days = 3 max-binlog-size = 500M slave-skip-errors = all optimizer_search_depth = 1