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|
+--+-----------+--------------+----------+-----+-------------+-----------+-------+----+------+--------+-----------+

Answer 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.

Answer 2

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

Answer 3

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.