How do you know how many Aurora cluster replicas have been running at any given time in the past?

I tried searching for various metrics available in an Aurora cluster on AWS. There appears to be no metric that tells you how many nodes are in a cluster at any given time. There are such metrics by ELB, target group, or ASG.

Optimization: Does the Aurora PostgreSQL database use a slower query plan than a normal PostgreSQL for an identical query?

After the migration of an application and its database from a classic PostgreSQL database to an Amazon Aurora RDS PostgreSQL database (both using version 9.6), we have found that a specific query runs much slower, around 10 times slower, in Aurora than in PostgreSQL.

Both databases have the same configuration, either for hardware or pg_conf.

The query itself is quite simple. It is generated from our backend written in Java and using jOOQ to write the queries:

with "all_acp_ids"("acp_id") as (
    select acp_id from temp_table_de3398bacb6c4e8ca8b37be227eac089
) 
select distinct "public"."f1_folio_milestones"."acp_id", 
    coalesce("public"."sa_milestone_overrides"."team", 
    "public"."f1_folio_milestones"."team_responsible") 
from "public"."f1_folio_milestones" 
left outer join 
    "public"."sa_milestone_overrides" on (
        "public"."f1_folio_milestones"."milestone" = "public"."sa_milestone_overrides"."milestone" 
        and "public"."f1_folio_milestones"."view" = "public"."sa_milestone_overrides"."view" 
        and "public"."f1_folio_milestones"."acp_id" = "public"."sa_milestone_overrides"."acp_id"
)
where "public"."f1_folio_milestones"."acp_id" in (
    select "all_acp_ids"."acp_id" from "all_acp_ids"
)

With temp_table_de3398bacb6c4e8ca8b37be227eac089 being a single column table, f1_folio_milestones (17 million entries) and sa_milestone_overrides (About 1 million entries) are similarly designed tables that have indexes on all the columns used to LEFT OUTER JOIN.

When we run it in the normal PostgreSQL database, it generates the following query plan:

Unique  (cost=4802622.20..4868822.51 rows=8826708 width=43) (actual time=483.928..483.930 rows=1 loops=1)
  CTE all_acp_ids
    ->  Seq Scan on temp_table_de3398bacb6c4e8ca8b37be227eac089  (cost=0.00..23.60 rows=1360 width=32) (actual time=0.004..0.005 rows=1 loops=1)
  ->  Sort  (cost=4802598.60..4824665.37 rows=8826708 width=43) (actual time=483.927..483.927 rows=4 loops=1)
        Sort Key: f1_folio_milestones.acp_id, (COALESCE(sa_milestone_overrides.team, f1_folio_milestones.team_responsible))
        Sort Method: quicksort  Memory: 25kB
        ->  Hash Left Join  (cost=46051.06..3590338.34 rows=8826708 width=43) (actual time=483.905..483.917 rows=4 loops=1)
              Hash Cond: ((f1_folio_milestones.milestone = sa_milestone_overrides.milestone) AND (f1_folio_milestones.view = (sa_milestone_overrides.view)::text) AND (f1_folio_milestones.acp_id = (sa_milestone_overrides.acp_id)::text))
              ->  Nested Loop  (cost=31.16..2572.60 rows=8826708 width=37) (actual time=0.029..0.038 rows=4 loops=1)
                    ->  HashAggregate  (cost=30.60..32.60 rows=200 width=32) (actual time=0.009..0.010 rows=1 loops=1)
                          Group Key: all_acp_ids.acp_id
                          ->  CTE Scan on all_acp_ids  (cost=0.00..27.20 rows=1360 width=32) (actual time=0.006..0.007 rows=1 loops=1)
                    ->  Index Scan using f1_folio_milestones_acp_id_idx on f1_folio_milestones  (cost=0.56..12.65 rows=5 width=37) (actual time=0.018..0.025 rows=4 loops=1)
                          Index Cond: (acp_id = all_acp_ids.acp_id)
              ->  Hash  (cost=28726.78..28726.78 rows=988178 width=34) (actual time=480.423..480.423 rows=987355 loops=1)
                    Buckets: 1048576  Batches: 1  Memory Usage: 72580kB
                    ->  Seq Scan on sa_milestone_overrides  (cost=0.00..28726.78 rows=988178 width=34) (actual time=0.004..189.641 rows=987355 loops=1)
