{"id":445,"date":"2024-09-23T09:33:37","date_gmt":"2024-09-23T09:33:37","guid":{"rendered":"https:\/\/ramansaini.in\/blog\/?p=445"},"modified":"2024-09-30T06:44:54","modified_gmt":"2024-09-30T06:44:54","slug":"mysql-configuration-for-production-setup","status":"publish","type":"post","link":"https:\/\/ramansaini.in\/blog\/mysql-configuration-for-production-setup\/","title":{"rendered":"MySQL – A Guide For Production Setup Configuration"},"content":{"rendered":"\n

MySQL does not provide a default production setup when installed. It can serve connections and queries up to a limit and then it will start rejecting the connections. In this blog, we will explore the configuration variables that can be used to optimize the performance of the MySQL server and use the maximum resources from the server so that maximum efficiency can be brought out without increasing server size.<\/p>\n\n\n\n

The file we will be making changes is: mysqld.cnf<\/code><\/p>\n\n\n\n

This file can be found at \/etc\/mysql\/mysql.conf.d\/mysqld.cnf <\/code>. You can start adding the variables at the end of the file. You can also add the variables in file
\/etc\/mysql\/my.cnf<\/code> but you will need to add those variables below [mysqld]<\/code> text.<\/p>\n\n\n\n

We will explore the variables one by one and at the end of the blog, you will find the complete file containing all the variables which can be added directly. The values used as examples are the minimum resource configuration which are tested for runtime metrics.<\/p>\n\n\n\n

Here\u2019s an explanation of each variable in the provided my.cnf<\/code> (MySQL configuration) file:<\/p>\n\n\n\n

[mysqld]<\/code><\/h3>\n\n\n\n

This specifies that the following settings are for the MySQL server daemon.<\/p>\n\n\n\n

Basic Configuration:<\/h3>\n\n\n\n
    \n
  • port = 3306<\/code><\/strong>: Defines the port on which MySQL will listen for connections (default is 3306).<\/li>\n\n\n\n
  • socket = \/tmp\/mysql.sock<\/code><\/strong>: Specifies the socket file location used for local connections.<\/li>\n<\/ul>\n\n\n\n

    Connection Management:<\/h3>\n\n\n\n
      \n
    • back_log = 50<\/code><\/strong>: Determines how many connections can be in the queue before MySQL starts rejecting new ones.<\/li>\n\n\n\n
    • max_connections = 100<\/code><\/strong>: Sets the maximum number of simultaneous client connections MySQL can handle.<\/li>\n\n\n\n
    • wait_timeout = 256<\/code><\/strong>: The amount of time (in seconds) a connection can remain idle before being closed.<\/li>\n\n\n\n
    • max_connect_errors = 10<\/code><\/strong>: If there are more than this number of failed connection attempts from a host, MySQL blocks it until you flush the host’s table or manually unblock it.<\/li>\n<\/ul>\n\n\n\n

      Cache and Buffer Sizes:<\/h3>\n\n\n\n
        \n
      • table_open_cache = 2048<\/code><\/strong>: Specifies how many table descriptors MySQL can keep open simultaneously. A higher number can help reduce disk I\/O.<\/li>\n\n\n\n
      • max_allowed_packet = 16M<\/code><\/strong>: The maximum size of one packet or any generated\/intermediate string.<\/li>\n\n\n\n
      • max_heap_table_size = 512M<\/code><\/strong>: The maximum size for in-memory tables (Heap tables).<\/li>\n<\/ul>\n\n\n\n

        Buffer Settings (Used for Performance Tuning):<\/h3>\n\n\n\n
          \n
        • read_buffer_size = 64M<\/code><\/strong>: The size of the buffer used for sequential reads (large scans).<\/li>\n\n\n\n
        • read_rnd_buffer_size = 64M<\/code><\/strong>: The size of the buffer used when reading rows in a sorted order.<\/li>\n\n\n\n
        • sort_buffer_size = 64M<\/code><\/strong>: The buffer size used for sorts performed during query execution.<\/li>\n\n\n\n
        • join_buffer_size = 64M<\/code><\/strong>: The buffer used for joins that do not use indexes (improves join performance).<\/li>\n<\/ul>\n\n\n\n

          Thread Management:<\/h3>\n\n\n\n