How did you minimize your mysql server memory consumption?

rdn

Well-known member
My MariaDB server consumes 40-50% of my total memory.
How can I minimize this without sacrificing performance?
I also prefer faster forum than low memory consumption but slower forum.

But then, I tried to ask here, maybe someone can advice?
I'm already using APC and Memcached.

Thanks !
 
Have you tried Percona instead of MariaDB?
Not sure why are you using Memcached, it's pretty much pointless for single server environment. APC sucks since it is in beta for decades and breaks very often. Try xCache.

Anyway, maybe you need more memory. How much RAM do you have?
How is your SQL config file looks like?
Try disabling all add-ons and see what happens to your RAM consumption. Then enable them one by one and see what triggers it (if any).
 
  • Like
Reactions: rdn
Generally, more memory optimally allocated to MySQL/MariaDB/Percona = better performance. Less memory = less performance.

Make sure you are properly reading memory usage for Linux first http://www.linuxatemyram.com

Never had any problems with APC Cache been mainly using APC 3.1.13 or 3.1.5-dev builds. Unfortunately, alot of distro's repositories still only offer APC 3.1.9 which is ancient.
 
  • Like
Reactions: rdn
Server Specs:
CPU: Intel i5 (4 cores / 4 Threads)
Frequency: 3.1GHz (3.8GHz Turbo Boost)
RAM: 8 GB DDR3
CentOS 6.5 Server
Nginx 1.5.7
Php 5.5.7
Using APC
MariaDb latest version

my.conf
Code:
[mysqld]
character-set-server=utf8
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

#bind-address=127.0.0.1

#tmpdir=/home/mysqltmp

skip-federated
#skip-pbxt
#skip-pbxt_statistics
skip-archive
#skip-name-resolve
#old_passwords
back_log = 75
max_connections = 300
key_buffer_size = 1024M
myisam_sort_buffer_size = 32M
myisam_max_sort_file_size = 2048M
join_buffer_size = 256K
read_buffer_size = 256K
sort_buffer_size = 512K
table_definition_cache = 8192
table_open_cache = 8192
thread_cache_size = 64
wait_timeout = 120
connect_timeout = 10
tmp_table_size = 512M
max_heap_table_size = 512M
max_allowed_packet = 32M
max_seeks_for_key = 1000
group_concat_max_len = 1024
max_length_for_sort_data = 1024
net_buffer_length = 16384
max_connect_errors = 100000
concurrent_insert = 2
read_rnd_buffer_size = 256K
bulk_insert_buffer_size = 8M
query_cache_limit = 512K
query_cache_size = 96M
query_cache_type = 1
query_cache_min_res_unit = 2K
query_prealloc_size = 262144
query_alloc_block_size = 65536
transaction_alloc_block_size = 8192
transaction_prealloc_size = 4096
default-storage-engine = InnoDB

log_warnings=1
slow_query_log=0
long_query_time=1
slow_query_log_file=/var/lib/mysql/slowq.log
log-error=/var/log/mysqld.log

# innodb settings
#innodb_extra_rsegments = 4
innodb_purge_threads=1
innodb_doublewrite = 1

innodb_file_per_table = 1
innodb_open_files = 1000
innodb_data_file_path= ibdata1:10M:autoextend
innodb_buffer_pool_size = 3G
innodb_additional_mem_pool_size = 32M

innodb_log_files_in_group = 2
innodb_log_file_size = 64M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 2
innodb_thread_concurrency = 0
innodb_lock_wait_timeout=50
innodb_flush_method = O_DIRECT
innodb_support_xa=1

# 200 * # DISKS
innodb_io_capacity = 100
innodb_read_io_threads = 2
innodb_write_io_threads = 2

# mariadb settings
[mariadb]
#thread-handling = pool-of-threads
#thread-pool-size= 20
#mysql --port=3307 --protocol=tcp
#extra-port=3307
#extra-max-connections=1

userstat = 0
key_cache_segments = 1
aria_group_commit = none
aria_group_commit_interval = 0
aria_log_file_size = 32M
aria_log_purge_type = immediate
aria_pagecache_buffer_size = 8M
aria_sort_buffer_size = 8M

[mariadb-5.5]
#ignore_db_dirs=
query_cache_strip_comments=0