Planning time: 3.561 ms
Execution time: 489.223 ms

And it works quite well as you can see, less than a second for the query.
But in the instance of Aurora, this happens:

Unique  (cost=2632927.29..2699194.83 rows=8835672 width=43) (actual time=4577.348..4577.350 rows=1 loops=1)
  CTE all_acp_ids
    ->  Seq Scan on temp_table_de3398bacb6c4e8ca8b37be227eac089  (cost=0.00..23.60 rows=1360 width=32) (actual time=0.001..0.001 rows=1 loops=1)
  ->  Sort  (cost=2632903.69..2654992.87 rows=8835672 width=43) (actual time=4577.348..4577.348 rows=4 loops=1)
        Sort Key: f1_folio_milestones.acp_id, (COALESCE(sa_milestone_overrides.team, f1_folio_milestones.team_responsible))
        Sort Method: quicksort  Memory: 25kB
        ->  Merge Left Join  (cost=1321097.58..1419347.08 rows=8835672 width=43) (actual time=4488.369..4577.330 rows=4 loops=1)
              Merge Cond: ((f1_folio_milestones.view = (sa_milestone_overrides.view)::text) AND (f1_folio_milestones.milestone = sa_milestone_overrides.milestone) AND (f1_folio_milestones.acp_id = (sa_milestone_overrides.acp_id)::text))
              ->  Sort  (cost=1194151.06..1216240.24 rows=8835672 width=37) (actual time=0.039..0.040 rows=4 loops=1)
                    Sort Key: f1_folio_milestones.view, f1_folio_milestones.milestone, f1_folio_milestones.acp_id
                    Sort Method: quicksort  Memory: 25kB
                    ->  Nested Loop  (cost=31.16..2166.95 rows=8835672 width=37) (actual time=0.022..0.028 rows=4 loops=1)
                          ->  HashAggregate  (cost=30.60..32.60 rows=200 width=32) (actual time=0.006..0.006 rows=1 loops=1)
                                Group Key: all_acp_ids.acp_id
                                ->  CTE Scan on all_acp_ids  (cost=0.00..27.20 rows=1360 width=32) (actual time=0.003..0.004 rows=1 loops=1)
                          ->  Index Scan using f1_folio_milestones_acp_id_idx on f1_folio_milestones  (cost=0.56..10.63 rows=4 width=37) (actual time=0.011..0.015 rows=4 loops=1)
                                Index Cond: (acp_id = all_acp_ids.acp_id)
              ->  Sort  (cost=126946.52..129413.75 rows=986892 width=34) (actual time=4462.727..4526.822 rows=448136 loops=1)
                    Sort Key: sa_milestone_overrides.view, sa_milestone_overrides.milestone, sa_milestone_overrides.acp_id
                    Sort Method: quicksort  Memory: 106092kB
                    ->  Seq Scan on sa_milestone_overrides  (cost=0.00..28688.92 rows=986892 width=34) (actual time=0.003..164.348 rows=986867 loops=1)
Planning time: 1.394 ms
Execution time: 4583.295 ms

It effectively has a lower overall cost, but it takes almost 10 times longer than before!

Disabling merge combinations causes Aurora to return to a hash combination, which gives the expected runtime, but permanent deactivation is not an option. However, interestingly enough, disabling nested loops gives an even better result while using a combination combination …

