AWS RDS performance is very very low

Question

There are two databases, AWS RDS and Digitalocean $ 5 VPS. I imported the same .sql file to both databases, the performance difference between them is almost 66000%

select count(special_cargo_id) from special_cargos;

AWS RDS elapsed time = 35.681seconds

DO 5$ VPS on Mysql elapsed time = 0.086seconds

Details:

Table Lenght

1.85GB Rows ~260k

MySQL Version 8

Table Engine InnoDB

RDS OUTPUT

CREATE TABLE `special_cargos` (
  `special_cargo_id` int(11) NOT NULL AUTO_INCREMENT,
  `barcode` varchar(20) DEFAULT '',
  `salesCode` varchar(15) NOT NULL,
  `price` varchar(15) NOT NULL,
  `para` varchar(3) DEFAULT NULL,
  `postaceki` varchar(25) DEFAULT '',
  `iban` varchar(30) DEFAULT NULL,
  `send_type` tinyint(1) unsigned NOT NULL,
  `customer_id` int(11) NOT NULL,
  `address_id` int(11) NOT NULL,
  `height` varchar(5) DEFAULT '',
  `weight` varchar(5) DEFAULT '',
  `width` varchar(5) NOT NULL DEFAULT '',
  `desi` varchar(5) DEFAULT '',
  `length` varchar(255) DEFAULT '',
  `customer_firstname` varchar(32) NOT NULL,
  `customer_lastname` varchar(32) NOT NULL,
  `customer_company` varchar(40) NOT NULL,
  `customer_address` varchar(255) NOT NULL,
  `customer_city` int(11) NOT NULL DEFAULT '0',
  `customer_city_name` varchar(55) DEFAULT '',
  `customer_zone_id` int(11) NOT NULL DEFAULT '0',
  `customer_zone_name` varchar(55) DEFAULT '',
  `customer_postcode` varchar(10) NOT NULL,
  `firstname` varchar(32) NOT NULL,
  `lastname` varchar(32) NOT NULL,
  `company` varchar(40) DEFAULT '',
  `address` varchar(255) NOT NULL,
  `country_id` int(5) DEFAULT NULL,
  `country_name` varchar(64) DEFAULT NULL,
  `city_id` int(11) NOT NULL,
  `city_name` varchar(55) NOT NULL,
  `district_id` int(11) NOT NULL,
  `district_name` varchar(64) NOT NULL,
  `email` varchar(96) NOT NULL,
  `phone` varchar(55) NOT NULL,
  `postcode` varchar(10) DEFAULT '',
  `product` text,
  `sendText` text,
  `returnText` text,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  `deleted_at` datetime DEFAULT NULL,
  `cargo_firm` tinyint(1) NOT NULL DEFAULT '0',
  `ups_barcode_link` text,
  `status` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`special_cargo_id`),
  KEY `customer_id` (`customer_id`),
  KEY `special_cargo_id` (`special_cargo_id`),
  KEY `barcode` (`barcode`),
  KEY `salesCode` (`salesCode`),
  KEY `price` (`price`),
  KEY `postaceki` (`postaceki`),
  KEY `customer_firstname` (`customer_firstname`),
  KEY `customer_lastname` (`customer_lastname`),
  KEY `firstname` (`firstname`),
  KEY `lastname` (`lastname`),
  KEY `customer_company` (`customer_company`),
  KEY `customer_address` (`customer_address`),
  KEY `customer_city_name` (`customer_city_name`),
  KEY `customer_zone_name` (`customer_zone_name`),
  KEY `company` (`company`),
  KEY `address` (`address`),
  KEY `city_name` (`city_name`),
  KEY `district_name` (`district_name`),
  KEY `email` (`email`),
  KEY `phone` (`phone`),
  KEY `postcode` (`postcode`),
  KEY `index_special_cargos` (`special_cargo_id`)
) ENGINE=InnoDB AUTO_INCREMENT=497539 DEFAULT CHARSET=utf8

+-----------------------+----------+
|Variable_name          |Value     |
+-----------------------+----------+
|innodb_buffer_pool_size|1073741824|
+-----------------------+----------+

+--+-----------+--------------+----------+-----+-------------+-----------+-------+----+------+--------+-----------+
|id|select_type|table         |partitions|type |possible_keys|key        |key_len|ref |rows  |filtered|Extra      |
+--+-----------+--------------+----------+-----+-------------+-----------+-------+----+------+--------+-----------+
|1 |SIMPLE     |special_cargos|NULL      |index|NULL         |customer_id|4      |NULL|146973|100     |Using index|
+--+-----------+--------------+----------+-----+-------------+-----------+-------+----+------+--------+-----------+

