1

I have a server with 128GB RAM but the CPU always show more than 100% (sometimes >1000%) in top command for mysqld process Here is part of configuration for innodb in my.cnf Please advise how do I fine tune the configurations. Thanks

   innodb_buffer_pool_size         = 60G   #global buffer
   innodb_additional_mem_pool_size = 4M    #global buffer
   innodb_status_file                      #extra reporting
   innodb_file_per_table                   #enable always
   innodb_flush_log_at_trx_commit  = 0     #2/0 = perf, 1 = ACID
   innodb_table_locks              = 0     #preserve table locks
   innodb_log_buffer_size          = 256M  #global buffer
   innodb_lock_wait_timeout        = 60
   innodb_thread_concurrency       = 16    #recommend 2x core quantity
   innodb_commit_concurrency       = 16    #recommend 4x num disks
   innodb_support_xa               = 0     #recommend 0, disable xa to negate extra disk flush
   # skip-innodb-doublewrite

   #innodb_force_recovery       = 3
Caterpillar
  • 1,132
  • 2
  • 23
  • 47
user1199249
  • 11
  • 1
  • 3

1 Answers1

3

First thing first: a CPU usage > 100% on a Linux system does not mean there is a problem, only that the system is using more than one CPU. A 16-core system can have a maximum load of 1600%, so a 1000% load on such a system would mean is quite loaded, but definitely not overloaded.

Is the server actually performing poorly, or are you simply concerned about what seems to be a high CPU usage, but might be perfectly fine for a dedicated DB server?

That said, having lots and lots of RAM isn't going to do any help if the server is under heavy CPU load; RAM only helps with caching contents and reducing disk I/O (which in most cases is a bottleneck), not with actual processing.

You should have a look at what your server is actually doing, in terms of user load and actual queries, and then optimize your DB (as in tables, keys and indexes) as much as possible for the job it needs to do; or, if all else fails, throw more CPU cores at it and/or find a way to spread the load across mutiple servers.

Without knowing anything at all about what your server is doing it's impossible to tell you more than this.

Massimo
  • 70,200
  • 57
  • 200
  • 323
  • Hi I would want to optimised my innodb databases which always have slow querries. Can someone tell me the way to do so? – user1199249 Mar 02 '12 at 02:57
  • Not with so little information. Anyway, if you have (specific) DB optimization needs, it would be better to ask on http://dba.stackexchange.com. – Massimo Mar 02 '12 at 09:19