Every so often people ask me the question how should they estimate memory consumption by MySQL Server in given configuration. What is the formula they could use.
The reasons to worry about memory usage are quite understandable. If you configure MySQL Server so it uses too small amount of memory it will likey perform suboptimally. If you however configure it so it consumes too much memory it may be crashing , failing to execute queries or make operation to swap seriously slowing down. On now legacy 32bit platforms you could also run out of address space so that had to be watched as well.
Having said so, I do not think looking for the secret fomula to compute your possible memory usage is the right approach to this problem. The reasons are – this formula is very complex nowadays and what is even more important “theoretically possible” maximum it provides have nothing to do with real memory consumptions. In fact typical server with 8GB of memory will often run with maximum theoretical memory usage of 100GB or more. Furthermore there is no easy “overcommit factor” you can use – it really depends on application and configuration. Some applications will drive server to 10% of theoretical memory consumptions others only to 1%.
So what could you do instead ? First take a look at global buffers which are allocated at start and always where – these are key_buffer_size, innodb_buffer_pool_size, innodb_additional_memory_pool_size, innodb_log_buffer_size, query_cache_size. If you’re using MyISAM seriously you can also add the size of Operation System cache you would like MySQL to use for your table. Take this number add to it number of memory Operation System and other applications need, add might be 32MB more for MySQL Server code and various small static buffers. This is memory which you can consider used when you just start MySQL Server. The rest of memory is available for connections. For exampe with 8GB server you might have everything listed adding up to 6GB, so you have 2GB left for your threads.
Each thread connecting to MySQL server will needs its own buffers. About 256K is allocated at once even if thread is idle – they are used by default thread stack, net buffer etc. If transaction is started some more space can add up. Running small queries might only barely increase memory consumption for given thread, however if table will perform complex operations such as full table scans, sorts, or need temporary tables as much as read_buffer_size, sort_buffer_size, read_rnd_buffer_size, tmp_table_size of memory might be allocated. But they are only allocated upon the need and freed once given stage of query is done. Some of them are allocated as single chunk at once others, for example tmp_table_size is rather maximum amount of memory MySQL will allocate for this operation. Note it is more complicated than once may think – multiple buffers of the same type might be allocated for exampe to handle subqueries. For some special queries memory usage might be even larger – bulk inserts may allocate bulk_insert_buffer_size bytes of memory if done to MyISAM tables. myisam_sort_buffer_size used for ALTER TABLE, OPTIMIZE TABLE, REPAIR TABLE commands.
For OLTP applications with simple queries memory consumption is often less than 1MB per thread with default buffers, and you really do not need to increase per thread buffers unless you have complex queries. Sorting 10 rows will be as fast with 1MB sort buffer as with 16MB (actually 16MB might be even slower but it is other story).
Another approach you may take is to come up with amount of memory you want MySQL Server to consume at peak. This can be easily computed by memory needed for OS, File Cache and other applications. For 32bit envinronment you also should keep 32bit limits into account and probably limit “mysqld” size to about 2.5GB (exact number depens on a lot of factors). Now you can use “ps aux” to see VSZ – Virtual Memory allocated by MySQL process. You can also look at “Resident Memory” but I find it less helpful as it may down because of swapping – not what you would like to see. Monitor how the value changes so you know memory requirements with current settings and increase/decrease values appropriately.
Some may say, Hey we want to have 100% guarantee our server will never run out of memory, no matter which queries or users will decide to run. Unfortunately this is as much close to impossible to be impractical. Here is why:
List of rarely considered MySQL Server Memory Requirements
- Thread buffers can be allocated more than once for each thread. Consider for example subqueries – each layer may need its own read_buffer,sort_buffer, tmp_table_size etc
- Many variabes can be set per connection. So you can’t relay on global values if developers may use their local values to run some queries.
- There can be mutiple key caches. Multiple key caches can be created to accomodate query executions
- Query Parsing and optimization needs memory. This is usually small to be ignored but certain queries can have very large memory requrement for this step, especially specially crafted ones.
- Stored Procedures. Compex stored procedures may require a lot of memory
- Prepared statements and Cursors. Single connection may have many prepared statements and cursors. Their number finally can be limited but each of them still can have very large memory consumption
- Innodb Table Cache. Innodb has its own table cache in which meta data about each table accessed from the start is stored. It is never purged and may be large if you have a lot of tables. It also means user having CREATE TABLE privilege should be able to run MySQL server out of memory
- MyISAM buffers. MyISAM may allocate buffer which is large enough to contain largest record in the given table which is held until table is closed.
- Federated Storage Engine. This may have unbound memory requirements retriving result sets from remove queries.
- Blobs may require 3x time of memory. This is important if you’re deaing with large Blobs (your max_allowed_packet is large) Processing of 256MB of blob may require 768MB of memory.
- Storage Engines. In general storage engines may have their own per thread or global memory allocations which are not tuned as buffers. Watch for these especially now with many storage engines being released for MySQL by various parties.
I do not pretend this to be complete list. On the contrary I’m quite sure I’ve missed something (drop me a note if you have something to add). But the main point is – there are a lot of memory consumers out where and trying to find peak possible usage for each is impractical – so my advice would be measure what you get in practice and how memory consumption reacts to changing various variables. For example you may find out increasing sort_buffer_size from 1MB to 4MB and 1000 max_connections increases peak memory consumption just 30MB not 3000MB as you might have counted.
I want to add about InnoDB memory. InnoDB allocates additional memory for internal needs (adaptive hash index, etc) which you should take into account. My investigations show it is about
innodb_buffer_pool_size / 20 + ~20MB
“In fact typical server with 8GB of memory will often run with maximum theoretical memory usage of 100GB or more.”
Is it correct?
Yes that is correct.
Here is example – You could have such server running with max_connections=1000 and myisam_sort_buffer_size=256M.
If it happens so all connections will start to repair tables at the same time it will need 1000*256MB = 256GB of memory.
In practice however you would not have more than 2-3 connections doing it at the same time so you happen to be fine.
Hi.
We have a really fantastics dating site serving 8000 logged in users. However the service goes down too often to call it stable. We have a montoring tool that states that we often hit the “max_connections” threshold (650) connections.
Cpu is fine (about 100% of 400% available, MPM and dual core reports 4 processors).
We have 6Gb of physical memory available but since we run on 32 bit we cannot turn up the innodb_buffer_pool higher than 1.8G right? 2.4 kernel (cannot change to 2.6 because of internal politics). Tmp area is mounted on RAM disk.
We only have innodb in the app.
The application is designed in a way that if allowed we will have something like 1000+ db connections since every user clicks on a link, refreshes his/her browser every 10 secs or so. We cache alot but still we have db problems.
a. The thing that I really, really would like to know is howto track down where the bottleneck is… I’ve read Peter Z two guides but cannot really convert the gained knowledge to solve our situation.
Lesser questions but probably as well important.
b. The question really is how do you determine the memory consumption per connection in average?
This leads to the next question
c. Can we have a limit of 600 connections when every connection consumes X mb. If the answer to ‘b’ is 2mb then the total amount of memory would be something like 1800 + 2*600 = 3G right? To much to handle for 32 bit?
Some stats at writing time (18:37 friday) System copes fine with these figures.
Queries/sec: 265
Traffic: 1Mb/sec
Connections:259
‘Aborted_clients’, ‘267108’
‘Aborted_connects’, ‘25997’
‘Binlog_cache_disk_use’, ‘145’
‘Binlog_cache_use’, ‘9638599’
‘Bytes_received’, ‘2671219831’
‘Bytes_sent’, ‘2416986544’
‘Com_admin_commands’, ’76’
‘Com_alter_db’, ‘0’
‘Com_alter_table’, ‘7’
‘Com_analyze’, ‘0’
‘Com_backup_table’, ‘0’
‘Com_begin’, ‘0’
‘Com_change_db’, ‘155’
‘Com_change_master’, ‘0’
‘Com_check’, ‘0’
‘Com_checksum’, ‘0’
‘Com_commit’, ‘9558970’
‘Com_create_db’, ‘0’
‘Com_create_function’, ‘0’
‘Com_create_index’, ‘0’
‘Com_create_table’, ’31’
‘Com_dealloc_sql’, ‘0’
‘Com_delete’, ‘348788’
‘Com_delete_multi’, ‘1’
‘Com_do’, ‘0’
‘Com_drop_db’, ‘0’
‘Com_drop_function’, ‘0’
‘Com_drop_index’, ‘0’
‘Com_drop_table’, ‘0’
‘Com_drop_user’, ‘0’
‘Com_execute_sql’, ‘0’
‘Com_flush’, ‘108’
‘Com_grant’, ‘0’
‘Com_ha_close’, ‘0’
‘Com_ha_open’, ‘0’
‘Com_ha_read’, ‘0’
‘Com_help’, ‘0’
‘Com_insert’, ‘17889656’
‘Com_insert_select’, ’11’
‘Com_kill’, ’47’
‘Com_load’, ‘0’
‘Com_load_master_data’, ‘0’
‘Com_load_master_table’, ‘0’
‘Com_lock_tables’, ‘102’
‘Com_optimize’, ‘0’
‘Com_preload_keys’, ‘0’
‘Com_prepare_sql’, ‘0’
‘Com_purge’, ‘0’
‘Com_purge_before_date’, ‘0’
‘Com_rename_table’, ‘0’
‘Com_repair’, ‘0’
‘Com_replace’, ‘20801587’
‘Com_replace_select’, ‘0’
‘Com_reset’, ‘0’
‘Com_restore_table’, ‘0’
‘Com_revoke’, ‘0’
‘Com_revoke_all’, ‘0’
‘Com_rollback’, ‘4666272’
‘Com_savepoint’, ‘0’
‘Com_select’, ‘378247551’
‘Com_set_option’, ‘285487367’
‘Com_show_binlog_events’, ‘0’
‘Com_show_binlogs’, ‘0’
‘Com_show_charsets’, ‘2’
‘Com_show_collations’, ‘176788’
‘Com_show_column_types’, ‘0’
‘Com_show_create_db’, ‘8’
‘Com_show_create_table’, ‘2661’
‘Com_show_databases’, ’93’
‘Com_show_errors’, ‘0’
‘Com_show_fields’, ‘4260’
‘Com_show_grants’, ‘0’
‘Com_show_innodb_status’, ‘167091’
‘Com_show_keys’, ‘136’
‘Com_show_logs’, ‘0’
‘Com_show_master_status’, ‘105’
‘Com_show_ndb_status’, ‘0’
‘Com_show_new_master’, ‘0’
‘Com_show_open_tables’, ‘0’
‘Com_show_privileges’, ‘0’
‘Com_show_processlist’, ’20’
‘Com_show_slave_hosts’, ’61’
‘Com_show_slave_status’, ‘13834’
‘Com_show_status’, ‘194766’
‘Com_show_storage_engines’, ‘0’
‘Com_show_tables’, ‘2842’
‘Com_show_variables’, ‘176825’
‘Com_show_warnings’, ‘8’
‘Com_slave_start’, ‘0’
‘Com_slave_stop’, ‘0’
‘Com_stmt_close’, ‘304204263’
‘Com_stmt_execute’, ‘304205150’
‘Com_stmt_prepare’, ‘410680831’
‘Com_stmt_reset’, ‘0’
‘Com_stmt_send_long_data’, ‘0’
‘Com_truncate’, ‘0’
‘Com_unlock_tables’, ‘102’
‘Com_update’, ‘15146253’
‘Com_update_multi’, ‘0’
‘Connections’, ‘263413’
‘Created_tmp_disk_tables’, ‘0’
‘Created_tmp_files’, ‘4001’
‘Created_tmp_tables’, ‘669137’
‘Delayed_errors’, ‘0’
‘Delayed_insert_threads’, ‘0’
‘Delayed_writes’, ‘0’
‘Flush_commands’, ‘5’
‘Handler_commit’, ‘19092941’
‘Handler_delete’, ‘0’
‘Handler_discover’, ‘0’
‘Handler_read_first’, ‘1100773’
‘Handler_read_key’, ‘2491307870’
‘Handler_read_next’, ‘1266206297’
‘Handler_read_prev’, ‘0’
‘Handler_read_rnd’, ‘234159075’
‘Handler_read_rnd_next’, ‘2358572961’
‘Handler_rollback’, ‘4947643’
‘Handler_update’, ‘12524’
‘Handler_write’, ‘292923929’
‘Key_blocks_not_flushed’, ‘0’
‘Key_blocks_unused’, ‘28987’
‘Key_blocks_used’, ‘1332’
‘Key_read_requests’, ‘1552507’
‘Key_reads’, ‘1654’
‘Key_write_requests’, ‘114303’
‘Key_writes’, ‘1346’
‘Max_used_connections’, ‘651’
‘Not_flushed_delayed_rows’, ‘0’
‘Open_files’, ’95’
‘Open_streams’, ‘0’
‘Open_tables’, ‘512’
‘Opened_tables’, ‘183240’
‘Qcache_free_blocks’, ‘136’
‘Qcache_free_memory’, ‘10272736’
‘Qcache_hits’, ‘14774371’
‘Qcache_inserts’, ‘83902401’
‘Qcache_lowmem_prunes’, ‘4314’
‘Qcache_not_cached’, ‘294363493’
‘Qcache_queries_in_cache’, ‘177’
‘Qcache_total_blocks’, ‘498’
‘Questions’, ‘1462755007’
‘Rpl_status’, ‘NULL’
‘Select_full_join’, ‘0’
‘Select_full_range_join’, ‘0’
‘Select_range’, ‘5683551’
‘Select_range_check’, ‘0’
‘Select_scan’, ‘1104831’
‘Slave_open_temp_tables’, ‘0’
‘Slave_retried_transactions’, ‘0’
‘Slave_running’, ‘OFF’
‘Slow_launch_threads’, ‘0’
‘Slow_queries’, ‘27405’
‘Sort_merge_passes’, ‘376’
‘Sort_range’, ‘56479689’
‘Sort_rows’, ‘1344092495’
‘Sort_scan’, ‘935242’
‘Table_locks_immediate’, ‘895966639’
‘Table_locks_waited’, ‘20096’
‘Threads_cached’, ‘0’
‘Threads_connected’, ‘260’
‘Threads_created’, ‘124272’
‘Threads_running’, ‘3’
‘Uptime’, ‘4433556’
# top
18:38:40 up 175 days, 2:56, 1 user, load average: 0.79, 1.00, 0.95
328 processes: 326 sleeping, 2 running, 0 zombie, 0 stopped
CPU states: cpu user nice system irq softirq iowait idle
total 15.0% 0.0% 9.5% 0.0% 0.3% 6.5% 68.4%
cpu00 22.1% 0.0% 11.1% 0.1% 1.1% 6.1% 59.1%
cpu01 14.9% 0.0% 7.5% 0.0% 0.0% 5.9% 71.5%
cpu02 10.5% 0.0% 12.5% 0.0% 0.1% 7.1% 69.5%
cpu03 12.7% 0.0% 6.7% 0.0% 0.0% 6.7% 73.7%
Mem: 5907484k av, 5889348k used, 18136k free, 0k shrd, 34132k buff
4607808k actv, 882264k in_d, 96252k in_c
Swap: 4194224k av, 553604k used, 3640620k free 3352196k cached
#iostat 5 2
avg-cpu: %user %nice %sys %iowait %idle
20.49 0.00 6.29 20.80 52.41
Device: tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn
cciss/c1d0 39.88 218.38 89.96 3304016450 1360995328
cciss/c1d0p1 39.88 218.38 89.96 3304016194 1360995328
cciss/c1d1 66.15 227.50 255.83 3441930690 3870514728
cciss/c1d1p1 66.15 227.50 255.83 3441930434 3870514728
cciss/c0d0 0.86 3.52 11.26 53198730 170422784
cciss/c0d0p1 0.27 0.48 4.77 7251314 72197656
cciss/c0d0p2 0.01 0.06 0.10 858968 1573160
cciss/c0d0p3 0.00 0.00 0.00 168 0
cciss/c0d0p5 0.12 0.11 1.14 1650506 17248664
cciss/c0d0p6 0.27 1.95 2.77 29569906 41860440
cciss/c0d0p7 0.11 0.38 2.16 5719090 32723824
cciss/c0d0p8 0.08 0.54 0.32 8148218 4819040
cciss/c0d0p9 0.00 0.00 0.00 248 0
# vmstat 5 2
procs memory swap io system cpu
r b swpd free buff cache si so bi bo in cs us sy id wa
2 2 553604 18116 37400 3349360 0 0 0 0 0 0 1 0 1 1
0 0 553604 18548 37316 3349456 0 0 1210 143 2476 3809 19 1 70 10
mysql conf:
[mysqld]
server-id=5908
port=3309
socket=/tmp/mysql.sock
pid-file=/var/run/mysql/mysql.pid
datadir=/db/disk2/mysql/data
log-bin=/var/log/mysql/log
log-slow-queries=/var/log/mysql/slow-queries.log
max_connections=650
open_files_limit=6144
max_connect_errors=10000
interactive_timeout=3600
wait_timeout=3600
key_buffer_size=32M # Only for MyISAM which isnt used.
sort_buffer_size=2M
query_cache_size=10M # Might be increased
thread_cache_size=40
table_cache=512 # Default 64 was used before.
skip-locking
innodb_data_home_dir=/db/disk2/data
innodb_data_file_path=ibdata1:10240M;ibdata2:10240M;ibdata3:10240M;ibdata4:10240M;ibdata5:10M:autoextend
innodb_log_group_home_dir=/var/log/mysql
innodb_log_arch_dir=/var/log/mysql
innodb_table_locks=0
innodb_buffer_pool_size=1800M # USE ALL MEMORY AVAILABLE
#innodb_log_buffer_size=8M # Lowered from 32M according to MySQL
innodb_additional_mem_pool_size=20M
innodb_file_io_threads=4
innodb_lock_wait_timeout=15 # CHANGED from 50
innodb_log_files_in_group=3
innodb_flush_log_at_trx_commit=0
#innodb_log_file_size=400M # Approx 20% of buffer pool
innodb_thread_concurrency=64 # Default is 8 wich is a way to low.
Puhhhhh!
That was alot of text. Could someone please help us out a bit!
Regards
//Marcus Herou
Marcus,
I will reply you, providing as much of free help as I can (I guess you know we also provide commercial consulting services) but please could you post your message to forums instead:
http://forum.mysqlperformanceblog.com/
Thank you.
ok well i have problems can you help me with that?
Is “MyISAM buffers” stated in the “List of rarely considered MySQL Server Memory Requirements” from the key buffer?
If so, would explain more about the difference?
Thank you,
What’s the difference between innodb table cache and Memory pool?
Hello,
I’m running MySQL 5.1.33 for Solaris 8. The insert speed is about 2300 rows/sec at 240Kb/s. Is it the normal speed or par below the standard? I’m quite a newbie hence the question.
Hi,
Are there any easy to implement rules as to what paramaters to set based on server memory size and number/types of databases and tables?
Hello,
I have a 64GB server, innodb buffer pool set to 40GB or less, use innodb_flush_method = O_DIRECT, 2X8GB innodb log, and has 30000 connections with 20 thread pool worker threads. It ran out of free memory and got heavy swap at the same time OS cache is 12GB. What’s the possible reason the redhat linux 5 can’t use the 12GB cache, but use swap instead?
Thanks.
Liwen,
How much VSZ do you get for MySQL in this case? When you have 30000 connections there can be significant amount of memory allocated per connection, which can also be tuned by adjusting various per thread buffers
Hi Peter,
Could you plz say that which configuration for mysql is the best for a server with 16GB memory, while we want to mysql not uses more than 12GB of all!
Hi Mr. Peter,
I have problem with mysql server.
here of my server detail :
DELL R710, Memory :16GB, OS : Ubuntu server 11.04.
I use Mysql server version 5.5. some table using engine MYISAM and other is INNODB. till now use for both of that type is running well. but when many user acces my web, this server become very very slow.
i have about 400 user active.
here of my.cnf setting :
[client]
port = 3306
socket = /var/run/mysqld/mysqld.sock
[mysqld]
user = mysql
port = 3306
socket = /var/run/mysqld/mysqld.sock
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
skip-locking
key_buffer = 384M
max_allowed_packet = 1M
table_cache = 512
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size = 32M
thread_concurrency = 8
log-bin=mysql-bin
#innodb_buffer_pool_size = 11468M
#innodb_log_file_size = 2867M
#innodb_log_buffer_size = 8M
#innodb_flush_log_at_trx_commit = 1
#innodb_thread_concurrency = 8
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
[isamchk]
key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M
[myisamchk]
key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout
#——————————————————————————————–
please give me your solution…
thanks,
andy sinaga
hi peter,
I would like to run MySQL5.5.14 with InnoDB ONLY, plus PHP 5.2, Apache 2.2 and Memcached on the SAME box.
The specs are:
Operating System: Debian GNU/Linux 5.0 Lenny/Stable (64 bit)
Dual Processor Quad Core Xeon 2 x 4MB cache (2.0Ghz)
2 X 250GB SATA II HDD
12 GB DDR3 RAM
My question is: how much RAM should i give to MySQL? 4GB? 6GB? 3GB? Please advise.
Many thanks in advance….
Moses, please use the forums and don’t double-comment on more blog posts.
Thanks, was very useful!
Hi Peter,
We are having a problem with CPU high usage, its is going up to 98%. We have a DELL server with 2 – 4 cores CPUs means total 8 cores and 32 GB RAM. We are using Percona 5.5 and our most of tables are innodb, size of database 3 GB:
Please let me know if we are having any wrong value or we can do some settings
Here is mysql configuration (We are having only 1 mysql server, no replication setup)
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
server-id=1
#replicate-same-server-id=0
log-bin=newcrmdb1-bin
#binlog_do_db=crmdb
binlog_do_db=newcrmdb
binlog-ignore-db=mysql,test
#replicate_do_db=crmdb
#replicate_do_db=newcrmdb
#auto-increment-increment = 3
#auto-increment-offset = 1
#log-slave-updates
skip_name_resolve
#master-host = 192.168.0.203
#master-user = replication
#master-password = rplpassword
#master-port = 3306
#innodb_flush_log_at_trx_commit=0
sync_binlog=0
slave-skip-errors = 1062
back_log = 75
# skip-innodb
max_connections = 600
#thread_stack= 150M
key_buffer = 256M
key_buffer_size = 256M
myisam_sort_buffer_size = 16M
join_buffer_size = 64M
read_buffer_size = 16M
sort_buffer_size = 8M
table_cache = 3600
table_definition_cache = 4096
thread_cache_size = 16K
wait_timeout = 120
connect_timeout = 60
tmp_table_size = 192M
max_heap_table_size = 192M
max_allowed_packet = 64M
#max_connect_errors = 10000
read_rnd_buffer_size = 8M
bulk_insert_buffer_size = 8M
query_cache_limit = 254M
query_cache_size = 254M
query_cache_type = 1
query_prealloc_size = 65536
query_alloc_block_size = 131072
default-storage-engine = InnoDB
#thread_concurrency = 32
innodb_buffer_pool_size = 4096M
innodb_lock_wait_timeout = 120
innodb_use_sys_malloc = 0
long_query_time = 8
#log-slow-queries = /var/log/mysql-slow-query.log
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
#old_passwords=1
# Disabling symbolic-links is recommended to prevent assorted security risks;
# to do so, uncomment this line:
# symbolic-links=0
So, how much RAM is needed for a database with a table of 1 billion records ?
I am trying to optimise mysql to use as less memory as possible. following is my configuration
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
symbolic-links=0
bind-address=127.0.0.1
#script
thread_concurrency=2
query_cache_size = 32M
query_cache_type=1
query_cache_limit=5M
# MyISAM #
#key-buffer-size = 32M
#myisam-recover = FORCE,BACKUP
# SAFETY #
#max-allowed-packet = 16M
#max-connect-errors = 1000000
# CACHES AND LIMITS #
tmp-table-size = 32M
max-heap-table-size = 32M
#query-cache-type = 0
#query-cache-size = 0
max-connections = 50
thread-cache-size = 16
#open-files-limit = 65535
#table-definition-cache = 1024
#table-open-cache = 2048
sort_buffer_size=4M
join_buffer_size=4M
# INNODB #
innodb-flush-method = O_DIRECT
#innodb-log-files-in-group = 2
#innodb-log-file-size = 5M
#innodb-flush-log-at-trx-commit = 1
innodb-file-per-table = 1
innodb-buffer-pool-size = 1256M
# LOGGING #
log-error = /var/log/mysqld.log
log-queries-not-using-indexes = 1
slow-query-log = 1
slow-query-log-file = /var/log/mysqld-slow.log
I am using centos 6 64bit with 4gb ram. My apache prefork settings is as follows
StartServers 20
MinSpareServers 20
MaxSpareServers 20
ServerLimit 58
MaxClients 58
MaxRequestsPerChild 1000
I have allocated memcache with MAXCONN=”256″ CACHESIZE=”256″.
Some cacti graphs over last 3 months
http://i.imgur.com/0DAjVFw.png
http://i.imgur.com/35pGvFc.png
http://i.imgur.com/7I4vMhR.png
http://i.imgur.com/yBe4Geg.png
Problem i have is OS runs out of memory and kills mysql. I have noticed recently mysql using all connections whcih does not make sense as the site is not busy. It gets used more often during lunch times, which is when apache consumes avg over 100mb per process.
Hi sharif, thanks for the question! However, Peter wrote this post 9 years ago so I doubt he’ll see it to respond. The best place to ask this is on our MySQL discussion forums here: https://www.percona.com/forums/
my server have 12 gb of ram and 20 % ie 2.4 GB allocated for mysql.
i would like to know how much max_connections can i setup.
If i set up huge number like 5000 what will happen?