A great place to be!

September 2009 Blog Posts (10)

Fun with mysqlslap benchmarking

Mysqlslap is a good benchmarking tool but can be much more versatile in my opinion. So, recently, I was benchmarking a problem that was, not hard to solve, but somewhat tricky to benchmark. Using mysqlslap was the fastest way to get the answers I wanted with the least amount of BS, or so I thought!

Lets take a look:

-- The problem

1. A MySQL instance with 5… Continue

Added by Chris on September 22, 2009 at 7:06pm — No Comments

What's faster than REPAIR?

After Chris' great post, What's faster than ALTER, I got thinking about REPAIR. REPAIR TABLE can be slow, but may seem necessary if your table is corrupt.

A little background:

MyISAM tables are prone to corruption if they are receiving updates/inserts/deletes/etc and your server crashes. Doing a REPAIR TABLE can generally "fix" them, but it can take time. The larger the table,… Continue

Added by Sarah Sproehnle on September 16, 2009 at 9:00pm — 6 Comments

The Project Formally Known as Golden Gate

So I was watching the discovery channel about the maintenance of the Golden Gate bridge. I was surprised to find out that it takes two years to paint the whole thing, then, when they are "finished" the job starts all over again. Basically, this is a never ending project! I started to think about the larger, several thousand server, MySQL installations I've worked on and how to improved performance. Oddly enough The Project Formally Known as Golden Gate was born! Thought I would change the name… Continue

Added by Chris on September 14, 2009 at 10:00pm — 1 Comment

Using TMPFS for MySQL's tmpdir

There have been a lot of systems I have looked at where the Created_tmp_disk_tables was very high. MySQL has good documentation on how it uses memory, specifically temp tables, here.

I would like to talk about not "why" MySQL does this but how to speed up the performance when MySQL internally or users create temporary tables to disk. A great solution is TMPFS, a quick how to is… Continue

Added by Chris on September 12, 2009 at 9:27pm — 3 Comments

What's faster than ALTER?

Well, the answer I always get is "DROP". Then I would say... "what if you wanted to preserve your data?" It is very common for DBAs to think only in terms of what is obviously available in MySQL, ALTER, than all of the tools that are really there. I have been placed in situations in many companies where ALTER was just not the way to go.

Given the amount of MySQL usage throughout the WEB2.0/IT industry and the fact that data sets seem to keep growing... I have come up with a great… Continue

Added by Chris on September 10, 2009 at 2:30pm — 8 Comments

Partitioning by dates: the quick how-to

There is thorough documentation about the Partitioning feature in MySQL 5.1. There are also nice articles like this one by Robin. However, I thought it would be useful to have a quick "how-to" guide to partitioning by dates. I'll use the world schema to keep it easy.

Partitioning a… Continue

Added by Sarah Sproehnle on September 8, 2009 at 12:18pm — 8 Comments

Testing Replication Over the Pond – Part 2 Secure

Testing Secure Replication

For the first test I used encrypted (SSL) replication and inserted 200,000 records using three 10 minute disconnection intervals per hour.

After several hundred thousand of inserts, deletes and updates on the SOA and RR tables simultaneously over a normally connected SSL replication channel I have the following results:

RR Update Test

Test 1:

RR Records Updated (Changes Set) = 129145

Elapsed… Continue

Added by Chris on September 6, 2009 at 8:41pm — No Comments

Testing Replication Over the Pond - Part 1 Non-Secure

Testing Non-Secure Replication

A series of experiments were conducted to determine whether MySQL replication would prove to be reliable with SSL enabled. Please note that all tests were conducted using the MyDNS schema with includes the SOA and RR tables on MySQL 5.1.

The first experiment sets focused on replication operations and not on a predetermined set of Insert, Update or Delete patterns. So Inserts were used since they are the easiest to tag and verify.… Continue

Added by Chris on September 6, 2009 at 8:40pm — No Comments

Index for god sake!

This is an old post I put on the old mysqlhow2.com that ended up on modphp.org. Both sites are run by people I know so it's coo. Anyway, I thought it should be here as well.

Lets look at the results of the query without returning all the results:

NOTE: Use EXPLAIN

- Explain will show the possible rows to be examined in your query. (The more rows the slower the return)

- Example of EXPLAIN: (we will be using the world database by… Continue

Added by Chris on September 6, 2009 at 8:39pm — No Comments

What if your slave(s) can't keep up?

MySQL replication is a great feature: it's easy to setup and relatively easy to manage, it can provide scalabilty and availability, a master can serve dozens of slaves, etc. However, sometimes people have the problem that the slaves can't keep up; they get further and further behind, which is a major problem. So this post talks about why that happens (sometimes) and what you can do about it.

Causes:

The most common reason that a slave cannot keep up with the…

Continue

Added by Sarah Sproehnle on September 6, 2009 at 6:30pm — 1 Comment

Featured Blog Posts

View All

Latest Blog Posts

Monthly Archives

2011

October (1)
June (2)
February (1)
January (1)

2010

December (3)
October (3)
September (2)
July (1)
June (1)
April (3)
March (1)
February (3)
January (1)