MySQL's maximum memory usage is dangerously high

Question

I'm trying to understand what is not working in my configuration.

When I run the

    [root@server ~]# ./mysqltuner.pl
 >>  MySQLTuner 1.9.8
         * Jean-Marie Renouard <jmrenouard@gmail.com>
         * Major Hayden <major@mhtx.net>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.pl/
 >>  Run with '--help' for additional options and output filtering
[--] Skipped version check for MySQLTuner script
[!!] Your MySQL version 10.2.43-MariaDB-log is EOL software!  Upgrade soon!
[OK] Operating on 64-bit architecture
-------- Log file Recommendations ------------------------------------------------------------------
[OK] Log file /var/log/mysqld.log exists
[--] Log file: /var/log/mysqld.log(1M)
[OK] Log file /var/log/mysqld.log is not empty
[OK] Log file /var/log/mysqld.log is smaller than 32 Mb
[OK] Log file /var/log/mysqld.log is readable.
[!!] /var/log/mysqld.log contains 7295 warning(s).
[!!] /var/log/mysqld.log contains 2416 error(s).
[--] 68 start(s) detected in /var/log/mysqld.log
[--] 1) 2022-04-15 15:28:49 139969276774592 [Note] /usr/sbin/mysqld: ready for connections.
[--] 2) 2022-04-15 15:01:58 139671548569792 [Note] /usr/sbin/mysqld: ready for connections.
[--] 3) 2022-04-15 15:00:37 139816143530176 [Note] /usr/sbin/mysqld: ready for connections.
[--] 4) 2022-04-11 15:13:19 140028901791936 [Note] /usr/sbin/mysqld: ready for connections.
[--] 5) 2022-03-29  2:44:09 140256877758656 [Note] /usr/sbin/mysqld: ready for connections.
[--] 6) 2022-03-22  2:42:21 140486199806144 [Note] /usr/sbin/mysqld: ready for connections.
[--] 7) 2022-02-14  2:42:44 139938060060864 [Note] /usr/sbin/mysqld: ready for connections.
[--] 8) 2022-02-14  2:42:41 139960106510528 [Note] /usr/sbin/mysqld: ready for connections.
[--] 9) 2022-02-09  2:45:31 140350442416320 [Note] /usr/sbin/mysqld: ready for connections.
[--] 10) 2022-02-09  2:45:28 139988696365248 [Note] /usr/sbin/mysqld: ready for connections.
[--] 49 shutdown(s) detected in /var/log/mysqld.log
[--] 1) 2022-04-15 15:28:49 139670594152192 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 2) 2022-04-15 15:01:57 139815146489600 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 3) 2022-04-15 14:58:06 140027817096960 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 4) 2022-04-11 15:12:31 140255785293568 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 5) 2022-03-29  2:44:09 140485092288256 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 6) 2022-03-22  2:42:20 139937820497664 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 7) 2022-02-14  2:42:44 139959094945536 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 8) 2022-02-14  2:42:40 140349468514048 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 9) 2022-02-09  2:45:31 139987684747008 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 10) 2022-02-09  2:45:27 140367091803904 [Note] /usr/sbin/mysqld: Shutdown complete
-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +Aria +CSV +InnoDB +MEMORY +MRG_MyISAM +MyISAM +PERFORMANCE_SCHEMA +SEQUENCE
[--] Data in InnoDB tables: 3.9G (Tables: 718)
[OK] Total fragmented tables: 0
-------- Analysis Performance Metrics --------------------------------------------------------------
[--] innodb_stats_on_metadata: OFF
[OK] No stat updates during querying INFORMATION_SCHEMA.
-------- Views Metrics -----------------------------------------------------------------------------
-------- Triggers Metrics --------------------------------------------------------------------------
-------- Routines Metrics --------------------------------------------------------------------------
-------- Security Recommendations ------------------------------------------------------------------
[OK] There are no anonymous accounts for any database users
[OK] All database users have passwords assigned
[!!] There is no basic password file list!
-------- CVE Security Recommendations --------------------------------------------------------------
[--] Skipped due to --cvefile option undefined
-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 30m 9s (233K q [128.918 qps], 1K conn, TX: 1G, RX: 56M)
[--] Reads / Writes: 93% / 7%
[--] Binary logging is disabled
[--] Physical Memory     : 7.6G
[--] Max MySQL memory    : 45.1G
[--] Other process memory: 0B
[--] Total buffers: 416.0M global + 302.3M per thread (151 max threads)
[--] P_S Max memory usage: 93M
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 3.4G (45.17% of installed RAM)
[!!] Maximum possible memory usage: 45.1G (590.18% of installed RAM)
[!!] Overall possible memory usage with other process exceeded memory
[OK] Slow queries: 0% (33/233K)
[OK] Highest usage of available connections: 6% (10/151)
[OK] Aborted connections: 0.14%  (2/1476)
[!!] CPanel and Flex system skip-name-resolve should be on
[OK] Query cache is disabled by default due to mutex contention on multiprocessor machines.
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 40K sorts)
[!!] Joins performed without indexes: 559
[!!] Temporary tables created on disk: 87% (28K on disk / 32K total)
[OK] Thread cache hit rate: 99% (10 created / 1K connections)
[OK] Table cache hit rate: 99% (865 hits / 872 requests)
[OK] table_definition_cache(2097152) is upper than number of tables(984)
[OK] Open file limit used: 0% (38/40K)
[OK] Table locks acquired immediately: 100% (134 immediate / 134 locks)
-------- Performance schema ------------------------------------------------------------------------
[--] Performance_schema is activated.
[--] Memory used by P_S: 93.6M
[--] Sys schema is installed.
-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is disabled.
-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 18.3% (23.4M used / 128.0M cache)
[OK] Key buffer size / total MyISAM indexes: 128.0M/137.0K
[OK] Read Key buffer hit rate: 96.6% (716 cached / 24 reads)
-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[!!] InnoDB buffer pool / data size: 128.0M/3.9G
[OK] Ratio InnoDB log file size / InnoDB Buffer pool size: 14.0M * 2/128.0M should be equal to 25%
[OK] InnoDB buffer pool instances: 1
[--] Number of InnoDB Buffer Pool Chunk : 1 for 1 Buffer Pool Instance(s)
[OK] Innodb_buffer_pool_size aligned with Innodb_buffer_pool_chunk_size & Innodb_buffer_pool_instances
[OK] InnoDB Read buffer efficiency: 99.63% (202127202 hits/ 202873879 total)
[!!] InnoDB Write Log efficiency: 83.74% (66789 hits/ 79762 total)
[OK] InnoDB log waits: 0.00% (0 waits / 12973 writes)
-------- Aria Metrics ------------------------------------------------------------------------------
[--] Aria Storage Engine is enabled.
[OK] Aria pagecache size / total Aria indexes: 128.0M/0B
[OK] Aria pagecache hit rate: 98.7% (1M cached / 24K reads)
-------- TokuDB Metrics ----------------------------------------------------------------------------
[--] TokuDB is disabled.
-------- XtraDB Metrics ----------------------------------------------------------------------------
[--] XtraDB is disabled.
-------- Galera Metrics ----------------------------------------------------------------------------
[--] Galera is disabled.
-------- Replication Metrics -----------------------------------------------------------------------
[--] Galera Synchronous replication: NO
[--] No replication slave(s) for this server.
[--] Binlog format: MIXED
[--] XA support enabled: ON
[--] Semi synchronous replication Master: Not Activated
[--] Semi synchronous replication Slave: Not Activated
[--] This is a standalone server
-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
    You are using n unsupported version for production environments
    Upgrade as soon as possible to a supported version !
    Check warning line(s) in /var/log/mysqld.log file
    Check error line(s) in /var/log/mysqld.log file
    MySQL was started within the last 24 hours - recommendations may be inaccurate
    Reduce your overall MySQL memory footprint for system stability
    Dedicate this server to your database for highest performance.
    name resolution is enabled due to cPanel doesn't support this disabled.
    We will suggest raising the 'join_buffer_size' until JOINs not using indexes are found.
             See https://dev.mysql.com/doc/internals/en/join-buffer-size.html
             (specially the conclusions at the bottom of the page).
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries which have no LIMIT clause
Variables to adjust:
  *** MySQL's maximum memory usage is dangerously high ***
  *** Add RAM before increasing MySQL buffer variables ***
    skip-name-resolve=0
    join_buffer_size (> 40.0M, or always use indexes with JOINs)
    tmp_table_size (> 16M)
    max_heap_table_size (> 16M)
    key_buffer_size (~ 24M)
    innodb_buffer_pool_size (>= 3.9G) if possible.

