0

I am very new to this stuff. I just wanted to ask if somebody has a good my.cnf for my MariaDB database because every time i have more than 1000 connections to my website it starts to have hight load time up to 16 Seconds under this value it loads instand.

My mysql settings are very default i only changed the max_connections.

max_connections        = 10000

Server Specs:

  • CPU: Intel i7-4790K 4c / 8t 4GHz
  • RAM: 16 GB DDR3
  • Debian 9
  • PHP Version 7.0.33
  • Apache 2.4.25
  • MariaDb 10.1.41
Improve this question
4
  • Check mysqltuner It's a script which indicates what parameter can/should/could be changed ...
    – Luuk
    Dec 22 '19 at 13:57
  • 10K is very high. Probably there are some slow queries that are preventing the connections from getting out of the way fast. Come back after gathering the info in mysql.rjweb.org/doc.php/mysql_analysis
    – Rick James
    Dec 23 '19 at 3:29
  • From MySQL command prompt, SHOW GLOBAL STATUS LIKE 'max_used_connections'; to determine a REASONABLE number for max_connections. Usually used * 1.10 would accomodate growth and not blow up your RAM footprint required.
    – Wilson Hauck
    Dec 24 '19 at 17:54
  • Set innodb_buffer_pool to about 70% of RAM; leave the other settings at defaults.
    – Rick James
    Sep 1 at 19:17
1

max_connections depends on RAM because each connection uses RAM for read/join/sort buffers. You can not set max_connections arbitrary high. In general the RAM consumption can be represented that way:

+------+-----------------------+---------------------+-------------+
|  OS  | MyISAM/InnoDB buffers | Connections buffers | Third party |
|      |                       +---------------------+             |
|      |                       | | | | | | | ...     |             |
+------+-----------------------+---------------------+-------------+
  • Host's OS need some RAM for the proper operation - usually 1-2GB+.
  • Third party services (apache/php/etc) requirements should be considered.
  • DB engines are used RAM for caching. MyISAM caches indexes only while InnoDB caches both data and indexes.
  • All active connections consumes a lot of RAM.

For maximum performance InnoDB should have innodb pool big enough to fit all the tables and their indexes in the RAM. max_connections should be tuned along with the read_buffer, join_buffer and sort_buffer values.

But the main attention should be paid to the queries optimization. You can't get a good overall peformance if the single query is slow. Even if you have the host with 256-512 GB of RAM it can be slow if the frequenlty executed queries are slow.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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