1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

How did you minimize your mysql server memory consumption?

Discussion in 'Server Configuration and Hosting' started by RoldanLT, Jan 12, 2014.

  1. RoldanLT

    RoldanLT 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 !
  2. Moshe1010

    Moshe1010 Well-Known Member

    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).
    RoldanLT likes this.
  3. p4guru

    p4guru Well-Known Member

    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.
    RoldanLT likes this.
  4. RoldanLT

    RoldanLT Well-Known Member

    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

    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
    # innodb settings
    #innodb_extra_rsegments = 4
    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_flush_method = O_DIRECT
    # 200 * # DISKS
    innodb_io_capacity = 100
    innodb_read_io_threads = 2
    innodb_write_io_threads = 2
    # mariadb settings
    #thread-handling = pool-of-threads
    #thread-pool-size= 20
    #mysql --port=3307 --protocol=tcp
    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
    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
    #nice = -5
    open-files-limit = 8192
    max_allowed_packet = 32M
    key_buffer = 32M
    sort_buffer = 16M
    read_buffer = 16M
    write_buffer = 16M
  5. p4guru

    p4guru Well-Known Member

    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.
    RoldanLT likes this.
  6. Moshe1010

    Moshe1010 Well-Known Member

    What is the size of your database?
    What hard drive are you using? (HDD/SSD?)
    RoldanLT likes this.
  7. craigiri

    craigiri Well-Known Member

    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.
    RoldanLT likes this.
  8. RoldanLT

    RoldanLT Well-Known Member

    • 1.6GB
    • HDD
    Thanks @craigiri
  9. RoldanLT

    RoldanLT Well-Known Member

    Here it is:

    Attached Files:

  10. MattW

    MattW Well-Known Member

    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)

                 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
    SiteOwner and RoldanLT like this.
  11. RoldanLT

    RoldanLT Well-Known Member

    Thanks @MattW
    Changes I made:

    MattW likes this.
  12. RoldanLT

    RoldanLT Well-Known Member

    I changed innodb_log_file_size to 256M also :)
  13. Moshe1010

    Moshe1010 Well-Known Member

    Is there an add-on/tool like that for Litespeed/Apache?
  14. RoldanLT

    RoldanLT Well-Known Member

    It will work fine on Litespeed or apache I think.
    Moshe1010 likes this.
  15. craigiri

    craigiri Well-Known Member

    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.
  16. p4guru

    p4guru Well-Known Member

    works for Apache, Nginx and LiteSpeed on CentOS/Debian and WHM/Cpanel CentOS http://mysqlmymon.com
    Moshe1010 likes this.
  17. CyclingTribe

    CyclingTribe Well-Known Member

    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)?
    Last edited: Feb 3, 2014
    maszd and MattW like this.
  18. RoldanLT

    RoldanLT Well-Known Member

    Yes off course, I already did that :)
    As always, changing any values in my.conf needs a mysql server restart.
  19. Moshe1010

    Moshe1010 Well-Known Member

    I got this:

    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?
  20. RoldanLT

    RoldanLT Well-Known Member

    You should increase InnoDB Buffer Pool Size, to something like 2G or 3G.

Share This Page