37

Upgrading an instance in RDS means RDS will be physically migrating the database to a new instance, likely on a different physical host, so downtime would not be avoidable. Migrating to provisioned IOPS would likely mean your data would be migrated to a new EBS volume (and the server might be migrated to a new instance as well with this change, depending on ...


34

This happens when not having USAGE privilege on any of the schemas of search_path. By default the pseudo-role public (all users) has this privilege on the publicschema, so this error happens only after revoking it explicitly with: revoke usage on schema public from public; This is required when it's not desirable that people peek into other people schemas, ...


19

I'm a big AWS fan in general... but RDS, not so much. @RolandoMySQLDBA has pointed out are some pretty good points against it. The only advantage I see in RDS compared to MySQL on EC2 are the ability to do point and click snapshots, clones, and point-in-time recovery, but these are not nearly sufficient to make up for the loss of control and flexibility ...


17

You probably don't want to hear this, but the best option to speed up SELECT DISTINCT is to avoid DISTINCT to begin with. In many cases (not all!) it can be avoided with better database-design or better queries. Sometimes, GROUP BY is faster, because it takes a different code path. In your particular case, it doesn't seem like you can get rid of DISTINCT. ...


17

You have at least two options. The first one makes use of a small query and a text editor. We have to collect the schemata of our interest: SELECT nspname FROM pg_namespace; You can add a WHERE clause if you want to limit the scope. Copy the output and amend it, so you get a number of GRANT USAGE ON SCHEMA ... TO your_role; commands. Then just feed ...


16

Each connection carries the load of per-connection buffers as set by these parameters join_buffer_size sort_buffer_size read_buffer_size read_rnd buffer_size Changing the number of connections increases the amount of memory each connection can demand to this : (join_buffer_size+sort_buffer_size+read_buffer_size +read_rnd buffer_size) X max_connections I ...


16

You can't add your own extensions to RDS, at least not ones that require superuser rights (like anything with C code). This is one of the downsides you accept in exchange for convenient management. If the extension only includes simple plpgsql and sql functions you can add the functions manually. That is not possible with anything requiring superuser ...


13

Run this query to show if/when your tables were last vacuumed. SELECT * FROM pg_stat_all_tables; A table won't be vacuumed until the insert/update/delete threshold is reached, which is 20%. You can tweak the setting in RDS by creating a new DB Parameter Group and change the setting you want.


11

You'll have to test it. You could do some back of the envelope calculations to approximate the number of I/Os per insert, multiply it by the number of transactions per second, add in some buffer room etc, but it's much easier to just test it. The easiest thing to do is to allocate a best guess, then go back and increase or decrease it to match you're real ...


10

You should fully tune the MySQL Environment, particularly your InnoDB settings. (See the bottom of my Answer for tuning tips). This would be much better than fighting Amazon for elbow room in RAM/Disk. Why did I say fight? If you just spun up an Amazon RDS instance of MySQL, you would subject yourself to whatever constraints are given. All models of MySQL ...


10

If scaling out DB Servers is not your cup of tea, then Amazon RDS is OK to use because all bells and whistles come with it. Those who simply want moderate HA, backups, and scaling out benefit a great deal. On the flip side, if you want to scale up hardware, that is out of the question for RDS. What if you want to scale up MySQL's capabilities? Unfortunately,...


10

session_replication_role I found an alternative way of disabling foreign keys -- https://stackoverflow.com/a/18709987 set session_replication_role = replica; And re-enabling them with set session_replication_role = default; This works on RDS but still requires unusual privileges (i.e. not granted by default). dropping and recreating FKs Alternative ...


10

Currently there are a few available extensions in Amazon RDS -- detailed in the "Database Engine Features" chapter. You can issue the following command to get the available extensions supported: SHOW rds.extensions;


10

AFAIK, you're right that AWS RDS Aurora (a MySQL 5.6 fork) does not support automatic or transparent read/write splitting: http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_Aurora.html In order to do that in a way that's completely transparent to the application, you would need an intermediate proxy. Your application would then always connect to ...


9

You want ALTER DEFAULT PRIVILEGES. Give the rds_superuser default access rights to all new tables. This only affects tables created after the ALTER. For existing tables you must GRANT rights.


9

I just wanted to point out that AWS has updated and now have a cluster Read endpoint that does load balancing in case anyone runs into this from Google. https://aws.amazon.com/blogs/aws/new-reader-endpoint-for-amazon-aurora-load-balancing-higher-availability/


9

As a_horse_with_no_name originally suggested in question comments: --assuming we have tables: a, b, ... CREATE TABLE a ( a_id integer NOT NULL PRIMARY KEY, a_name text ) ; CREATE TABLE b ( b_id integer NOT NULL PRIMARY KEY, b_name text ) ; First we create a global sequence and add a column in each table: -- create one global sequence to be used ...


9

This answer applies to AWS Aurora not straight MySQL RDS. Just because SHOW ENGINES says the database has the MEMORY storage engine doesn't mean you can actually use it. We have found that in AWS Aurora the SHOW ENGINES; call will tell you that the MEMORY engine is supported. However, if you try to change the engine to MEMORY or create a non-temporary table ...


8

RDS for MySQL 5.6 supports "in" and "out" migration strategies of live workloads using MySQL's native replication, but RDS for MySQL 5.5, they only support "in" migration... which is unfortunate, because essentially every possible combination except the one that you need is available. That would have been nice. :( If your dump download took 3 hours and ...


8

Like most things, it's a cost vs effort/complexity trade-off. RDS is easier, but less flexible, and costs more for the same level of performance. (In particular, note that there's no way to replicate into or out of RDS)


