4

Below is my .cnf file and if I see my CPU consumption 99.99% of it my the mysqld commands. The MySQL server is connected from remote machines that update data in it frequently, but I make sure that the remote server's open a connection, read/write/update and then close it. Also the remote server reads a lot.

What can I do to reduce my cpu consumption. FYI, I am using 2 core CPU with 4GB RAM.

[client] port = 3306 socket = /var/run/mysqld/mysqld.sock [mysqld_safe] socket = /var/run/mysqld/mysqld.sock nice = 0 [mysqld] user = mysqluser pid-file = /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock port = 3306 basedir = /usr datadir = /var/lib/mysql tmpdir = /tmp lc-messages-dir = /usr/share/mysql skip-external-locking wait_timeout = 20 interactive_timeout = 60 bind-address = <IP-ADDRESS> key_buffer = 16M max_allowed_packet = 16M thread_stack = 192K thread_cache_size = 8 myisam-recover = BACKUP max_connections = 300 query_cache_limit = 20M query_cache_size = 128M log_error = /var/log/mysql/error.log log_slow_queries = /var/log/mysql/mysql-slow.log long_query_time = 4 log-queries-not-using-indexes expire_logs_days = 10 max_binlog_size = 100M [mysqldump] quick quote-names max_allowed_packet = 16M [mysql] [isamchk] key_buffer = 16M

Also pasting output from mysqltuner.

 >> MySQLTuner 1.4.0 - Major Hayden <[email protected]> >> Bug reports, feature requests, and downloads at http://mysqltuner.com/ >> Run with '--help' for additional options and output filtering [OK] Logged in using credentials from debian maintenance account. [OK] Currently running supported MySQL version 5.5.38-0ubuntu0.14.04.1-log [OK] Operating on 64-bit architecture -------- Storage Engine Statistics ------------------------------------------- [--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM [--] Data in InnoDB tables: 2G (Tables: 26) [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17) [!!] Total fragmented tables: 26 -------- Performance Metrics ------------------------------------------------- [--] Up for: 21m 51s (37K q [28.525 qps], 31K conn, TX: 6M, RX: 7M) [--] Reads / Writes: 97% / 3% [--] Total buffers: 304.0M global + 2.7M per thread (5000 max threads) [!!] Maximum possible memory usage: 13.4G (347% of installed RAM) [!!] Slow queries: 18% (6K/37K) [OK] Highest usage of available connections: 0% (16/5000) [OK] Key buffer size / total MyISAM indexes: 16.0M/100.0K [OK] Query cache efficiency: 20.2% (7K cached / 36K selects) [OK] Query cache prunes per day: 0 [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 6K sorts) [OK] Temporary tables created on disk: 25% (54 on disk / 215 total) [OK] Thread cache hit rate: 99% (16 created / 31K connections) [OK] Table cache hit rate: 25% (74 open / 289 opened) [OK] Open file limit used: 0% (49/25K) [OK] Table locks acquired immediately: 100% (29K immediate / 29K locks) [!!] InnoDB buffer pool / data size: 128.0M/2.9G [OK] InnoDB log waits: 0 -------- Recommendations ----------------------------------------------------- General recommendations: Run OPTIMIZE TABLE to defragment tables for better performance MySQL started within last 24 hours - recommendations may be inaccurate Reduce your overall MySQL memory footprint for system stability Variables to adjust: *** MySQL's maximum memory usage is dangerously high *** *** Add RAM before increasing MySQL buffer variables *** innodb_buffer_pool_size (>= 2G)

I wasn't getting this issue at all, but when I started connecting like 10 remote servers to this MySQL server and when those ten servers started doing SELECT and UPDATE queries on this single MySQL server, I see this rise in CPU consumption.

Improve this question
1

4 Answers 4

Reset to default
2

I suspect that you have several issues here that are stacking up and causing problems.

  1. Disk i/o is fragmented, and probably inefficient. OPTIMIZE TABLE on each of your tables should sort that out and help a bit.
  2. Memory is tuned horribly, likely causing issues with having to swap frequently.
  3. Slow queries - this is the big suspect for your high CPU usage. I'd check your slow log and see what you can optimize by adding indexes/reorganizing queries/etc. There's probably a fairly common query thats doing tens or hundreds of thousands of comparisons that are killing your performance.