I am really concerned by the "MySQL's maximum memory usage is dangerously high" message, because that means that the server can crash unexpectedly if MySQL tries to get more memory than RAM.

Here is my my.cnf :

[root@server ~]# cat /etc/my.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
[mysqld]
performance-schema=0

Remove leading # and set to the amount of RAM for the most important data
cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
innodb_buffer_pool_size = 128M

Remove leading # to turn on a very important data integrity option: logging
changes to the binary log between backups.
log_bin

Remove leading # to set options mainly useful for reporting servers.
The server defaults are faster for transactions and fast SELECTs.
Adjust sizes as needed, experiment to find the optimal values.
join_buffer_size = 40M
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
max_allowed_packet=16M
open_files_limit=40000
innodb_file_per_table=1
query_cache_size=0
query_cache_type=0
query_cache_limit=1M
tmp_table_size=16M
max_heap_table_size=16M
performance_schema=ON
innodb_log_file_size=14M
table_definition_cache=-1
slow_query_log = 1
slow-query_log_file = /var/log/mysql-slow.log
long_query_time = 2

I tried to change the join_buffer_size = 40M sort_buffer_size = 2M read_buffer_size = 2M read_rnd_buffer_size = 2M

but it seems to get worse. My initial my.cnf was :

[root@server ~]# cat /etc/my.cnf.backup
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
[mysqld]
performance-schema=0

