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.
innodb_buffer_pool
to about 70% of RAM; leave the other settings at defaults.