DBA Dojo

Re-constructing directory structure on Linux

2012-11-13T19:31:00.000-08:00

If ever you need to re-construct the directory structure on Linux/Unix on a different machine you can just run this command. # Generates a list of mkdir commands to re-construct the directory structure from current location find . -type d| while read -r line; do echo "mkdir -p $line"; done If you are wanting to copy files as well, just use scp or rsync The use case for these kind of commands

Oracle Fun with Predicate pushdown

2011-11-06T00:57:00.000-07:00

I had some fun recently with a Oracle database choosing a poor execution plan.The problem was with a view which had a column which was explicitly cast to a value.For example:create table vw_tempasselectcast(ID) as NUMBER(19,0) as ID,Name varchar2(50)from very_large_table ajoin large_table b on a.ID = b.IDwhere Name = 'whatever' ;Oracle in this case was unable to use the ability to push predicates

Oracle RAC on EC2 redux

2011-03-21T04:11:00.000-07:00

I was reading some RSS feeds the other day and noticed that Jeremy Schneider over at Ardent Performance Computing was working on getting Oracle RAC working on Amazon EC2.http://www.ardentperf.com/2011/03/04/byo-oracle-rac-on-ec2/He looks to have solved the whole Virtual IP issue by using another instance. Nice solution!When I get a spare moment (don't believe for a minute that the lack of posts

mysqlslap howto

2010-07-30T21:33:00.000-07:00

I noticed that people were hitting the site for information on how to run mysqlslap.To help out those searchers, here is a quick mysqlslap howtoMake sure you have mysql 5.1.4 or higher. Download MySQL from the MySQL websiteMake sure your MySQL database is running.Run mysqlslap, using progressively more concurrent threads: mysqlslap --concurrency=1,25,50,100 --iterations=10 --number-int-cols=2 \-

MySQL Error: error reconnecting to master

2009-10-05T22:44:00.000-07:00

Error message:Slave I/O thread: error reconnecting to masterLast_IO_Error: error connecting to masterDiagnosis:Check that the slave can connect to the master instance, using the following steps:Use ping to check the master is reachable. eg ping master.yourdomain.comUse ping with ip address to check that DNS isn't broken. eg. ping 192.168.1.2Use mysql client to connect from slave to master. eg

Upcoming articles for 2009

2009-08-09T00:24:00.001-07:00

I was reviewing what I had written over the last 2 years, and how people had reacted via comments and page views, even what keywords were most popular. Here is the first draft (pending input from interested readers via adding a comment) More dbt2 benchmark articles. Amazon EC2 LVM snapshots vs EBS snapshots. Backup software for MySQL and specifically on EC2. Benchmarking Amazon

OurDelta MySQL on EC2 - updating binaries

2009-06-08T18:00:00.000-07:00

Given the amount of time since my last post on installing OurDelta MySQL on EC2. It allowed me to show quickly how to get your OurDelta MySQL install up-to-date.Prerequisites:You have already installed the OurDelta Repository as per this documentationhttp://ourdelta.org/centosTo update:Now just yum update to get the latest version:http://ourdelta.org/release-5077-d8yum update MySQL-OurDelta*It is

OurDelta MySQL on EC2 - install

2009-02-09T03:44:00.000-08:00

Summary:Arjen would give me an earful if I got this wrong or poorly worded."OurDelta produces enhanced builds for MySQL, with OurDelta and third-party patches, for common production platforms" from http://ourdelta.org/aboutOver the next series of articles I am going to put the many additions to the MySQL 5.0 baseline through their paces on Amazon EC2.Using a base CentOS 4.4 I had lying around on

Sysbench fileio vs small EC2 with EBS

2008-09-01T17:51:00.000-07:00

Background:I got a request from a reader to repeat the sysbench fileio benchmarks against the new Amazon EBS (Elastic Block Store).As mentioned elsewhere as this is similar to network attached storage (NAS) it will be constrained by the speed of network.The past series on sysbench fileio on EC2Sysbench fileio vs EC2 Part 1Sysbench fileio vs Large EC2 Part 2seeker io benchmark vs EC2Results:As

How to use EC2 mountpoints

2008-05-26T04:13:00.000-07:00

It has been quiet around here. My workload has been high and I have been spending my down time reading, rather than experimenting and hence publishing.Frederic commented in a recent post about Sysbench fileio benchmark.If you want to see whole set of posts use EC2 BenchmarkThere were three questions and my reply was going to be long. So now it is a post.Questions:/dev/sda/ : do you mean that it

Sysbench fileio vs XLarge EC2 Part 3

2008-04-16T04:00:00.000-07:00

Background:If you have been following the series. I have gone back to checking the raw IO performance of all the Amazon EC2 instances.Sysbench fileio vs EC2 Part 1Sysbench fileio vs Large EC2 Part 2seeker io benchmark vs EC2In the meantime Amazon has finally announced that they are providing persistent storage for all EC2 instances. This has removed a bunch of complexity from running a database

Is EC2 useful as a database server

2008-04-05T18:28:00.001-07:00

Plenty of people have been excited by the prospect of Amazon EC2 and the ability to scale out your databases as load increases from your original configuration. I noticed Morgan Tocker and Carl Mercier are going to be presenting on this topic at the upcoming MySQL ConferenceHowever almost immediately people are worried about the lack of persistent of data across instance terminations.In a sense

Sysbench fileio vs Large EC2 Part 2

