Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

We have moved our server from old 8 GB RAM Server to new 16 GB RAM server so that we could have better performance.

The server is still consuming lot of MEMORY.

The tables in the database are not designed for InnoDB. The DB physical file size is approximately 2.8 GB.

my.cnf parameters are :

[client]
#password           = your_password
port                = 3306
socket              = /var/lib/mysql/mysql.sock

[mysqld]
port = 3306
socket = /var/lib/mysql/mysql.sock
skip-locking
#skip-bdb#niraj
skip-external-locking
key_buffer                  = 128M
max_length_for_sort_data    = 1024
max_tmp_tables              = 32M
table_cache                 = 64
max_allowed_packet          = 128M
sort_buffer_size            = 32M
read_buffer_size            = 10M
join_buffer_size            = 256M
read_rnd_buffer_size        = 64M 
myisam_sort_buffer_size     = 256M
thread_cache_size           = 64
query_cache_size            = 256M
thread_concurrency          = 8
max_connect_errors          = 100
log-bin=mysql-bin
server-id                            = 1
set-variable = max_connections       = 10000
set-variable = connect_timeout       = 280
set-variable = interactive_timeout   = 280
set-variable = net_read_timeout      = 300
innodb_buffer_pool_size              = 3G
innodb_additional_mem_pool_size      = 32M
innodb_log_file_size                 = 768M
innodb_log_buffer_size               = 16M
#innodb_flush_log_at_trx_commit      = 1
innodb_lock_wait_timeout             = 50

[mysqldump]
quick
max_allowed_packet          = 64M

[mysql]
no-auto-rehash

[isamchk]
key_buffer              = 64M
sort_buffer_size        = 256k 
read_buffer             = 256k
write_buffer            = 256k

[myisamchk]
key_buffer              = 64M
sort_buffer_size        = 256M
read_buffer             = 256k
write_buffer            = 256k

[mysqlhotcopy]
interactive-timeout

Please any one can validate my.cnf and suggest why taking much memory.

share|improve this question

closed as unclear what you're asking by Mark Storey-Smith, Max Vernon, StanleyJohns, Mat, dezso Sep 2 '13 at 5:27

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question.If this question can be reworded to fit the rules in the help center, please edit the question.

    
You might want to see this similar (possibly duplicate) question. –  James Jul 31 '13 at 15:10
    
You need to explain... much memory is "a lot?" Also, how many concurrent client connections to you typically have? –  Michael - sqlbot Jul 31 '13 at 21:47

1 Answer 1

Your max_connections variable is very high.

See the mysql manual:

The maximum number of connections MySQL can support depends on the quality of the thread library on a given platform, the amount of RAM available, how much RAM is used for each connection, the workload from each connection, and the desired response time. Linux or Solaris should be able to support at 500 to 1000 simultaneous connections routinely and as many as 10,000 connections if you have many gigabytes of RAM available and the workload from each is low or the response time target undemanding.

https://dev.mysql.com/doc/refman/5.5/en/too-many-connections.html

With 10,000, you are at the maximum number of connections, which is probably your problem, as the allowed memory usage for an individual connection then gets multiplied by 10,000.

You can decrease the number of connections needed by optimizing your queries. Frequently, query optimization ( run explain, make sure you have properly indexed your tables) solve issues with overload.

share|improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.