Remove leading # and set to the amount of RAM for the most important data
cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
innodb_buffer_pool_size = 128M

Remove leading # to turn on a very important data integrity option: logging
changes to the binary log between backups.
log_bin

Remove leading # to set options mainly useful for reporting servers.
The server defaults are faster for transactions and fast SELECTs.
Adjust sizes as needed, experiment to find the optimal values.
join_buffer_size = 128M
sort_buffer_size = 2M
read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
innodb_buffer_pool_size=122683392
max_allowed_packet=268435456
open_files_limit=40000
innodb_file_per_table=1
query_cache_size=0
query_cache_type=0
query_cache_limit=1M
tmp_table_size=16M
max_heap_table_size=16M
performance_schema=ON
innodb_buffer_pool_size=260M
innodb_log_file_size=14M
table_definition_cache=-1
slow_query_log = 1
slow-query_log_file = /var/log/mysql-slow.log
long_query_time = 2

Any suggestion, or blatant error or misconfiguration that anyone could see ?

score 2 · Answer 1 · answered Apr 15 '22 at 17:59

This is an old default:

innodb_buffer_pool_size = 128M

Raise the setting to 5G for better performance. However, this is going in the opposite direction from your Question. Offhand, I don't see any settings that are "too large".

max_connections = 151 is OK. So, that tool must be complaining about "302.3M per thread".

"Temporary tables created on disk" (and other things) imply that some tables are poorly indexed and/or queries poorly written. You seem to have the slowlog turned on. Use pt-query-digest to discover which query is the "worst".

Be aware that their formula is imperfect. (There is no perfect formula.) Still, that tool found something. Let's dig deeper; provide the info asked for here: http://mysql.rjweb.org/doc.php/mysql_analysis#tuning

As for "crashing" due to not enough RAM, setting up "swap" will cause the database to slow down instead of crashing.

Bill Karwin · Answer 2 · 2022-04-15T18:34:29.027

I agree with Rick James' answer, but I want to state this even more directly:

The "maximum memory" warning from mysqltuner is rubbish. It is an impractical estimate, because it has almost zero chance of happening.

The memory usage they base their calculation on has to do with buffers that are allocated conditionally, depending on certain query behavior. Not all queries allocate all those buffers. Even if they do allocate the buffers, they may not allocate the full size of the buffer.

The chance that a given query will allocate all the possible buffers at their maximum size is remotely small.

In addition, even if you have max_connections=151, it's typical in a running system that not all connections are executing a query at the same time. Unless you have a very busy system with bad lock contention, I would expect the number of threads running a query at any given moment to be 10-20 at most.

I ignore the maximum memory warnings from automatic tuning tools like MySQL Tuner. The estimate of "maximum memory" given by MySQL Tuner has been many times the size of RAM on every database server I have administered.

So how should one estimate memory usage?

By observation.

Monitor the actual memory usage of your mysqld process by using tools like top or ps. Ideally you would have a continuous monitoring system to make graphs over time, so you could observe the trend.

This is much more accurate than relying on MySQL Tuner or any other estimate. Those estimates are not taking into account your database traffic or activity.

score 2 · Answer 3 · answered Apr 20 '22 at 22:21

Analysis of GLOBAL STATUS and VARIABLES:

Observations:

Version: 10.2.43-MariaDB-log
7.6 GB of RAM
Uptime = 00:49:57; Please rerun SHOW GLOBAL STATUS after several hours.
153 QPS

The More Important Issues:

Some specific changes:

table_definition_cache = 2000  -- it is entries, not bytes!
key_buffer_size = 40M  -- since MyISAM is not being used
innodb_buffer_pool_size = 4G  -- the primary use for RAM
max_allowed_packet = 50M

If running on SSD disk:

