Hot answers tagged

10

For Aurora Postgres, there's two relevant cluster-level parameters (note they're not instance-level parameters): rds.force_ssl and ssl. I haven't tested this myself but you should be able to modify them in the usual way using DB Parameter Groups.


8

Here's the trick, at least with pgAdmin v4: Leave the tablespace blank. It will default to "pg_default" when creating the database.


7

UUIDs are useful when you have clients independently generating unique identifiers. id INT UNSIGNED AUTO_INCREMENT is smaller, faster, 'ordered', etc. Use UUIDs only if you don't have a viable alternative. More discussion: http://mysql.rjweb.org/doc.php/uuid In my opinion, sha256 is overkill for a 'digest'. 500K rows INSERTed per day? That's 6/second? ...


5

I have never used Aurora and there might differences with MySQL but there is a method that works very often in MySQL in similar issues, when the execution plan is not optimal, i.e. when it does the joins first and then has to do the ORDER BY of the big intermediate result set. Instead of joining the 2 tables, we try to first LIMIT the results in a derived ...


4

I can't tell if this involves Aurora, but here is something worth noting: There are occasions when InnoDB will leave lock lying around. How does this happen ? About six years ago, I answer this question : Are InnoDB Deadlocks exclusive to INSERT/UPDATE/DELETE?. In that post I mentioned the following from the MySQL Documentation: When InnoDB performs a ...


3

Standard PostgreSQL runs on a file system and depends on the file system cache in addition to the PostgreSQL buffer cache. Aurora PostgreSQL runs on top of Aurora Storage, which is not a file system and doesn't have a separate cache on the instance. So, with Aurora PostgreSQL we set the default for shared_buffers to 75% of the total memory on the instance. ...


3

Yes, you are paying twice for the instances, but only once for the storage, because they share a single Aurora cluster storage volume (there is only one volume per cluster), which is part of why replication is nearly instantaneous. You can remove the reader instance, as long as you understand that this will slow down recovery if the lone remaining instance ...


3

This certainly looks like a corrupt index. First things first, take a DB snapshot and store it someplace which no one has write access to. This being RDS Aurora and not the community PostgreSQL, your first (and likely last) recourse would be to talk to AWS support. You can try to reindex the entire database if you want, but if there is corruption in the ...


3

I'm speaking to the currently available version of Aurora which is MySQL 5.6 compatible. On the surface, it works just like MySQL. I have two databases on my Aurora cluster. My guess it can hold as many databases as MySQL or at least more than you would ever need. Where things differ is in how backups are handled. Aurora, like other RDS offerings, does ...


2

You got it wrong. Aurora is its own backend, but it comes with MySQL compatibility: Amazon Aurora is designed to be compatible with MySQL 5.6, so that existing MySQL applications and tools can run without requiring modification. The PostgreSQL compatibility is only available as a preview yet, in only one AWS region, after sign-up.


2

Finally found the answer, its an Aurora specific state that has the same meaning as the blank state in MySQL. Cleaned Up state means it has completed and cleaned up and waiting for the connection to close. https://forums.aws.amazon.com/thread.jspa?messageID=708499


2

INSERT (etc) must check that there are no FOREIGN KEY violations. Hence it is slower. Think of it as checking to see if an entry is in an index.


2

