Here is a procedure from Microsoft on how to achieve what you are asking. Your issue is that the only session that can exist in the database when changing it to RCSI is the Alter DATABASE statement. However, the mirroring session exists, so there 2 sessions connected to the database. You only want a single session to exist. You need to break mirroring , make the change to RCSI and then re-enable mirroring. Don't take my word for it. Review the Microsoft link below https://techcommunity.microsoft.com/t5/datacat/how-to-enable-rcsi-for-a-database-with-database-mirroring/ba-p/305043

This is a classic problem in any environment (cloud, VMWare, bare-metal) What you need to do you are already doing: Delete the rows in the table in chunks but you need to delete all the rows. Why ??? The data and index pages (each 16K) in the InnoDB Buffer Pool carry the tablespace ID reference to the table they came from. When you drop am InnoDB table, are the data and index pages are checked to see if they are in the InnoDB Buffer Pool and are up-to-date. Those pages must be invalidated in the InnoDB Buffer Pool if they are there. That can take time as you already surmised. Once all 8 billion rows are deleted, drop the table should be faster. You have already done your due diligence in this are. What to do next ??? SUGGESTION #1 : Truncate the Table TRUNCATE TABLE mygianttable; SUGGESTION #2 : Make the table BLACKHOLE ALTER TABLE mygianttable ENGINE=BLACKHOLE; DROP TABLE mygianttable; SUGGESTION #3 : Restart MySQL with empty InnoDB Buffer Pool, and Drop Table Go to DB Parameter Set innodb_buffer_pool_dump_at_shutdown OFF Set innodb_buffer_pool_load_at_startup to OFF Restart RDS Instance (Starts with Empty Buffer Pool) Drop the Table Set innodb_buffer_pool_dump_at_shutdown to OriginalValue Set innodb_buffer_pool_load_at_startup to OriginalValue Give it a Try !!!

There are multiple places where there might be a bottleneck. HTTP The client process that then talks to MySQL Connecting to MySQL (for a single action) Competing with other actions to do the Update (or IODKU) Plan A: Scrape the web server's log to see that someone made a request. (The web service may not let you do this.) This would let you batch the data, and even de-duplicate much of the data. Periodically in the scraping, do a batch IODKU into the table. The timestamp updating would be delayed, but that may be OK. Plan B: If the table where the update is occurring is somewhat busy with other stuff, it may be better to split this column out into a separate table. (I often recommend this for a "Likes" and "Visits" counter.) Plan Like Plan B, but stage the updates in a very simple table that is periodically flushed with batch inserts into the appropriate locations. Until you know which part of the processing is the bottleneck, you can't be sure that any of my suggestions (or others) would be beneficial.

b) confirmed from fivetran support, this is an expected behavior and this value is true only when active replication is happening. waiting for the required WAL log which was already flushed at postgres side. postgresLSN (fivetran side) can be manually updated by fivetran support with the latest available restart_lsn value(data might be missing) or can resync the complete data to resolve the issue.

I'm still waiting for AWS to provide a full PG RDS restore from snapshot stored on S3. at the moment this is the closest thing you can do, per table basis only. https://aws.amazon.com/about-aws/whats-new/2019/04/amazon-rds-postgresql-supports-data-import-from-amazon-s3/

Taken from https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.PostgreSQL.CommonDBATasks.html : rdstopmgr – A role that's used internally by Amazon RDS to support Multi-AZ deployments.

The computation of when the column will overflow is complex. 250 TINYINTs (1 byte + overhead) would easily fit, but a TEXT, BLOB, and certain other datatypes will take a minimum of 20 bytes; some cases 40 bytes. Hence, the unexpected overflow. I recommend doing one or more of the following: Shrink the datatypes. (This is not applicable to your "table1".) Some programmers blindly use BIGINT and DOUBLE for numeric values. These take 8 bytes each; there are smaller datatypes that my suffice. Do not have an "array" spread across columns. "Vertically" split the table, moving some columns to another table. Then use the same PRIMARY KEY for both tables. (If that is AUTO_INCREMENT, then it applies only to the main table.) As above, but move sets of columns that are often NULL. Then you can simply leave out the row in the new table. (Use LEFT JOIN when putting them back together.) Combine some columns together in some way, perhaps via JSON. This will decrease the number of columns, hence the per-column overhead. And the JSON will use a minimum of 20 (or whatever), which is less than 20 * the number of columns it replaces. If you provide the current SHOW CREATE TABLE with the actual column names, I may have more tips.

