New answers tagged

0

There's no way we can predict how long it will take. This depends on the RDS instance size, the data types of columns you are indexing, as well as concurrent load on the RDS instance, etc. MySQL 5.6 and later have Online DDL which is supposed to allow some concurrent query traffic against your table. Read https://dev.mysql.com/doc/refman/5.7/en/innodb-online-...


0

Foreign keys are not there to get in your way and "annoy" you. They are there to protect the relational integrity of the data in your tables. A simplistic example - you cannot have an Order_Line record that doesn't have a corresponding Order_Header record. Look at the data you are trying to load and find out why it doesn't "fit" with what ...


1

AWS also recommends logical replication to minimise downtime during major version upgrade. For upgrades from PostgreSQL 10.x to upper version native replication can be used, for older versions you can use pglogical (you can use pglogical on latest versions also) -- both solutions supported by RDS. setup new empty RDS instance of desired version dump schema ...


0

Mysql replication is a pull model. Communication happens from slave --> master. When it comes to AWS: We need to create a network between master and slave by creating routes tables/VPC peering(if master and slave are in different VPC) etc. Security group can be the issue in most of the cases, security group is stateful. We need to make sure that ...


0

In pgAdmin: Just load the pem file as the "Root certificate", in my case, it is a root certificate called ca.pem. Worked for me. (Not sure about the SSL compression, I just clicked it as well, should work without it.)


2

You are currently removing 855 rows using a filter on col3 and col4, in order to find 10 rows which pass that filter. So as I feared, the things that fail that filter might be rarer than other things, but they are sitting right in the way. And the next time you need 10 more things, they will still be in the way. And the next time. Not only are you doing ...


0

I believe you need to increase your shared_buffer. The issue with 40% allocation is described here: https://www.postgresql.org/docs/current/runtime-config-resource.html "If you have a dedicated database server with 1GB or more of RAM, a reasonable starting value for shared_buffers is 25% of the memory in your system. There are some workloads where even ...


0

Your WHERE clause has these elements: col1 = constant (equality) col2 = constant (equality) col3 = constant (equality) col4 > constant (range) To satisfy this query from your existing columns as efficiently as possible, use a composite (multicolumn) BTREE index on all those columns. You can put cols 1-3 in any order you wish in the index. But col4, the ...


4

Do not use autocommit=OFF -- it can lead to forgetting to COMMIT, which leads to an automatic ROLLBACK. Either have autocommit=ON or explicitly have pairs of BEGIN and COMMIT. (This agrees with J.D.; I just want to add emphasis about this common mistake.)


4

Check the autocommit setting. It's possible that it's disabled (set to off) on your RDS instance and now all your uncommitted queries are getting rolled back because you're not explicitly committing.


0

You should try to ANALYZE the table and see if that works. The problem is that PostgreSQL uses an index to support the ORDER BY, but not the WHERE condition. To force PostgreSQL to not use that index, use ORDER BY ts + INTERVAL '0 seconds';


0

If writes to the RDS Instance had not occurred for over an hour, all binlogs whose timestamp is older than an hour must disappear after mysqld starts up in the RDS Instance. If you want the binlogs to stay longer, raise the retention hours to 4 (or higher) call mysql.rds_set_configuration('binlog retention hours', 4); just before reboot. Don't lower it ...


Top 50 recent answers are included