By using AWS re:Post, you agree to the Terms of Use
/Database/Questions/
Questions in Database
Sort by most recent
  • 1
  • 90 / page

Browse through the questions and answers listed below or filter and sort to narrow down your results.

DMS giving error while replicating a table

I am doing refactoring from Oracle to postgres. All the tables data migrated from Oracle to postgressql. I am having issue with one table, below error found in postgres log files. Please let me know how to fix this error. 2022-07-05 20:02:07 UTC::@:[4551]:LOG: checkpoint starting: time 2022-07-05 20:02:07 UTC::@:[4551]:LOG: checkpoint complete: wrote 1 buffers (0.0%); 0 WAL file(s) added, 1 removed, 0 recycled; write=0.106 s, sync=0.003 s, total=0.148 s; sync files=1, longest=0.003 s, average=0.003 s; distance=65536 kB, estimate=119791 kB 2022-07-05 20:07:07 UTC::@:[4551]:LOG: checkpoint starting: time 2022-07-05 20:07:07 UTC::@:[4551]:LOG: checkpoint complete: wrote 1 buffers (0.0%); 0 WAL file(s) added, 0 removed, 1 recycled; write=0.106 s, sync=0.003 s, total=0.136 s; sync files=1, longest=0.003 s, average=0.003 s; distance=65536 kB, estimate=114365 kB 2022-07-05 20:09:00 UTC:10.0.0.192(44794):toype@bm:[29897]:ERROR: permission denied for table job_log 2022-07-05 20:09:00 UTC:10.0.0.192(44794):toype@bm:[29897]:CONTEXT: SQL statement "UPDATE central.job_log SET finished = --(CLOCK_TIMESTAMP() AT TIME ZONE COALESCE(CURRENT_SETTING('aws_oracle_ext.tz', TRUE), 'UTC'))::TIMESTAMP(0), current_timestamp, job_state = PJOBSTATUS, task_state = PTASKSTATUS WHERE schema = PSCHEMA AND job_id = PJOB_ID AND finished IS NULL" PL/pgSQL function central.job_logger(text,double precision,text,text,double precision,double precision) line 62 at SQL statement SQL statement "CALL central.job_logger(SESSION_USER, new.job_id, new.job_type, new.requester_id, new.job_state, new.task_state)" PL/pgSQL function "job_trig$job"() line 4 at CALL COPY job, line 1: "33984,"finsum","auto",4,"finsum",4,"<parameter name=\"user\" value=\"NSC\" /><parameter name=\"netId..." 2022-07-05 20:09:00 UTC:10.0.0.192(44794):toype@bm:[29897]:STATEMENT: COPY "toype"."job" FROM STDIN WITH DELIMITER ',' CSV NULL 'attNULL' ESCAPE '\' 2022-07-05 20:12:07 UTC::@:[4551]:LOG: checkpoint starting: time 2022-07-05 20:12:08 UTC::@:[4551]:LOG: checkpoint complete: wrote 12 buffers (0.0%); 0 WAL file(s) added, 0 removed, 1 recycled; write=1.212 s, sync=0.004 s, total=1.241 s; sync files=6, longest=0.004 s, average=0.001 s; distance=65558 kB, estimate=109485 kB
0
answers
0
votes
4
views
asked 7 hours ago

S3 Batch Operations job fails due to missing VersionId

