I have an RDS MySQL instance db.m5.4xlarge with 8.3TB of data, and I want to shrink it to 4TB to save costs because it actually has 4TB of data. What are the viable options? I've tried several methods, aiming to complete it within 8 hours: 1. DMS (Database Migration Service): When using DMS, there were index issues. Before the migration, adding indexes caused it to report duplicate IDs, but I checked and couldn't find any duplicates. After the migration, if I add the indexes back, the data doesn't sync. 2. Mysqldump: It takes too long. For 450GB, it's already taking nearly 6 hours. 3. Take Snapshot and restore to smaller storage: but the options on AWS console restrict me to downsize to a volume that is smaller than its original size[enter image description here](https://i.sstatic.net/kEyNI6ib.png)

I have a table of yearly data [size 500GB = ~1 billion rows] in aurora mysql 3.02.2. requirement is to migrate this table to another aurora mysql [PROD] using DMS. we initiated the DMS task with data validation enabled and also with indexes & PK in target, which caused slowness to other production processes due to load on the DB. so i did some research and looking to get some suggestions here please: *Note: this new table in prod will **NOT** be used by the application until this work is completed and renamed to current table name * 1. Load data first using DMS and create index manually later on a large table like this is the correct approach? **OR** 2. should i use DMS task filtering on "datetime" bigint column to load month by month data to the new table where the index build happens when the data is written over to the table. This can be done over the course of few days for each month (assuming we run the DMS task for few hours each day) **OR** any better method ? 3. does index creation generate lot of temp files on such a large table, which will lead to any memory issues **OR** should i use something like "ALGORITHM=INPLACE" in create index statement? 4. current parameter settings which i see related are "innodb_ddl_buffer_size=1MB" & "innodb_file_per_table=ON". any other parameters i should check? the target production db have 24x7 data written into it and i am looking for a way to avoid any slowness or performance issues when this table with index is being copied over.

I have recently discovered [Transact-SQL snapshot backups](https://learn.microsoft.com/en-us/sql/relational-databases/backup-restore/create-a-transact-sql-snapshot-backup?view=sql-server-ver17) . They let you interact with snapshot backups, like this flowcharts explains. It is obvious that this is compatible with a traditional SAN. I have found that EBS has [something similar](https://aws.amazon.com/blogs/modernizing-with-aws/automating-sql-server-point-in-time-recovery-using-ebs-snapshots/) , but are the EBS-style snapshots compatible with Transact-SQL snapshot backups? That is, can EBS generate snapshots that would agree with syntax like this?

BACKUP DATABASE testdb1
TO DISK = 'D:\Temp\db.bkm'
WITH METADATA_ONLY, FORMAT;

How can I configure AWS services to notify me of failed pg_cron job runs in the cron.job_run_details table using only the AWS Management Console or PostgreSQL settings, without writing any code?

I have an ASP classic web application based on an SQL Server database, with an admin component. The database has a linked server with Microsoft.ACE.OLEDB.12.0 provider to allow bulk import of records from an access database. Over the years, I have gotten it to work on Windows 2008 server, 2012 server, and Windows 10 (my development machine), though not without losing some hair. Now I am trying to port the app to an AWS EC2 instance with 2022 Server Datacenter edition, and can't make it work. I can run a linked server query in a SQL Server Management Studio window with no problem, so it works to that extent -- the linked server exists and works in principle. But if the same query is attempted from the web app, it fails with: > "Microsoft OLE DB Provider for ODBC Drivers error '80040e14' [Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "[name of server]" returned message "Unspecified error". The error message seems very uninformative -- just a long-winded way of saying the query didn't work for unknown reasons. It's possible to query the SQL server directly from the web app, and to query the Access database directly from the web app using the same provider as used by the linked server. Only linked server queries fail. Because it works from my admin login via Management Studio but not from an IIS process, the problem could be 1) permissions; or 2) something about configuration of IIS or its application pools or the ability of the query to work from within an IIS process. Regarding permissions, I already made sure IUSR has full control of the folder where the linked server Access database sits. Based on other research, I tried firing up Dcomcnfg and giving IUSR full permissions for MSDAINITIALIZE (even though this was not necessary on my 2012 Server or Windows 10 machines). It didn't help. Regarding configuration, "Allow in process" is checked for the linked server provider, same as for my earlier machines, where linked server queries are successful from the web app.

I am following this guide in order to migrate MariaDB RDS instance into AuroraDB for MySQL. https://aws.amazon.com/blogs/database/migrate-self-managed-mariadb-to-amazon-aurora-mysql/ Reduce downtime associated with logical exports In step 6 I ran in AuroraDB instance I got Fatal error during log purge, I have no idea what is wrong. I continue to step 8 and I got Replica has encountered an error. Run SHOW REPLICA STATUS; to see the error. When I run SHOW REPLICA STATUS; I got:

{
	"data":
	[
		{
			"Replica_IO_State": "",
			"Source_Host": "my-mariadb.xxxxxx.us-west-2.rds.amazonaws.com",
			"Source_User": "repl_user",
			"Source_Port": 3306,
			"Connect_Retry": 60,
			"Source_Log_File": "mysql-bin-changelog.024419",
			"Read_Source_Log_Pos": 245991,
			"Relay_Log_File": "relaylog.000002",
			"Relay_Log_Pos": 1716,
			"Relay_Source_Log_File": "",
			"Replica_IO_Running": "No",
			"Replica_SQL_Running": "No",
			"Replicate_Do_DB": "",
			"Replicate_Ignore_DB": "",
			"Replicate_Do_Table": "",
			"Replicate_Ignore_Table": "",
			"Replicate_Wild_Do_Table": "",
			"Replicate_Wild_Ignore_Table": "mysql.%",
			"Last_Errno": 13117,
			"Last_Error": "Could not find relay log file.",
			"Skip_Counter": 0,
			"Exec_Source_Log_Pos": 0,
			"Relay_Log_Space": 168667,
			"Until_Condition": "None",
			"Until_Log_File": "",
			"Until_Log_Pos": 0,
			"Source_SSL_Allowed": "No",
			"Source_SSL_CA_File": "",
			"Source_SSL_CA_Path": "",
			"Source_SSL_Cert": "",
			"Source_SSL_Cipher": "",
			"Source_SSL_Key": "",
			"Seconds_Behind_Source": null,
			"Source_SSL_Verify_Server_Cert": "No",
			"Last_IO_Errno": 13114,
			"Last_IO_Error": "Got fatal error 1236 from source when reading data from binary log: 'Failed to replace GTID event with backwards-compatible event: corrupt event.; the first event 'mysql-bin-changelog.024419' at 162661, the last event read from 'mysql-bin-changelog.024419' at 245991, the last byte read from 'mysql-bin-changelog.024419' at 246037.'",
			"Last_SQL_Errno": 13117,
			"Last_SQL_Error": "Could not find relay log file.",
			"Replicate_Ignore_Server_Ids": "",
			"Source_Server_Id": 1235731222,
			"Source_UUID": "",
			"Source_Info_File": "mysql.slave_master_info",
			"SQL_Delay": 0,
			"SQL_Remaining_Delay": null,
			"Replica_SQL_Running_State": "",
			"Source_Retry_Count": 86400,
			"Source_Bind": "",
			"Last_IO_Error_Timestamp": "240417 12:41:55",
			"Last_SQL_Error_Timestamp": "240417 12:41:55",
			"Source_SSL_Crl": "",
			"Source_SSL_Crlpath": "",
			"Retrieved_Gtid_Set": "",
			"Executed_Gtid_Set": "",
			"Auto_Position": 0,
			"Replicate_Rewrite_DB": "",
			"Channel_Name": "",
			"Source_TLS_Version": "",
			"Source_public_key_path": "",
			"Get_Source_public_key": 0,
			"Network_Namespace": ""
		}
	]
}

I have tried to search a few error messages and have no solution at all.

Ok I'm facing a real crazy issue, I usually connect through AWS VPN Client Endpoint normally and I can access my database. Today I tried and TablePlus just froze, checked if I'm connected through mysql in the terminal and I'm connected, but when I use for example

backend;

it freezes on

table information for completion of table and column names You can turn off this feature to get a quicker startup with -A

I try with -A and it works fine till I for example tried

* from table_1 limit 10

worked fine, but made the limit 20, it froze again. Retried again and asked for the count, worked fine, tried a specific row after 20 it returned fine, tried

table_1

and nothing is corrupted, checked for stuck transactions/processes and nothing was there. Rebooted the rds and still. I even increased from t3.micro to large (I know it's still tiny but I have only 10,000 rows total max) and nothing changed it still freezes and I can't access the database. Spent the whole day debugging with AI but still reached a dead end. I checked CloudWatch logs, all I found was there was an update to mysql done on April 7th, I tried upgrading to 8.4.4 maybe it would fix it but still freeze issue. Another thing from my logs was

-04-28T20:55:07.997349Z 22 [Warning] [MY-010055] [Server] IP address 'xxx.xx.xx.xxx' could not be resolved: Name or service not known

and everyday it's a different ip (I run a cron job everyday), I checked the subnet with that ip in it's range and it's configured correctly. I also created another instance but in the same VPC and it had the same freeze issue. Any help would be appreciated!

New to cloud and have questions on AWS RDS SQL Server Instance, Question in Maintenance Plan. As a DBA team we are expecting RDS access and admin access on SQL box. But was limited with setupadmin and process admin. Not able to see the error log even. I don't even see the maintenance plan under management. Questions: 1) Why limiting access? since we are not the DBAs for these servers? 2) Do we need to set up regular backups(full, log) for the user databases of AWS will be taking care of it? 3) How about the user database maintenance like index maintenance and update stats? Thank you in advance !

**Situation :** I have a redis docker container running on local with some data of different kinds : Set, Hash, GeoHash. **Want to :** Move the data from local redis container to a newly created Elasticache instance ( Cluster mode Disabled ) **Tried :** **Approach 1 :** Created a rdb file of local docker container data with : `sudo docker exec -it bash redis-cli `save. This creates dump.rdb in /data Copied rdb file on local from container Copied rdb file to EC2 instance which is running in same VPC, Subnets as of Elasticache Ran : cat dump.rdb | redis-cli -h .cache.amazonaws.com -p 6379 --pipe This fails with : >All data transferred. Waiting for the last reply... \ >ERR unknown command 'REDIS0012', with args beginning with: 'redis-ver7.4.1'\ >ERR unknown command 'redis-bits@ctime$used-mem', with args beginning with: Last reply received from server. \ >errors: 2, replies: 2 **Approach 2 :** While creating new Elasticache instance, there is an option to restore from backup ( below image ), this also fails with the local redis generated rdb file. Seems like this only works with Elasticache generated backups only. Don't know for sure, just a guess. Question : How to take backup of a docker running redis instance & create a Elasticache instance with the same data ? Please help with this, Thanks.

I'm running two Postgres 15 (*PostgreSQL 15.7 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 7.3.1 20180712 (Red Hat 7.3.1-12), 64-bit*) RDS instances in AWS, one for my **staging** environment and one for my **production** environment. We're running a query that is taking a lot longer in the **production** envinroment than in **staging** one. The **production** envinroment even has less data than **staging** (at least in the selected/joined tables). Also, the **production** environment is not being heavily used, we're in an early testing stage, so there's basically one person using it at night for testing purpose. This is the query:

SELECT arenas.id,
       arenas.display_name,
       arenas.cover_image_path,
       arenas.slug,
       addresses.zip_code,
       addresses.street,
       addresses.number,
       addresses.complement,
       addresses.district,
       addresses.latitude,
       addresses.longitude,
       cities.NAME                                       AS city_name,
       states.NAME                                       AS state_name,
       states.uf                                         AS state_uf,
       Array_to_json(Array_agg(DISTINCT ss2.sport_code)) AS available_sports,
       Earth_distance(Ll_to_earth (addresses.latitude, addresses.longitude),
       Ll_to_earth (-10.5555, -41.2751))                 AS
       meters_distance_between_user_and_arena
FROM   "arenas"
       INNER JOIN "addresses"
               ON "arenas"."id" = "addresses"."addressable_id"
                  AND "addresses"."addressable_type" = 'App\Models\Arena'
                  AND "addresses"."type" = 'COMMERCIAL'
                  AND Earth_distance(Ll_to_earth (addresses.latitude,
                                     addresses.longitude),
                          Ll_to_earth (-10.5555, -41.2751))  '2024-10-06 01:31:18' ) )
                          AND "stripe_status" != 'incomplete_expired'
                          AND "stripe_status" != 'unpaid'
                          AND "stripe_status" != 'past_due'
                          AND "stripe_status" != 'incomplete')
       AND "business_hours_data" IS NOT NULL
       AND "arenas"."deleted_at" IS NULL