If none of those bump up performance to what you want, it may be time to scale up your database server. 2 cores doesn't tell me anything about the processing power of the machine, and 4 GB of ram is pretty tiny these days.

For more specific advice, please add more detail to your description. SSDs or rotating disks? What CPU? What class of ram? How many queries/min? How many clients, etc?

Improve this answer
8
  • I use a 4GB Ram 20GB SSD Disk Ubuntu 14.04 Commented Apr 21, 2015 at 22:31
  • I have the slow queries log inspected and the maximum query time for a SELECT query was 0.4 for 311416 rows examined. Commented Apr 21, 2015 at 22:34
  • When you say "2. Memory is tuned horribly... " What do you mean by that? How can I overcome this problem? Commented Apr 21, 2015 at 22:35
  • Well, the mysqltuner script is telling you. Your maximum memory usage is 3.4x how much ram you have, 2.7 megs per thread is really not that much, and you have a 128 meg innodb buffer pool for almost 3 gigs of data. Commented Apr 21, 2015 at 22:38
  • So one solution would be to increase the buffer pool by increasing query_cache_size from 128MB to 1GB? ?? Commented Apr 21, 2015 at 22:41
1

If you are able to play around with settings a little, I would try this in my.cnf:

innodb_buffer_pool_size = 3000M

then restart mysql and check your performance. This would allow you to keep much more of your database in memory, reducing the disk/memory thrashing that you may be seeing. Given that you have 4Gb memory, and assuming this server is ONLY a db server, you can increase the buffer pool size up to about 80% of the system memory.

Improve this answer
7
  • Doesn't do anything! :(. I have 4GB ram and I have some ML engine running therefore I made innodb_buffer_pool_size = 1000M instead Commented Apr 22, 2015 at 3:36
  • What is an ML engine? You might be running too many different apps on your instance, and you should definitely look into optimizing the tables, and getting more resources for this database. It sounds like you could actually use something closer to 16Gb for this to behave properly. Commented Apr 22, 2015 at 3:42
  • Machine Learning python engine that crunches lots of data on the MySQL database that resides on the same server... Basically I am bootstrapping my startup and I have everything at one place. Commented Apr 22, 2015 at 3:55
  • Then you can probably expect continued poor performance until you can throw more resources at the problem :) Commented Apr 22, 2015 at 4:05
  • Didn't know running SELECT and UPDATE queries from multiple servers on a signle MySQL database can literally eat up my resources Commented Apr 22, 2015 at 4:13
0

What happens: you have try to put an elefant into the hamster's ball.

What to do: decrease the memory footprint for mysql

. . . . . . . . . . [mysqld] user = mysqluser pid-file = /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock log_error = /var/log/mysql/error.log log_slow_queries = /var/log/mysql/mysql-slow.log long_query_time = 10 log-queries-not-using-indexes expire_logs_days = 10 max_binlog_size = 100M port = 3306 basedir = /usr datadir = /var/lib/mysql tmpdir = /tmp lc-messages-dir = /usr/share/mysql skip-external-locking wait_timeout = 20 interactive_timeout = 60 bind-address = <IP-ADDRESS> myisam-recover = BACKUP ## REASONABLE MEMORY PARAMETERS max_connections = 64 thread_cache_size = 2 thread_concurrency = 4 key_buffer_size = 64M join_buffer_size = 2M sort_buffer_size = 2M read_buffer_size = 2M read_rnd_buffer_size = 8M myisam_sort_buffer_size = 16M query_cache_limit = 2M query_cache_size = 32M table_open_cache = 512 max_allowed_packet = 1M . . . . . . . . . .
Improve this answer
0

You should analyze your most frequent queries with EXPLAIN command. The output will tell you how your queries use table indices, which directly affects the performance.

Improve this answer
1
  • Slow queries are all queries that lasts for more then 4 seconds (by config) despite of reason. When server is configured properly, most probable reason for slow query is the lack of index or some other query inefficiency. Here the reason is the massive system swapping/paging because of badly configured mysql Commented Apr 23, 2015 at 9:27

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.