Amazon Rds

C'è la guida totale della sezione http://aws.amazon.com/documentation/rds/ che poi porta a quella per un primo utilizzo veloce http://docs.amazonwebservices.com/AmazonRDS/latest/GettingStartedGuide/Welcome.html

C'è un video carino che fa vedere in 12 minuti l'uso dei db http://aws.amazon.com/rds/

I costi e i prezzi sono su questa pagina http://aws.amazon.com/rds/ si deve considerare sia il tipo di istanza che si prende che lo storage quando si sceglie che il numero di operazioni di i/o . Quest'ultimo ovviamente è il parametro difficile da calcolare a priori.
C'è sempre la solita modalità di amazon se compri ad ore ti costa di più che ad anno. Finche tieni operativo il db anche se non lo utilizzi lo paghi. Puoi terminare l'istanza facendo uno snapshot per eventualmente ripristinarlo dopo.

La guida per il primo utilizzo è molto chiara solo a una cosa sono dovuto arrivare con intuito. I permessi di accesso in base all'indirizzo ip e il ccdir , con un indirizzo ip esterno tutto chiaro perchè 8.8.8.8/32 permette l'accesso solo dalle connessioni provenienti da quell'ip. Mentre la macchina all'interno di amazon nonostante avessi messo il corretto indirizzo ip non mi si collegava. La macchina stava nella stessa region del db, li avevo messi vicino non so se è utile, quindi per raggiungere il db usava l'indirizzo privato e non si collegava. Mettendo l'indirizzo privato della macchina amazon tra le impostazioni di connessione invece funzionava.

Cambiare i parametri a linea di comando

Per cambiare i parametri del db si devono scaricare i tool a linea di comando denominata CLI che sono dei programmi java. Dalla console web si possono solo leggere le informazioni. Per funzionare si devono settare queste variabili d'ambiente
La guida è qui http://docs.amazonwebservices.com/AmazonRDS/latest/CommandLineReference/Welcome.html

export AWS_RDS_HOME=/home/utente/Scaricati/RDSCli-1.8.002
export JAVA_HOME=/usr
export PATH=$PATH:${AWS_RDS_HOME}/bin
export AWS_CREDENTIAL_FILE=/home/utente/Scaricati/RDSCli-1.8.002/credential-file-path.hyperborea
export EC2_REGION=eu-west-1

Maggiori istruzioni sono nella guida online , se non si mette la region lui mette la prima di default che è una dell'america e quindi dice che non ci sono istanze.

Copiare il file di template delle credential e modificarlo inserendo il contenuto che si trova tramite la pagina web sul proprio account "Security Credencial"

A questo punto si possono lanciare i comandi . Per verificare se l'istanza è collegata si può usare

rds-describe-db-instances --headers

Security group limitation

For DB security group, you can specify up to 20 rules in a security group. Since you have 20 entries in this security group, you would not be able to add more rules.

To learn about the Db security group limit, please refer this link(in the first paragraph):
http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_WorkingWithSecurityGroups.html

To solve this issue, here are some workarounds:

1.You can create a new DB security group and attach multiple security groups to one RDS instance.
After creating a new security group, here are the steps to modify DB instance:
a).Sign in to the AWS Management Console and open the Amazon RDS console at https://console.aws.amazon.com/rds/.
b).In the navigation pane, click Instances.
c).Select the check box for the DB instance that you want to change, and then click Modify.
d).In the Modify DB Instance dialog box, under "Security Groups", please choose multiple security groups(you can leverage "CTRL" button for Windows to choose multiple of them)
e).To apply the changes immediately, select the "Apply Immediately"check box.
f).Click Yes, Modify.

2. Consolidate some of the authorizations into /31 or /30 rules if possible

3. If this security group is applied to multiple DB instances, please create a separate security group for each db instance and only grant users access to the databases you specifically need to access; the authorization limit is per security group, not per account.

4. Investigate ways for users to use the same source IP address for connecting to the database (e.g. ssh tunnels)

slow query logging

understand why the query are slow
http://www.anujgakhar.com/2014/02/16/enabling-slow-query-log-on-amazon-rds/

IOPS

http://stackoverflow.com/questions/18777070/aws-rds-provisioned-iops-really-worth-it

From this link
http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_PIOPS.html

I have extracted the most important things

Disk Metrics definition:
IOPS – the number of IO operations completed per second. This metric is reported as the average IOPS for a given time interval. Amazon RDS reports read and write IOPS separately on one minute intervals. Total IOPS is the sum of the read and write IOPS. Typical values for IOPS range from zero to tens of thousands per second.

