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 !!!
Best Answer
I show some tools I know.
pg_activity is a simple monitoring tool like top.
pg_statsinfo and pg_stats_reporter are high quality utilities that monitor statistics and the activity of PostgreSQL as well as Operating system.
There are some general purpose monitoring tools such as Cacti and Munin. Those support postgres by providing modules and they can easily be used.
BTW, I know a great book: The Art of Computer Systems Performance Analysis. This gives you some hints for benchmarking.