how to fix slow query of mysql on Amazon and CPU 100% on the small rds instance

mysql amazon-web-services rds

The second query's EXPLAIN plan give it away. You need an index.

The clause WHERE familyName='Thompson' AND firstName='David' is the clue.

Simply run the following:

ALTER TABLE iApps ADD INDEX familyName_firstName_ndx (familyName,firstName);

I can assure you this will speed up this particular query. Other queries that have different WHERE clauses will require different indexes.

As for the CPU spike, that should not be a surprise. A full table scan on a 1 million row table will push data in and out of the InnoDB Buffer Pool. Keep in mind that the Transaction Logs in Amazon RDS are always the same size (innodb_log_file_size = 128M) for all seven RDS models. Transacitons that are passing through the same rows you are selecting would also have bearing on CPU usage as it manipulate the Transaction Logs as well.

BonjoBananas

Updated on September 18, 2022

Comments

BonjoBananas over 1 year
I am trying to create a function that reads a file and I don't understand what I am doing wrong. I am getting the error SyntaxError: Unexpected token )
```
function WordCount(){}

WordCount.prototype.readfile = function(file) {
  fs.readFile(file, 'utf8', function (err, data)) {
    if (err) throw err;
  }
}
module.exports = WordCount;
```
Test:
```
describe('wordCount',function(){

  var wordCount;

  beforeEach(function(){
    wordcount = new WordCount
  });

describe("When trying to read a file", function() {
  it('will not throw an error when reading the file', function() {
    expect(wordCount.readFile(reader)).not.toThrow(err);
  });
  it('will throw an error if a file is missing', function() {
    expect(wordCount.readFile()).toThrow(err);
  });
 });
});
```
- ceejayoz over 11 years
  
  "1m" means what? 1 megabyte? 1 million rows? What happens if you EXPLAIN the query?
- David Thompson over 11 years
  
  @ceejayoz sorry about that, it's 1 million. I mean writing and reading for the query.
- Mike over 11 years
  
  first you need to post the query and then put explain in front of the query and post that
- Michael Hampton over 11 years
  
  Indexes. You need indexes.
- David Thompson over 11 years
  
  @MichaelHampton what do you mean by indexes? Sorry, I am really a newbie to the database
- David Thompson over 11 years
  
  @Mike I post one query with EXPLAIN here, the time seems quite small, does it mean the server is ok?
- Mike over 11 years
  
  what are you trying to do with the query?
- David Thompson over 11 years
  
  @MichaelHampton I have added another query, which is executed in my python code.
- Mike over 11 years
  
  this is really off topic.. its not a server problem.. you just don't know sql at all.
- David Thompson over 11 years
  
  @Mike Sorry, the first one is just an example I could come up then. The second one is what I really use in my code
- Mike over 11 years
  
  Go learn about full text searches or indexes in mysql
- David Thompson over 11 years
  
  @Mike I don't really know anything about mysql, could you please point out any good resource for me to learn sql? Thanks a lot.
- Michael Hampton over 11 years
  
  You really need indexes.
- edkeveked over 6 years
  
  You have a syntax error: fs.readFile(file, 'utf8', function (err, data){ your function here instead})
David Thompson over 11 years

Thanks a lot for answering. Can you also explain why the cpu of RDS instance is 100% consumed?
ceejayoz about 11 years

Because every time you look for a single row, it's having to look at all one million rows. If you had indexes on the right columns, it'd only have to look at a few dozen or hundred.
David Thompson about 11 years

@ceejayoz thanks for explaining this with plain English.