How to get more connections for mariaDB or how to reduce RAM usage per thread?



  • We have an MariaDB 10.3 database server running on an 8 core and 64GB RAM machine. The database engine is innoDB. Our current max_connections = 175 and our DB admin tells me that it is not possible to increase the number of connections any further (at least not without increasing the physical RAM etc).

    We have a high load scenario and need to increase the number from 175 to say 350. Is that possible by "tuning" the below numbers?

    My gut feeling is that by optimizing the DB settings, more should be possible. Judging by the comments it seems that our admin did run MySQL Tuner. When talking to him I would like to bring some suggestions for improvement thus I would be interested in the opinion of the SO community regarding our settings.

    # Memory-Sizing
    open-files-limit                = 16384
    table_cache                     = 8192
    thread_cache_size               = 32
    max_allowed_packet              = 128M
    myisam_sort_buffer_size         = 32M
    key_buffer_size                 = 128M
    tmp_table_size                  = 1G
    max_heap_table_size             = 1G
    query_cache_type                = 1
    query_cache_size                = 16M
    query_cache_limit               = 16M
    sort_buffer_size                = 8M
    read_buffer_size                = 1M
    read_rnd_buffer_size            = 512K
    

    InnoDB

    innodb_open_files = 16384
    innodb_strict_mode = 1
    innodb_log_file_size = 6G
    innodb_log_buffer_size = 128M
    innodb_lock_wait_timeout = 1200
    innodb_large_prefix = 1
    innodb_buffer_pool_size = 30G
    innodb_buffer_pool_instances = 30

    Tuning-Results

    @see https://github.com/major/MySQLTuner-perl

    skip-name-resolve = 1
    join_buffer_size = 3M
    performance_schema = ON

    replication

    sync_binlog = 5
    max_binlog_size = 512M

    I used two different DB memory calculators ( https://www.abhinavbit.com/p/mysql-memory-calculator.html , https://www.mysqlcalculator.com/ ) with different results. Also I read http://mysql.rjweb.org/doc.php/ricksrots "Rule of thumbs". I think that the tmp_table_size (and heap) might be too big - at least one memory calculator indicates that this size is needed per connection. I also calculated the Created disk tmp tables ratio as http://techinfobest.com/optimize-mysql-tmp_table_size/ here and the result is 0,21% - which seems quite low.

    Tmp_disk_tables=((created_tmp_disk_tables*100/(created_tmp_tables+created_tmp_disk_tables))
    = ((11597*100/(5453174 + 11597))
    = 0,2122%
    

    Also some are https://stackoverflow.com/questions/45412537/should-i-turn-off-query-cache-in-mysql to turn off query cache.

    (Too) many variables so I appreciate your time and help.

    Update: Thank you all for your comments and answers. To be clear: I do trust our admin - that is not the issue 🙂 Our setup: We have a loadbalancer which equally distributes the load to currently 12 application server nodes. Each of the servers has connection pool configured with max 14 connections (min 2). There are some additional connections for management, maintenance and to be able to connect to the DB directly. And yes we have hit the No managed connections available within configured blocking timeout - not on all server nodes but on some. Usually this setup runs fine however in peak situations this is happening (higher amount of parallel users/usage). So the question is really how to be able to increase the number of connections.

    Now to your questions:

    • If connections come mostly from a web server, then it is probably configured too high. Answer: The connections come from an application server which runs a web application
    • If it is coming from applications, are they failing to close their connections? Answer: From what I did analyze they do not. In peak situation we indeed have this many parallel users using the system
    • Is there some form of "connection pooling"? If so, what limits does it have? Answer: Yes we have: min 2 connections and max 14 connections per server.
    • "high load scenario" Answer: means many parallel users on the system. Interestingly the load (CPU wise) on our application server nodes is relatively low so they could serve more users however we are then limited by the max DB connections (thus my question to increase those)
    • Slow queries Answer: We do write a slow query log (log all queries >2s) and 99% of the DB queries finish below 1s


  • You can increase max_connections without increasing RAM. BUT--Let's discuss whether 175 might actually be too big.

    If Max_used_connections has not reached 175, then max_connections is not the real problem.

    If you have hit that limit, then let's start by investigating what the clients are.

    • If connections come mostly from a web server, then it is probably configured too high.
    • If it is coming from applications, are they failing to close their connections?
    • Is there some form of "connection pooling"? If so, what limits does it have?

    Turn off the query_cache; it is (usually) more burden than benefit.

    Set these below 1% of RAM: tmp_table_size, max_heap_table_size. They are not just per query, but possibly per subquery. Anyway, 1G is very much into "diminishing returns". Disk-based temp tables happen for a variety of reasons; changing those two settings cannot get rid of all disk temp tables.

    Based on the setting of innodb_buffer_pool_size, I suggest that a lot of RAM is going unused.

    I agree that 0.21% is quite low. If you would like some more metrics like that, see http://mysql.rjweb.org/doc.php/mysql_analysis#tuning . It will also provide metrics saying whether the Query cache is being useful.

    "high load scenario" -- What does that mean? If it is "high 'Load Average'", that that is equivalent to "high CPU". This issue is best tackled by looking for the slowest and/or most common queries and trying to speed them up. Use the http://mysql.rjweb.org/doc.php/mysql_analysis#slow_queries_and_slowlog to help with that.




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2