2

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 (1,2) with different results. Also I read Ricks "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 suggested 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 suggesting 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
3
  • SHOW GLOBAL STATUS LIKE 'Max_used_connections'; -- to see if it is actually hitting 175.
    – Rick James
    Mar 1, 2022 at 18:07
  • @WilsonHauck A) 215 B+C) See pstbn E)giving an error F)in the works
    – Lonzak
    Mar 2, 2022 at 17:34
  • @WilsonHauck The full version is 10.3.31-MariaDB-0+deb10u1-log. (I had to redact certain values for security reasons but you are right - the version is ok to know, Running on debian 10) We use an 1TB SSD.
    – Lonzak
    Mar 3, 2022 at 7:26

3 Answers 3

5

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 SlowLog to help with that.

2
  • Thank your your answer! I updated my post with parts of the answers. With 1% do you mean ~1% each or 0.5+0.5%? The size of innodb_buffer_pool is a question on its own. We did measurements and the size is this big to keep all indices in RAM. We are also writing slow query logs which logs DB queries over 2s and 99% of the calls are below 1s.
    – Lonzak
    Mar 2, 2022 at 8:56
  • I uploaded the two queries from your page to pstbn
    – Lonzak
    Mar 2, 2022 at 17:30
1

Well, I did not find much of significance in these values. The slowlog may provide more info.

Hmmm... 12 app servers, each connecting up to 14 times. Max_used_connections = 164. That sounds like you came close to hitting 14 on each server at least once in the last 4 months. Alas, that does not give any useful clues in what to do next in that area.

And 916 QPS is rather high.

While you could increase max_connections and the 14 per app server, I worry that the server will bog down.

If you expect to double the traffic in the near future, let's talk about having multiple MariaDB servers -- either a Primary plus at least two Replicas, or a Galera cluster.

If the slowlog finds something to speed up, that will cut down on the 'need' to increase max_connections. If long_query_time=2 does not give much info, it can be lowered. (The number is a float.)

But, I find that even with long_query_time set "too high", it is quite useful at providing useful info on a severe spike. The thundering herd leads to all queries taking much longer than they should.

On the other hand If you "never" have high CPU, then it should be safe to increase the 14.


Analysis of GLOBAL STATUS and VARIABLES:
 

Observations:

  • Version: 10.3
  • 64 GB of RAM
  • Uptime = 116d 05:46:57
  • 916 QPS

The More Important Issues:

  • Lower these to under 1% of RAM: tmp_table_size, max_heap_table_size.

  • Check to see if the code is issuing COMMIT sometimes when it is not necessary.

