Mariadb – DB Highest usage of available connections increases

mariadb

Hi I use aptitude portal and whenever userlimit exceeds 2000 I get 300 dbconnection upto 1000 it was only 18.Kindly help me to rectify issue.Is this configurations enough for 10000 users.

My.cnf: https://pastebin.com/Zqz9VzZ0
mysqltuner:https://pastebin.com/2vDEqJgk
tuningprimer:https://pastebin.com/QPB1Z4Tx
global varialble:https://pastebin.com/Bfb88905,
https://pastebin.com/rBKUSXF7
global status:https://pastebin.com/CxLNVyiA
fullprocesslist:https://pastebin.com/3KKpHd3s
iostat:https://pastebin.com/UQydYMbL

and HTOP
enter image description here

Best Answer

Observations:

  • Version: 10.4.13-MariaDB-1:10.4.13+maria~focal-log
  • 62 GB of RAM
  • Uptime = 08:43:34; some GLOBAL STATUS values may not be meaningful yet.
  • You are not running on Windows.
  • Running 64-bit version
  • You appear to be running entirely (or mostly) InnoDB.

The More Important Issues:

Lots of "slow" queries. Lower long_query_time and turn on the slowlog. Then follow instructions in http://mysql.rjweb.org/doc.php/mysql_analysis#slow_queries_and_slowlog (This is based on temp tables, disk temp tables, slow queries, scans, full joins, etc, per second)

Lower key_buffer_size to 30M

You have only a few GB of data? Much of this 62GB machine is going unused? Or will more of the buffer_pool get used as time goes on? (I see that the STATUS was taken after only 8 hours.)

Is this a Primary, with Replica(s) attached? Or is the binlog being used for something else?

If the disk is SSD, I think that innodb_flush_neighbors should be 0.

I recommend turning the Query cache OFF, with size 0.

SHOW VARIABLES ... is happening 26 times a second?! Is a 3rd party package being sloppy?

Are you using ENGINE=Aria for some tables?

Details and other observations:

( Key_blocks_used * 1024 / key_buffer_size ) = 0 * 1024 / 512M = 0 -- Percent of key_buffer used. High-water-mark. -- Lower key_buffer_size (now 536870912) to avoid unnecessary memory usage.

( (key_buffer_size / 0.20 + innodb_buffer_pool_size / 0.70) ) = ((512M / 0.20 + 16384M / 0.70)) / 63488M = 40.9% -- Most of available ram should be made available for caching. -- http://mysql.rjweb.org/doc.php/memory

( table_open_cache ) = 7,655 -- Number of table descriptors to cache -- Several hundred is usually good.

( 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_page_cleaners / innodb_buffer_pool_instances ) = 4 / 16 = 0.25 -- innodb_page_cleaners -- Recommend setting innodb_page_cleaners (now 4) to innodb_buffer_pool_instances (now 16)

( innodb_lru_scan_depth ) = 1,024 -- "InnoDB: page_cleaner: 1000ms intended loop took ..." may be fixed by lowering lru_scan_depth

( Innodb_buffer_pool_pages_free * 16384 / innodb_buffer_pool_size ) = 743,605 * 16384 / 16384M = 70.9% -- buffer pool free -- buffer_pool_size is bigger than working set; could decrease it

( Innodb_buffer_pool_pages_free / Innodb_buffer_pool_pages_total ) = 743,605 / 1026016 = 72.5% -- Pct of buffer_pool currently not in use -- innodb_buffer_pool_size (now 17179869184) is bigger than necessary?

( Innodb_buffer_pool_bytes_data / innodb_buffer_pool_size ) = 4,626,980,864 / 16384M = 26.9% -- Percent of buffer pool taken up by data -- A small percent may indicate that the buffer_pool is unnecessarily big.

( Innodb_pages_written/Innodb_data_writes ) = 575,977/3375098 = 17.1% -- Seems like these values should be equal?

( Innodb_os_log_written ) = 7,275,202,048 / 31414 = 231591 /sec -- This is an indicator of how busy InnoDB is. -- Very idle or very busy InnoDB.