GROUP  BY "arenas"."id",
          "arenas"."cover_image_path",
          "addresses"."latitude",
          "addresses"."longitude",
          "addresses"."zip_code",
          "addresses"."street",
          "addresses"."number",
          "addresses"."complement",
          "addresses"."district",
          "cities"."name",
          "states"."name",
          "states"."uf"
ORDER  BY "meters_distance_between_user_and_arena" ASC;

This is the *EXPLAIN ANALYSE* from **production** envinroment:

Sort  (cost=55657.12..55795.57 rows=55380 width=315) (actual time=563.084..563.104 rows=1 loops=1)
  Sort Key: (sec_to_gc(cube_distance((ll_to_earth((addresses.latitude)::double precision, (addresses.longitude)::double precision))::cube, '(3491544.0649759113, -4339378.172513269, -3108045.069568795)'::cube)))
  Sort Method: quicksort  Memory: 25kB
  ->  GroupAggregate  (cost=12417.08..43152.98 rows=55380 width=315) (actual time=563.077..563.097 rows=1 loops=1)
        Group Key: arenas.id, addresses.latitude, addresses.longitude, addresses.zip_code, addresses.street, addresses.number, addresses.complement, addresses.district, cities.name, states.name, states.uf
        ->  Sort  (cost=12417.08..12555.53 rows=55380 width=286) (actual time=222.049..445.141 rows=102240 loops=1)
              Sort Key: arenas.id, addresses.latitude, addresses.longitude, addresses.zip_code, addresses.street, addresses.number, addresses.complement, addresses.district, cities.name, states.name, states.uf
              Sort Method: external merge  Disk: 28144kB
              ->  Hash Join  (cost=17.39..668.95 rows=55380 width=286) (actual time=0.709..15.847 rows=102240 loops=1)
                    Hash Cond: (arenas.id = field_time_slots.arena_id)
                    ->  Hash Join  (cost=9.60..37.48 rows=260 width=382) (actual time=0.604..1.425 rows=480 loops=1)
                          Hash Cond: (arenas.id = ss2.arena_id)
                          ->  Nested Loop  (cost=7.39..32.21 rows=52 width=339) (actual time=0.523..1.121 rows=96 loops=1)
                                ->  Seq Scan on sports  (cost=0.00..1.16 rows=1 width=9) (actual time=0.006..0.009 rows=1 loops=1)
                                      Filter: (code = 'BEACH_TENNIS'::text)
                                      Rows Removed by Filter: 12
                                ->  Hash Join  (cost=7.39..30.53 rows=52 width=350) (actual time=0.515..1.070 rows=96 loops=1)
                                      Hash Cond: (cities.state_ibge_code = states.ibge_code)
                                      ->  Nested Loop  (cost=5.78..28.77 rows=52 width=340) (actual time=0.488..0.953 rows=96 loops=1)
                                            ->  Nested Loop  (cost=5.49..11.03 rows=52 width=332) (actual time=0.466..0.640 rows=96 loops=1)
                                                  Join Filter: (arenas.id = services.arena_id)
                                                  ->  Nested Loop  (cost=2.05..4.72 rows=4 width=305) (actual time=0.422..0.444 rows=4 loops=1)
                                                        Join Filter: (arenas.id = fields.arena_id)
                                                        ->  Nested Loop  (cost=2.05..3.62 rows=1 width=289) (actual time=0.412..0.417 rows=1 loops=1)
                                                              Join Filter: (arenas.id = addresses.addressable_id)
                                                              ->  Merge Join  (cost=2.05..2.08 rows=1 width=174) (actual time=0.023..0.027 rows=1 loops=1)
                                                                    Merge Cond: (arenas.id = contacts.contactable_id)
                                                                    ->  Sort  (cost=1.02..1.02 rows=1 width=158) (actual time=0.012..0.013 rows=1 loops=1)
                                                                          Sort Key: arenas.id
                                                                          Sort Method: quicksort  Memory: 25kB
                                                                          ->  Seq Scan on arenas  (cost=0.00..1.01 rows=1 width=158) (actual time=0.006..0.006 rows=1 loops=1)
                                                                                Filter: ((approved_at IS NOT NULL) AND (business_hours_data IS NOT NULL) AND (deleted_at IS NULL))
                                                                    ->  Sort  (cost=1.03..1.04 rows=1 width=16) (actual time=0.008..0.009 rows=1 loops=1)
                                                                          Sort Key: contacts.contactable_id
                                                                          Sort Method: quicksort  Memory: 25kB
                                                                          ->  Seq Scan on contacts  (cost=0.00..1.02 rows=1 width=16) (actual time=0.006..0.006 rows=1 loops=1)
                                                                                Filter: (is_main AND ((contactable_type)::text = 'App\Models\Arena'::text))
                                                                                Rows Removed by Filter: 1
                                                              ->  Seq Scan on addresses  (cost=0.00..1.52 rows=1 width=115) (actual time=0.386..0.387 rows=1 loops=1)
                                                                    Filter: (((addressable_type)::text = 'App\Models\Arena'::text) AND ((type)::text = 'COMMERCIAL'::text) AND (sec_to_gc(cube_distance((ll_to_earth((latitude)::double precision, (longitude)::double precision))::cube, '(3491544.0649759113, -4339378.172513269, -3108045.069568795)'::cube))   Seq Scan on fields  (cost=0.00..1.05 rows=4 width=16) (actual time=0.009..0.020 rows=4 loops=1)
                                                              Filter: ((deleted_at IS NULL) AND ((status)::text = 'A'::text))
                                                  ->  Materialize  (cost=3.43..5.57 rows=13 width=27) (actual time=0.011..0.035 rows=24 loops=4)
                                                        ->  Hash Join  (cost=3.43..5.50 rows=13 width=27) (actual time=0.041..0.092 rows=24 loops=1)
                                                              Hash Cond: (service_prices.service_id = service_sport.service_id)
                                                              ->  Seq Scan on service_prices  (cost=0.00..1.75 rows=36 width=8) (actual time=0.006..0.032 rows=36 loops=1)
                                                                    Filter: is_default
                                                                    Rows Removed by Filter: 39
                                                              ->  Hash  (cost=3.41..3.41 rows=2 width=51) (actual time=0.030..0.036 rows=3 loops=1)
                                                                    Buckets: 1024  Batches: 1  Memory Usage: 9kB
                                                                    ->  Hash Join  (cost=2.20..3.41 rows=2 width=51) (actual time=0.025..0.034 rows=3 loops=1)
                                                                          Hash Cond: (service_sport.service_id = services.id)
                                                                          ->  Hash Join  (cost=1.07..2.27 rows=3 width=27) (actual time=0.014..0.019 rows=3 loops=1)
                                                                                Hash Cond: (field_service.service_id = service_sport.service_id)
                                                                                ->  Seq Scan on field_service  (cost=0.00..1.13 rows=13 width=8) (actual time=0.003..0.004 rows=13 loops=1)
                                                                                ->  Hash  (cost=1.06..1.06 rows=1 width=19) (actual time=0.005..0.006 rows=1 loops=1)
                                                                                      Buckets: 1024  Batches: 1  Memory Usage: 9kB
                                                                                      ->  Seq Scan on service_sport  (cost=0.00..1.06 rows=1 width=19) (actual time=0.003..0.004 rows=1 loops=1)
                                                                                            Filter: (sport_code = 'BEACH_TENNIS'::text)
                                                                                            Rows Removed by Filter: 4
                                                                          ->  Hash  (cost=1.07..1.07 rows=4 width=24) (actual time=0.008..0.009 rows=4 loops=1)
                                                                                Buckets: 1024  Batches: 1  Memory Usage: 9kB
                                                                                ->  Seq Scan on services  (cost=0.00..1.07 rows=4 width=24) (actual time=0.004..0.006 rows=4 loops=1)
                                                                                      Filter: ((deleted_at IS NULL) AND (NOT is_private) AND ((status)::text = 'A'::text))
                                                                                      Rows Removed by Filter: 2
                                            ->  Memoize  (cost=0.29..8.31 rows=1 width=24) (actual time=0.001..0.001 rows=1 loops=96)
                                                  Cache Key: addresses.city_ibge_code
                                                  Cache Mode: logical
                                                  Hits: 95  Misses: 1  Evictions: 0  Overflows: 0  Memory Usage: 1kB
                                                  ->  Index Scan using cities_ibge_code_unique on cities  (cost=0.28..8.30 rows=1 width=24) (actual time=0.016..0.016 rows=1 loops=1)
                                                        Index Cond: (ibge_code = addresses.city_ibge_code)
                                      ->  Hash  (cost=1.27..1.27 rows=27 width=16) (actual time=0.020..0.020 rows=27 loops=1)
                                            Buckets: 1024  Batches: 1  Memory Usage: 10kB
                                            ->  Seq Scan on states  (cost=0.00..1.27 rows=27 width=16) (actual time=0.006..0.010 rows=27 loops=1)
                          ->  Hash  (cost=2.15..2.15 rows=5 width=43) (actual time=0.076..0.078 rows=5 loops=1)
                                Buckets: 1024  Batches: 1  Memory Usage: 9kB
                                ->  Nested Loop  (cost=1.03..2.15 rows=5 width=43) (actual time=0.070..0.074 rows=5 loops=1)
                                      Join Filter: (ss2.arena_id = subscriptions.arena_id)
                                      ->  HashAggregate  (cost=1.03..1.04 rows=1 width=16) (actual time=0.060..0.061 rows=1 loops=1)
                                            Group Key: subscriptions.arena_id
                                            Batches: 1  Memory Usage: 24kB
                                            ->  Seq Scan on subscriptions  (cost=0.00..1.02 rows=1 width=16) (actual time=0.008..0.009 rows=1 loops=1)
                                                  Filter: (((ends_at IS NULL) OR ((ends_at IS NOT NULL) AND (ends_at > '2024-10-06 01:31:18'::timestamp without time zone))) AND ((stripe_status)::text  'incomplete_expired'::text) AND ((stripe_status)::text  'unpaid'::text) AND ((stripe_status)::text  'past_due'::text) AND ((stripe_status)::text  'incomplete'::text) AND ((type)::text = 'access'::text))
                                      ->  Seq Scan on service_sport ss2  (cost=0.00..1.05 rows=5 width=27) (actual time=0.006..0.007 rows=5 loops=1)
                    ->  Hash  (cost=5.13..5.13 rows=213 width=16) (actual time=0.098..0.099 rows=213 loops=1)
                          Buckets: 1024  Batches: 1  Memory Usage: 18kB
                          ->  Seq Scan on field_time_slots  (cost=0.00..5.13 rows=213 width=16) (actual time=0.023..0.055 rows=213 loops=1)
Planning Time: 8.780 ms
Execution Time: 568.033 ms

This is the *EXPLAIN ANALYSE* from **staging** envinroment:

Sort  (cost=102.30..102.31 rows=2 width=405) (actual time=85.416..85.430 rows=1 loops=1)
  Sort Key: (sec_to_gc(cube_distance((ll_to_earth((addresses.latitude)::double precision, (addresses.longitude)::double precision))::cube, '(3491544.0649759113, -4339378.172513269, -3108045.069568795)'::cube)))
  Sort Method: quicksort  Memory: 25kB
  ->  GroupAggregate  (cost=101.18..102.29 rows=2 width=405) (actual time=85.406..85.420 rows=1 loops=1)
        Group Key: arenas.id, addresses.latitude, addresses.longitude, addresses.zip_code, addresses.street, addresses.number, addresses.complement, addresses.district, cities.name, states.name, states.uf
        ->  Sort  (cost=101.18..101.19 rows=2 width=397) (actual time=65.212..66.575 rows=10800 loops=1)
              Sort Key: arenas.id, addresses.latitude, addresses.longitude, addresses.zip_code, addresses.street, addresses.number, addresses.complement, addresses.district, cities.name, states.name, states.uf
              Sort Method: quicksort  Memory: 3448kB
              ->  Nested Loop  (cost=72.78..101.17 rows=2 width=397) (actual time=34.249..43.485 rows=10800 loops=1)
                    ->  Index Only Scan using sports_pkey on sports  (cost=0.15..8.17 rows=1 width=32) (actual time=0.019..0.024 rows=1 loops=1)
                          Index Cond: (code = 'BEACH_TENNIS'::text)
                          Heap Fetches: 1
                    ->  Hash Join  (cost=72.63..92.98 rows=2 width=429) (actual time=34.228..41.163 rows=10800 loops=1)
                          Hash Cond: (ss2.arena_id = arenas.id)
                          ->  Seq Scan on service_sport ss2  (cost=0.00..17.50 rows=750 width=48) (actual time=0.004..0.011 rows=5 loops=1)
                          ->  Hash  (cost=72.62..72.62 rows=1 width=493) (actual time=34.210..34.221 rows=2700 loops=1)
                                Buckets: 4096 (originally 1024)  Batches: 1 (originally 1)  Memory Usage: 1053kB
                                ->  Nested Loop  (cost=50.45..72.62 rows=1 width=493) (actual time=0.641..31.447 rows=2700 loops=1)
                                      ->  Nested Loop  (cost=50.30..72.44 rows=1 width=452) (actual time=0.629..23.566 rows=2700 loops=1)
                                            ->  Nested Loop Semi Join  (cost=50.01..64.14 rows=1 width=468) (actual time=0.617..7.491 rows=2700 loops=1)
                                                  Join Filter: (arenas.id = subscriptions.arena_id)
                                                  ->  Hash Join  (cost=49.88..61.77 rows=8 width=452) (actual time=0.605..2.421 rows=2700 loops=1)
                                                        Hash Cond: (field_time_slots.arena_id = arenas.id)
                                                        ->  Seq Scan on field_time_slots  (cost=0.00..10.23 rows=423 width=16) (actual time=0.004..0.047 rows=423 loops=1)
                                                        ->  Hash  (cost=49.86..49.86 rows=1 width=436) (actual time=0.589..0.596 rows=18 loops=1)
                                                              Buckets: 1024  Batches: 1  Memory Usage: 14kB
                                                              ->  Nested Loop  (cost=9.47..49.86 rows=1 width=436) (actual time=0.317..0.582 rows=18 loops=1)
                                                                    Join Filter: (arenas.id = contacts.contactable_id)
                                                                    Rows Removed by Join Filter: 18
                                                                    ->  Nested Loop  (cost=9.47..48.81 rows=1 width=420) (actual time=0.312..0.543 rows=18 loops=1)
                                                                          Join Filter: (services.id = service_sport.service_id)
                                                                          ->  Nested Loop  (cost=9.32..48.58 rows=1 width=412) (actual time=0.299..0.474 rows=58 loops=1)
                                                                                Join Filter: (services.id = field_service.service_id)
                                                                                ->  Nested Loop  (cost=0.57..34.38 rows=1 width=404) (actual time=0.289..0.377 rows=34 loops=1)
                                                                                      Join Filter: (arenas.id = fields.arena_id)
                                                                                      Rows Removed by Join Filter: 30
                                                                                      ->  Nested Loop  (cost=0.42..26.21 rows=1 width=388) (actual time=0.279..0.335 rows=16 loops=1)
                                                                                            Join Filter: (services.id = service_prices.service_id)
                                                                                            Rows Removed by Join Filter: 76
                                                                                            ->  Nested Loop  (cost=0.42..25.01 rows=1 width=380) (actual time=0.272..0.305 rows=4 loops=1)
                                                                                                  Join Filter: (addresses.addressable_id = arenas.id)
                                                                                                  ->  Nested Loop  (cost=0.28..16.84 rows=1 width=268) (actual time=0.262..0.288 rows=4 loops=1)
                                                                                                        Join Filter: (addresses.addressable_id = services.arena_id)
                                                                                                        Rows Removed by Join Filter: 4
                                                                                                        ->  Index Scan using addresses_addressable_type_addressable_id_index on addresses  (cost=0.14..8.67 rows=1 width=244) (actual time=0.254..0.272 rows=2 loops=1)
                                                                                                              Index Cond: ((addressable_type)::text = 'App\Models\Arena'::text)
                                                                                                              Filter: (((type)::text = 'COMMERCIAL'::text) AND (sec_to_gc(cube_distance((ll_to_earth((latitude)::double precision, (longitude)::double precision))::cube, '(3491544.0649759113, -4339378.172513269, -3108045.069568795)'::cube))   Index Scan using services_arena_id_name_deleted_at_unique on services  (cost=0.14..8.16 rows=1 width=24) (actual time=0.004..0.006 rows=4 loops=2)
                                                                                                              Filter: ((NOT is_private) AND ((status)::text = 'A'::text))
                                                                                                              Rows Removed by Filter: 1
                                                                                                  ->  Index Scan using arenas_pkey on arenas  (cost=0.14..8.16 rows=1 width=112) (actual time=0.003..0.003 rows=1 loops=4)
                                                                                                        Index Cond: (id = services.arena_id)
                                                                                                        Filter: ((approved_at IS NOT NULL) AND (business_hours_data IS NOT NULL) AND (deleted_at IS NULL))
                                                                                            ->  Seq Scan on service_prices  (cost=0.00..1.12 rows=6 width=8) (actual time=0.002..0.004 rows=23 loops=4)
                                                                                                  Filter: is_default
                                                                                      ->  Index Scan using fields_arena_id_name_deleted_at_unique on fields  (cost=0.14..8.16 rows=1 width=16) (actual time=0.001..0.002 rows=4 loops=16)
                                                                                            Filter: ((status)::text = 'A'::text)
                                                                                ->  Bitmap Heap Scan on field_service  (cost=8.76..14.14 rows=5 width=8) (actual time=0.001..0.001 rows=2 loops=34)
                                                                                      Recheck Cond: (service_id = service_prices.service_id)
                                                                                      Heap Blocks: exact=34
                                                                                      ->  Bitmap Index Scan on field_service_arena_id_service_id_field_id_unique  (cost=0.00..8.76 rows=5 width=0) (actual time=0.001..0.001 rows=2 loops=34)
                                                                                            Index Cond: (service_id = service_prices.service_id)
                                                                          ->  Index Only Scan using service_sport_service_id_sport_code_unique on service_sport  (cost=0.15..0.22 rows=1 width=40) (actual time=0.001..0.001 rows=0 loops=58)
                                                                                Index Cond: ((service_id = field_service.service_id) AND (sport_code = 'BEACH_TENNIS'::text))
                                                                                Heap Fetches: 18
                                                                    ->  Seq Scan on contacts  (cost=0.00..1.04 rows=1 width=16) (actual time=0.001..0.001 rows=2 loops=18)
                                                                          Filter: (is_main AND ((contactable_type)::text = 'App\Models\Arena'::text))
                                                                          Rows Removed by Filter: 1
                                                  ->  Index Scan using subscriptions_arena_id_stripe_status_index on subscriptions  (cost=0.14..0.28 rows=1 width=16) (actual time=0.001..0.001 rows=1 loops=2700)
                                                        Index Cond: (arena_id = field_time_slots.arena_id)
                                                        Filter: (((ends_at IS NULL) OR ((ends_at IS NOT NULL) AND (ends_at > '2024-10-06 01:31:18'::timestamp without time zone))) AND ((stripe_status)::text  'incomplete_expired'::text) AND ((stripe_status)::text  'unpaid'::text) AND ((stripe_status)::text  'past_due'::text) AND ((stripe_status)::text  'incomplete'::text) AND ((type)::text = 'access'::text))
                                            ->  Index Scan using cities_ibge_code_unique on cities  (cost=0.28..8.30 rows=1 width=24) (actual time=0.005..0.005 rows=1 loops=2700)
                                                  Index Cond: (ibge_code = addresses.city_ibge_code)
                                      ->  Index Scan using states_pkey on states  (cost=0.15..0.18 rows=1 width=56) (actual time=0.002..0.002 rows=1 loops=2700)
                                            Index Cond: (ibge_code = cities.state_ibge_code)
Planning Time: 6.426 ms
Execution Time: 85.641 ms

Any idea why this might be happening? I didn't focus on performance because we're in an early stage and thought it wouldn't matter now because we have so little data. We've already tried upgrading the server instance from a db.t3.micro to db.t3.small, nothing changed. We've also tried restoring it in another availability zone, nothing happened. I tried restoring the production dump locally and running the query, and it the costs 6000, but still, a lot less then 50000. When running the query in a local dev environment, it also costs 100. Obviously, I can rewrite and improve this query, I'll do it very soon. But I'd really like to understand what is happening here. EDIT: amount of data

WITH tbl AS
  (SELECT table_schema,
          TABLE_NAME
   FROM information_schema.tables
   WHERE TABLE_NAME not like 'pg_%'
     AND table_schema in ('public'))
SELECT sum((xpath('/row/c/text()', query_to_xml(format('select count(*) as c from %I.%I', table_schema, TABLE_NAME), FALSE, TRUE, '')))[1] ::text::int) AS rows_n
FROM tbl
ORDER BY rows_n DESC;

Production amount of rows in each table: https://pastebin.com/7gj5PxRw Staging amount of rows in each table: https://pastebin.com/507x3mP0 EDIT 2: Laurenz's suggestion actually helped, and the Execution Time has improved, however, I still can't understand why the plan had so many rows. I'd really like to deep dive into that. For now, I've separated this query into 2 distinct queries, and the performance has abruptly increased.

I only have direct access to the SQL Server. How can I check if it's RDS, RDS Custom (which allow to connect to the VM), or installed manually on a VM (EC2)?

I have a PostgreSQL database that is running on AWS. I also have a database running locally on a Raspberry Pi, that is supposed to replicate the changes from the AWS one. I was thinking of using Postgres' logical replication feature, but I am not sure if it is doable without setting a static IP address or a DNS entry for my Raspberry Pi, which I don't want to do. Is it doable? Is it a good idea? I looked at AWS IoT Core, which would be AWS's solution for this problem, but I feel like it is an overkill.

I am trying to figure out a way to have 2 Aurora MySQL clusters in a Multi master setup where both clusters can serve traffic, and the data should be in sync between both clusters even if it is eventually consistent and needs to be done manually. The issue I am facing is that we have a column with AUTO_INCREMENT property and it is the primary key. If we are performing write operations on both the clusters, with ongoing replication from one cluster to the other, there could be a chance that there would be conflict in the auto increment column. Is there any workaround to this problem?

I've been using Amazon RDS instances for Postgres databses for a while, but I haven't had to do a lot of upgrading on large databases until recently. I hit an issue where one database was hundreds of gigs large. The upgrade never completes because the instance is oom killed. I've been reading a lot on the AWS website about common practices for [upgrading databases major versions](https://aws.amazon.com/blogs/database/best-practices-for-upgrading-amazon-rds-to-major-and-minor-versions-of-postgresql/) , but I haven't seen anything about large databases oom killing the process and how to avoid. I believe at least one of our databases on this instance have the potential to eventually get up to 500 gigs or so. Obviously scaling instances vertically to that much memory isn't a great solution (and probably doesn't exist). Also, scaling horizontally JUST for memory to upgrade doesn't seem to be a good approach. What is the normal way to handle this?

I created an AWS mySQL RDS instance (free tier), but i am unable to connect to it from workbench or cli. I have enabled public access to the DB while creation and allowed "All traffic" while trying to debug. Following are the DB details - The DB is configured to use the default security group Following is the error i get when i try accessing via cli

MySQL  JS > \connect admin@db.abcd.ap-south-1.rds.amazonaws.com:3306
Creating a session to 'admin@db.abcd.ap-south-1.rds.amazonaws.com:3306'
Enter password: 
MySQL Error 2003 (Can't connect to MySQL server on '..ap-south-1.rds.amazonaws.com' (60)): HY000

I used telnet to check connectivity but got an error -

telnet db.abcd.ap-south-1.rds.amazonaws.com 3306
db.abcd.ap-south-1.rds.amazonaws.com: nodename nor servname provided, or not known

I am new to AWS and this seems like an easy thing. Can you pls help me with fixing this?

***Scenario:*** Every day you receive a SQL Server 2016 database backup set that is composed of multiple partitioned .bak files of size 1GB each: backup1.bak (1GB) backup2.bak (1GB) backup3.bak (1GB) ... backup20.bak (1GB) This database is consistently growing, but the backups will always be partitioned by 1GB size. 20GB database contains 20 * 1GB partitions, 21GB database contains 21 * 1GB partitions and so on. You DO NOT have control of the backup producer. You need to restore this SQL Server instance on Amazon RDS for SQL Server. ***Problem:*** As noted in Amazon RDS documentation : > You can't back up to or restore from more than 10 backup files at the > same time. ***Considerations:*** Also noted in that same doc: > RDS supports native restores of databases up to 16 TB ***Question:*** It seems like combining these partitioned backup files into one .bak file is the best approach for use on RDS. As the consumer, HOW do you combine these .bak files into one when you have no control of how they are produced? The first solution that comes to mind is to spin up an EC2/SQLServer instance, restore the database from the partitions, perform a backup as one file, load one file to S3, restore RDS from one file on S3. But is this really the best option? Is there a more elegant way?

In my db it is 32 gb ram but it fill now 24 gb rapidly. After hiiting the 32 mark the db restarted automatically . I have set the buufer size 27gb. then also it increasing.How do i free up the ram aor any solution reagarding to it. And my db server is on aws server. I have set the innodbbuffer size27gb.

We have an r6g.large write instance in our cluster which is perfectly enough for us during off-peak hours which is about 16 hours a day on workdays. But during the 8 peak hours we would need a larger instance let's say an r6g.2xlarge minimum. Between the peak and off-peak hours it's not allowed to have any downtime! Our solution would be to have the r6g.large write instance and use the r6g.2xlarge reader instance during the peak time. But in the off-peak hours the reader should stop completely. But to our knowledge this on demand 6g.2xlarge reader instance can't be stopped this is a limitation of the stop function (right?). So what's the best practice to do this? Delete the reader instance every day and create a new one? Is it a good way to do it? Seems a bit odd because we lose all the metrics and reports but running it 0-24 costs a lot. (We tried serverless V2 but it's buggy and expensive, it has random spikes and doesn't scale down to 0.5-1 ACU we tried everything.) Additional info: Our database is 700gb which may take a lot to start. Does it have any associated cost with it (like data transfer usage as Aurora is multi AZ)?

What I want to do: > Install Docker and use a Postgres image to create a database to use with a Nest.js tut What i have tried: I have installed docker using: yum install docker Which yields when I do docker --version: Docker version 20.10.17, build 100c701 But when I try systemctl start docker.service I get: > System has not been booted with systemd as init system (PID 1). Can't operate. Failed to connect to bus: Host is down I remember using installing docker on a mac and it was a cinch Is it really this hard to install just because of AL2023?

We are duplicating the data inside a rather Amazon RDS MySQL big table, about 2 GB of data. We are doing that by having batches of 30 entries moved at a time (INSERT INTO etc SELECT FROM ... LIMIT x OFFSET y). The script is working correctly. However, on my local machine with similar anonymized data took about 4 minutes. In RDS it seems to be taking hours. When investigating seems that RAM and processor are not under stress. However, the write seems to be limited at 15 MB / s. The volume is a gp2, that should have at least 128 MB / s. What else can cap that speed?