Programming Tutorials

Sample my.cnf (my.ini) for MySQL with 1GB RAM

By: Murali in MySQL Tutorials on 2023-05-17  

For a VM with 1GB memory running both MySQL and Apache, you'll need to configure MySQL to use an appropriate amount of memory to ensure optimal performance. Here's a sample my.cnf configuration that you can start with:

[mysqld]
# Basic Settings
max_connections = 100
key_buffer_size = 16M
max_allowed_packet = 16M
thread_stack = 192K
thread_cache_size = 8

# Performance Tuning
table_open_cache = 64
sort_buffer_size = 512K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
join_buffer_size = 512K
tmp_table_size = 16M
max_heap_table_size = 16M
query_cache_type = 1
query_cache_limit = 1M
query_cache_size = 16M

# InnoDB Settings
innodb_buffer_pool_size = 256M
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 8M
innodb_thread_concurrency = 8
innodb_flush_method = O_DIRECT

# Logging
log_error = /var/log/mysql/error.log
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow_query.log
long_query_time = 2

# Other Settings
default_storage_engine = InnoDB
character_set_server = utf8mb4
collation_server = utf8mb4_unicode_ci

[mysqld_safe]
log_error = /var/log/mysql/mysql_error.log
pid_file = /var/run/mysqld/mysqld.pid

[mysql]
default_character_set = utf8mb4

[client]
default_character_set = utf8mb4

Please note that these values are just examples and may need to be adjusted based on your specific workload and requirements. It's essential to monitor the server's performance and make further adjustments if needed.

After making changes to the my.cnf file, restart MySQL to apply the new configuration.

Keep in mind that running both MySQL and Apache on a VM with limited memory can lead to resource constraints and potential performance issues. It's recommended to monitor the server's memory usage and consider upgrading the VM or allocating more resources if needed.