8

You must be running in a Security Group firewall problem. Go to your RDS Dashboard, select Instances and open the instance you want to connect to. Look for a line like this : Security Groups rds-launch-wizard (sg-3e9axxx) ( active ). You should be able to click on rds-launch-wizard (sg-3e9axxx) which leads you to the EC2 Dashboard in the Security Groups ...


7

Just a guess as there isn't enough information to really figure this out but it could be network latency. You're saying that local connection to local DB is fast but local to remote DB is slow. All other things being equal the only difference is the network connection between you (local) and the remote DB. Generally AWS is pretty fast and has a stable ...


7

I used the basic iostat (iostat -h) tool to get an idea of how many iops I was currently using. From that I extrapolated how much I'd use if I was under 4 times that amount of load and went with that amount. For me that worked out to 780 IOPS so I went with 800 IOPS.


7

You can't download a snapshot from RDS you have to use a tool like pg_dump This has already been answered multiple times on regular Stack Overflow: https://stackoverflow.com/questions/14916899/download-rds-snapshot


7

The problem was that our new RDS instance was being throttled on writeIOPS, we could see queued disk operations (DiskQueueDepth of 10 or greater) and we could see writeIOPS averaging 300, whereas Amazon only provide 100 reliable iOPS (source). The solution was to fork out approx $200 per month for "provisioned iOPS". 1000 is the smallest you could buy (...


7

you can rename the database using the following command but make sure to take backup first CREATE database new_db_name; RENAME TABLE db_name.table1 TO new_db_name, db_name.table2 TO new_db_name; DROP database db_name; references: http://www.rndblog.com/how-to-rename-a-database-in-mysql/ https://stackoverflow.com/questions/12190000/rename-mysql-database


7

Yes, it is safe. Vacuum full will recreate the tables as new objects and only when they're done they will be put to use. If you cancel it, the new files not yet put to use will be removed and old ones will be kept. Of course if you would cancel it by killing the process handling the vacuum there would be files left, but still the database would be intact.


7

There is a subtle problem hidden in your ORDER BY clause: ORDER BY updated_at DESC Would sort NULL values first. I assume you do not want that. Your column updated_at can be NULL (lacks a NOT NULL constraint). The missing constraint should probably be added. Your query should be fixed either way: SELECT * FROM orders WHERE shop_id = 3828 ORDER BY ...


7

Just found out that this was a bug that has been fixed in the next release, MySQL 5.7.11. Extract from the changelog: InnoDB: Creating a table with a full-text index and a foreign key constraint failed when foreign_key_checks was disabled. (Bug #22094601, Bug #78955) It took me time to figure out that the full-text index was the source of the problem, I ...


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