I created a POC Batch Operation Job that I want to Invoke Lambda function. This Lambda will get the file, do transformation, copy transformed file into new bucket, and delete file from old bucket upon completion. The Batch job fails before invoking my lambda and report csv shows following errors: ``` eceeecom-5732-poc-old,emailstore/0079d564-dccc-4066-a42d-8d9113097d02,,failed,400,InvalidRequest,Task failed due to missing VersionId eceeecom-5732-poc-old,emailstore/00975dec-f64b-4932-a1e8-9ec1284f76bb,,failed,400,InvalidRequest,Task failed due to missing VersionId ``` My manifest.json ``` { "sourceBucket" : "poc-old", "destinationBucket" : "arn:aws:s3:::poc-new", "version" : "2016-11-30", "creationTimestamp" : "1656633600000", "fileFormat" : "CSV", "fileSchema" : "Bucket, Key, VersionId, IsLatest, IsDeleteMarker, Size, LastModifiedDate, ETag, StorageClass, IsMultipartUploaded, ReplicationStatus, EncryptionStatus, ObjectLockRetainUntilDate, ObjectLockMode, ObjectLockLegalHoldStatus, IntelligentTieringAccessTier, BucketKeyStatus, ChecksumAlgorithm", "files" : [ { "key" : "emailstore/eceeecom-5732-poc-old/emailstore-inventory-config/data/b84c7842-bc58-40b9-afb0-622060853c8a.csv.gz", "size" : 623, "MD5checksum" : "XYZ" } ] } ``` When I unzip above csv.gz file, I observe: ``` "eceeecom-5732-poc-old","emailstore/0079d564-dccc-4066-a42d-8d9113097d02","","true","false","150223","2022-06-30T21:22:06.000Z","60d3815bd0e85e3b689139b6938362b4","STANDARD","false","","SSE-S3","","","","","DISABLED","" "eceeecom-5732-poc-old","emailstore/00975dec-f64b-4932-a1e8-9ec1284f76bb","","true","false","46054","2022-06-30T21:22:06.000Z","214c15f193c58defbbf938318e103aed","STANDARD","false","","SSE-S3","","","","","DISABLED","" ``` Clearly there is no **Version Id** and that is a culprit, but how can I make Inventory configuration not ask for Version Id to be added to manifest? When I was reading about Inventory list, it said that Version ID field is not included if the list is only for the current version of objects: https://docs.aws.amazon.com/AmazonS3/latest/userguide/storage-inventory.html
1
answers
0
votes
24
views
asked 4 days ago

Amazon Athena error on querying DynamoDB exported data

**Background** We've configured an export to s3 from dynamodb using the native dynamodb s3 export, and ION as the format output. After this, we've created a table in Athena ``` CREATE EXTERNAL TABLE export_2022_07_01_v4 ( `_PK` STRING, URL STRING, Item struct< `_PK`:string, URL:string > ) ROW FORMAT SERDE 'com.amazon.ionhiveserde.IonHiveSerDe' WITH SERDEPROPERTIES ( "ignore_malformed_ion" = "true" ) STORED AS ION LOCATION '...'; ``` Querying this works all right for small simple queries, but attempting to produce a full output with ``` UNLOAD ( SELECT Item.URL FROM "helioptileexports"."export_2022_07_01_v4" WHERE Item.URL IS NOT NULL ) to '...' WITH (format = 'TEXTFILE') ``` Results in this error ``` HIVE_CURSOR_ERROR: Syntax error at line 1 offset 2: invalid syntax [state:STATE_BEFORE_ANNOTATION_DATAGRAM on token:TOKEN_SYMBOL_OPERATOR] This query ran against the "helioptileexports" database, unless qualified by the query. Please post the error message on our forum or contact customer support with Query Id: f4ca5812-1194-41f1-bfda-00a1a5b2471b ``` **Questions** 1. Is there a way to make Athena more tolerant of formatting errors on specific files? As shown in the example, we are attempting without success to use `ignore_malformed_ion`. Is there anything beyond that that can be done? 2. Is this a bug on DynamoDB ION export process? 3. Is there any mechanism or logging to identify the files which have the malformed data and remove them?
0
answers
0
votes
15
views
asked 5 days ago

Aurora upgrade 2 to 3 / MySql 5.7 to 8.0: potential bug in pre-check validation (depreciated words)

