I want to get opinions from outside of my daily circle of people on the best server hardware to use for MySQL. I remember from the conference somebody (Pipes?) mentioning a particular Dell server with multiple disk RAID10 that could supposedly be had for about $6k but I completely misplaced the model number (Frank, did you get my email?).

I know that a multi-disk RAID array with a bunch of fast disks (15k RPM?) is probably the most important method of improving performance, followed by the amount of RAM, so I'm trying to find the best combination/balance of the two. However, server prices on the Internet range so much that I don't even know where to begin to tell a good deal from a bad one. I don't think SSDs can play a role here, because we …

At the MySQL Conference & Expo 2008, Britt Crawford and Justin McCarthy, both from Cafepress.com, gave us a very interesting talk on scaling with HiveDB. I took a few notes (pasted below), their slides are online (warning: 6.1MB PDF), and if you’re after their abstract its available as well.

I also took a video of them (refer to Slide 12, for the IRC conversation):

The quick notes:

• OLTP optimised (as it serves cafepress.com)
• Cannot lock tables, or take it offline
• Constant response time is more important than low latency (little slower query is ok, just not exponentially …

A couple more articles I saw posted out on the web on Sun’s new SSD offerings.  In the The official press release…  their are very scant details on the sun offerings right now.  But the official release says:

“Sun is already shipping Solaris ZFS software optimized for SSD technologies through the OpenSolaris(TM) community and is the first major systems vendor to add an end-to-end Flash-based disk product line to its portfolio, leapfrogging competitors and giving customers 3x better performance at one-fifth the energy consumption of traditional spinning disk offerings.”

Hopefully I am misreading this… 3X performance seems slower then the other SSD I have looked at.

In …

Properties:

Description:

If you have slow query logs enabled (with --log-slow-queries), this variable will help interpret all those queries that are not using indexes as slow queries.

Usage:

While …

Properties:

Description:

Starting with 5.1.5, MySQL has implemented ROW based replication format which logs the physical …

Properties:

Description:

This variable changes the way InnoDB open files and flush data to disk and is should be considered as very important for InnoDB performance. By default, InnoDB uses …

Properties:

Description:

This option, currently available only for Linux, if set enables the usage of large pages by MySQL. Many operating systems/system architectures support optional memory pages bigger than the default size (4 KB). Usually the large page size is 2 MB, this …

With all due respect to the technology and it's advocates (myself included), after a surge in articles describing the merits of using memcached I'm just pushing a thought breakpoint for developers to think whether they actually need it or not?

Recently, I ran into cases where the developers have decided to use memcached over MySQL style architecture after reading some/many good/nice articles about it without giving a damn to their requirements. I would like to list few things as a checklist for developers to decide on their architecture. There is still no precise answer but sometimes few cases can be just ruled out :).

1. What is the total size of your data? It might be a possibility that you can keep the data in memory in each node, or MySQL can just keep the whole thing (data+indexes) in a buffer.
2. How frequently your data is updated? Very frequent updates may lead to low cache hit ratio for memcached …

MySQL has a handy feature, that allows you to turn an INSERT into an UPDATE if a unique or primary key duplication is detected:

http://dev.mysql.com/doc/refman/5.1/en/insert-on-duplicate.html

A common usage pattern for this is “lazy initialization“ of a row in a database, which is exactly what my team was using it for yesterday to solve a problem in the backend for version 2.0 of the MySQL Enterprise Monitor. However, we ran into an issue where Hibernate would throw an exception complaining that when the INSERT was turned into an UPDATE, it couldn‘t retrieve the generated primary key value (we are using auto increments on this particular table, as it‘s not a high insertion-rate table).

To understand why this happens, you have to know a little bit about how Statement.getGeneratedKeys() works with MySQL‘s JDBC driver. …

I am, for the most part, a do-it-yourself type of person. I fix my own car if I can; I even have four healthy tomato plants growing in pots outside as we speak — the plants will take that little extra CO2 out of the air and give me great tasting tomatoes (soon… i hope!)

But I digress.

Whether to use an ETL tool such as Kettle (aka Penatho Data Integration) for a project involving large data transfers is a typical “build vs. buy” type of decision, one that is fairly well understood and I don’t wish to repeat it all here — putting together some Perl scripts to do the job, you typically get great performance, development speed and accessibility. This would need to be balanced against the benefits of ETL tools and their potential drawbacks (development speed, license costs and performance …