innodb_io_capacity = 1000
innodb_flush_neighbors = 0

There are hints of inefficient queries. If you are having a performance problem, please start a new Question with some specifics.

Details and other observations:

( Key_blocks_used * 1024 / key_buffer_size ) = 60 * 1024 / 128M = 0.05% -- Percent of key_buffer used. High-water-mark. -- Lower key_buffer_size (now 134217728) to avoid unnecessary memory usage.

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

( ( Key_reads + Key_writes + Innodb_pages_read + Innodb_pages_written + Innodb_dblwr_writes + Innodb_buffer_pool_pages_flushed ) / innodb_io_capacity / Uptime ) = ( 107 + 0 + 2402450 + 50096 + 2603 + 50049 ) / 200 / 2997 = 418.0% -- This may be a metric indicating what innodb_io_capacity is set reasonably. -- Increase innodb_io_capacity (now 200) if the hardware can handle it.

( innodb_buffer_pool_size ) = 128M -- InnoDB Data + Index cache -- 128M (an old default) is woefully small.

( innodb_buffer_pool_size ) = 128M / 8160437862.4 = 1.6% -- % of RAM used for InnoDB buffer_pool -- Set to about 70% of available RAM. (To low is less efficient; too high risks swapping.)

( innodb_lru_scan_depth ) = 1,024 -- innodb_lru_scan_depth is a very poorly named variable. A better name would be innodb_free_page_target_per_buffer_pool. It is a number of pages InnoDB tries to keep free in each buffer pool instance to speed up read and page creation operations. -- "InnoDB: page_cleaner: 1000ms intended loop took ..." may be fixed by lowering lru_scan_depth

( innodb_io_capacity ) = 200 -- When flushing, use this many IOPs. -- Reads could be slugghish or spiky. Use 2000 if using SSD drive.

( innodb_io_capacity_max / innodb_io_capacity ) = 2,000 / 200 = 10 -- Capacity: max/plain -- Recommend 2. Max should be about equal to the IOPs your I/O subsystem can handle. (If the drive type is unknown 2000/200 may be a reasonable pair.)

( Innodb_pages_written / Innodb_buffer_pool_write_requests ) = 50,096 / 253941 = 19.7% -- Write requests that had to hit disk -- Check innodb_buffer_pool_size (now 134217728)

( innodb_log_buffer_size / innodb_log_file_size ) = 16M / 14M = 114.3% -- Buffer is in RAM; file is on disk. -- The buffer_size should be smaller and/or the file_size should be larger.

( Innodb_os_log_written / (Uptime / 3600) / innodb_log_files_in_group / innodb_log_file_size ) = 63,871,488 / (2997 / 3600) / 2 / 14M = 2.61 -- Ratio -- (see minutes)

( Uptime / 60 * innodb_log_file_size / Innodb_os_log_written ) = 2,997 / 60 * 14M / 63871488 = 11.5 -- Minutes between InnoDB log rotations Beginning with 5.6.8, innodb_log_file_size can be changed dynamically; I don't know about MariaDB. Be sure to also change my.cnf -- (The recommendation of 60 minutes between rotations is somewhat arbitrary.) Adjust innodb_log_file_size (now 14680064). (Cannot change in AWS.)

( innodb_flush_method ) = innodb_flush_method = -- How InnoDB should ask the OS to write blocks. Suggest O_DIRECT or O_ALL_DIRECT (Percona) to avoid double buffering. (At least for Unix.) See chrischandler for caveat about O_ALL_DIRECT

( default_tmp_storage_engine ) = default_tmp_storage_engine =

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

( ( Innodb_pages_read + Innodb_pages_written ) / Uptime / innodb_io_capacity ) = ( 2402450 + 50096 ) / 2997 / 200 = 409.2% -- If > 100%, need more io_capacity. -- Increase innodb_io_capacity (now 200) if the drives can handle it.

( innodb_io_capacity ) = 200 -- I/O ops per second capable on disk . 100 for slow drives; 200 for spinning drives; 1000-2000 for SSDs; multiply by RAID factor. Limits write IO requests per second (IOPS). -- For starters: HDD: 200; SSD: 2000.

( innodb_adaptive_hash_index ) = innodb_adaptive_hash_index = ON -- Whether to use the adapative hash (AHI). -- ON for mostly readonly; OFF for DDL-heavy

( innodb_flush_log_at_trx_commit ) = 1 -- 1 = secure; 2 = faster -- (You decide) Use 1, along with sync_binlog (now 0)=1 for the greatest level of fault tolerance. 0 is best for speed. 2 is a compromise between 0 and 1.

