Select Page

Month: August 2017

How to Convert EC2 Instance PV to HVM Virtualization Type

Scenario: Convert t1.micro to t2.micro instance type conversion As t1.micro is PV (Paravirtual) machine and t2.micro is HVM (Hardware-assisted Virtual Machine) Note: please take backup before performing conversion. Steps before converting from PV to HVM: Go to the AWS Console and launch a new HVM instance (Amazon 64-bit Linux AMI , t2.micro). Stop the PV instance. Take a snapshot of the root device and create a new volume (SOURCE VOLUME) from this snapshot. Stop the HVM instance. Take a snapshot of the root device on the new instance and create a new volume (TARGET VOLUME) from this snapshot. Using the AWS Console: Attach SOURCE VOLUME to the new instance as /dev/xvdf. Attach TARGET VOLUME to the new instance as /dev/xvdg. Steps for actual converting from PV to HVM process: SSH to the new instance and get root access: sudo su – Mount the source and target drives. mkdir -p /mnt/source && mount /dev/xvdf /mnt/source mkdir -p /mnt/target && mount /dev/xvdg1 /mnt/target Delete everything but /boot on the target volume: cd /mnt/target && ls | grep -v boot | xargs rm -Rf Delete /boot on the source volume: rm -Rf /mnt/source/boot Copy the source volume’s data to the target volume preserving all the attributes: rsync -aAXHPv /mnt/source/ /mnt/target Stop the system and detach all volumes using the AWS console. Attach the TARGET VOLUME on the new instance as /dev/xvda.  Start...

Read More

Alter on Big Table in RDS (Solution to “ERROR 1114 (HY000): table full” Error)

Tables which are very big in size (~> 100GB to 600GB). Doing alter on such big tables is HIGH memory and CPU intensive task. when you try to alter query on one of the tables it gave “ERROR 1114 (HY000): table full” error… Why this issue occurred even though Amazon Aurora storage volume will automatically grow upto 64TB. ? First we will understand the storage in RDS Aurora. There are 2 types of storage in Aurora. Instance store which is local storage where temporary objects are stored and the main storage for data. Therefore, when you run ALTER on a table, it will generate a temp table and RDS aurora would use instance storage to store the temp table. For your instance which is db.r3.large instance, it has 1×32 GB  of local storage so if your temp objects on the instance gets to be larger than this size, you get the “table full’ error. Also, the local storage limit is different from the total storage volume available for your Aurora instance and based on your database usage, your Amazon Aurora storage volume will automatically grow, up to 64 TB, in 10GB increments. Solutions To overcome the issue, you can scale the instance up to get more local storage to run your ALTER, alter the table then scale down instance type. This result to have some downtime while upgrade / downgrade instance type. You can also...

Read More

RDS Max_DB Connection Limits as per DB Instance Type

Most of the time we get below error while taking access of DB.                            ” ERROR 1040 (08004): Too many connections “ Let’s understand what is max_db_connections : Determines the maximum number of concurrent connections to the database server. This chart shows the max_connections limit, set by AWS, at various MySQL RDS instance sizes. Class Type Max_Connection t2.micro 66 t2.small 150 m3.medium 296 t2.medium 312 M3.large 609 t2.large 648 M4.large 648 M3.xlarge 1237 R3.large 1258 M4.xlarge 1320 M2.xlarge 1412 M3.2xlarge 2492 R3.xlarge 2540 R3.2xlarge 5080     R3.4xlarge 10160 AWS sets the MySQL max_connections system variable according to the size of the RDS instance.  max_connections is the maximum permitted number of simultaneous or concurrent client connections.  A “Too many connections” error message may be displayed if this is reached. If an application implements long-running queries or creates several concurrent connections from the web or app side, the number of connections is just as important as vCPU cores, memory level or storage IO. for more info, see the MySQL max_connections sys variable, or AWS’s post regarding DB parameter groups. When running a standby server, you must set this parameter to the same or higher value than on the master server. Otherwise, queries will not be allowed in the standby server. Do you know that, by default, each instance comes with a different number of max_connections?Command to check max_db_connection#mysql>show max_connections; The numbers in the max_connections column looks slightly awkward...

Read More


 “RDS FOR SIMPLICITY AND REDUCED MAINTENANCE AND MANAGEMENT EFFORTS” Relational Database Service (RDS) is a web service provided by Amazon and most of the company use this service because it is fast, secure and cost-effective component of a modern cloud infrastructure. RDS provides six familiar database engines, including popular open-source options like PostgreSQL, MySQL, MariaDB. In short, it is a Database as a Service, where Amazon deploys and operates your database. It takes care of tasks like backup and patching the database software, as well as high availability It’s also easy to get good baseline performance with RDS. Each RDS instance is pre-configured and optimized based on the selected size. RDS instances scale by adjusting DB instance type (memory or compute power up or down) as needed. It’s also simple to configure read replicas or set up synchronous replication across availability zones for enhanced performance, availability, and durability. Using RDS, the fully managed service option, can be great for removing a majority of DBA tasks. However, the service does not currently provide the option to run every type of task. RDS also manages replication. If developers need high availability in a database, the multi-availability zone option in RDS can be used. With that option, a replica database is kept in sync with the primary database in case of failure. Keeping UP with Cost: “Pay for what you use” you need...

Read More