#innodb_lazy_drop_table=1
innodb_read_ahead = linear
innodb_adaptive_flushing_method = estimate
innodb_flush_neighbor_pages = 1
innodb_stats_update_need_lock = 0
innodb_log_block_size = 512

log_slow_filter =admin,filesort,filesort_on_disk,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk

[mysqld_safe]
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysqld.log
#nice = -5
open-files-limit = 8192

[mysqldump]
quick
max_allowed_packet = 32M

[myisamchk]
key_buffer = 32M
sort_buffer = 16M
read_buffer = 16M
write_buffer = 16M

[mysqlhotcopy]
interactive-timeout
 
As you use Centmin Mod try mysqlmymonlite addon http://centminmod.com/addons.html#mysqlmymonlite to gather more detail server stats as my.cnf settings alone won't tell us much in itself.

From the my.cnf alone you have 3GB allocated to InnoDB and that's close to 1/2 your 8GB memory, so can see why but if you have up to 3GB of InnoDB data, then you do have optimally allocated memory to InnoDB/MySQL.
 
  • Like
Reactions: rdn
There is nothing wrong with mysql using 1/2 or more of your RAM.......

In fact:
"innodb_buffer_pool_size:InnoDB relies heavily on the buffer pool and should be set correctly, so be sure to allocate enough memory to it. Typically a good value is 70%-80% of available memory"

So please don't try to fix a problem which does not exist. My 8G servers uses virtually all of the RAM, and most of that is probably for the combo of mysql and ES.
 
  • Like
Reactions: rdn
As you use Centmin Mod try mysqlmymonlite addon http://centminmod.com/addons.html#mysqlmymonlite to gather more detail server stats as my.cnf settings alone won't tell us much in itself.

From the my.cnf alone you have 3GB allocated to InnoDB and that's close to 1/2 your 8GB memory, so can see why but if you have up to 3GB of InnoDB data, then you do have optimally allocated memory to InnoDB/MySQL.
Here it is:
 

Attachments

You can initially reduce your consumption by reducing the number of max connections allocated. You have it set to 300, but have only used 10 so far

Max Used Connections: 10/300 (3.33%)

Key Buffer is also set at 1G, but not being used:

Key Buffer Used: 1.00k/1.00G (0.00%)

You also are only using an ACTUAL 4.7GB of RAM, and still have 3GB free (just being used for caching things.....which is good)

Rich (BB code):
             total       used       free     shared    buffers     cached
Mem:          7876       7400        475          0        501       2101
Low:          7876       7400        475
High:            0          0          0
-/+ buffers/cache:       4798       3078
Swap:         1021         10       1011
 
As you use Centmin Mod try mysqlmymonlite addon http://centminmod.com/addons.html#mysqlmymonlite to gather more detail server stats as my.cnf settings alone won't tell us much in itself.

From the my.cnf alone you have 3GB allocated to InnoDB and that's close to 1/2 your 8GB memory, so can see why but if you have up to 3GB of InnoDB data, then you do have optimally allocated memory to InnoDB/MySQL.
Is there an add-on/tool like that for Litespeed/Apache?
 
phpmyadmin as well as a number of perl and other server scripts can also check your mysql stats and make suggestions. I would not take any of them as a lone indicator, but along with other stuff they are probably good.
 
If you only changed it in your .ini file then it won't have changed on the server. (y)

You also need to shutdown MySQL, rename the old log files, then restart it to create the newly sized ones (as described here)?
Yes off course, I already did that :)
As always, changing any values in my.conf needs a mysql server restart.
 
I got this:

Code:
ySQL server was started 2014-01-31 01:36:10
Uptime: 3 days 4 hours 4 min 25 sec
Uptime 273865
Uptime_since_flush_status 273865

Max Used Connections:      38/80 (47.50%)
Key Buffer Used:           824.00k/512.00M (0.16%)
Current Key Buffer Usage:  94.00M (18.36%)
InnoDB Buffer Pool:        1.00G/1.00G (100.00%)
Table Locks Waited:        16 Rate: 0.0/s (0.00%)

total used free shared buffers cached
Mem: 4840 4552 287 0 253 1336
Low: 4840 4552 287
High: 0 0 0
-/+ buffers/cache: 2962 1878
Swap: 1024 0 1024

Should I increase my InnoDB Buffer Pool and decrease Key Buffer Used?
 
Top Bottom