Sandeep B. Posted June 2, 2023 Share Posted June 2, 2023 If you have a MariaDB/MySQL VPS server with 4GB of RAM, you can optimize its performance by following these steps: If you’re looking for more than 4gb of ram configuration just divide the value with 4 and multiply the result with the amount of RAM, for example : 256/4 = 64 and you want it for 8 gb of ram just multiply it with the result i.e. 64 x 8 = 512 For MariaDB : Edit the MariaDB configuration file /etc/my.cnf.d/server.cnf using your favorite text editor: nano /etc/my.cnf.d/server.cnf Add the following lines under the [mysqld] section: default-storage-engine = InnoDB innodb_flush_method = O_DIRECT innodb_log_file_size = 128M innodb_buffer_pool_size = 128M max_allowed_packet = 128M max_connections = 200 key_buffer_size = 256M tmp_table_size = 64M max_heap_table_size = 64M For MySQL : Edit the MySQL configuration file /etc/my.cnf using your favorite text editor: nano /etc/my.cnf Add the following lines under the [mysqld] section: default-storage-engine = InnoDB innodb_flush_method = O_DIRECT innodb_log_file_size = 128M innodb_buffer_pool_size = 128M max_allowed_packet = 128M max_connections = 200 key_buffer_size = 256M tmp_table_size = 64M max_heap_table_size = 64M After you’ve added this values restart MariaDB/Mysql server : MariaDB server restart : systemctl restart mariadb MySQL server restart : systemctl restart mysql mysqld Link to comment Share on other sites More sharing options...
Netino Posted January 3 Share Posted January 3 It seems like a good configuration, except for the parameter: tmp_table_size = 64MB This resource is used per connection, so it should be calculated as follows: tmp_table_size = [total memory available] / max_connections For a server with 4Gb total memory, with max_connections = 200, it should be something around: tmp_table_size = 20.5Mb Or, reduce max_connections to a more realistic number, compatible with the user's needs. All of the following parameters must be considered per connection: sort_buffer_size read_buffer_size read_rnd_buffer_size join_buffer_size thread_stack binlog_cache_size tmp_table_size The following parameters make up the base memory consumed by mariadb, which is consumed only once: key_buffer_size query_cache_size innodb_buffer_pool_size innodb_additional_mem_pool_size innodb_log_buffer_size You can use the following script to calculate the total memory to be consumed by mariadb/mysql, included per connection: #!/bin/sh # you might want to add some user authentication here mysql -e "show variables; show status" | awk ' { VAR[$1]=$2 } END { MAX_CONN = VAR["max_connections"] MAX_USED_CONN = VAR["Max_used_connections"] BASE_MEM=VAR["key_buffer_size"] + VAR["query_cache_size"] + VAR["innodb_buffer_pool_size"] + VAR["innodb_additional_mem_pool_size"] + VAR["innodb_log_buffer_size"] MEM_PER_CONN=VAR["read_buffer_size"] + VAR["read_rnd_buffer_size"] + VAR["sort_buffer_size"] + VAR["join_buffer_size"] + VAR["binlog_cache_size"] + VAR["thread_stack"] + VAR["tmp_table_size"] MEM_TOTAL_MIN=BASE_MEM + MEM_PER_CONN*MAX_USED_CONN MEM_TOTAL_MAX=BASE_MEM + MEM_PER_CONN*MAX_CONN printf "+------------------------------------------+--------------------+\n" printf "| %40s | %15.3f MB |\n", "key_buffer_size", VAR["key_buffer_size"]/1048576 printf "| %40s | %15.3f MB |\n", "query_cache_size", VAR["query_cache_size"]/1048576 printf "| %40s | %15.3f MB |\n", "innodb_buffer_pool_size", VAR["innodb_buffer_pool_size"]/1048576 printf "| %40s | %15.3f MB |\n", "innodb_additional_mem_pool_size", VAR["innodb_additional_mem_pool_size"]/1048576 printf "| %40s | %15.3f MB |\n", "innodb_log_buffer_size", VAR["innodb_log_buffer_size"]/1048576 printf "+------------------------------------------+--------------------+\n" printf "| %40s | %15.3f MB |\n", "BASE MEMORY", BASE_MEM/1048576 printf "+------------------------------------------+--------------------+\n" printf "| %40s | %15.3f MB |\n", "sort_buffer_size", VAR["sort_buffer_size"]/1048576 printf "| %40s | %15.3f MB |\n", "read_buffer_size", VAR["read_buffer_size"]/1048576 printf "| %40s | %15.3f MB |\n", "read_rnd_buffer_size", VAR["read_rnd_buffer_size"]/1048576 printf "| %40s | %15.3f MB |\n", "join_buffer_size", VAR["join_buffer_size"]/1048576 printf "| %40s | %15.3f MB |\n", "thread_stack", VAR["thread_stack"]/1048576 printf "| %40s | %15.3f MB |\n", "binlog_cache_size", VAR["binlog_cache_size"]/1048576 printf "| %40s | %15.3f MB |\n", "tmp_table_size", VAR["tmp_table_size"]/1048576 printf "+------------------------------------------+--------------------+\n" printf "| %40s | %15.3f MB |\n", "MEMORY PER CONNECTION", MEM_PER_CONN/1048576 printf "+------------------------------------------+--------------------+\n" printf "| %40s | %18d |\n", "Max_used_connections", MAX_USED_CONN printf "| %40s | %18d |\n", "max_connections", MAX_CONN printf "+------------------------------------------+--------------------+\n" printf "| %40s | %15.3f MB |\n", "TOTAL (MIN)", MEM_TOTAL_MIN/1048576 printf "| %40s | %15.3f MB |\n", "TOTAL (MAX)", MEM_TOTAL_MAX/1048576 printf "+------------------------------------------+--------------------+\n" }' Note that the 'Max_used_connections' parameter gives a realistic number for the total number of connections since the last start of the mariadb server, and can be used to adjust the 'max_connections' parameter. Regards, Netino 1 Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now