mySQL RDS instance performance on Amazon Webservice

Question

I am new to amazon web services and have set up a mySQL RDS instance.

I am currently using the db.t2.micro server and have set up a loop which inserts 1000 records into the database. As we can see from the code this operation takes around 16 seconds to complete.

If I were aiming to be getting the speed down to have the 1000 inserts completed in 1 second, what specs would I need to be looking at changing to achieve this?

<?php

$time = time();

// connection crednetials

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
} 

$i = 0;
while($i < 1000){
$sql = "INSERT INTO test (value)
VALUES ('$i')";

if ($conn->query($sql) === TRUE) {
   // echo "New record created successfully";
} else {
    echo "Error: " . $sql . "<br>" . $conn->error;
}

$i++;
}
$conn->close();

$time2 = time();

$diff = $time2 - $time;

echo "Difference: ".$diff;
?>

thanks!

Possible duplicate of [AWS increase iops](http://stackoverflow.com/questions/43497576/aws-increase-iops) — fire, Apr 19 '17 at 16:05

score 1 · Accepted Answer · edited May 23 '17 at 11:46

If you use the trial t2.micro and set aside 20GB of storage, you will face a few issue

Maximum IOPS = 20GB x 3 = 60 IOPS, for 1k records, 1000/60 = 16.6 seconds So you want to get 1000 iops, you can take advantages of gp2 per GB 3 IOPS, allocate 330GB = 333 x 3 = 999 IOPS. After this point, you will face data transfer limitation. Nevertheless, you can write and call a MYSQL stored procedure to simulate the raw insert within the RDS to proof the IO performance.
Mysql still use some CPU even for simple record insertion. t2.* instance type just give you continuous burst processing that only last 30 mins, afterwards, it will throttle below 25% of CPU. If you have that much transaction, try use c3/c4/m3/m4 etc.

Though AWS document about Storage for Amazon RDS, other factor might kick in , e.g. network latency between your web server and RDS if they are not sitting in the same place.

So the best way to benchmark RDS is to put the benchmark stored procedure inside MYSQL RDS and call them as shown in this question answer. There is zero network latency to satisfied your raw benchmark need.

Here is the code that copy from question mentioned

DELIMITER $$
CREATE PROCEDURE InsertRand(IN NumRows INT, IN MinVal INT, IN MaxVal INT)
    BEGIN
        DECLARE i INT;
        SET i = 1;
        START TRANSACTION;
        WHILE i <= NumRows DO
            INSERT INTO rand_numbers VALUES (MinVal + CEIL(RAND() * (MaxVal - MinVal)));
            SET i = i + 1;
        END WHILE;
        COMMIT;
    END$$
DELIMITER ;

CALL InsertRand(1111, 2222, 5555);

(update) :

Batch insert is not possible for transaction base application. It is important to optimize with various MySQL Insert Performance technique. Unfortunately, technique such as LOAD DATA INFILE is not applicable to RDS.

Hi Mootmoot, Thanks for your response. I have increased the server to a db.t2.medium. I have allocated 300GB SSD storage and yet still I am still getting a much lower iops than expected. Why could this be? https://ibb.co/hUj0vk — MattBlack, Apr 20 '17 at 08:27
@MattBlack From the screenshot, it seems the write stuck at 100 IOPS and the queue depth is pretty low. Did you run your php program from your local machine or inside EC2 that share the same VPC with the RDS? — mootmoot, Apr 20 '17 at 09:03
Hi, Im running the script from a EC2 instance and yes it shares the same VPC. Its wierd, its like its being capped at 100 iops and I cannot figure out why — MattBlack, Apr 20 '17 at 13:13
@MattBlack : what happens if you use the stored procedure code? — mootmoot, Apr 20 '17 at 16:31

score 0 · Answer 2 · answered Apr 19 '17 at 16:46

There are a few approaches I'd recommend:

1 - Can you batch up the inserts? There is overhead in that execution, so if you can do 50, 100 at a time you will massively decrease the overhead and thus reduce the run time.

2 - If that isnt giving benefit, I would look at increasing the IOPs of your server.

But I strongly recommend using (1) as the solution.

mySQL RDS instance performance on Amazon Webservice

2 Answers2

Linked