You can provision:

  • MySQL, PostgreSQL, or Oracle DB instance with up to 30,000 IOPS and 3 TB of allocated storage.
  • SQL Server DB instance with up to 10,000 IOPS and 1 TB of allocated storage.

The ratio of the requested IOPS rate to the amount of storage allocated is important.

  • between 3:1 and 10:1 for MySQL, PostgreSQL, and Oracle DB instances.
  • 10:1 for SQL Server DB instances.

You must avoid to pass the 10:1 ratio

For example:

  • 1000 IOPS and 200 GB storage (a ratio of 5:1)
  • 2000 IOPS with 200 GB of storage (a ratio of 10:1

Factors That Affect Realized IOPS Rates

Page Size and Channel Bandwidth

The theoretical maximum IOPS rate is also a function of database I/O page size and available channel bandwidth

  • MySQL uses a page size of 16 KB,
  • Oracle, PostgreSQL (default), and SQL Server use 8 KB.

On a DB instance with a full duplex I/O channel bandwidth of 1000 megabits per second (Mbps),
the maximum IOPS for page I/O is about

  • 6,250 IOPS in each direction (input/output channel) for 16 KB I/O
  • 12,500 IOPS in each direction for 8 KB I/O.

A workload consisting of 50% reads and 50% writes could reach 12,500 IOPS with 16 KB I/O or 25,000 IOPS with 8 KB I/O.

there are maximux values
DB Engine Page Size Maximum IOPS Rate
MySQL 16 KB 20,000
PostgreSQL 8 KB 25,000
Oracle 8 KB 25,000
SQL Server 8 KB 10,000

but If you provision an IOPS rate that is higher than the maximum or that is higher than your realized IOPS rate, you may still benefit from reduced latency and improvements in overall throughput.

DB Instance Class

if you use iops you need to use at least a large instance

Database Workload

System activities such as automated backups, DB snapshots, and scale storage operations may consume some I/O, which will reduce the overall capacity available for normal database operations. If your database design results in concurrency issues, locking, or other forms of database contention, you may not be able to directly use all the bandwidth that you provision.

To help you verify that you are making the best use of your Provisioned IOPS storage, we have added a new CloudWatch Metric called Disk Queue Depth.

If your application is maintaining an average queue depth of approximately

  • 5 outstanding I/O operations per 1000 IOPS that you provision

you can assume that you are consuming the capacity that you provisioned.

  • if you provisioned 10,000 IOPS, you should have a minimum of 50 outstanding I/O operations

in order to use the capacity you provisioned.

Using Provisioned IOPS Storage with Multi-AZ, Read Replicas, Snapshots, VPC, and DB Instance Classes

  • Read Replicas – The type of storage on a read replica is independent of that on the master DB instance. For example, if the master DB instance uses standard storage, you can add read replicas that use Provisioned IOPS storage and vice versa.
  • You can use Provisioned IOPS storage with any DB instance class. However, smaller DB instance classes will not consistently make the best use of Provisioned IOPS storage

Getting the Most out of Amazon RDS Provisioned IOPS

For example, consider a heavily loaded OLTP database provisioned for 10,000 Provisioned IOPS that runs consistently at the channel limit of 105 Mbps throughput for reads. The workload isn’t perfectly balanced, so there is some unused write channel bandwidth. The instance would consume less than 10,000 IOPS and but would still benefit from increasing capacity to 20,000 Provisioned IOPS.

Increasing Provisioned IOPS capacity from 10,000 to 20,000 doubles the system’s capacity for concurrent IO. Increased concurrency means decreased latency, which allows transactions to complete faster, so the database transaction rate increases. Read and write latency would improve by different amounts and the system would settle into a new equilibrium based on whichever resource becomes constrained first.

It is possible for Provisioned IOPS consumption to actually decrease under these conditions even though the database transaction rate can be much higher. For example, you could see write requests decline accompanied by an increase in write throughput. That’s a good indicator that your database is making better use of group commit. More write throughput and the same write IOPS means log writes have become larger but are still less than 16 KB. More write throughput and fewer write IO means log writes have become larger and are averaging larger than 16 KB since those IO requests consume more than one IO of Provisioned IOPS capacity.

Salvo diversa indicazione, il contenuto di questa pagina è sotto licenza Creative Commons Attribution-ShareAlike 3.0 License