Details and other observations:

  • ( table_open_cache ) = 8,192 -- Number of table descriptors to cache -- Several hundred is usually good.
  • ( innodb_buffer_pool_size ) = 30,720 / 65536M = 46.9% -- % of RAM used for InnoDB buffer_pool -- Set to about 70% of available RAM. (To low is less efficient; too high risks swapping.)
  • ( innodb_buffer_pool_instances ) = 30 -- For large RAM, consider using 1-16 buffer pool instances, not allowing less than 1GB each. Also, not more than, say, twice the number of CPU cores. -- Recommend no more than 16. (Beginning to go away in 10.5)
  • ( innodb_lru_scan_depth * innodb_buffer_pool_instances ) = 1,024 * 30 = 30,720 -- A metric of CPU usage. -- Lower either number.
  • ( innodb_lru_scan_depth * innodb_page_cleaners ) = 1,024 * 4 = 4,096 -- Amount of work for page cleaners every second. -- "InnoDB: page_cleaner: 1000ms intended loop took ..." may be fixable by lowering lru_scan_depth: Consider 1000 / innodb_page_cleaners (now 4). Also check for swapping.
  • ( innodb_lru_scan_depth ) = 1,024 -- innodb_lru_scan_depth is a very poorly named variable. A better name would be innodb_free_page_target_per_buffer_pool. It is a number of pages InnoDB tries to keep free in each buffer pool instance to speed up read and page creation operations. -- "InnoDB: page_cleaner: 1000ms intended loop took ..." may be fixed by lowering lru_scan_depth
  • ( innodb_io_capacity ) = 200 -- When flushing, use this many IOPs. -- Reads could be slugghish or spiky. Use 2000 if using SSD drive.
  • ( innodb_doublewrite ) = innodb_doublewrite = OFF -- ON leads to extra I/O, but extra safety in crash. -- OFF is OK for FusionIO, Galera, Replicas, ZFS.
  • ( innodb_log_buffer_size ) = 128M -- Suggest 2MB-64MB, and at least as big as biggest blob set in transactions. -- Adjust innodb_log_buffer_size (now 134217728).
  • ( Uptime / 60 * innodb_log_file_size / Innodb_os_log_written ) = 10,043,217 / 60 * 6144M / 1591819351040 = 677 -- Minutes between InnoDB log rotations Beginning with 5.6.8, innodb_log_file_size can be changed dynamically; I don't know about MariaDB. Be sure to also change my.cnf -- (The recommendation of 60 minutes between rotations is somewhat arbitrary.) Adjust innodb_log_file_size (now 6442450944). (Cannot change in AWS.)
  • ( innodb_flush_method ) = innodb_flush_method = fsync -- How InnoDB should ask the OS to write blocks. Suggest O_DIRECT or O_ALL_DIRECT (Percona) to avoid double buffering. (At least for Unix.) See chrischandler for caveat about O_ALL_DIRECT
  • ( Handler_rollback ) = 189,403,449 / 10043217 = 19 /sec -- Why so many rollbacks?
  • ( Innodb_row_lock_time_avg ) = 8,946 -- Avg time to lock a row (millisec) -- Possibly conflicting queries; possibly table scans.
  • ( Innodb_row_lock_time_max ) = 472,943 -- Max time to lock a row (millisec) -- Possibly conflicting queries; possibly table scans.
  • ( innodb_lock_wait_timeout ) = 1,200 -- Two battling InnoDB transactions, but not a deadlock -- one will wait this long (seconds) in hopes of getting the desired locks. -- Fix the cause of timeouts rather than increasing this value.
  • ( innodb_flush_neighbors ) = 1 -- A minor optimization when writing blocks to disk. -- Use 0 for SSD drives; 1 for HDD.
  • ( innodb_io_capacity ) = 200 -- I/O ops per second capable on disk . 100 for slow drives; 200 for spinning drives; 1000-2000 for SSDs; multiply by RAID factor. Limits write IO requests per second (IOPS). -- For starters: HDD: 200; SSD: 2000.
  • ( innodb_adaptive_hash_index ) = innodb_adaptive_hash_index = ON -- Whether to use the adapative hash (AHI). -- ON for mostly readonly; OFF for DDL-heavy
  • ( innodb_flush_log_at_trx_commit ) = 1 -- 1 = secure; 2 = faster -- (You decide) Use 1, along with sync_binlog (now 5)=1 for the greatest level of fault tolerance. 0 is best for speed. 2 is a compromise between 0 and 1.
  • ( sync_binlog ) = 5 -- Use 1 for added security, at some cost of I/O =1 may lead to lots of "query end"; =0 may lead to "binlog at impossible position" and lose transactions in a crash, but is faster. 0 is OK for Galera.
  • ( innodb_adaptive_hash_index ) = innodb_adaptive_hash_index = ON -- Usually should be ON. -- There are cases where OFF is better. See also innodb_adaptive_hash_index_parts (now 8) (after 5.7.9) and innodb_adaptive_hash_index_partitions (MariaDB and Percona). ON has been implicated in rare crashes (bug 73890). 10.5.0 decided to default OFF.
  • ( innodb_print_all_deadlocks ) = innodb_print_all_deadlocks = OFF -- Whether to log all Deadlocks. -- If you are plagued with Deadlocks, turn this on. Caution: If you have lots of deadlocks, this may write a lot to disk.
  • ( min( tmp_table_size, max_heap_table_size ) ) = (min( 1024M, 1024M )) / 65536M = 1.6% -- Percent of RAM to allocate when needing MEMORY table (per table), or temp table inside a SELECT (per temp table per some SELECTs). Too high may lead to swapping. -- Decrease tmp_table_size (now 1073741824) and max_heap_table_size (now 1073741824) to, say, 1% of ram.
  • ( local_infile ) = local_infile = ON -- local_infile (now ON) = ON is a potential security issue
  • ( bulk_insert_buffer_size ) = 8M / 65536M = 0.01% -- Buffer for multi-row INSERTs and LOAD DATA -- Too small could hinder such operations.
  • ( Qcache_not_cached ) = 2,027,192,644 / 10043217 = 201 /sec -- SQL_CACHE attempted, but ignored -- Rethink caching; tune qcache
  • ( Qcache_free_blocks / Qcache_total_blocks ) = 1,997 / 6500 = 30.7% -- Fragmentation in Query Cache. -- Various things.
  • ( Qcache_inserts - Qcache_queries_in_cache ) = (1063262492 - 2234) / 10043217 = 105 /sec -- Invalidations/sec.
  • ( (query_cache_size - Qcache_free_memory) / Qcache_queries_in_cache / query_alloc_block_size ) = (16M - 13111704) / 2234 / 16384 = 0.1 -- query_alloc_block_size vs formula -- Adjust query_alloc_block_size (now 16384)
  • ( tmp_table_size ) = 1024M -- Limit on size of MEMORY temp tables used to support a SELECT -- Decrease tmp_table_size (now 1073741824) to avoid running out of RAM. Perhaps no more than 64M.
  • ( (Com_insert + Com_update + Com_delete + Com_replace) / Com_commit ) = (201391833 + 120793648 + 101525982 + 0) / 833693705 = 0.508 -- Statements per Commit (assuming all InnoDB) -- Low: Might help to group queries together in transactions; High: long transactions strain various things.
  • ( binlog_format ) = binlog_format = MIXED -- STATEMENT/ROW/MIXED. -- ROW is preferred by 5.7 (10.3)
  • ( expire_logs_days ) = 90 -- How soon to automatically purge binlog (after this many days). Being replaced by binlog_expire_logs_seconds. -- Too large (or zero) = consumes disk space; too small = need to respond quickly to network/machine crash. (Not relevant if log_bin (now ON) = OFF)
  • ( Syncs ) = 13,032,779 / 10043217 = 1.3 /sec -- Sync to disk for binlog and perhaps .frm, not InnoDB.
  • ( thread_pool_max_threads ) = 65,536 -- One of many settings for MariaDB's thread pooling -- Lower the value.

