A Swiss Geek previously in Singapore, now in Portugal

Server

MySQL: deleting a huge amount of rows

One of our MySQL tables has started to grow out of control with more than 1 billion rows (that’s 109). The table is a typical “Rails Active-Record table” with id as primary key (auto increment), created_at, updated_at and a few columns for the business data. The table has multiple indexes on various columns, some of them having a cardinality in the millions. created_at and status don’t have an index. The majority of results from Google, involved peoples with millions of rows and deleting them in batches of 1000.

Read More…

AWS, VPN and Public IP

Once you start to integrate services with Telcos, ISPs or other major Network players who are not “Cloud Aware”, you will need to go the VPN way using IPSec. Their setups have used all the available IP’s defined by RFC 1918 (or just don’t want to use them). They will want to use Public IP’s behind your VPN. The problem is that your EC2 instances are running behind a 1:1 NAT with only a private IP’s attached to them. To add to the complexity, your instances are part of an auto-scaling group, with IP’s all over 10.0.0.0/8. Luckily the way to solve this problem is actually quite easy, we just need to add some iptables rules and a proxy.

Read More…

Dynamically resizing EBS volumes

AWS provides multiples services that adjust sizes to your actual usage. This is not really the case when it comes to EBSEBS. You can see EBS has a hard drive that you can attach to a running EC2 instance. The attached EBS will be accessible like any other driver under /dev/xvdX. The idea here, is to provide a way to increase and decrease the size of the used EBS volumes according to the actual usage of the disk, and even allow you to increase the partition size beyond the 1TB limit.

Read More…

Qmail: Routing outgoing SMTP through smarthost

If, like me, you are still using an old style LAMP stack combining Web and E-Mail, then you certainly made a “stay over” in SPAM-paradise. The Stack I self-host my E-Mails, using QMail and vpopmail with a bunch of intermediate tools like Spamassassin, Clamav, Greylisting, DNSBL checks. This helps to clean out incoming SPAM. Messages are delivered to users through courier-imap or qmail-pop3d. For outgoing messages, Qmail sends (via qmail-remote) the messages via SMTP to the concerned MX servers.

Read More…