Unique  (cost=3610230.74..3676431.05 rows=8826708 width=43) (actual time=2.465..2.466 rows=1 loops=1)
  CTE all_acp_ids
    ->  Seq Scan on temp_table_de3398bacb6c4e8ca8b37be227eac089  (cost=0.00..23.60 rows=1360 width=32) (actual time=0.004..0.004 rows=1 loops=1)
  ->  Sort  (cost=3610207.14..3632273.91 rows=8826708 width=43) (actual time=2.464..2.464 rows=4 loops=1)
        Sort Key: f1_folio_milestones.acp_id, (COALESCE(sa_milestone_overrides.team, f1_folio_milestones.team_responsible))
        Sort Method: quicksort  Memory: 25kB
        ->  Merge Left Join  (cost=59.48..2397946.87 rows=8826708 width=43) (actual time=2.450..2.455 rows=4 loops=1)
              Merge Cond: (f1_folio_milestones.acp_id = (sa_milestone_overrides.acp_id)::text)
              Join Filter: ((f1_folio_milestones.milestone = sa_milestone_overrides.milestone) AND (f1_folio_milestones.view = (sa_milestone_overrides.view)::text))
              ->  Merge Join  (cost=40.81..2267461.88 rows=8826708 width=37) (actual time=2.312..2.317 rows=4 loops=1)
                    Merge Cond: (f1_folio_milestones.acp_id = all_acp_ids.acp_id)
                    ->  Index Scan using f1_folio_milestones_acp_id_idx on f1_folio_milestones  (cost=0.56..2223273.29 rows=17653416 width=37) (actual time=0.020..2.020 rows=1952 loops=1)
                    ->  Sort  (cost=40.24..40.74 rows=200 width=32) (actual time=0.011..0.012 rows=1 loops=1)
                          Sort Key: all_acp_ids.acp_id
                          Sort Method: quicksort  Memory: 25kB
                          ->  HashAggregate  (cost=30.60..32.60 rows=200 width=32) (actual time=0.008..0.008 rows=1 loops=1)
                                Group Key: all_acp_ids.acp_id
                                ->  CTE Scan on all_acp_ids  (cost=0.00..27.20 rows=1360 width=32) (actual time=0.005..0.005 rows=1 loops=1)
              ->  Materialize  (cost=0.42..62167.38 rows=987968 width=34) (actual time=0.021..0.101 rows=199 loops=1)
                    ->  Index Scan using sa_milestone_overrides_acp_id_index on sa_milestone_overrides  (cost=0.42..59697.46 rows=987968 width=34) (actual time=0.019..0.078 rows=199 loops=1)
Planning time: 5.500 ms
Execution time: 2.516 ms

We have asked the AWS support team, they are still analyzing the problem, but we are wondering what could cause that problem to occur. What could explain such a difference in behavior?

While looking at part of the database documentation, I read that Aurora favors the cost over time and, therefore, uses the query plan that has the lowest cost.

But as we can see, it is far from optimal given its response time … Is there a threshold or configuration that can make the database use a more expensive, but faster, query plan?

mysql – RDS Aurora – get the number of bytes scanned by a query?

Is it possible to get the number of bytes read by a query in Aurora? If it is not at the query level, is it possible to have it at the table level?

I have investigated performance statistics and performance_schema tables, but they all seem to have data only for ROWS_SCANNED, not the number of physical bytes. The metrics for I / O in performance statistics also appear to be at the database level, not in the table.

amazon rds – 2nd AWS Aurora RDS reader not used

Running a Aurora RDS Postgres with a writer and two readers.

One reader is used a lot, but the other is hardly used according to CloudWatch.

Cloudwatch

The Laravel application is connected to points of the reading host to the endpoint of the reader cluster so that Aurora can balance internally, but does not seem to be doing that, but only favors the first reader.

Are there any incorrect settings in the RDS or something else?

Apply a DDL statement with zero downtime with Aurora Mysql