Abnormally small:

Innodb_dblwr_pages_written = 0
Sort_priority_queue_sorts = 0.02 /HR
Table_locks_immediate = 0.77 /HR
eq_range_index_dive_limit = 0
innodb_page_cleaners / innodb_buffer_pool_instances = 0.133
interactive_timeout = 600

Abnormally large:

Binlog_bytes_written = 167602 /sec
Binlog_snapshot_position = 3.25e+8
Busy_time = 2.76e+7
Com_commit = 83 /sec
Com_drop_index = 0.00036 /HR
Com_purge_before_date = 0.0025 /HR
Com_show_generic = 0.0014 /HR
Com_show_status = 0.13 /sec
Cpu_time = 2.94e+6
Handler_discover = 0.075 /HR
Handler_read_rnd_next / Handler_read_rnd = 13,901
Innodb_buffer_pool_pages_dirty = 136,388
Innodb_buffer_pool_pages_misc = 1.84e+19
Performance_schema_file_instances_lost = 47,805
Qcache_hits = 338 /sec
Qcache_inserts = 105 /sec
Slave_connections = 34
gtid_domain_id = 4.0e+6
innodb_open_files = 16,384
max_heap_table_size = 1024MB
max_user_connections = 260
min(max_heap_table_size, tmp_table_size) = 1024MB
performance_schema_events_stages_history_size = 20
performance_schema_events_statements_history_size = 20
performance_schema_events_waits_history_size = 20
query_cache_limit = 1.68e+7
table_open_cache / max_connections = 31.3
tmp_memory_table_size = 1024MB

Abnormal strings:

Innodb_have_snappy = ON
Slave_heartbeat_period = 0
Slave_received_heartbeats = 0
aria_recover_options = BACKUP,QUICK
init_connect = SET NAMES utf8mb4
innodb_fast_shutdown = 1
innodb_use_native_aio = OFF
myisam_stats_method = NULLS_UNEQUAL
old_alter_table = DEFAULT
sql_slave_skip_counter = 0
userstat = ON
wsrep_data_home_dir = /mysql/data/

Query Cache