SIMPLE VPS ON MYSQL OUTPUT

CREATE TABLE `special_cargos` (
  `special_cargo_id` int(11) NOT NULL AUTO_INCREMENT,
  `barcode` varchar(20) NOT NULL,
  `salesCode` varchar(15) NOT NULL,
  `price` varchar(15) NOT NULL,
  `para` varchar(3) DEFAULT NULL,
  `postaceki` varchar(25) NOT NULL,
  `iban` varchar(30) DEFAULT NULL,
  `send_type` tinyint(1) unsigned NOT NULL,
  `customer_id` int(11) NOT NULL,
  `address_id` int(11) NOT NULL,
  `height` varchar(5) NOT NULL,
  `weight` varchar(5) NOT NULL,
  `width` varchar(5) NOT NULL,
  `desi` varchar(5) NOT NULL,
  `length` varchar(255) NOT NULL,
  `customer_firstname` varchar(32) NOT NULL,
  `customer_lastname` varchar(32) NOT NULL,
  `customer_company` varchar(40) NOT NULL,
  `customer_address` varchar(255) NOT NULL,
  `customer_city` int(11) NOT NULL,
  `customer_city_name` varchar(55) NOT NULL,
  `customer_zone_id` int(11) NOT NULL,
  `customer_zone_name` varchar(55) NOT NULL,
  `customer_postcode` varchar(10) NOT NULL,
  `firstname` varchar(32) NOT NULL,
  `lastname` varchar(32) NOT NULL,
  `company` varchar(40) NOT NULL,
  `address` varchar(255) NOT NULL,
  `country_id` int(5) DEFAULT NULL,
  `country_name` varchar(64) DEFAULT NULL,
  `city_id` int(11) NOT NULL,
  `city_name` varchar(55) NOT NULL,
  `district_id` int(11) NOT NULL,
  `district_name` varchar(64) NOT NULL,
  `email` varchar(96) NOT NULL,
  `phone` varchar(55) NOT NULL,
  `postcode` varchar(10) NOT NULL,
  `product` text,
  `sendText` text NOT NULL,
  `returnText` text NOT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  `deleted_at` datetime DEFAULT NULL,
  `cargo_firm` int(1) NOT NULL DEFAULT '0',
  `ups_barcode_link` text,
  `status` int(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`special_cargo_id`),
  KEY `customer_id` (`customer_id`),
  KEY `special_cargo_id` (`special_cargo_id`),
  KEY `barcode` (`barcode`),
  KEY `salesCode` (`salesCode`),
  KEY `price` (`price`),
  KEY `postaceki` (`postaceki`),
  KEY `customer_firstname` (`customer_firstname`),
  KEY `customer_lastname` (`customer_lastname`),
  KEY `firstname` (`firstname`),
  KEY `lastname` (`lastname`),
  KEY `customer_company` (`customer_company`),
  KEY `customer_address` (`customer_address`),
  KEY `customer_city_name` (`customer_city_name`),
  KEY `customer_zone_name` (`customer_zone_name`),
  KEY `company` (`company`),
  KEY `address` (`address`),
  KEY `city_name` (`city_name`),
  KEY `district_name` (`district_name`),
  KEY `email` (`email`),
  KEY `phone` (`phone`),
  KEY `postcode` (`postcode`)
) ENGINE=InnoDB AUTO_INCREMENT=497037 DEFAULT CHARSET=utf8

+-----------------------+---------+
|Variable_name          |Value    |
+-----------------------+---------+
|innodb_buffer_pool_size|134217728|
+-----------------------+---------+

+--+-----------+--------------+----------+-----+-------------+-----------+-------+----+------+--------+-----------+
|id|select_type|table         |partitions|type |possible_keys|key        |key_len|ref |rows  |filtered|Extra      |
+--+-----------+--------------+----------+-----+-------------+-----------+-------+----+------+--------+-----------+
|1 |SIMPLE     |special_cargos|NULL      |index|NULL         |customer_id|4      |NULL|133967|100     |Using index|
+--+-----------+--------------+----------+-----+-------------+-----------+-------+----+------+--------+-----------+

Hi, both of them have 2GB of RAM and 2vCPU, I don't think there is a difference of 60000% difference between them — Hakan, Jun 01 '20 at 15:18
Did you perhaps forget to create an index on the RDS database? — Mark B, Jun 01 '20 at 15:21
hi @mokugo-devops I get close results on both db.t2.small and db.t3.medium — Hakan, Jun 01 '20 at 15:30
Hi, can you check the BurstBalance value when you was doing the import? — Chris Williams, Jun 01 '20 at 15:35