The first set of graphs implies a backup on odd-numbered dates. Yes, throttle the number of clients you run; this avoids flooding of MySQL. MySQL gives every connection equal access to resources, thereby leading to long delays in any of them finishing. You say "workers". Do you have some kind of "queue". Be aware that if you launch workers "too fast", they could fail to finish before new ones are added to the mix. This can especially happen if you are using "corn" and one instance is not finishing before the next instance is launched. Use the http://mysql.rjweb.org/doc.php/mysql_analysis#slow_queries_and_slowlog to locate the "worst" queries -- namely the ones to optimize (via better indexes, etc) first. "innob_db_data_writes and innodb_rows_inserted metrics" - Sounds like inefficient queries; the slowlog should help in diagnosing. "filesystem writes and row updates, and not memory caches" -- Yeah. High "reads" might indicate the need for more RAM. "swap insanity" -- Probably RDS is carefully tuned to always avoid swapping. (The analysis, above, will help see it that is the case.) The "innodb%" metrics do not include swapping. "disk space usage shows that after reset, the data is gone" -- Again, this would show up as more innodb reads, not writes. Provide the GLOBAL STATUS and VARIABLES for analysis as mentioned here: http://mysql.rjweb.org/doc.php/mysql_analysis#tuning

Multi-AZ RDS uses block-level replication to replicate the I/O, so it will wait for block I/O requests on the local instance, then replicates that write to the instance on the other AZ. Using innodb_flush_log_at_trx_commit = 2, log writes use buffered I/O, so they may delay the block I/O request for up to 1 second. In other words, flushing the innodb log is what triggers the sync to the other multi-AZ instance. The multi-AZ sync doesn't happen until the innodb log is flushed locally first. Therefore multi-AZ does not mitigate the risk of data loss if you use a relaxed values for innodb_flush_log_at_trx_commit. If you're trying to get better performance by using that relaxed setting, but also have assurance of HA, you cannot do that. RDS performance is generally bad. If you need high write throughput, you need a different platform.

Per https://aws.amazon.com/premiumsupport/knowledge-center/native-backup-rds-sql-server/ : When the rds_backup_database or rds_restore_database stored procedure is called, the task starts and outputs the information about the task. ... When the lifecycle status of the task is SUCCESS, the task is complete. ... You can get the Task ID after you perform the backup or restore statement. Or, you can use the following script to identify all the completed and pending tasks for a particular database: exec msdb.dbo.rds_task_status @db_name='database_name' To track the status of the job, use this SQL statement: exec msdb..rds_task_status @task_id= 5 So, after initiating the backup with rds_backup_database, you would just need to enter a loop where you use rds_task_status to find the current status, and if it is not "SUCCESS", you could use WAITFOR DELAY '00:00:30' to sleep for 30 seconds, before continuing your loop to check status again & repeat. There is also https://stackoverflow.com/a/47640253/1948808 that implements a similar solution from PowerShell. Whether you implement your code in PowerShell or T-SQL, the logic for checking & looping would be similar.

Your query seems to from v10 and thus is missing two reg types. 'pg_catalog.regcollation' 'pg_catalog.regnamespace'