( Innodb_log_writes ) = 2,642,325 / 31414 = 84 /sec

( innodb_flush_neighbors ) = 2 -- A minor optimization when writing blocks to disk. -- Use 0 for SSD drives; 1 for HDD.

( innodb_adaptive_hash_index ) = innodb_adaptive_hash_index = OFF -- 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).

( 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.

( net_buffer_length / max_allowed_packet ) = 16,384 / 16M = 0.10%

( local_infile ) = local_infile = ON -- local_infile (now ON) = ON is a potential security issue

( Qcache_free_memory / query_cache_size ) = 67,091,072 / 64M = 100.0% -- Pct Query Cache free -- lower query_cache_size (now 67108864) Lowering it releases RAM for other uses, but since the free space will vary over time, this one reading may fool you.

( Qcache_not_cached ) = 56,159,244 / 31414 = 1787 /sec -- SQL_CACHE attempted, but ignored -- Rethink caching; tune qcache

( Qcache_not_cached / (Qcache_hits + Com_select + Qcache_not_cached) ) = 56,159,244 / (0 + 56285506 + 56159244) = 49.9% -- Percent of SELECTs that were not cached in the QC. -- QC is not very useful.

( Qcache_hits / (Qcache_hits + Com_select) ) = 0 / (0 + 56285506) = 0 -- Hit ratio -- SELECTs that used QC -- Consider turning off the query cache.

( Qcache_hits / (Qcache_hits + Qcache_inserts + Qcache_not_cached) ) = 0 / (0 + 0 + 56159244) = 0 -- Query cache hit rate -- Probably best to turn off the QC.

( Qcache_free_blocks / Qcache_total_blocks ) = 1 / 1 = 100.0% -- Fragmentation in Query Cache. -- Various things.

( (query_cache_size - Qcache_free_memory) / Qcache_queries_in_cache / query_alloc_block_size ) = (64M - 67091072) / 0 / 16384 = INF -- query_alloc_block_size vs formula -- Adjust query_alloc_block_size (now 16384)

( Created_tmp_tables ) = 5,762,704 / 31414 = 183 /sec -- Frequency of creating "temp" tables as part of complex SELECTs.

( Created_tmp_disk_tables ) = 2,021,318 / 31414 = 64 /sec -- Frequency of creating disk "temp" tables as part of complex SELECTs -- increase tmp_table_size (now 268435456) and max_heap_table_size (now 268435456). Check the rules for temp tables on when MEMORY is used instead of MyISAM. Perhaps minor schema or query changes can avoid MyISAM. Better indexes and reformulation of queries are more likely to help.

( tmp_table_size ) = 256M -- Limit on size of MEMORY temp tables used to support a SELECT -- Decrease tmp_table_size (now 268435456) to avoid running out of RAM. Perhaps no more than 64M.

( Com_show_variables ) = 807,549 / 31414 = 26 /sec -- SHOW VARIABLES ... -- Why are you requesting the VARIABLES so often?

( Select_full_join ) = 520,405 / 31414 = 17 /sec -- joins without index -- Add suitable index(es) to tables used in JOINs.

( Select_scan ) = 6,348,030 / 31414 = 202 /sec -- full table scans -- Add indexes / optimize queries (unless they are tiny tables)

( Select_scan / Com_select ) = 6,348,030 / 56285506 = 11.3% -- % of selects doing full table scan. (May be fooled by Stored Routines.) -- Add indexes / optimize queries

( Com_insert + Com_delete + Com_delete_multi + Com_replace + Com_update + Com_update_multi ) = (2147365 + 225000 + 11 + 0 + 3061464 + 8) / 31414 = 172 /sec -- writes/sec -- 50 writes/sec + log flushes will probably max out I/O write capacity of normal drives

( binlog_format ) = binlog_format = MIXED -- STATEMENT/ROW/MIXED. -- ROW is preferred by 5.7 (10.3)

( Syncs ) = 2,651,275 / 31414 = 84 /sec -- Sync to disk for binlog and perhaps .frm, not InnoDB.

( slow_query_log ) = slow_query_log = OFF -- Whether to log slow queries. (5.1.12)

( long_query_time ) = 5 -- Cutoff (Seconds) for defining a "slow" query. -- Suggest 2

( Slow_queries ) = 5,037,399 / 31414 = 160 /sec -- Frequency (Slow queries per sec) -- Rework slow guys; improve indexes; watch disk space for slow log file

( Slow_queries / Questions ) = 5,037,399 / 71051250 = 7.1% -- Frequency (% of all queries) -- Find slow queries; check indexes.

( log_queries_not_using_indexes ) = log_queries_not_using_indexes = ON -- Whether to include such in slowlog. -- This clutters the slowlog; turn it off so you can see the real slow queries. And decrease long_query_time (now 5) to catch most interesting queries.

( Connections ) = 2,396,599 / 31414 = 76 /sec -- Connections -- Increase wait_timeout (now 60); use pooling?

Abnormally small:

(query_cache_size - Qcache_free_memory) / query_cache_size = 0.03%
1 - Qcache_free_memory / query_cache_size = 0.03%
Acl_database_grants = 0
Acl_proxy_users = 0
Acl_users = 0
Handler_read_rnd_next / Handler_read_rnd = 1.13
Sort_priority_queue_sorts = 7.2 /HR
innodb_flushing_avg_loops = 5
innodb_lru_scan_depth / innodb_io_capacity = 0.0341
innodb_spin_wait_delay = 4
key_cache_division_limit = 50
lock_wait_timeout = 86,400
query_cache_limit = 131,072
query_cache_min_res_unit = 2,048
wait_timeout = 60

Abnormally large:

Aria_pagecache_read_requests = 8961 /sec
Aria_pagecache_reads = 64 /sec
Aria_pagecache_writes = 212 /sec
Aria_transaction_log_syncs = 971
Binlog_cache_use = 87 /sec
Com_begin = 14 /sec
Com_create_index = 2.9 /HR
Com_create_temporary_table = 1.3 /HR
Com_drop_temporary_table = 1.3 /HR
Com_select = 1791 /sec
Com_show_slave_hosts = 0.57 /HR
Com_show_storage_engines = 0.57 /HR
Created_tmp_files = 0.32 /sec
Feature_check_constraint = 0.11 /HR
Feature_subquery = 17 /sec
Handler_commit = 2160 /sec
Handler_discover = 6 /HR
Handler_prepare = 359 /sec
Handler_read_rnd = 6318 /sec
Innodb_buffer_pool_pages_free = 743,605
Innodb_os_log_fsyncs = 84 /sec
Memory_used_initial = 7.43e+8
Select_range = 99 /sec
Sort_rows = 6554 /sec
Sort_scan = 137 /sec
Table_open_cache_hits = 2576 /sec
Threads_cached = 127
back_log / max_connections = 927.7%
histogram_size = 254
innodb_buffer_pool_chunk_size = 1024MB
innodb_io_capacity_max = 60,000
innodb_max_dirty_pages_pct_lwm = 10
innodb_open_files = 7,655
innodb_write_io_threads = 16
key_cache_age_threshold = 7,200
key_cache_block_size = 16,384
max_prepared_stmt_count = 100,000
optimizer_use_condition_selectivity = 4
performance_schema_events_stages_history_size = 20
performance_schema_events_statements_history_size = 20
performance_schema_events_waits_history_size = 20
performance_schema_max_cond_classes = 90
performance_schema_max_socket_instances = 2,068
performance_schema_max_thread_instances = 2,148

Abnormal strings:

aria_recover_options = BACKUP,QUICK
disconnect_on_expired_password = OFF
histogram_type = DOUBLE_PREC_HB
innodb_fast_shutdown = 1
innodb_monitor_enable = %
innodb_use_atomic_writes = ON
log_slow_admin_statements = ON
log_slow_verbosity = query_plan
myisam_stats_method = NULLS_UNEQUAL
old_alter_table = DEFAULT
opt_s__optimize_join_buffer_size = on
optimizer_trace = enabled=off
plugin_maturity = gamma
use_stat_tables = PREFERABLY_FOR_QUERIES
wsrep_debug = NONE
wsrep_load_data_splitting = OFF