At a guess - STUFF, QUOTENAME, and FOR XML PATH are being used to build an aggregated string out of the inner query - specifically, to build a column list for use in a later dynamic query. MySQL uses backticks (`) to "quote" DB object names, and there's a GROUP_CONCAT aggregate function built-in. The CROSS APPLY isn't actually using anything from the query ...


2

As far as i know you can not replicate Aurora (slave ) from EC2 (master), please correct me if I am wrong. Due to the super grant lack required to do the "Change Master". I would recommend to use DMS (databawse migration server) for that task. migration steps: set route53 TTL to 1 min set read_only in current master, to make sure no more reads go into the ...


2

In the update statement, MySQL has to compute "date_sub(created, interval 30 day)" for each row, and compare it with compliance_overridden_date. In the select, it only has to compute date_sub(now(), interval 30 day) once. Your where-clause does not allow MySQL to use an index to select the rows to be modified. And since all rows must be read, it's better ...


2

Based on the description, this is related to Amazon Aurora product, and not PostgreSQL by itself. See https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Aurora.AuroraPostgreSQL.html Amazon Aurora PostgreSQL is a fully managed, PostgreSQL-compatible, relational database engine that combines the speed and reliability of high-end commercial databases ...


2

I received the answer from Kevin Jernigan of Amazon: A recent change in the Aurora PostgreSQL infrastructure made some internal schemas visible to customers. The “apgcc” schema is used for internal maintenance, and we are happy to drop it for you if it is causing an issue. We will address this in a future release.


2

Full Disclosure: I am the product manager for Aurora PostgreSQL. We are working to add support for the feature you noticed that is already available for RDS for PostgreSQL and for Aurora MySQL, to automatically copy database logs to Cloudwatch. While I don’t have a date to communicate, we plan to launch this feature soon - stay tuned! Kevin Jernigan ...


1

The ROLLBACK must finish in order to get the database back to a table state. (Without knowing the details of the load, I cannot advise on autocommit.)


1

What solved it for me was setting the min and max instance count higher than before (forcing aurora to scale up, for example from min 2 ACU to min 4 ACU), and after scaling restoring the scaling settings. I used 'Force scaling the capacity to the specified values when the timeout is reached' and 'Apply immediately'. It also solved another issue we had, the ...


1

This is on AWS Aurora, using postgres-10.6, in case that makes a difference. Yes, this matters quite a lot. Native parallel btree index builds were introduced in v11, so your "max_parallel_workers" setting won't matter for index builds under v10. Unless you upgrade, you will have to parallelize them yourself by opening multiple sessions in parallel and ...


1

As per @Pavel Tsiukhtsiayeu blog documentation Migrating PostgreSQL standalone instance to Aurora (Amazon RDS) The idea behind migration is simple: create Aurora cluster in RDS, setup database source and target endpoints at DMS, and launch database migration task. The data migration task will migrate all data from the source database, and replicate ongoing ...


1

From @nathanlong's comment to my question: On Sep 25, 2018, they announced "Amazon Aurora with PostgreSQL Compatibility Supports PostgreSQL 10" - https://aws.amazon.com/about-aws/whats-new/2018/09/amazon-aurora-postgresql-compatibility-supports-postgresql104/. They explicitly mentioned "native table partitioning", though there are features in PosgreSQL 11 ...


1

It's good that innodb_buffer_pool_size is not there. Even if you could manipulate its value, best practices for Aurora says you should leave it alone. Please notice what the Best Practices Documentation says under the subheading Default parameter values and their importance: Certain DB instance parameters contain variables or formulas, in which the ...


1

This query doesn't take advantage of the primary key in any way. The number of rows examined (about 160K rows) shows this. So your assumption that your UPDATE does nothing is incorrect: it examines several rows. The reason why your SELECT is faster is pretty clear. This expression is computed once, at the beginning of query execution: DATE_SUB(NOW(), ...


1

% deliberately does not include localhost. This decision was possibly made to emphasize the difference between the relatively secure local connection versus coming in from the World Wild Web. Or perhaps because it implies a socket instead of TCP/IP. (There is not much performance difference.) So, either Use the IP (or hostname) of the machine, or GRANT ...


1

This appears to be the result of the MySQL engine upgrade to version 5.7.3 or later, where the definition of some tables in the performance schema have changed. If you were running standard MySQL, mysql_upgrade would have fixed that. You'll need to contact Amazon support for ways to upgrade your existing databases to the new engine version. In the worst ...


1

We have identified the root cause: it's the innodb_autoinc_lock_mode = 1 . Here is the summary from the official doc: 0: traditional lock mode, provided for backward compatibility, performance testing, and working around issues with “mixed-mode inserts”, due to possible differences in semantics. 1: consecutive lock mode: In this mode, “bulk inserts” use ...


1

Maybe there could be a few 'master' APIs, each doing some subset of the 250-300 inserts? For ACID, each transaction requires a disk write -- that is what is limiting your speed. Not knowing details of your SQL, I can only guess at things to help: Batching INSERTs into a table (multiple rows in a single transaction). Each of the 25 threads does only one (...


1

You are taking a hash of a phone number? Why? Indexing the phone number is arguably better than building a hash and indexing that. For one thing, the phone number is always(?) shorter than the hash. Kill the ALTER and rethink the task. The likely reason for the problem is the randomness of the column being indexed -- Toward the end of the process, it ...


Only top voted, non community-wiki answers of a minimum length are eligible