2008-03-25T06:35:00.000-07:00

Overview:I reran the sysbench fileio benchmarking tool on a large EC2 instance. This is basically the middle range offering from Amazon. The large instance runs a 64 bit OS of your choice and safely sits in the commodity PC/Server range (with a little extra memory).If you missed the first article, I ran the same sysbench fileio benchmark on the small EC2 instanceInstall:Follow the instructions to

Seeker io benchmark on small EC2

2008-03-25T03:39:00.000-07:00

Frederic posted an interesting link to the seeker io script in the sysbench fileio vs small EC2 article.He was concerned that the seeker was showing difference numbers to sysbench fileio, especially on the /mnt mountpoint.I read the thread and there was another comment made concerning how Xen could use a sparse file image to improve the io performance. I tested that theory out and found as soon

Sysbench fileio vs EC2 Part 1

2008-03-12T03:22:00.000-07:00

Overview:Peter Zaitsev's recent article about Evaluating IO subsystem performance for MySQL spurred my interest in doing something similar on EC2.I have covered running sysbench against MySQL on EC2 however not specifically used sysbench to test IO. Rather I had used bonnie++ and iozone to do that.I don't have a lot of respect for the EC2 small instance. Whilst it was reasonable in the middle of

MySQL Master-Master replication table sync

2008-03-04T02:19:00.000-08:00

I saw a post by Baron mentioning that his tool maatkit is best for handling situations where a master-master replication setup has got out of sync.If you think Baron was blowing his own trumpet he has good reason to. I have used his mk-archiver tool as part of the Maatkit to make the problem of archiving and purging data much easier. This was much easier than rolling my own solution.Anyhow. I

MySQL Multi Master-Master on EC2

2008-02-26T03:41:00.000-08:00

Background:As I mentioned in the previous post on running multiple mysql instances on EC2 using mysqld_multi. This was the first step in running 2 or more EC2 Amazon Machine Image (AMI) as a Multi-instance Master-Master (MIMM) replication cluster.The idea cames from a blog article from Apokalyptik.comIt is about improving the availability of your databases on EC2 and allowing easy backups

MySQL SolidDB vs MySQLSlap EC2

2008-02-13T01:56:00.001-08:00

Overview:I am always keen to try and test out new technologies. The idea of having a choice in storage engines is a great feature of MySQL. In MySQL 5.1, the MySQL engine API is available to write your own storage engine.In the meantime, there are a couple of MySQL storage engines produced by 3rd party companies. SolidDB is one of them.I wanted to install and then test the SolidDB engine against

MySQL vs MySQLSlap Round 3

2008-02-02T16:03:00.000-08:00

In this continuing series on using mysqlslap to pound mysql databases.http://blog.dbadojo.com/2007/08/mysql-vs-mysqlslap.htmlhttp://blog.dbadojo.com/2008/01/mysql-vs-mysqlslap-round-2.htmlI was moving towards running mysqlslap to test any concurrency limits inherent in mysql-proxy. However I was unable to get the small instances on EC2 to handle even small concurrent levels (500 concurrent users)

MySQL vs MySQLslap round 2

2008-01-23T02:07:00.000-08:00

I was going to post some more on the clash of the titans i.e. mysql-proxy vs mysqlslap, however after doing some retesting, I never got past ramping the number of concurrent sessions in mysqlslap.I wanted to make sure the Mysql database could handle the raw load, before adding another layer in the form of mysql-proxy.Back in August I did some preliminary work with MySQL and mysqlslap.So given

MySQL-Proxy vs MySQLSlap Round 1

2008-01-14T03:22:00.000-08:00

Outline:Given the mysql-proxy is moving toward a production ready version (at some point). I thought it would be useful to put it up against mysqlslap and see how it fared.In this round, I wanted to see if mysql-proxy could handle various workloads and concurrent connections in the plain vanilla load-balancing state.Essentially we are building on past experience.http://blog.dbadojo.com/2007/12/

Multiple MySQL instances on EC2

2008-01-03T03:57:00.000-08:00

Overview:I am keen to see what other people's thoughts of MySQL and using MySQL on EC2 are in general. So I read any blogs which discuss them with interest.In the latter part of last year I read an interesting article on proposing to run multiple MySQL instances on a single EC2.Running multiple MySQL instances on one box is reasonably straightforward. Most of the exposure I have seen is either

MySQL Proxy on EC2

2007-12-08T20:54:00.000-08:00

MySQL Proxy is a tool to sit between the client and database. It can load balance, redirect queries, track slow queries amongst other possible uses.http://dev.mysql.com/doc/refman/5.0/en/mysql-proxy.htmlhttp://forge.mysql.com/wiki/MySQL_ProxyThere is a ramp up within MySQL to get MySQL Proxy into a production ready product. So it is time to check out the product and see if the stated simple

Making Logical Volumes on EC2

2007-11-30T20:49:00.000-08:00

The MySQL backups using LVM Snapshots post is now by far the most popular post.Here is shell script which helps get the Logical Volumes (LV) setup in the first place.### START OF SCRIPT ####!/bin/sh# Name: make_mnt_LV.sh# Script to make EC2 /mnt into a LVM volumemodprobe dm-snapshotumount /mntpvcreate /dev/sda2vgcreate vg /dev/sda2lvcreate -L30720M -n myvmdisk1 vgmkfs -t ext3 /dev/vg/