Amazon Webservice上的mySQL RDS实例性能 [英] mySQL RDS instance performance on Amazon Webservice

问题描述

我是Amazon Web Services的新手，并且已经设置了mySQL RDS实例.

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

我当前正在使用db.t2.micro服务器，并建立了一个循环，该循环将1000条记录插入数据库.从代码中我们可以看到，此操作大约需要16秒钟.

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.

如果我打算降低速度以在1秒内完成1000次插入，那么我需要考虑改变哪些规格才能实现这一目标?

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;
?>

谢谢！

推荐答案

如果您使用试用版t2.micro并预留20GB的存储空间，则会遇到一些问题

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

1. 最大IOPS = 20GB x 3 = 60 IOPS，对于1k记录，1000/60 = 16.6秒 因此，您要获得1000 iops，可以利用每GB 3 IOPS的gp2优势，分配330GB = 333 x 3 = 999 IOPS.此后，您将面临数据传输限制.不过，您可以编写并调用 MYSQL存储过程以模拟RDS中的原始插入，以证明IO性能.

1. 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仍会使用一些CPU. t2.*实例类型只是为您提供了仅持续30分钟的连续突发处理，之后它将限制在CPU的25％以下.如果您有那么多交易，请尝试使用c3/c4/m3/m4等.

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.

尽管有关 Amazon RDS的存储，其他因素可能会起作用，例如如果Web服务器和RDS不在同一位置，则它们之间的网络延迟.

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.

因此，对RDS进行基准测试的最佳方法是将基准存储过程放入MYSQL RDS中，并按以下

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);

(更新):

基于事务的应用程序无法进行批量插入.重要的是要使用各种 MySQL插入性能技术.不幸的是，诸如LOAD DATA INFILE之类的技术不适用于RDS.

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.

这篇关于Amazon Webservice上的mySQL RDS实例性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！