I have an Aurora Mysql 5.6.10a db cluster with a teacher and a reading, to which I need to apply a migration with a downtime close to zero. I am trying to get a comment field to accept emojis by updating the encoding to utf8mb4. Because I am running this in millions of rows, this is an expensive update.

alter table tablename modify `comments` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL;

Reading the AWS documentation, I am exploring the update in a replica of my production database and then I use failover to exchange instances. However, I encounter problems with blocking the query. The steps I have done are:

1) Update the cluster parameter group for replication with the new encodings / collation. Expected by resynchronization.

2) The read_only flag was updated to false in both the paramgroup group and the param group, for replication. We wait for the update to apply. I also launched a reboot just in case.

3) SELECT @@global.read_only; sample 0 for this value, so it has been updated.

The execution of the DDL query results in:

ERROR 1290 (HY000): The MySQL server is running with the --read-only option so it cannot execute this statement

I have also received:

ERROR 2013 (HY000): Lost connection to MySQL server during query

In the back of my mind, it occurs to me that all my approach is terrible. Now I will simply add a new column at the end of my table and update my application to point to the new column for a less risky approach.

But, since I have now spent 2 hours trying to simply run my DDL, I am invested and hate not understanding why I cannot execute the above. I guess this is a limitation of Aurora / replication based on the nature of the query, but I would like an authorized response to what is happening.

– EDIT –

Ok, apparently, even adding a new column to my table still results in a table lock, so I go back to the starting point. I didn't think that would happen. What is the best practice here?

Aurora Profit – auroraprofit.com – HYIPs

For those who are already familiar with auroraprofit.com and for those who are just getting acquainted with the company, we offer a unique and profitable investment strategy. We are studying profitable Bitcoin mining and cryptocurrency trading to participate in modern financial markets, as well as to participate in the development of mining algorithms. In recent years, our experts have learned the technical and financial characteristics of the cryptocurrency and, recently, auroraprofit.com made a decisive decision upon entering the international investment market and expanding the business geography. The company's financial activity in Britain and beyond is well known and we have strong support from many of our partners and customers worldwide. As you know, most investment companies focus in any direction, which gives them a stable income. This cannot be said about our team due to the aggregation of financially lucrative areas on our side. First, auroraprofit.com is the group of related professionals from different fields of finance, economics, commerce and marketing. We are involved in the search and development of all opportunities to earn money: we exchange currencies and cryptocurrencies, as well as profitably speculate on values. Our principle is that time is money, so we never lose it for anything and spend only on profits. Each new day brings us a new income. In the arsenal of our experts, there are several financial instruments at the same time. Mainly, we are focused on operating with quick profits. We never miss any opportunity to earn money and fulfill all obligations with our numerous partners. The lack of government regulation in the field of cryptocurrency allows us to avoid paying taxes legally and reduce the additional costs of the company, offices and a considerable workforce. To be more flexible and competitive, the company's management has developed a versatile and interesting marketing plan for investors. An exhaustive theoretical basis of our proposal allows us to maintain an infallible business, in a timely manner to accumulate profits and, without a doubt, pay in full. Today, you can join the high performance investment program and start growing with auroraprofit.com. We declare that the project is open to all interested parties with minimum requirements. If you still have any questions, do not hesitate to contact our customer service. We look forward to a long and sustained partnership with our investors!

Aurora Profit – Auroraprofit.com

I am not an administrator / project owner! !!!

Small size image

Date online: 2019-10-23

Investment Plans: 0.91% per hour for 5 days, main included

4.8% daily for 40 days, principal included

241% After 5 days, principal included

Minimum Expenditure: $ 10

Reference Commission: 5%

Type of withdrawal Instant

Licensed script

DDoS Protection by DDOS-Guard

Accpet payment: Perfectmoney, Payeer.

About the project:

QUOTE

