Is MySQL trigger for 100 Millions daily updates can impact data base cluster performance?

Question

I have a MySQL (Aurora 5.7) table in data base which has

1. 700 Million rows
2. 9 Indexes
3. 25 Columns
4. 100 Million daily updates
5. and 5 Million daily inserts

Overall this table is very busy and now we need to run some extracts on this data base to generate reports .

The issue is not only this but the column involved in Extract is not indexed so it takes 5 to 6 hours to perform extracts.

Extracts is just basically we get counts based on dates which is not indexed and we can not create indexes as this can impact application .

As we don't have index query goes for full table scan and impacts the Aurora cluster and breaks the Global Data base replication .

Even though we run this query in DR reader nodes it impacts the cluster and AWS Supports and Experts also has confirmed this .

So we dont have option to run extracts on main table and this is where we are exploring other options .

Option 1 :

Take daily snapshot restore and run daily report in new cluster. This option looks very good but the issue is cost we have 18 TB data base and my extras daily will run for 10 to 15 hours because 21 such reports has to be generated so monthly cost will be close to $ 7 k

Option 2 :

Takes Daily snapshot and extras only required tables for report to s3 and then run Athena query .

But the issue is export to S3 is taking long time and cost again is very very high because even though we export few tables from Snapshot we are getting charged for complete snapshot ,18 TB export which is again 6 K $ per month .

Option 3:

Have trigger on this bust table and inserts all the inserts and updates to new table and then run report on new table.

I need help to decide which is best option and if we go with Trigger option then there will 100 millions triggers daily and this many trigger can impacts data base ?

I have also looked at using DMS on this table and moving data to another table but that would be continuous replication which again can impact Cluster .

NOTE : Sorry to say but we can not afford any downtime for the application

Please suggest .

Answer 1

It is true that triggers incur some cost to the database. You should think about it in terms of the number of index writes.

When you insert or update a table without triggers, you can estimate the cost by counting the number of indexes, plus the primary key, which is the clustered index. You have nine indexes, so this is 9+1 = 10 writes per insert or update.

If you also have a trigger, the execution of trigger code is a small overhead, but it is more important to count the cost of the index writes by statements in the trigger. The index writes are more costly, compared to the trigger code itself.

So if your insert/update spawns a trigger that writes to another table, the cost will be the 10 index writes for the original table, plus as many index writes as the number of indexes in the additional table. Suppose you have one more index in that second table, it would then be 11 index writes, for a total of 21. So it is slightly more than double the cost.

My knowledge of Aurora is that it disables the InnoDB change buffer, because of the distributed storage. This means that all index writes must be merged at the time of your insert/update.

If I were working on your database, I would just add an index to the original table, using pt-online-schema-change. This allows clients to read and write the table while the index is being built. We use this at my company, and we do hundreds of schema changes per week for large tables, with no downtime.

Pt-online-schema-change uses triggers while it's building the index, but those triggers are dropped after this change is done. Also note that if you have long-running transactions against the table, it will conflict with pt-online-schema-change, because it needs a brief exclusive metadata lock at the start and the end of its work.

Answer 2

1200/second does not necessarily overwhelm a SSD system, so I will suggest improvements in the single server (or single cluster) solution you are struggling with.

We need to see more info -- the CREATE TABLE, the summarizing reports, etc.

Are the 1200 Updates/second mostly the simple bumping of a counter? If so,...

• I recommend having a table(s) with just the Primary key and a number.
• Or collecting counts externally, then gathering them together to bump the counters less frequently than one count at a time. (This gets tricky, we need to see the topology of the clients, too.
• Maybe these high speed ingestion tips would help.

Is the data essentially "write once"? That is, would it work to build and maintain a "summary table". This might involve summarizing the last hour's data just after the top of the hour.

With some of those changes, the main table is unlikely to need more than one or two indexes, not 9. This will improve performance. http://mysql.rjweb.org/doc.php/summarytables