Oh, I promised to make a recommendation about the Query cache. The above list is the important highlights; in looking at the details, I get inconclusive results. Some details:

  • query_cache_type = ON -- you could switch to DEMAND and change all SELECTs to include either SQL_CACHE or SQL_NO CACHE as appropriate. This would make the cache more efficient.
  • Since you have lots of RAM, increasing 16MB some (say 32MB) might help. (There is really no information to conclusively say whether this or other changes will help or hurt.)
  • Qcache_lowmem_prunes = 9.4/second -- This is 'moderate', but is supports my previous note.
  • Qcache_free_memory / query_cache_size = 78% -- This is puzzling. Perhaps the cache had been freshly purged.
  • Qcache_queries_in_cache = 2234
  • Qcache_inserts = 105/sec (contrast: 660 SELECTs/sec)
12
  • You are saying that tmp_table_size, max_heap_table_size are now 1.6% and we should lower it. 1GB=1.6% => both values are 3.2% => So it is 1% for each of the values? 1% of 64G would be 655 MB. However a few lines below you suggest to set tmp_table_size to 64M. So the max_heap_table_size must be 655M-64M=591M? Or do I get this wrong? Or if each value should be 1% then how does the 64M fits in?
    – Lonzak
    Mar 3, 2022 at 10:27
  • @Lonzak - (The 1% rule is not strict.) Lower each setting to under 1% of RAM. When limiting a "temp" table that the Optimizer creates to help with some SELECTs, it limits the in-memory table to min(tmp_table_size, max_heap_table_size) or creates an on-disk tmp table.
    – Rick James
    Mar 3, 2022 at 16:01
  • @Lonzak - As for the inconsistency of suggesting 64M -- Sorry. 64M is usually plenty for most applications. Without a lot more info about your queries, I advise between 645M and 1% of RAM -- either one is probably "good enough". Less than 64M may slow down some queries; more than 1% may lead to swapping, which is terrible for performance.
    – Rick James
    Mar 3, 2022 at 16:05
  • @Lonzak - And any advice is subject to exceptions. And subject to the queries involved. And subject to the data. And may change tomorrow after the data changes. Etc. I found almost nothing to criticize in your settings. (Most systems I have reviewed have a dozen recommendations, some "very important".
    – Rick James
    Mar 3, 2022 at 16:08
  • 1
    @Lonzak - I added a section with more info on Query_cache.
    – Rick James
    Mar 4, 2022 at 16:17
1

Rate Per Second = RPS

Suggestions to consider for your my.cnf[mysqld] section to expedite query completion

innodb_lru_scan_depth=100  # from 1024 to conserve 90% CPU cycles used for function every SECOND
innodb_buffer_pool_instances=16  # from 30 to reduce overhead of managing 14 instances
read_rnd_buffer_size=96K  # from 512K to reduce handler_read_rnd_next RPS of 150,967 
join_buffer_size=1M  # from 3M for your join row pointer management
sort_buffer_size=2M  # from 8M and could cause non-lethal increase in sort_merge_passes

The last 2 are per connection reduction of 6M of RAM.

Observations,

  1. max_connections has current limit of 262 and your max_used_connections was 164 for 116 days of uptime. Until max_used_connections is 90% of availability keep it where it is.
  2. Disabling use of Query Cache with query_cache_type=OFF and query_cache_size=0 would eliminate the qcache_lowmem_prunes running 9 times every SECOND of your 116 days of uptime.
  3. com_rollback averages 1 every 6 SECONDS. Imagine the overhead and try to eliminate the cause. Our general log analysis would assist in finding the problem.
  4. com_stmt_prepare is reported as running every 13 SECONDS. Why would there be NO com_stmt_execute actions reported? Normally we see matching numbers.
  5. com_stmt_close is running every 13 SECONDS to release the resources which is good.
  6. select_scan reported table scan RPS of 25 for the 116 days of uptime. Indication of columns needing to be indexed in some tables and would cut query execution time required, not to mention overhead of scanning tables.

There are other configuration variables in need of adjustment.

1
  • These are all dynamic variables and do not REQUIRE stop/start of instance. Login as root, get to MySQL command prompt, SET GLOBAL variable_name=value; (use of K or M will likely be rejected) Alternative for 96K is 96*1024 AND alternative for 1M is 1*1024*1024 AND alternative for 2M is 2*1024*1024 to achieve the same goal. Verify your SET is active with SHOW GLOBAL VARIABLES LIKE 'variable_name'; to confirm in 1 minute. Change your my.cnf after testing, before next STOP/START. Mar 3, 2022 at 21:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.