Hot answers tagged

11

SQL Server does not automatically change the recovery model. If you set it to simple, then see later it is set to full, either: Someone else changed it back to full The database has been restored from a backup where the database is set to full recovery There may be a scheduled agent job that is changing the recovery model.


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 ...


9

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

Asking "What AWS instance to choose for write-heavy PostgreSQL database server" is somewhat like asking "which spoon should I use to cut up this loaf of bread". The answer is: don't use a spoon, use a knife. Or in this case, real hardware like a dedicated server with a batch of good quality SSDs and a decent raid controller that has a battery-backed write-...


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)


7

I had the same issue with our staging RDS. After quite a while I found a blog post that says: In a very busy database, with many writes per second, this number will remain fairly accurate. However, in a system where there are few writes, the "replication_delay" will continually grow because the last replayed transaction timestamp isn't increasing. To ...


6

If you only have 40% of 15 GB = 6 GB free, you aren't going to be able to have two copies of a 7 GB table on the instance at the same time, whether you use ALTER TABLE (which usually creates an entire copy of the table and then replaces the existing table with it, as RolandoMySQLDBA explained) or create another table and insert the data. It sounds like ...


6

A pg_dump output with the -Fc (custom) is not a SQL script, it's a compressed archive that only pg_restore is able to handle. You may either pass the dump file to pg_restore and pipe its output to psql (often done that way and close to what you tried): pg_restore /home/jboss/proc_dump_jan15.dump | psql --host=blahrds [other options]... ...or use the -d,-...


6

Yes. Think of EC2 as a machine. It has a disk attached - a EBS (Elastic Block Store), and the disk size is your limit. Lets say your ec2 instance has a 20gig disk. If you database grow over 20gig, you will have to resize your attached disk. I am not aware of a auto-scalling method for EC2 disks. But you can simply make a snapshot of a EBS, create another ...


6

Changing Instance Class: From the documentation "Modifying a DB Instance and Using the Apply Immediately Parameter", changing the instance class and setting "Apply Immediately" to false will result in: The change being applied during the next maintenance window. The change causing an outage to occur. Since the instance is Multi-AZ and you're changing the ...


6

In AWS EC2, you don't get the ability to add additional network ADAPTERS to your VM. You can add additional network INTERFACES, which are just different IPs/subnets/routes/etc, but they're using the same underlying network adapters (cards). You can choose to use instance types with faster networking ports, though. The EC2 instance configuration page lists ...


6

You can directly upload the (gzipped) pg_dump results to s3 using the aws cli: pg_dump -Z 9 -v -h ${DATABASE_HOST} -U ${DATABASE_USER} -d ${DATABASE_NAME} | aws s3 cp --storage-class STANDARD_IA --sse aws:kms - s3://my-bucket/dump.sql.gz At work we wrapped this in a script which additionally fetches the required credentials from parameter store and then ...


5

Here is the Problem: Whenever you run ALTER TABLE, there must be a temp table used. The temp table is not being written to your data folder. It is most likely written to /rdsdbdata/tmp. Please this query SHOW VARIABLES LIKE 'tmpdir'; This will give you an idea where that folder is. Again, by default, it is /rdsdbdata/tmp. Why would that halt things for ...


5

I am 99% sure that Amazon RDS does not give you a 'root' access. i.e. You can't create a user as 'root'@'localhost' or 'root'@''. Can you confirm user you have used while launching Amazon RDS instance? and make sure that Password matches with user created.


5

