# AWS EC2 with MySQL not using anywhere near the resources available **URL:** \ **Category:** MySQL & MariaDB\ **Tags:** mysql\ **Created:** [July 26, 2021, 2:08am UTC](https://forums.percona.com/t/aws-ec2-with-mysql-not-using-anywhere-near-the-resources-available/11513 "2021-07-26T02:08:48Z")\ **Posts on this page:** 10\ **Page:** 1
**Author:** ![Thomi77](https://avatars.discourse-cdn.com/v4/letter/t/e56c9b/32.png) [@Thomi77](https://forums.percona.com/u/Thomi77)\ **Post date:** [July 26, 2021, 2:08am UTC](https://forums.percona.com/t/aws-ec2-with-mysql-not-using-anywhere-near-the-resources-available/11513/1 "2021-07-26T02:08:48Z")
I have an EC2 (r5.2Xlarge) with MySql 8.0.22 installed and have 2 schemas with each around 1.3 TB. Tables contain between 300 million and 2 billion rows. For disks I use GP3 with 16,000 IOPS and 1000 MB/s. So this is quite good I would assume. **All tables are InnoDB.** Now I run a query where i copy 830,000,000 rows from table 1 to table 2 that takes around 5 days to complete. This is just unbelievebably slow. query that i run **insert into table2 (id,value\_id) Select id,value\_id from table1;** This query starts off with 8000 inserts per second but then drops to an average of 2500 to 3000 after couple of minutes. I can see on AWS performance monitoring that the cpu sits at around 5% and IO for read at around 20 and write at around 4000. write performance is hovering at around 100mb/s (see pic below). this for me is a sign that I am not using the hardware to its maximum as neither disks nor CPU is at capacity and one would assume in such a data heavy query as above, the disk should run at its maximum. Can somebody have a look at the extract below and maybe see some values that are off the charts? I have spent a long time googling but the messages out there are very contradicting and I am afraid to change settings if the research has not given me a clear understanding of what it does. extracts from innodb status: ```auto ----------------- BACKGROUND THREAD ----------------- srv_master_thread loops: 139616 srv_active, 0 srv_shutdown, 16834 srv_idle srv_master_thread log flush and writes: 0 ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 3380537 OS WAIT ARRAY INFO: signal count 5555711 RW-shared spins 969285, rounds 1446597, OS waits 477320 RW-excl spins 1510270, rounds 24745577, OS waits 740601 RW-sx spins 2014, rounds 59331, OS waits 1966 Spin rounds per wait: 1.49 RW-shared, 16.38 RW-excl, 29.46 RW-sx -------- FILE I/O -------- I/O thread 0 state: waiting for completed aio requests (insert buffer thread) I/O thread 1 state: waiting for completed aio requests (log thread) I/O thread 2 state: waiting for completed aio requests (read thread) I/O thread 3 state: waiting for completed aio requests (read thread) I/O thread 4 state: waiting for completed aio requests (read thread) I/O thread 5 state: waiting for completed aio requests (read thread) I/O thread 6 state: complete io for buf page (write thread) I/O thread 7 state: complete io for buf page (write thread) I/O thread 8 state: complete io for buf page (write thread) I/O thread 9 state: complete io for buf page (write thread) Pending normal aio reads: [0, 0, 0, 0] , aio writes: [4, 0, 0, 0] , ibuf aio reads:, log i/o's:, sync i/o's: Pending flushes (fsync) log: 0; buffer pool: 293064 12836396 OS file reads, 705785528 OS file writes, 251455455 OS fsyncs 1 pending preads, 1 pending pwrites 6085.06 reads/s, 16383 avg bytes/read, 4331.99 writes/s, 1156.48 fsyncs/s ------------------------------------- INSERT BUFFER AND ADAPTIVE HASH INDEX ------------------------------------- Ibuf: size 133, free list len 957047, seg size 957181, 145190 merges merged operations: insert 422868, delete mark 0, delete 0 discarded operations: insert 0, delete mark 0, delete 0 Hash table size 13546877, node heap has 208 buffer(s) Hash table size 13546877, node heap has 618 buffer(s) Hash table size 13546877, node heap has 1 buffer(s) Hash table size 13546877, node heap has 1 buffer(s) Hash table size 13546877, node heap has 1 buffer(s) Hash table size 13546877, node heap has 1 buffer(s) Hash table size 13546877, node heap has 1 buffer(s) Hash table size 13546877, node heap has 773 buffer(s) 3259.31 hash searches/s, 3153.10 non-hash searches/s --- LOG --- Log sequence number 10458893569770 Log buffer assigned up to 10458893569770 Log buffer completed up to 10458893569770 Log written up to 10458893569381 Log flushed up to 10458893152041 Added dirty pages up to 10458893569770 Pages flushed up to 10458813503591 Last checkpoint at 10458813500688 34272162 log i/o's done, 881.10 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total large memory allocated 56044290048 Dictionary memory allocated 891577 Buffer pool size 3342336 Free buffers 6583 Database pages 3316993 Old database pages 1224525 Modified db pages 294704 Pending reads 0 Pending writes: LRU 0, flush list 28, single page 0 Pages made young 91169767, not young 2409531454 0.00 youngs/s, 0.01 non-youngs/s Pages read 12621539, created 5829939, written 536293398 0.00 reads/s, 0.00 creates/s, 0.00 writes/s Buffer pool hit rate 987 / 1000, young-making rate 1 / 1000 not 499 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 3316993, unzip_LRU len: 0 I/O sum[1425272]:cur[21320], unzip sum[0]:cur[0] ---------------------- INDIVIDUAL BUFFER POOL INFO ---------------------- ---BUFFER POOL 0 Buffer pool size 417792 Free buffers 953 Database pages 414480 Old database pages 153021 Modified db pages 36907 Pending reads 0 Pending writes: LRU 0, flush list 6, single page 0 Pages made young 11324843, not young 305126547 0.00 youngs/s, 0.00 non-youngs/s Pages read 1576101, created 729712, written 66959369 0.00 reads/s, 0.00 creates/s, 0.00 writes/s Buffer pool hit rate 988 / 1000, young-making rate 1 / 1000 not 493 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 414480, unzip_LRU len: 0 I/O sum[178159]:cur[2665], unzip sum[0]:cur[0] ---BUFFER POOL 1 Buffer pool size 417792 Free buffers 946 Database pages 414488 Old database pages 153019 Modified db pages 36836 Pending reads 0 Pending writes: LRU 0, flush list 10, single page 0 Pages made young 11180965, not young 302072441 0.00 youngs/s, 0.00 non-youngs/s Pages read 1568195, created 728393, written 66790908 0.00 reads/s, 0.00 creates/s, 0.00 writes/s Buffer pool hit rate 986 / 1000, young-making rate 1 / 1000 not 516 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 414488, unzip_LRU len: 0 I/O sum[178159]:cur[2665], unzip sum[0]:cur[0] ---BUFFER POOL 2 Buffer pool size 417792 Free buffers 1017 Database pages 414457 Old database pages 152972 Modified db pages 36608 Pending reads 0 Pending writes: LRU 0, flush list 6, single page 0 Pages made young 11651343, not young 296801601 0.00 youngs/s, 0.00 non-youngs/s Pages read 1580150, created 729267, written 67338866 0.00 reads/s, 0.00 creates/s, 0.00 writes/s Buffer pool hit rate 986 / 1000, young-making rate 1 / 1000 not 509 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 414457, unzip_LRU len: 0 I/O sum[178159]:cur[2665], unzip sum[0]:cur[0] ---BUFFER POOL 3 Buffer pool size 417792 Free buffers 946 Database pages 414513 Old database pages 153026 Modified db pages 37064 Pending reads 0 Pending writes: LRU 0, flush list 6, single page 0 Pages made young 11549266, not young 300684452 0.00 youngs/s, 0.00 non-youngs/s Pages read 1596663, created 728153, written 67299820 0.00 reads/s, 0.00 creates/s, 0.00 writes/s Buffer pool hit rate 986 / 1000, young-making rate 1 / 1000 not 491 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 414513, unzip_LRU len: 0 I/O sum[178159]:cur[2665], unzip sum[0]:cur[0] ---BUFFER POOL 4 Buffer pool size 417792 Free buffers 726 Database pages 414705 Old database pages 153101 Modified db pages 36600 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 11279236, not young 300731632 0.00 youngs/s, 0.00 non-youngs/s Pages read 1569208, created 728718, written 66867473 0.00 reads/s, 0.00 creates/s, 0.00 writes/s Buffer pool hit rate 986 / 1000, young-making rate 1 / 1000 not 515 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 414705, unzip_LRU len: 0 I/O sum[178159]:cur[2665], unzip sum[0]:cur[0] ---BUFFER POOL 5 Buffer pool size 417792 Free buffers 603 Database pages 414855 Old database pages 153155 Modified db pages 36645 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 11634232, not young 299370124 0.00 youngs/s, 0.00 non-youngs/s Pages read 1581611, created 728918, written 67297635 0.00 reads/s, 0.00 creates/s, 0.00 writes/s Buffer pool hit rate 987 / 1000, young-making rate 1 / 1000 not 470 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 414855, unzip_LRU len: 0 I/O sum[178159]:cur[2665], unzip sum[0]:cur[0] ---BUFFER POOL 6 Buffer pool size 417792 Free buffers 726 Database pages 414706 Old database pages 153100 Modified db pages 37179 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 11385018, not young 300961163 0.00 youngs/s, 0.00 non-youngs/s Pages read 1571483, created 728056, written 67087236 0.00 reads/s, 0.00 creates/s, 0.00 writes/s Buffer pool hit rate 986 / 1000, young-making rate 1 / 1000 not 495 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 414706, unzip_LRU len: 0 I/O sum[178159]:cur[2665], unzip sum[0]:cur[0] ---BUFFER POOL 7 Buffer pool size 417792 Free buffers 666 Database pages 414789 Old database pages 153131 Modified db pages 36865 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 11164864, not young 303783494 0.00 youngs/s, 0.00 non-youngs/s Pages read 1578128, created 728722, written 66652091 0.00 reads/s, 0.00 creates/s, 0.00 writes/s Buffer pool hit rate 986 / 1000, young-making rate 1 / 1000 not 507 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 414789, unzip_LRU len: 0 I/O sum[178159]:cur[2665], unzip sum[0]:cur[0] -------------- ROW OPERATIONS -------------- 0 queries inside InnoDB, 0 queries in queue 0 read views open inside InnoDB Process ID=16686, Main thread ID=140092498769664 , state=sleeping Number of rows inserted 468088646, updated 10678363, deleted 0, read 890691733 3056.35 inserts/s, 127.56 updates/s, 0.00 deletes/s, 403057.39 reads/s Number of system rows inserted 248, updated 420, deleted 707, read 33861 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT ============================ ``` ---
**Author:** ![matthewb](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/matthewb/32/34_2.png) [@matthewb](https://forums.percona.com/u/matthewb)\ **Post date:** [July 26, 2021, 4:07pm UTC](https://forums.percona.com/t/aws-ec2-with-mysql-not-using-anywhere-near-the-resources-available/11513/2 "2021-07-26T16:07:51Z")
Hi @Thomi77, Can you please share your my.cnf? ---
**Author:** ![Thomi77](https://avatars.discourse-cdn.com/v4/letter/t/e56c9b/32.png) [@Thomi77](https://forums.percona.com/u/Thomi77)\ **Post date:** [August 2, 2021, 2:12pm UTC](https://forums.percona.com/t/aws-ec2-with-mysql-not-using-anywhere-near-the-resources-available/11513/3 "2021-08-02T14:12:24Z")
[mysqld] !includedir /etc/mysql/conf.d/ !includedir /etc/mysql/mysql.conf.d/ default\_authentication\_plugin=mysql\_native\_password innodb\_max\_dirty\_pages\_pct = 0 innodb\_buffer\_pool\_size = 54000000000 innodb\_flush\_log\_at\_trx\_commit = 0 **ALL BELOW CHANGED AFTER THIS FORUM POST. HELPED BUT NOT FULLY** innodb\_io\_capacity = 5000 innodb\_io\_capacity\_max = 7000 innodb\_read\_io\_threads = 64 innodb\_thread\_concurrency = 0 innodb\_write\_io\_threads = 64 I have GP3 disks with 16,000 IO and 1GB/S (max for gp3). So I would like to make as much use of this as possible and I don’t see how a straight select insert cannot do that. copying 800 million rows straight should not be having many other constraints but disk performance I assumed ---
**Author:** ![matthewb](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/matthewb/32/34_2.png) [@matthewb](https://forums.percona.com/u/matthewb)\ **Post date:** [August 2, 2021, 3:18pm UTC](https://forums.percona.com/t/aws-ec2-with-mysql-not-using-anywhere-near-the-resources-available/11513/4 "2021-08-02T15:18:04Z")
This is essentially a single-thread operation (a query in mysql cannot use more than 1 thread). Have you done any sysbench IO tests using a single thread to set expectations? That’s what I would do next. I doubt a single thread will be able to max out your disk capacity. You should also `SET SESSION sql_log_bin=0` for the session running the query, unless you have replication requirements. This, innodb\_max\_dirty\_pages\_pct, should be at least 25. A setting of 0 means to flush all dirty pages immediately which might spend too much CPU reading flush lists. ---
**Author:** ![Thomi77](https://avatars.discourse-cdn.com/v4/letter/t/e56c9b/32.png) [@Thomi77](https://forums.percona.com/u/Thomi77)\ **Post date:** [August 25, 2021, 5:52pm UTC](https://forums.percona.com/t/aws-ec2-with-mysql-not-using-anywhere-near-the-resources-available/11513/5 "2021-08-25T17:52:19Z")
So what I have done now is after weeks of trying is to batch the initial load into multiple chunks of 150 million rows each. it wasn’t much faster but at least it didn’t break. I have I believe read a lot of things about mysql performance improvements and I have to say, I am not sure I got it yet fully. I am coming from the MSSQL world and there, it seems defaults are more geared towards performance 😉 so now I have a procedure that runs various updates, inserts, deletes and then calls again other procedures that do again multiple inserts and updates. Its an importer that is very modular built. It loads the delta now around every 4-5 minutes and should be hopefully done within 1 minute Rough workload : Table1: 1 Row inserted Table2: 3000 Rows inserted 3000 updated Table3: 4000 Rows inserted 1000 updated Table4: 10000 Rows inserted Table5: 6000 Rows Inserted (1 json column that can have big data, this is not the insert that takes signifigant time though at all) Table6: 5000 Rows inserted; 2000 Updated Table7: 3000 Rows inserted; 1000 updated; 1000 deleted I am not sure it matters that i place the rough workload as it doesn’t say much but no row is really big. I talk here about 4-5 bigint fields and 1-2 varchar (75) with hashes in it. I can assure all is properly indexed and there is not a single query that takes long executed as standalone. Every query in this entire import I call from MySQL Workbench does not take longer than 0.2s to return the full result. Now this procedure takes around 3-4 minutes!!! for each import. I do not see our 64GB 8 Core server even near its capacity nor are the disks. Far from it. Here all the settings I have placed based on what I have read so far [mysqld] !includedir /etc/mysql/conf.d/ !includedir /etc/mysql/mysql.conf.d/ default\_authentication\_plugin=mysql\_native\_password innodb\_buffer\_pool\_size = 54000M innodb\_flush\_log\_at\_trx\_commit = 0 innodb\_io\_capacity = 12000 innodb\_io\_capacity\_max = 13000 innodb\_read\_io\_threads = 64 innodb\_thread\_concurrency = 0 innodb\_write\_io\_threads = 64 innodb\_log\_file\_size = 4000M innodb\_log\_files\_in\_group=4 innodb\_max\_dirty\_pages\_pct = 35 innodb\_log\_buffer\_size = 1000M innodb\_flush\_method=O\_DIRECT I have also used the Percona PMM tool to find bottlenecks but unfortunately, most of the data (checkpoint age for example) I wasn’t able to see. Is what we try to do simply not what mysql is capable of or is it the way we setup the server. We cannot split this import up. 1 single mistake in any of the updates must result in a rollback of all. This is incremental data (with few exceptions), hence 1 wrong record would make the entire 1 TB database useless. Thank you so much for the first reply that helped already in the batch import. EDIT: I want to highlight that i can see the inserts are taking long. I can see while its running that an insert statement takes 20s of which the select takes 0.2 executed standalone. EDIT: the tables have all row\_format=compressed. That did definitely not help with the performance but made it worse. ---
**Author:** ![matthewb](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/matthewb/32/34_2.png) [@matthewb](https://forums.percona.com/u/matthewb)\ **Post date:** [August 26, 2021, 3:07pm UTC](https://forums.percona.com/t/aws-ec2-with-mysql-not-using-anywhere-near-the-resources-available/11513/6 "2021-08-26T15:07:46Z")
@Thomi77 , Did you correctly configure PMM? You have to setup the local agent to connect to mysql and collect the data. Again, please understand that what you are doing is a single thread of operation. If you have one connection to MySQL and are executing query after query, that will only ever use 1 thread. Also, you will not gain any benefit from using stored procedures. In fact, this is probably bottlenecking you since again, single thread usage. As you are coming from MSSQL, you will be extremely disappointed in the SP environment that MySQL provides. It is extremely rudimentary, there is no compile cache, no extensibility. > Is what we try to do simply not what mysql is capable of Of course it is. That’s why it’s #2 on [https://db-engines.com/](https://db-engines.com/) (above MSSQL I might add). There’s no way that millions of MySQL users would be using it if it couldn’t handle things. The issue here, from what I can tell based on your words in this post, is that you are trying to push the contents of a 6" pipe through a single 0.5" pipe (single thread) and are expecting the performance of 6" pipe. What you need is to use 12 0.5" pipes to get the same performance (multiple connections / multi-threaded). I can spin up an 8 core 64GB VM on AWS and can push 20,000 INSERTS/sec through MySQL using 64 connections. I would suggest that you try out [GitHub - akopytov/sysbench: Scriptable database and system performance benchmark](https://github.com/akopytov/sysbench) (use the oltp\_read\_write.lua script) and tell it to use `--threads 1` and then re-run using `--threads 64` Once you get the PMM agent properly setup on the DB server, check your disks as well to make sure you are not saturating them. Also, if you are INSERTing in NON-ascending PK order, you will be subject to page splits and random IO, as rows within the InnoDB engine are clustered on the PK and inserting randomly will cause additional IO. ---
**Author:** ![Thomi77](https://avatars.discourse-cdn.com/v4/letter/t/e56c9b/32.png) [@Thomi77](https://forums.percona.com/u/Thomi77)\ **Post date:** [August 26, 2021, 4:08pm UTC](https://forums.percona.com/t/aws-ec2-with-mysql-not-using-anywhere-near-the-resources-available/11513/7 "2021-08-26T16:08:46Z")
What you say makes total sense. I will work on getting PMM working properly and do a Benchmark test. Could it be that since I have made row\_format = compressed that i made things worse because now that 1 thread is busy with decompressing? We use compression level 6. I can see that in the delta load 1 core is always at capacity. at the initial load (select \* from table1) though no resource is even near capacity and we read with 100k/s rows which i find a bit small. What helped is to increase max\_allowed\_packet to max but only short term then back to 100k rows/s What happens if I change compression level to 3. will it only apply for new inserted rows? Edi: I used the AWS image and connected it to our database. so i have a separate instance now running PMM ---
**Author:** ![matthewb](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/matthewb/32/34_2.png) [@matthewb](https://forums.percona.com/u/matthewb)\ **Post date:** [August 26, 2021, 6:09pm UTC](https://forums.percona.com/t/aws-ec2-with-mysql-not-using-anywhere-near-the-resources-available/11513/8 "2021-08-26T18:09:27Z")
Yes, I would avoid the compression altogether or lower the level to save some CPU. Is your storage really that expensive or just too much? A complete 1TB of actual data? I’m pretty sure the default for max\_allowed\_packet is also the max 1G so that won’t change anything in terms of performance since everything is still single threaded. Instead of using PMM ‘Add Remote MySQL’, use the actual agent on the MySQL server so you can get all metrics. ---
**Author:** ![Thomi77](https://avatars.discourse-cdn.com/v4/letter/t/e56c9b/32.png) [@Thomi77](https://forums.percona.com/u/Thomi77)\ **Post date:** [August 27, 2021, 8:02am UTC](https://forums.percona.com/t/aws-ec2-with-mysql-not-using-anywhere-near-the-resources-available/11513/9 "2021-08-27T08:02:46Z")
4.6 TB database total without compression. That is quite expensive. Then you need snapshots, etc. What happens if we change compression level now? after 2.1 TB of data has been inserted compressed. as soon as I run the settngs change my DB is gone for couple days to re-compress all the data? ---
**Author:** ![matthewb](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/matthewb/32/34_2.png) [@matthewb](https://forums.percona.com/u/matthewb)\ **Post date:** [August 27, 2021, 2:28pm UTC](https://forums.percona.com/t/aws-ec2-with-mysql-not-using-anywhere-near-the-resources-available/11513/10 "2021-08-27T14:28:09Z")
Compression happens on page write, so if you change the parameter, it will not retroactively go fix every page that had a higher compression. It will only affect new page writes.