you should follow this passages: First, if your databases don’t already have an option group, you’ll want to add one. Here’s the CLI I used in PowerShell to add one to my database: aws rds create-option-group ` --option-group-name hsroptiongroup ` --engine-name sqlserver-ex ` --major-engine-version "14.00" ` --option-group-description "Option Group to customize SQL Server hsr" From there, you need to add an option to the option group. The command is pretty explicit: aws rds add-option-to-option-group ` --apply-immediately ` --option-group-name hsroptiongroup ` --options "OptionName=SQLSERVER_BACKUP_RESTORE, OptionSettings=[{Name=IAM_ROLE_ARN,Value=arn:aws:iam::xxxxxxx:role/sqlbackup}]" Attach the option group to the instance next: aws rds modify-db-instance ` --db-instance-identifier hsr ` --option-group-name hsroptiongroup ` --apply-immediately So, done, right? I can now run BACKUP DATABASE commands. Well…. No. The trick is, you’ve made it possible to get a SQL Server native backup. You didn’t make it possible to run the BACKUP command. Instead, you have to do something that looks like this: EXEC msdb.dbo.rds_backup_database @source_db_name = 'mydb', @s3_arn_to_backup_to = 'arn:aws:s3:::mybackup/mydb.bak', --[@kms_master_key_arn='arn:aws:kms:region:account-id:key/key-id'], @overwrite_s3_backup_file = 1, @type = 'FULL'; --, --[@number_of_files=n]; The deal is, you can get a native backup. That means, you can copy a file, in my case there, mydb.bak, and run a restore locally, or on another RDS instance. You can only take FULL or DIFFERENTIAL. You’ll note the parameter @kms_master_key_arn. That’s so you can encrypt the backup (a very good idea). I skipped setting up a key for this demo. There’s also a command for restoring, msdb.dbo.rds_restore_database. Source: https://www.scarydba.com/2020/06/29/sql-server-backups-on-aws-rds/

The https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_UpgradeDBInstance.Maintenance.html#USER_UpgradeDBInstance.OSUpgrades says you should be able to trigger the upgrade by choosing the instance that you want to apply the upgrade to and select “Maintenance and Backup” tab followed by selecting the pending maintenance and clicking “Apply Now” 

Depending on the format of the audit file you could query it directly with https://aws.amazon.com/athena/faqs/ from S3 if it’s a supported format. If the format isn’t directly supposed by AWS Athena you can use https://aws.amazon.com/glue/faqs/ to do a transformation as some of the answers on https://stackoverflow.com/questions/55284580/how-to-query-nested-xml-file-in-aws-athena-via-glue suggest.

Suggestion/Observations to consider to reduce time required for query completion. CREATE INDEX bicycles_ndx_status_number ON bicycles (status, number); Test your query to see if same results are provided but faster. EXPLAIN SELECT (your query) and observe ROWS column value. Posted EXPLAIN had ROWS at 2,082. The index should reduce the ROWS number significantly, saving time. Observation, AND Bicycle.number Observation 2 singular vs plural table name qualifiers. Is confusing to me when both are used but may be best for you. Observation 3 first line of defense when any query is slow ANALYZE TABLE table_name; EACH table to ensure indexes are CURRENT (they could have somehow become corrupted). This should be first attempt at correcting any SLOW query. Would not help you much because you NEED the additional BICYCLES multi-column index for high performance.

Unlike the https://docs.aws.amazon.com/AWSEC2/latest/UserGuide/instancedata-data-retrieval.html and ECS Tasks ( https://docs.aws.amazon.com/AmazonECS/latest/developerguide/task-metadata-endpoint.html / https://docs.aws.amazon.com/AmazonECS/latest/userguide/task-metadata-endpoint-fargate.html ) there is no metadata service that you are able to query to provide you with those details. The best that you will be able to get would be from system level views that might provide the hostname. However I would use caution with relying on that information as in a fully managed environment failovers and instance replacements happen automatically 99% of the time so the values you query could might be different the next time and may or may not have a standard nomenclature to them. The only way to get that information is to make a call to the RDS API endpoint for the cluster and/or instance that you are looking for. If you are running the sql via an external process that will connect to the instance you could put those checks as a prerequisite to connecting and running the script. However if you are manually run the script by connecting directly to the instance and hoping to prevent an "oops" action it's going to be up to the person taking the action to check their work.

I also contacted AWS support and they confirmed that reads from the disk cache don't contribute to the Read IOPs metric.