You can do the following: UNLOAD your data to S3 (http://docs.aws.amazon.com/redshift/latest/dg/r_UNLOAD.html) Run your deployment scripts against the new database COPY the data into the new database (http://docs.aws.amazon.com/redshift/latest/dg/r_COPY.html) This (UNLOAD/COPY) will be fairly quick for a development size dataset.


5

Other than outdated techniques like obtaining exclusive locks on tables and tactics like SELECT HIGH_PRIORITY and INSERT DELAYED -- which are either deprecated or otherwise not useful (especially with InnoDB) -- MySQL (including RDS) does not have any mechanisms for prioritization like this. Depending on the nature of your "longer running background queries"...


5

It seems that I had to re-run the AWS RDS PostGIS instructions: CREATE FUNCTION exec(text) returns text language plpgsql volatile AS $f$ BEGIN EXECUTE $1; RETURN $1; END; $f$; SELECT exec('ALTER TABLE ' || quote_ident(s.nspname) || '.' || quote_ident(s.relname) || ' OWNER TO rds_superuser') FROM ( SELECT nspname, relname FROM pg_class c JOIN ...


5

There is no limitation of 64KB on the size of a MySQL row. There are limitations, but almost never does anyone hit them. Can you point to where you got that limit? There are a couple of obscure limits in InnoDB, such as having dozens of big VARCHARs, but they are easily worked around. Here are the main limits for MySQL: http://mysql.rjweb.org/doc.php/...


5

T2 instances are given cpu time based on a credit system, and t2.micros are have the least overall cpu capability in the instance class. These credits are accumulated at a rate of 6 per hour on a t2.micro, which gives you the ability to burst to 100% of the cpu core for 6 minutes. All other times you are only granted 10% of the core. Furthermore, you will ...


5

Amazon RDS documentation provides a table of units for each parameter under the "Working with PostgreSQL Parameters" topic. Units are also usually included in the description of each parameter in the parameter group edit UI from the Amazon RDS web console. min_wal_size (Postgres 9.5 Parameter Group Family) Default: 16 Units: MB Allowed Values: 2-201326592 ...


5

If DMS is told to create the table on the destination (the default option) it only creates the most basic structure needed to move the raw data over. So you shouldn't trust that other metadata like default values are moved over either. You can either migrate all your changes after moving the data, or create the schema first on your destination and tell DMS ...


5

The issue related to JSONB columns is a known limitation of DMS. In particular, if you're using DMS to migrate from your EC2 instance to RDS Postgres, it will convert the type to varchar(765) and truncate any JSONB column longer than 756 chars. https://forums.aws.amazon.com/message.jspa?messageID=735961 We heard during AWS Summit - San Francisco that the ...


5

VACUUM processes launched by autovacuum can be safely killed with: SELECT pg_terminate_backend(PID_of_backend); Actually, any client processes in Postgresql can be terminated this way. Uncommitted work by this backend will be simply discarded. You could then re-run VACUUM manually at a low-traffic time: VACUUM VERBOSE users; Check whether Cost-based ...


5

Yes, it does. How come ? Run this query in the RDS Instance: mysql> select * from information_schema.engines where engine='MEMORY'\G *************************** 1. row *************************** ENGINE: MEMORY SUPPORT: YES COMMENT: Hash based, stored in memory, useful for temporary tables TRANSACTIONS: NO XA: NO SAVEPOINTS: ...


5

Got the answer from aws. NCHAR and NVARCHAR types are converted to CHAR and VARCHAR types, respectively, and are stored in the specified number of bytes. A VARCHAR(120) column consists of a maximum of 120 single-byte characters, 60 two-byte characters, 40 three-byte characters, or 30 four-byte characters. So, if I am storing double-byte characaters in SQL ...


5

No. It's not supported on RDS at this point. See https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_PostgreSQL.html#PostgreSQL.Concepts.General.FeatureSupport.Extensions. If you really need cstore_fdw, you'll have to manage your own PG instance.


4

I had discussed this in earlier posts Sep 05, 2012 : Is the overhead of frequent query cache invalidation ever worth it? Sep 26, 2013 : query cache hit value is not changing in my database InnoDB internals has a very hands-on approach to the query cache since it micromanages query cache entry invalidation. In the MySQL 4.x days, the query cache was ...


4

You are mistaken in your assertion that "the binary log retrieves fine." The output you are seeing is boilerplate output from mysqlbinlog... there's no evidence here that you are actually fetching the binlog. There's no such log file on the server. That's the meaning of this error. SHOW BINARY LOGS; ...to find the available logs on the server. You can ...


4

In general there is very little tool support at present for Redshift. We do all of our automation and deployment via psql shell scripts and we manage the code base using git version control. As far as schema changes we use a "migrations" approach (inspired by Rails) where we create ALTER TABLE …ADD COLUMN and ALTER TABLE …DROP COLUMN scripts to roll the ...


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