We have noticed that the pre-checks for the upgrade of MySQL 5.7 to MySQL 8 are having issues with character combinations that "resemble" depreciated words. For example, the depreciated "Group By ... DESC" is one of those constructs "[https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_UpgradeDBInstance.MySQL.html#USER_UpgradeDBInstance.MySQL.57to80Prechecks]()" "There must be no queries and stored program definitions from MySQL 8.0.12 or lower that use ASC or DESC qualifiers for GROUP BY clauses." While our stored procedures use Group by's, there is no associated "DESC" word with them. However, the character sequence does appear in the stored procedure in various forms: * There is a call to another stored procedure called "update_fc**desc**ription();". It has the characters "desc" within the name * There are columns in the queries (table columns) with the name "blah**Desc**riptionblah" * There is a block comment that has the word "**Desc**ription:" that describes the stored procedure (documentation) However, there are no "DESC" words associated with the "Group by". For testing, * I deleted the comments word, and that issue no longer appeared as an error * I renamed the call to the other stored procedure update_fc**desc**ription(); to update_fc**dxexscxrixp**tion();, and that issue no longer appeared as an error * The columns that have the characters "desc" I couldn't work around without a lot of changing to the stored procedure It seems that there is a Stackoverflow question outlining this behavior too: [https://stackoverflow.com/questions/71412470/aws-mysql-aurora-major-version-2-3-upgrade-pre-checks-obsolete-procedure]() Also, a "re:Post" question too: [https://repost.aws/questions/QUWJzlcpitRoGM0woZVOylBQ/aurora-2-to-3-mysql-5-7-to-8-0-upgrade-pre-check-incorrect-validation-on-store-procedure]() This is clearly a bug in the pre-check process and is limiting our upgrade from MySQL 5.7 to 8. Any updates on this being fixed/addressed? Thank you.
1
answers
0
votes
55
views
asked 9 days ago

Best practice for restoring an RDS Aurora snapshot into a CloudFormation-built solution

Hi experts, I'm looking for best practices in restoring data into a cloudformation-built system. I've got extensive cloudformation that builds a solution, including an RDS Aurora Serverless database cluster. Now I want to restore that RDS server from a snapshot. - I notice that restoring through the console creates a new cluster, and this is no longer in the cloudformation stack, so doesn't get updates (plus my existing RDS instance is retained) - I found the property `DbSnapshotIdentifier` in DBInstance along with this answer https://repost.aws/questions/QUGElgNYmhTEGzkgTUVP21oQ/restoring-rds-snapshot-with-cloud-formation, however I see in the docs that I can never change it after the initial deployment (it seems it will delete the DB if I do - see below). This means I could never restore more than once. https://docs.aws.amazon.com/AWSCloudFormation/latest/UserGuide/aws-properties-rds-database-instance.html#cfn-rds-dbinstance-dbsnapshotidentifier - I also found a StackOverflow post from 6 years ago with the same question but no real answers. https://stackoverflow.com/questions/32255309/how-do-i-restore-rds-snapshot-into-a-cloudformation For the DbSnapshotIdentifier point above, here's the relevant wording from their docs that concerns me: > After you restore a DB instance with a DBSnapshotIdentifier property, you must specify the same DBSnapshotIdentifier property for any future updates to the DB instance. When you specify this property for an update, the DB instance is not restored from the DB snapshot again, and the data in the database is not changed. However, if you don't specify the DBSnapshotIdentifier property, an empty DB instance is created, and the original DB instance is deleted It seems this should be simple but it's not. Please don't tell me I need to fall back to using `mysqlbackup` ¯\\_(ツ)\_/¯ Thanks in advance, Scott
1
answers
0
votes
30
views
asked 9 days ago

How can I speed up queries?

Hi, I just finished migrating a huge dataset to Timestream and I'm having a problem with query time slowing down dramatically as the table size grew. During beta testing everything worked fine. Queries were maybe a bit slower than i would have like, at around 2-3 seconds. However now that we have multiple terabytes of data in the table, queries are very slow at around 7-9 seconds. Those 7-9 seconds eventually go down to around 2 seconds if I keep running the same query. I assume this is because of caching and warming up of resources. I'm saving the DeviceId as a dimension, and a number from 1-255 as the measure name. I'm using both of these as predicates, as well as a time range, some other dimensions and a NOT NULL check on the value. `SELECT measure_name, value_double, time, DeviceId, DimensionA, DimensionB` `WHERE DeviceId = 'xxxxxxx'` `AND measure_name = '112'` `AND time BETWEEN TIMESTAMP '2022-06-01 00:00:00' AND TIMESTAMP '2022-06-07 00:00:00'` `AND value_double IS NOT NULL` `AND DimensionA = '0'` `AND DimensionB = '1'` `ORDER BY time DESC` `LIMIT 1000000000` I'm only being metered for the data I need, so I Timestream shouldn't be scanning unnecessary data. Yet it seems like Timestream is bad at pruning data quickly, because every query spends a significant amount of time loading the first page, while every following page is retrieved almost immediately. Do you have any idea how I can speed this up? We migrated to Timestream from Azure Table Storage, where the queries were virtually instant, and we might have to drop Timestream altogether after months of development because of the query latency.
1
answers
0
votes
29
views
asked 11 days ago

AWS 2dsphere limitation

Hi all, I am using DocumentDB with Mongo support on AWS and we are having documents that include geolocation. We have read the documentation of AWS for mongo support [here](https://docs.aws.amazon.com/documentdb/latest/developerguide/mongo-apis.html) but despite it says that it's supported we are receiving error during the creation of the index. The error we are getting when creating the index is : "*Command createIndexes failed : Index type not supported : 2dsphere*" The c# code that should generate the index is the below : ``` var prefixIndexName = nameof(Account.Address)+"."+nameof(Account.Address.Geolocation); if (!accountCollection.ExistIndex(prefixIndexName+"_2dsphere")) { Console.WriteLine("Seeding Geolocation Geo2DSphere Index ..."); var geoLocation = new StringFieldDefinition<Account>(prefixIndexName); var indexDefinition = new IndexKeysDefinitionBuilder<Account>().Geo2DSphere(geoLocation); var indexModel = new CreateIndexModel<Account>(indexDefinition, new CreateIndexOptions { Background = false }); accountCollection.CreateIndex(indexModel); } ``` The field that we are trying to add in the index is the "Address" and it looks like this : ``` "Address": { "CountryId": number, "PostCode": string, "AddressLine1": string, "AddressLine2": string, "City": string, "State": string, "Geolocation": { "type": "Point", "coordinates": decimal[] // e.g. [xx.xxxxxxx, xx.xxxxxxx] } } ``` The code is working on my local MongoDB installation, so I believe I am missing something to make it run on AWS. Any help you could provide is valuable, thanks in advance for your time!
0
answers
2
votes
13
views
asked 12 days ago

EC2s Development and Production Environments, Isolation, VPN, API GW, Private and Public Endpoints with RDS and Data Sanitization

Hi Everyone, I have the following idea for an infrastructure architecture in AWS but I believe that I need some help with clarifying several issues which I believe, the best answers to will come from here. I am thinking about the following layout: In production: 1. an EC2 with Apache that provides service portal for web users 2. an RDS for the sake of the portal 3. another EC2 with Apache and business-logic php application as CRM 4. the same RDS will be used by the CRM application as well In development: The same layout, with 1 EC2 for web client services, 1 EC2 for the sake of developing the CRM and an RDS for the data I thought about using two different VPCs for the sake of this deployment. I need data replication with sanitization from the production RDS to the development RDS (thinking either by SQL procedures or other method, didn't think about that yet, but I know I need it to be like that since I have no desire to enable my developers to work with real client data). Both the production and development CRM EC2s are exposing Web APIs Both the production and development service portals are exposing Web APIs Both the production and development CRM and service portal are web accessible For the development environment I want to enable access (Web and Web APIs) only through VPN, hence, I want my developers to connect with VPN clients to the development VPC with VPN and work against both EC2s on-top of that connection. I also want them to be able to test all APIs and thinking about setting an API Gateway on that private endpoint. For the production environment, I want to enable access (Web and Web APIs) to the CRM EC2 through VPN, hence, I want my business units to connect with their VPN clients to a production VPN gateway, and work against the CRM on-top of that connection. I don't want to expose my CRM to the world. For the production environment, I want to enable everyone on the internet (actually, not everyone, I want to Geo-Block access to the service portal, hence, I do believe I need Amazon CDN services enabled for that cause) to access the service portal, still, I want to enable an API Gateway for the Web APIs that are exposed by this service portal EC2. I've been reading about Amazon API gateway (and API Gateway Cache) and it's resource policy and VPC endpoints with their own security groups and Amazon Route 53 resolver for the sake of VPN connections. I also been reading lots about Amazon virtual private gateway and a private and public endpoints, but, I still can't figure-out with element comes to play where and how the interactions should be design for those elements. I believe I also need Amazon KMS for the keys, certificates and passwords, but, I'm still trying to figure out the right approach for the above, so, I'm leaving the KMS part for the end. of course I'm thinking about security at the top of my concerns, so, I do believe all connectivity's should be harden in-between the elements, is only using ACLs is the right way to go!? I would really appreciate the help
1
answers
0
votes
35
views
asked 12 days ago

Describe table in Athena fails with insufficient lake formation permissions

When I try to run the following query via the Athena JDBC Driver ```sql describe gitlab.issues ``` I get the following error: > [Simba][AthenaJDBC](100071) An error has been thrown from the AWS Athena client. FAILED: SemanticException Unable to fetch table gitlab. Insufficient Lake Formation permission(s) on gitlab (Service: AmazonDataCatalog; Status Code: 400; Error Code: AccessDeniedException; Request ID: be6aeb1b-fc06-410d-9723-2df066307b35; Proxy: null) [Execution ID: a2534d22-c4df-49e9-8515-80224779bf01] the following query works: ```sql select * from gitlab.issues limit 10 ``` The role that is used has the `DESCRIBE` permission on the `gitlab` database and `DESCRIBE, SELECT` permissions on the table `issues`. It also has the following IAM permissions: ```json { "Version": "2012-10-17", "Statement": [ { "Action": [ "athena:BatchGetNamedQuery", "athena:BatchGetQueryExecution", "athena:CreatePreparedStatement", "athena:DeletePreparedStatement", "athena:GetDataCatalog", "athena:GetDatabase", "athena:GetNamedQuery", "athena:GetPreparedStatement", "athena:GetQueryExecution", "athena:GetQueryResults", "athena:GetQueryResultsStream", "athena:GetTableMetadata", "athena:GetWorkGroup", "athena:ListDatabases", "athena:ListNamedQueries", "athena:ListPreparedStatements", "athena:ListDataCatalogs", "athena:ListEngineVersions", "athena:ListQueryExecutions", "athena:ListTableMetadata", "athena:ListTagsForResource", "athena:ListWorkGroups", "athena:StartQueryExecution", "athena:StopQueryExecution", "athena:UpdatePreparedStatement" ], "Resource": "*", "Effect": "Allow" }, { "Action": [ "glue:BatchGetCustomEntityTypes", "glue:BatchGetPartition", "glue:GetCatalogImportStatus", "glue:GetColumnStatisticsForPartition", "glue:GetColumnStatisticsForTable", "glue:GetCustomEntityType", "glue:GetDatabase", "glue:GetDatabases", "glue:GetPartition", "glue:GetPartitionIndexes", "glue:GetPartitions", "glue:GetSchema", "glue:GetSchemaByDefinition", "glue:GetSchemaVersion", "glue:GetSchemaVersionsDiff", "glue:GetTable", "glue:GetTableVersion", "glue:GetTableVersions", "glue:GetTables", "glue:GetUserDefinedFunction", "glue:GetUserDefinedFunctions", "glue:ListCustomEntityTypes", "glue:ListSchemaVersions", "glue:ListSchemas", "glue:QuerySchemaVersionMetadata", "glue:SearchTables" ], "Resource": "*", "Effect": "Allow" }, { "Condition": { "ForAnyValue:StringEquals": { "aws:CalledVia": "athena.amazonaws.com" } }, "Action": [ "s3:GetBucketLocation", "s3:GetObject", "s3:ListBucket", "s3:ListBucketMultipartUploads", "s3:ListMultipartUploadParts", "s3:AbortMultipartUpload", "s3:PutObject" ], "Resource": [ "arn:aws:s3:::aws-athena-query-results-123456789012-eu-west-1", "arn:aws:s3:::aws-athena-query-results-123456789012-eu-west-1/*", "arn:aws:s3:::aws-athena-federation-spill-123456789012-eu-west-1", "arn:aws:s3:::aws-athena-federation-spill-123456789012-eu-west-1/*" ], "Effect": "Allow" }, { "Action": [ "lakeformation:CancelTransaction", "lakeformation:CommitTransaction", "lakeformation:DescribeResource", "lakeformation:DescribeTransaction", "lakeformation:ExtendTransaction", "lakeformation:GetDataAccess", "lakeformation:GetQueryState", "lakeformation:GetQueryStatistics", "lakeformation:GetTableObjects", "lakeformation:GetWorkUnitResults", "lakeformation:GetWorkUnits", "lakeformation:StartQueryPlanning", "lakeformation:StartTransaction" ], "Resource": "*", "Effect": "Allow" }, { "Condition": { "ForAnyValue:StringEquals": { "aws:CalledVia": "athena.amazonaws.com" } }, "Action": "lambda:InvokeFunction", "Resource": "arn:aws:lambda:*:*:function:athena-federation-*", "Effect": "Allow" }, { "Condition": { "ForAnyValue:StringEquals": { "aws:CalledVia": "athena.amazonaws.com" } }, "Action": ["s3:GetBucketLocation", "s3:GetObject", "s3:ListBucket"], "Resource": "*", "Effect": "Allow" } ] } ``` even if I make the role a LakeFormation Admin, Database Creator, assign Super Permissions to the table and database and add the AdministratorAccess IAM Policy to the role it still fails.
0
answers
0
votes
25
views
asked 13 days ago
0
answers
0
votes
30
views

IAM poilcy for an user to access Enhanced Monitoring for RDS.

I am trying to create an IAM user that will have least privileges to be able to view enhanced monitoring for a particular RDS database. I have created a ROLE (Enhanced Monitoring) and attached a managed policy to it:'AmazonRDSEnhancedMonitoringRole'. This role is passed to RDS database using the passrole permission. The policy that I am attaching to this IAM user is as below: ``` { "Version": "2012-10-17", "Statement": [ { "Sid": "VisualEditor0", "Effect": "Allow", "Action": [ "cloudwatch:PutMetricData", "rds:*", "cloudwatch:GetMetricData", "iam:ListRoles", "cloudwatch:GetMetricStatistics", "cloudwatch:DeleteAnomalyDetector", "cloudwatch:ListMetrics", "cloudwatch:DescribeAnomalyDetectors", "cloudwatch:ListMetricStreams", "cloudwatch:DescribeAlarmsForMetric", "cloudwatch:ListDashboards", "ec2:*", "cloudwatch:PutAnomalyDetector", "cloudwatch:GetMetricWidgetImage" ], "Resource": "*" }, { "Sid": "VisualEditor1", "Effect": "Allow", "Action": [ "iam:GetRole", "iam:PassRole", "cloudwatch:*" ], "Resource": [ "arn:aws:cloudwatch:*:accountnumber:insight-rule/*", "arn:aws:iam::accountnumber:role/Enhanced-Monitoring", "arn:aws:rds:us-east-1:accountnumber:db:dbidentifier" ] } ] } ``` As you can see, I have given almost every permission to this user, but still I am getting 'Not Authorized' error on the IAM user RDS dashboard for enhanced monitoring, although cloudwatch logs are displaying normally. I am following this guide (https://docs.aws.amazon.com/IAM/latest/UserGuide/id_roles_use_passrole.html) for enhanced monitoring of RDS. Refer to example 2 on this page.
1
answers
0
votes
30
views
asked 13 days ago

Connecting TypeScript backend in EC2 to PostgreSQL RDS

I have a TypeScript backend running in a t3.micro EC2 instance. I'm using NPM as the package manager, and TypeORM to seed the database. I have a PostgreSQL database set up in RDS that I'm trying to connect to. In my local dev environment, I didn't have any problem seeding and running the backend and having it connect to RDS. However, in the EC2, it won't finish seeding when running ``` npm run seed ``` which runs the script ``` ts-node -r tsconfig-paths/register src/seeder/seed.ts ``` Running ```npm run migration:run``` runs the script ``` yarn run typeorm migration:run ``` and gives this error: ``` $ ts-node -r tsconfig-paths/register ./node_modules/typeorm/cli.js migration:run Error during migration run: TypeORMError: No connection options were found in any orm configuration files. at new TypeORMError (/home/ec2-user/backend/src/error/TypeORMError.ts:7:9) at ConnectionOptionsReader.<anonymous> (/home/ec2-user/backend/src/connection/ConnectionOptionsReader.ts:46:19) at step (/home/ec2-user/backend/node_modules/typeorm/node_modules/tslib/tslib.js:144:27) at Object.next (/home/ec2-user/backend/node_modules/typeorm/node_modules/tslib/tslib.js:125:57) at fulfilled (/home/ec2-user/backend/node_modules/typeorm/node_modules/tslib/tslib.js:115:62) error Command failed with exit code 1. info Visit https://yarnpkg.com/en/docs/cli/run for documentation about this command. ``` I'm not too experienced with this, but I've tried deleting node_modules, dist, yarn install, npm install. Any answers as to what I'm missing?
1
answers
0
votes
33
views
asked 13 days ago

How To Increase RDS Performance With Backups and Standby

I'm trying to duplicate (or at least approach) the performance of my bare metal machines on RDS. I started with db.m5.xlarge and gp2 storage, which I understand supports Baseline IOPS of 3x storage, with bursts to 3K if storage is less than 1TB (provided there are available burst credits to do so). I understand that the db.m5.xlarge instance itself supports baseline 6,000 IOPS and baseline throughput of 143.75MB. These are the references I used to determine that information: https://docs.aws.amazon.com/AWSEC2/latest/UserGuide/ebs-optimized.html https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Concepts.DBInstanceClass.html https://aws.amazon.com/ebs/volume-types/ My initial performance had storage allocation < 1TB and did not give me what I wanted, so after investigating I increased the Baseline IOPS of the storage to 6,000 IOPS, by increasing the storage allocation of the disk to 2TB. My understanding is that burst credits will be irrelevant in this configuration, since my Base IOPS are already over 3,000. This setup indeed gave me the performance I wanted, comparable to my bare metal machines. Yay! I consider this my baseline configuration. However, on this RDS machine I will also need Backups and Multi-AZ Standby. Adding Backups caused my same test load to take twice as long; adding backups _and_ standby caused my test load to take anywhere from 3.5 - 6x as long to complete. So the main question is, what do I need to do with this configuration to at least get it back somewhere close to my baseline? Looking at the graphs, I'm having a hard time finding any smoking guns. Running the test with Backups and Standby my CloudWatch Total IOPS never exceeded 2000, and Write Throughput was never higher than 73MB/S. Read Throughput did spike over 100MB/s (Max spike 154) a handful of times, but only for 1 minute each (the total test took nearly 90 minutes to complete). Enhanced Monitoring TPS (1 second granularity) never exceeded 5,000, Write Kb/s barely exceeded 100,000, and Read Kb/s peaked somewhere between 7k and 8k. So with the exception of the 5 Read Throughput spikes in the CloudWatch graphs all those metrics seem well within the Baseline performance of the machine and storage. I also tried it with the next instance class (Baseline 12,000 IOPS and 287.5 Baseline bandwidth) with similar results (see below). Other configs I've tried: db.m5.xlarge w/gp2 3TB allocated (9,000 IOPS) db.m5.2xlarge w/gp2 2TB allocated (6,000 IOPS) db.m5.2xlarge w/gp2 3TB allocated (9,000 IOPS) db.m5.xlarge w/io1 <1TB allocated, 6,000 IOPS selected db.m5.xlarge w/io1 <1TB allocated, 9,000 IOPS selected db.m5.2xlarge w/io1 <1TB allocated, 6,000 IOPS selected db.m5.2xlarge w/io1 <1TB allocated, 9,000 IOPS selected All of these configurations provided nearly identical results; the test run took at least an hour and in some cases much, much longer. What am I missing? Or what direction can I go to improve the machine's performance with backups and standby enabled? Thank you!
1
answers
0
votes
37
views
asked 14 days ago

Unit value in RDS CloudWatch Alarm is Null

Hello, I have been trying to obtain a value from an SNS message passed from a CloudWatch Alarm for the DatabaseConnections Metric. The value of the Unit is null hence I cannot parse the message to obtain the DBIdentifier value. Is there a reason why the CloudWatch alarm does not have a value for the Unit? Looking at the AWS RDS documentation, the value for DatabaseConnections Metric, Unit should be Count but the alarm gives null. https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/rds-metrics.html#rds-cw-metrics-instance Below is the message obtained from the SNS message body: ``` { "AlarmName": "awsrds-poc-test-High-DB-Connections", "AlarmDescription": "When the DB-Connections is lower than 1", "AWSAccountId": "<##########>", "AlarmConfigurationUpdatedTimestamp": "2022-06-17T08:54:32.735+0000", "NewStateValue": "ALARM", "NewStateReason": "Threshold Crossed: 1 out of the last 1 datapoints [0.0 (17/06/22 08:50:00)] was less than the threshold (1.0) (minimum 1 datapoint for OK -> ALARM transition).", "StateChangeTime": "2022-06-17T08:55:15.402+0000", "Region": "US East (N. Virginia)", "AlarmArn": "arn:aws:cloudwatch:us-east-1:678932343753:alarm:awsrds-poc-test-High-DB-Connections", "OldStateValue": "OK", "OKActions": [], "AlarmActions": [ "arn:aws:sns:us-east-1:678932343753:sns-alarm" ], "InsufficientDataActions": [], "Trigger": { "MetricName": "DatabaseConnections", "Namespace": "AWS/RDS", "StatisticType": "Statistic", "Statistic": "AVERAGE", "Unit": null, "Dimensions": [ { "value": "<######>", "name": "DBInstanceIdentifier" } ], "Period": 300, "EvaluationPeriods": 1, "DatapointsToAlarm": 1, "ComparisonOperator": "LessThanThreshold", "Threshold": 1, "TreatMissingData": "", "EvaluateLowSampleCountPercentile": "" } } ```
0
answers
0
votes
31
views
asked 15 days ago

How AWS DMS CDC is working successfully without CDC On-premise MSSQL CDC prerequisites config?

We're using DMS for CDC Only migration for the time b/w point in time restore and current DB state, i.e AWS DMS to replicate changes as of the point in time at which you started your bulk load to bring and keep your source and target systems in sync. We've configured AWS DMS (CDC Only) with source endpoint to On-premise SQL Server 2012 (Standard Edition) and Target endpoint with AWS RDS MSSQL 2019 (Standard Edition). By looking into AWS CDC pre-requisites documentation https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Source.SQLServer.html#CHAP_Source.SQLServer.Prerequisites Running below query on on-premise MSSQL 2012 instance returns an error, ref: https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Source.SQLServer.html#CHAP_Source.SQLServer.Prerequisites ``` use uat_testdb EXEC sys.sp_cdc_enable_db ``` Msg 22988, Level 16, State 1, Procedure sp_cdc_enable_db, Line 14 [Batch Start Line 0] This instance of SQL Server is the Standard Edition (64-bit). Change data capture is only available in the Enterprise, Developer, and Enterprise Evaluation editions. It looks ongoing replication CDC feature is supported only from MSSQL standard edition from 2016 SP1 and later. Could you please suggest if there any other workaround to complete CDC without upgrading our on-premise MSSSQL Standard Edition 2012 to Std Edition 2016 / Enterprise Edition? **However, without applying this CDC prerequisites config settings at on-premise DB instance, we can see the ongoing and replication b/w on-premise and RDS DBs instances statistics that shows sync updates of Inserts and Deletes. (Based on the testing target RDS DB instance sync. happening only for Insert and Delete operations of on-premise source db not for any updates) Could you please confirm/clarify if those CDC pre-requisites config are mandatory since we could see the replication successfully on DMS and why we're not getting any error /warning messages on AWS DMS for missing CDC prerequisites config. settings? Thanks.**[]()
1
answers
0
votes
23
views
asked 17 days ago

Athena federated query on PostgresSQL

Hi I am trying to execute queries on a postgresql database I created in AWS. I added a data source to Athena, I created the data source for postgresql and I created the lambda function. In lambda function I set: * default connection string * spill_bucket and spill prefix (I set the same for both: 'athena-spill'. In the S3 page I cannot see any athena-spill bucket) * the security group --> I set the security group I created to access the db * the subnet --> I set one of the database subnet I deployed the lambda function but I received an error and I had to add a new environment variable created with the connection string but named as 'dbname_connection_string'. After adding this new env variable I am able to see the database in Athena but when I try to execute any query on this database as: ``` select * from tests_summary limit 10; ``` I receive this error: ``` GENERIC_USER_ERROR: Encountered an exception[com.amazonaws.SdkClientException] from your LambdaFunction[arn:aws:lambda:eu-central-1:449809321626:function:data-production-athena-connector-nina-lambda] executed in context[retrieving meta-data] with message[Unable to execute HTTP request: Connect to s3.eu-central-1.amazonaws.com:443 [s3.eu-central-1.amazonaws.com/52.219.170.25] failed: connect timed out] This query ran against the "public" database, unless qualified by the query. Please post the error message on our forum or contact customer support with Query Id: 3366bd80-143e-459c-a4da-5350b5ab4a77 ``` What could be causing the problem? Thanks a lot!
2
answers
0
votes
50
views
asked 19 days ago
  • 1
  • 90 / page