For those who are already familiar with auroraprofit.com and for those who are just getting acquainted with the company, we offer a unique and profitable investment strategy. We are studying profitable Bitcoin mining and cryptocurrency trading to participate in modern financial markets, as well as to participate in the development of mining algorithms. In recent years, our experts have learned the technical and financial characteristics of the cryptocurrency and, recently, auroraprofit.com made a decisive decision upon entering the international investment market and expanding the business geography. The company's financial activity in Britain and beyond is well known and we have strong support from many of our partners and customers worldwide. As you know, most investment companies focus in any direction, which gives them a stable income. This cannot be said about our team due to the aggregation of financially lucrative areas on our side. First, auroraprofit.com is the group of related professionals from different fields of finance, economics, commerce and marketing. We are involved in the search and development of all opportunities to earn money: we exchange currencies and cryptocurrencies, as well as profitably speculate on values. Our principle is that time is money, so we never lose it for anything and spend only on profits. Each new day brings us a new income. In the arsenal of our experts, there are several financial instruments at the same time. Mainly, we are focused on operating with quick profits. We never miss any opportunity to earn money and fulfill all obligations with our numerous partners. The lack of government regulation in the field of cryptocurrency allows us to avoid paying taxes legally and reduce the additional costs of the company, offices and a considerable workforce. To be more flexible and competitive, the company's management has developed a versatile and interesting marketing plan for investors. An exhaustive theoretical basis of our proposal allows us to maintain an infallible business, in a timely manner to accumulate profits and, without a doubt, pay in full. Today, you can join the high performance investment program and start growing with auroraprofit.com. We declare that the project is open to all interested parties with minimum requirements. If you still have any questions, do not hesitate to contact our customer service. We look forward to a long and sustained partnership with our investors!

JOIN NOW

Refback: http://invest-analysis.org/?a=details&lid=2093

amazon rds: cannot connect to the RDS Aurora Mysql database even if it exists

We have an instance of RDS aurora mysql that has been running for a while without problems, however, today the connection of our application to the database failed, with an error ERROR 1049 (42000): Unknown database 'campspot_development'.

Things we have tried:
The connection to the database without specifying the name of the database is successful:
mysql -h write.staging-database.campspot.com -u campspot_staging -p

SHOW DATABASES; returns the database in question.

mysql> SHOW DATABASES;
+----------------------+
| Database |
+----------------------+
| information_schema |
| campspot_development |
+----------------------+
2 rows in set (0.01 sec)

If we connect using the chain above and check USE campspot_development Not successful. But if we execute a query with the default scheme to the table, it works.

SELECT * FROM campspot_development.Park;

The connection while specifying the database is unsuccessful, resulting in an unknown database error:
mysql -h write.staging-database.campspot.com -u campspot_staging -p campspot_development

What could be causing the database to be searchable and displayed in the database list, but to stop connecting directly?

hdr: what is the optimal resolution setting to send a plain picture of Capture One to Aurora?

I am trying to send a group of raw photos from Capture One to Aurora to create an HDR image.

A dialog box appears when I do it

enter the description of the image here

I think most of the options are fine, except & # 39; Resolution & # 39 ;: I'm not sure if & # 39; 300 px / in & # 39; It is the right value. I am not entirely clear about the original resolution of the original images (I am using a Fujifilm xt-20). I don't want to pass plain files to Aurora with less resolution.

What is the optimal value for the Resolution field in this case?

Amazon Web Services – AWS Aurora – How to enable serverless mode through CLI

I am using the following command to create an AWS Aurora Serverless instance

aws rds create-db-cluster --db-cluster-identifier test-cluster --database-name testdb --master-username test --master-user-password testtest --engine aurora --engine-mode serverless --region us-east-1

But I am getting the following error.

Unknown options: – server mode, no server

The above command works very well in my AWS account, but it does not work in my clients account. (I only have programmatic access to that account). I have verified the permits twice and I have similar permits to those of my own account.

Summary: The AWS command to create an aurora cluster without a server is working on one account but not on another account with similar permissions.

Account 1:
account1

Account2:
acc2