Gordan Bobić · Answer 1 · 2020-06-01T17:10:40.683

Can you provide the output of:

SHOW CREATE TABLE special_cargos;
SHOW GLOBAL VARIABLES LIKE 'innodb_buffer_pool_size';
EXPLAIN select count(special_cargo_id) from special_cargos;

on both RDS and your non-RDS instance?

Assuming for a moment that special_cargo_id is the primary key, and it fits into RAM, and innodb_buffer_pool_size is configured reasonably (on a server with 2GB of RAM, 1GB is a reasonable amount, though RDS should come pre-configured to a sane value), it should be running similarly on both nodes.

Two possible explanations come to mind:

1) On RDS you don't have special_cargo_id defined as the PK.

2) RDS is for some reason choosing to not use the index (comparison of EXPLAIN output will confirm whether this is happening).

Edit:

Actually - are you sure your $5 VPS is using InnoDB? If you are getting an answer back in 0.08s, that sounds like it isn't actually scanning even the index. There is a good chance that on the VPS you are using MyISAM, which updates the number of rows in the table header after every write, specifically to make SELECT COUNT(*) FROM table_name; queries return instantaneously - like you have observed.

Edit 2:

Wow - so VPS is configured with only 128MB of innodb_buffer_pool_size and it is STILL going faster than RDS configured with 1GB of innodb_buffer_pool_size, and the execution plan on both is the same?

OK, try this:

select count(1) from special_cargos FORCE INDEX (PRIMARY);

Just did. My mind is a little blown. I knew RDS was slow, but this is beyond slow, this is just something being outright broken somewhere... — Gordan Bobić, Jun 01 '20 at 17:08
select count(1) from special_cargos FORCE INDEX (PRIMARY); this also came out in the same time — Hakan, Jun 01 '20 at 17:27
Is "select count(*) from special_cargos FORCE INDEX (index_special_cargos);" any faster? — Christian, Jun 01 '20 at 17:38
`PRIMARY` will be slower than letting the Optmizer pick the "smallest" index. — Rick James, Jun 05 '20 at 05:49

score 1 · Answer 2 · answered Jun 01 '20 at 16:28

1

There are two aspects of the problem here. Aspect one and more important IMO is the query - it's bad. It does full index scan (yes, PRIMARY is also an index). Its performance scales linearly vs better and desired the B+tree's nlog(n).

Aspect two. Why the difference in the execution time. It can be related to caching (in DO's case the query was served from the buffer pool while in RDS from disk(=EBS)). It can be related to MySQL optimizer (DO and RDS could pick different indexes to serve the query PRIMARY vs smaller secondary index). But all that doesn't matter much due to the problems with the query itself.

answered Jun 01 '20 at 16:28

akuzminsky

2,190
15
21

The query is served from the disk, data set is bigger than the BP. I would compare the EBS volume on RDS and what do they have in a VM on DO? – akuzminsky Jun 02 '20 at 13:21
Also, you said RDS instance has 2G of RAM while the buffer pool is 1G. Does the RDS used swap a lot? – akuzminsky Jun 02 '20 at 13:22
RDS => 1000IOPS Reserved 300GB SSD, DO VPS => 20GB General SSD :) – Hakan Jun 03 '20 at 09:55
How many IOPS does DO's SDD give? Besides, individual IO response time matters a lot for MySQL. EBS is a network device after all. It will lose vs local SSD. On EC2 instances EBS response time is about 2ms while the local volume (some instance types have local storage) delivers ~ 0.2 ms. 10 times less! – akuzminsky Jun 03 '20 at 16:18
Anyway, I wouldn't bother much about the underlying hardware as long as queries are not optimal. – akuzminsky Jun 03 '20 at 16:19
What you say is confusing -- "linear" is faster than "n*log(n)". – Rick James Jun 05 '20 at 06:01
Since the PK is in each secondary index, it will use the "smallest" index. The `EXPLAIN` shows that it is using customer_id, which might be only 7MB in size. The size of the PK is effectively the size of the data -- it's the same BTree – Rick James Jun 05 '20 at 06:04
> "linear" is faster than "n*log(n)" good point. I always thought otherwise. – akuzminsky Jun 15 '20 at 22:27

score 0 · Answer 3 · answered Jun 05 '20 at 05:52

0

1GB for the buffer_pool is dangerously high when you have only 2GB of RAM. Probably that system was swapping furiously.

Lower it to 200M and see how fast it runs.

See if they have some way of monitoring swap usage.

answered Jun 05 '20 at 05:52

Rick James

135,179
13
127
222

AWS RDS performance is very very low

3 Answers3