( innodb_adaptive_hash_index ) = innodb_adaptive_hash_index = ON -- 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 (now 8) (MariaDB and Percona). ON has been implicated in rare crashes (bug 73890). 10.5.0 decided to default OFF.

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

( join_buffer_size * Max_used_connections ) = (40M * 46) / 8160437862.4 = 23.6% -- (A metric for pondering the size of join_buffer_size.) -- join_buffer_size (now 41943040) should probably be shrunk to avoid running out of RAM.

( max_allowed_packet ) = 256M / 8160437862.4 = 3.3% -- If you do not have large blobs (etc) to load, then decrease the value. Else decrease innodb_buffer_pool_size (now 134217728) to make room. Swapping is terrible for performance.

( innodb_ft_result_cache_limit ) = 2,000,000,000 / 8160437862.4 = 24.5% -- Byte limit on FULLTEXT resultset. (It grows as needed.) -- Lower the setting.

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

( Created_tmp_disk_tables / Questions ) = 42,340 / 457348 = 9.3% -- Pct of queries that needed on-disk tmp table. -- Better indexes / No blobs / etc.

( Created_tmp_disk_tables / Created_tmp_tables ) = 42,340 / 48168 = 87.9% -- Percent of temp tables that spilled to disk -- Maybe increase tmp_table_size (now 16777216) and max_heap_table_size (now 16777216); improve indexes; avoid blobs, etc.

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

( Uptime_since_flush_status ) = 2,997 = 49m 57s -- How long (in seconds) since FLUSH STATUS (or server startup). -- GLOBAL STATUS has not been gathered long enough to get reliable suggestions for many of the issues. Fix what you can, then come back in a several hours.

( Uptime ) = 2,997 = 49m 57s -- How long (in seconds) the server has been running. -- The system has not been up long enough to get reliable suggestions for many of the issues. Fix what you can, then come back with fresh values after the system has been running several hours.

( back_log ) = 80 -- (Autosized as of 5.6.6; based on max_connections) -- Raising to min(150, max_connections (now 151)) may help when doing lots of connections.

( thread_cache_size / Max_used_connections ) = 151 / 46 = 328.3% -- There is no advantage in having the thread cache bigger than your likely number of connections. Wasting space is the disadvantage.

( thread_pool_max_threads ) = 65,536 -- One of many settings for MariaDB's thread pooling -- Lower the value.

Abnormally small:

Handler_read_rnd_next / Handler_read_rnd = 0.436
Rows_read = 1.19e+8
innodb_log_block_size = 0
innodb_max_bitmap_file_size = 0
innodb_max_changed_pages = 0
innodb_mirrored_log_groups = 0
innodb_sched_priority_cleaner = 0
innodb_show_locks_held = 0

Abnormally large:

( Innodb_pages_read + Innodb_pages_written ) / Uptime = 818
Acl_database_grants = 686
Acl_users = 435
Tc_log_page_size = 4,096
innodb_adaptive_hash_index_partitions = 8
max_user_connections = 2.15e+9
table_definition_cache = 2.1e+6

Abnormal strings:

Slave_heartbeat_period = 0
Slave_received_heartbeats = 0
aria_recover_options = BACKUP,QUICK
innodb_corrupt_table_action = deprecated
innodb_fast_shutdown = 1
innodb_locking_fake_changes = OFF
innodb_use_global_flush_log_at_trx_commit = OFF
innodb_use_trim = ON
myisam_stats_method = NULLS_UNEQUAL
sql_slave_skip_counter = 0

MySQL's maximum memory usage is dangerously high

Remove leading # and set to the amount of RAM for the most important data

cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.

Remove leading # to turn on a very important data integrity option: logging

changes to the binary log between backups.

log_bin

Remove leading # to set options mainly useful for reporting servers.

The server defaults are faster for transactions and fast SELECTs.

Adjust sizes as needed, experiment to find the optimal values.

Disabling symbolic-links is recommended to prevent assorted security risks

pid-file=/var/run/mysqld/mysqld.pid

Remove leading # and set to the amount of RAM for the most important data

cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.

innodb_buffer_pool_size = 128M

Remove leading # to turn on a very important data integrity option: logging

changes to the binary log between backups.

log_bin

Remove leading # to set options mainly useful for reporting servers.

The server defaults are faster for transactions and fast SELECTs.

Adjust sizes as needed, experiment to find the optimal values.

join_buffer_size = 128M

sort_buffer_size = 2M

read_rnd_buffer_size = 2M

Disabling symbolic-links is recommended to prevent assorted security risks

pid-file=/var/run/mysqld/mysqld.pid

3 Answers3