Okay, so the issue is that this is just the wrong approach. @Laurenz Albe seems to be right - "Sorry Dave, I'm afraid I cannot do that". So what's a dba going to do? Well, my personal answer was: Setup a local instance of the same DB. So basically for me that was my dev setup for django with postgres backend on docker. Upload your fixtures to that. One can easily set the triggers off for that, be it on a table per table basis (as shown above) or even using set session_replication_role = replica; (see postgres docs for info), which is simpler. Or, if you don't want to have to force a constraints checks manually on postgres, you could simply set db_constraint=False on all models.ForeignKey in your django models, migrate, upload, then set to True again, migrate. Once you've uploaded the fixtures/data locally on postgres and validated that the constraints checks out, produce a data dump: pg_dump your_local_db_name > db_dump_filename That produces your full pg data dump. You should make sure your local owner for the tables is the same as the remote one. Since those are set by the dump file upon upload (ALTER TABLE your_table OWNER TO owner_name;). Otherwise find + replace the owner_name in the dump before uploading it. Then, to upload to rds: psql --host=.us-east-2.rds.amazonaws.com --port=5432 --username=postgres --password --dbname= < db_dump_filename You will be asked for the postgres user password. Then it will all run with outputs in terminal; I think you can use a -L flag to output stuff to a logfile locally (not tested). At any rate, look at the output, and if everything is clean your data is up there. Warnings: one should probably make sure that the remote db is the same as the local one (in terms of models). My aim was just to first populate an empty db. I'm guessing that in production, you'd want to dump from the remote rds instance first. Upload that locally to. Then upload additional fixtures. Then dump locally, and upload remote.

This is a defense in depth tactic. There are other defense mechanisms in place at AWS to prevent data leakage such as disk destruction on disposal or limiting access to data centers to approved employees. If those defense controls were to fail the encryption at rest would provide a layer of defense to prevent data leakage. As you pointed out, access to your account provides access to the data. The defense in depth for this attack surface is that you keep your access keys secure, rotate them every 90 days, and don't provide access where it isn't need. Your user accounts should have strong passwords, no reuse, rotated every 90 days, the right access controls, and MFA enabled. These are all defense in depth tactics to keep your environment secure. In general, from a non-AWS perspective, data at rest encryption could be the only layer of data leakage protection. Not everyone is SOC2/ISO27001/PCI/HIPAA compliant. Bottom line, is data at rest encryption security theater? No. Is it the first line of defense? No (in the case of AWS). Is it part of a broader security defense in depth strategy? Yes. References: https://csrc.nist.gov/glossary/term/defense_in_depth https://www.cisa.gov/uscert/bsi/articles/knowledge/principles/defense-in-depth

Initially I had this problem after making a failed request but without knowing it, I re-explained it. Following this my database was shut down in the rds, what I did was reboot it but when this process was over, the state was already "Storage-full". I make clear that the chart of the question, reflects the behavior of what happened to me, was in a matter of minutes that my storage was filled. After consulting multiple forums I gave with several things that were very useful to me to understand what happened and it is that at the end of the story, the physical space can be filled very easily at the end of logs. I'll try to be detailed so that people who don't have much idea as my case was can step by step.First of all, I recommend having a copy of the database and also increasing the storage of almighty bd by 10%, this is recommended in the AWS forums, first so that your application does not have inconveniences and second to remove the Storage-full status.(1) Let's start by mentioning that we will need to use SQL*PLUS, in my case I had to download the https://www.oracle.com/database/technologies/oracle-database-software-downloads.html#19c , in the following link you can find the different versions. 2) We require https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_ConnectToOracleInstance.html by means of SQL*Plus The highlight is the data we need to enter, this data we get from our connection to the bd that can be removed from the connection of the sql developer if they have it: or rds as indicated in the previous link (3) Once already connected we will consult the physical space that occupy the temporary files.SELECT tablespace_name, file_name, bytes/(1024*1024*1024) FROM dba_temp_files WHERE tablespace_name like 'TEMP%'; //retorna tablespace_name, nombre del archivo y el tamaño ocupado en gigas //A nosotros nos importa el tamaño y el tablespace_name dado que el primero nos confirma //si ahí está nuestro problema y el segundo se usa para el siguiente paso 4)Now it remains to follow the steps indicated https://stackoverflow.com/questions/1824572/how-to-shrink-temp-tablespace-in-oracle In the first response of the post of the link, which has all the credits for its answer, is the most complete I have found, so I thank you very much.And ready with the first two steps he indicates should suffice, otherwise it is necessary to follow the last one that is to create a new tablespace.Before doing this in my case I restarted my rds instance and then applied the steps he indicates because I was getting an error: "ora-03297 the file contains data used beyond the requested resize value" which is because there are temporary files that are being used.Well, I hope it's helpful.