Chris's Blog – September 2009 Archive (7)

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

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

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

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

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

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

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