Como Aumentar el consumo de RAM de una base de datos?

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 [email protected] 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)