1

I have a simple LAMP server on Amazon EC2. (find the specs towards the end of this message.) I used the following command in the terminal to see status of each query

watch --interval=2 "mysqladmin -u{username} -p{password} processlist"

I see roughly 4-5 queries running every now and then that result in 5-100 rows on an average (they're not very costly queries). In another console, I bring up the task manager using the "top" command. In it, I see mysqld using 150%+ CPU on an average time where the overall CPU usage is 80-90%. In the peak times, the overall CPU goes up to 100%. I've also realized the update takes a long timer sometimes. This server only uses MySQL.

The select queries that are running frequently are pulling on indexes. It's very strange to see the MySQL hogging up to more than 3/4th of the CPU to run just a few queries. I think I just don't have the correct configuration or need to fine tune MySQL configuration for a better performance. Anyone have any idea why this may be happening?

Server Specs:

  • Ubuntu 12.04
  • Intel Xeon E5-2670 v2 (Ivy Bridge) Processors
  • 7.5 GB RAM
  • SSD

MySQL Specs: (Ver 14.14 Distrib 5.5.37, for debian-linux-gnu (x86_64) using readline 6.2)

  • max_allowed_packet 64M
  • innodb_read_io_threads 4
  • innodb_write_io_threads 4

After a few modification: I modified the queries to ensure the where clause is using all index and it's using the correct type. In the execution plan or EXPLAIN statement, I see the following

Select_type: simple type: index_merge possible_keys: idx_tickets_city_code,index3,index6 key: index5,idx_tickets_city_code key_len: 4,4 rows:1612 extra: Using intersect(index3,idx_tickets_city_code);

Total result count is 142 rows and the CPU usage is still ~60% (a little high) for few instances of this query running every few seconds.

  • High CPU almost always means lack of a good index and/or poorly formulated query. "index merge" is a clue -- Have you heard of "composite" or "compound" indexes? INDEX(a,b) is not the same as INDEX(a), INDEX(b); the former is much better in certain situations. Please show us SHOW CREATE TABLE and the SELECT; it will be much easier to discuss specifics than make guesses. See my index cookbook . – Rick James Jun 28 '17 at 21:45
0

It is not a strange - in most of cases it just indicate, some of queries do not proper use indexes.

Possible reasons:

  • sort operations
  • group operations
  • type conversions (compare varchar with integer). In some cases MySQL could show You - it use index, but it will be high cpu usage (100%+ from single query)

Other possible reason - number of rows processed before send to client Query which return just 100 rows, could sort millions before calculate result.

If You already identify this 4-5 queries - just go thru each and check:

  • Execution plan
  • table structure (types) - check types for all columns used in WHERE and/or JOIN, types must be same
  • indexes

Add for answer for comment:

  • view inside view - definitely could be reason of high cpu usage
  • not always present index used by SQL, You can check it by simple command

    EXPLAIN SELECT ... there Your query

it will show You, what real indexes used or not

You can add result in Your question and we could check, how it work

  • I have gone through the queries that were sorting results each time and corrected so it doesn't sort as well. I did not know about the type conversions. Let me try to check if the where clause uses the correct type. I do have a view inside of a view that runs every 2-3 minutes. Do you think that's causing the spike? I also have the right indexes set. From my understanding, an index is what's going to be used in the where clause, correct? I really appreciate your response – Neel Patel Jun 15 '17 at 12:43
  • I will add notes for Your question – a_vlad Jun 15 '17 at 13:03
  • I modified the queries to ensure the where clause is using all index and it's using the correct type. In the execution plan or EXPLAIN statement, I see the following Select_type: simple type: index_merge possible_keys: idx_tickets_city_code,index3,index6 key: index5,idx_tickets_city_code key_len: 4,4 rows:1612 extra: Using intersect(index3,idx_tickets_city_code);Using Where Total result count is 142 rows – Neel Patel Jun 15 '17 at 13:59
  • What do you think is still causing the CPU to run high? – Neel Patel Jun 15 '17 at 14:07
0

Consider using the Percona configuration tool for MySQL here https://tools.percona.com/

They also have a query advisor tool.

Both require registration in order to use them.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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