6

I'm studying to be a DBA right now because I have a massive live database that is very sensitive that I manage. Here's the current system stats of my dedicated database server:

  • CentOS 5.9
  • 24gb Ram
  • 8 Core CPU
  • 50gb FusionIO disk for /var/lib/mysql
  • MySQL 5.5.23

    top - 18:40:27 up 14 days, 4:43, 1 user, load average: 19.72, 22.62, 24.04

    Tasks: 183 total, 3 running, 180 sleeping, 0 stopped, 0 zombie

    Cpu(s): 69.9%us, 0.4%sy, 0.0%ni, 29.2%id, 0.0%wa, 0.1%hi, 0.4%si, 0.0%st

    Mem: 24685224k total, 20172096k used, 4513128k free, 343420k buffers

    Swap: 2007284k total, 0k used, 2007284k free, 729004k cached

    PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND

    5446 mysql 15 0 18.4g 17g 6176 R 765.8 76.2 114437:38 mysqld

I'm currently using 43gb of that 50gb FusionIO slice. MySQL averages around 700 QPS and 75-90% CPU usage. Here's my my.cnf file:

[mysqld]
user=mysql
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

#innodb
#innodb_log_file_size = 256M
#innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 2
innodb_lock_wait_timeout=50
innodb_file_per_table
innodb_buffer_pool_size=16G
innodb_buffer_pool_instances=4
#eliminating double buffering
innodb_flush_method = O_DIRECT
flush_time=86400

skip-name-resolve
query_cache_limit=4M
query_cache_size=256M
sort_buffer_size=8M
read_rnd_buffer_size=1M
max_connections=5000
interactive_timeout=60
wait_timeout=300
connect_timeout=30
thread_cache_size=32
key_buffer=124M
tmp_table_size=4096M
max_heap_table_size=256M
join_buffer=16M
max_connect_errors=2000
table_cache=2048
thread_concurrency=12
long_query_time=5
log-slow-queries=/var/log/mysql-slow.log
#table_definition_cache=384
max_allowed_packet=1024M
#server-id=20
#log-bin=mysql-bin
#expire_logs_days=10

event_scheduler=ON


[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

can you guys recommend any config changes I should make to MySQL or CentOS that can greatly reduce my CPU usage? Or for that matter any other resource improvements?

1
  • All of my queries are very optimized and all are using all of the indexes possible. I've spent about 3 months going through every single query and optimizing them and comparing all of the EXPLAIN outputs. The fact of the matter is that I do several million requests per hour on this server and mysql is using too much CPU. Maybe it's not using too much, but I'm just concerned that a constant 75-90% is way too much.
    – Logan Best
    Jun 20, 2013 at 3:11

4 Answers 4

10

You need to unleash two things

I have this discussed this over the years. Please see some of my earlier posts on getting InnoDB to engage more CPU/Core activity.

Give it a Try !!!

UPDATE 2013-06-20 02:39 EDT

Your last comment

Hey Rolando, I added read_io and write_io at 8 and read through your other posts but it doesn't seem to be bringing down the CPU usage. I even dropped the 4 buffer pool instances down to 1 in hopes that it would have better performance, but still no go. Do you have any more suggestions that I could implement that will help drop CPU usage?

I would suggest raising innodb_buffer_pool_instances to 8 to match the number of cores. The MySQL Documentation suggests 1G per buffer pool instance. Then, you would raise to 16 (since you have innodb_buffer_pool_size at 16G). Try 8 first, then 16.

Even with this, I have some rather distressing news: Here is a Press Release From FusionIO from 2 years ago. Paragraph 2 says:

The StatSoft white paper concluded that Fusion ioDrives significantly increased the I/O performance in the STATISTICA suite of analytics software products and solutions, greatly increasing CPU utilization and efficiency. With ioMemory, StatSoft achieved 300 and 500 percent data performance and latency reduction improvements when compared to legacy disk-based storage. With the increased I/O performance enabled by the Fusion ioDrives, CPU utilization increased to 90 percent in tests of large data sets, versus the 32 percent CPU utilization observed with the disk-based technology.

If you were using RAID 10 SAS or even SSDs for /var/lib/mysql, InnoDB would have its day with the CPUs. However, from the looks of this Press Release, InnoDB is competing with FusionIO for CPU Utilization.

SUGGESTION

Find out what the IOPs are for the FusionIO disk and set innodb_io_capacity to that number. The default value for innodb_io_capacity is 200. If the IOPs are greater than 200, then please set it. If the IOPs are in the 1000's, then InnoDB has a chance to be on a level playing field with the FusionIO.

Give it a Try !!!

5
  • Thank you Rolando. I'll check through these immediately and see what I can come up with.
    – Logan Best
    Jun 20, 2013 at 3:12
  • Hey Rolando, I added read_io and write_io at 8 and read through your other posts but it doesn't seem to be bringing down the CPU usage. I even dropped the 4 buffer pool instances down to 1 in hopes that it would have better performance, but still no go. Do you have any more suggestions that I could implement that will help drop CPU usage?
    – Logan Best
    Jun 20, 2013 at 6:11
  • I updated my answer... Jun 20, 2013 at 6:39
  • @Logan : can you tell what helped you to optimize cpu usage as m facing same as well. Jul 24, 2017 at 11:12
  • In regards to the FusionIO press release, it sounds less like InnoDB is competing with FusionIO and more like the FusionIO device is so much faster that even MySQL becomes CPU-bound rather than I/O bound.
    – Dan Udey
    May 28, 2018 at 18:44
4

The first thing I'd check is what queries are running at those busy times - it may be a query design problem rather than a hardware or database configuration one. For instance without the right indexes or with queries written so the query planner can't use them you may find a lot of time is being spent scanning table or index structures in memory which would chew CPU resource.

2
  • All of my queries are very optimized and all are using all of the indexes possible. I've spent about 3 months going through every single query and optimizing them and comparing all of the EXPLAIN outputs. The fact of the matter is that I do several million requests per hour on this server and mysql is using too much CPU. Maybe it's not using too much, but I'm just concerned that a constant 75-90% is way too much.
    – Logan Best
    Jun 20, 2013 at 3:11
  • Of course it may just be that you need more CPU power, and may have to try scale the database over more than one machine. Jun 20, 2013 at 15:18
0

I have found that if tables get too large the server load can go up dramatically and CPU usage skyrocket.

-1

I had the same issue. Some of the tables have MyISAM while others have InnoDB. Just fixing this we are lower the CPU usage from 98% to 26%. We will try to optimise some of the SQL. Hopefully, this will bring more joy.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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