sorted by: recent | see : popular

Content Tagged with commentary + MySQL

MySQL University has been running for the last 18 months, and we’ve covered a wide range of topics, from the internals of MySQL right up to Amazon’s EC2, using MySQL in the Solaris/OpenSolaris Webstack and a description of the forthcoming MySQL Online Backup.

Personally, I think they’re great. Obviously many times I am scribe and am there for the sessions, but I listen to lots of the sessions anyway, and I’m yet to be disappointed by the content. What’s really great is that in all the cases the person you are listening to is probably the person that either developed, or helped drive development of the particular function, or, in the case of some of the external tools (EC2, for example), these guys are expert in it. The experience is not quite as thrilling as attending the MySQL User Conference, but the content is just the same.

The problem is that despite all the work we do to get the presenters, interesting topics, and promotion of the upcoming sessions, we don’t always get as many attendees as we want or expect.

So, I’m wondering why this should be the case. We know that the current presentation system is not ideal (and we’re working on that), but I’m interested to hear people’s opinions on MySQL University. If you want to help shape the future of MySQL University, then comment here, and either answer the questions below, or make up your own.

• Have you attended any MySQL University sessions. How many?
• How would you rate the sessions generally? A simple good or bad will do
• If you haven’t attended any sessions, or don’t regularly attend them, why not?
• Have you ever looked at/listened to the past sessions that provide on MySQL Forge?

Please, I’m interested to hear.

Sometimes there are funny communication styles between people who are geographically distributed and working together all the time. Recently one of our team members echoed back to me some answers I gave over a chat session: Q: Is it OK for me to buy quad-core servers? A: The old man walks slow but carries much, whilst [...]

There is much discussion of why MySQL is more widely adopted than PostgreSQL. The discussion I’ve heard is mostly among the PostgreSQL community members, who believe their favorite database server is better in many ways, and are sometimes puzzled why people would choose an inferior product.

There are also many comparison charts that show one server is better than the other in some ways. These don’t really seem to help people with this question, either!

I can’t answer for everyone, but I can put it in the form of a question: if I were to replace MySQL with PostgreSQL, what things do I rely on that would become painful or even force a totally different strategy? The answer turns out to be fairly simple for me: replication and upgrades.

Replication

Love it or hate it, MySQL’s built-in replication is absolutely key to much of what I do with MySQL. I can truthfully say that it has lots of problems and limitations. But I can also say this about it:

• It’s included by default with the server. PostgreSQL’s have historically not been included. (I think this is about to change, but I’m not sure.)
• It is conceptually very simple. You could call that a weakness and a limitation, but you could also say that it enables a tremendous amount of flexibility. I tend to hold with the latter view. PostgreSQL’s replication technologies have a very different complexity profile. That scares me.
• It is easy to set up (it takes just a couple of commands) and is easily scriptable. This is mostly due to its simplicity. I am happy because I know it inside and out.
• It is generally very low overhead. PostgreSQL’s main replication system is built on top of triggers and is said not to scale very well. (Disclaimer: this is only what people have told me; I haven’t battle-tested it. But I’m afraid of it.)
• There is only One Way To Do It. PostgreSQL has lots of different replication systems. That in itself is a pretty significant deterrent for me.

Regardless of the technical strengths and weaknesses of each database’s replication systems, it is my perception that MySQL’s ultimately lets me do incredibly flexible and useful things; in general it is Just Enough and has just the right combinations of qualities for lots of purposes. And each of its weaknesses is easily avoided or worked around, or just sidestepped — because MySQL replication’s simplicity and flexibility lets me easily choose a different approach.

In-Place Upgrades

MySQL’s files are extremely portable between versions, between operating systems, and even between platforms most of the time (unless you have a system that doesn’t use IEEE floating-point format, but who does these days?). That means an upgrade is dead simple.

This may not seem like a big deal, but I work with a lot of data. When you do that, you have to consider the alternatives: what if I couldn’t upgrade in-place?

That’s the current state of PostgreSQL. You have to dump and reload your data, and when you have a terabyte of data, that’s no fun. The workarounds usually involve replicating your data to another server, switching to the other server, upgrading, and switching back. But why should you have to have another server just to upgrade your data?

I see this as a significant — even critical — sticking point. It’s something I just don’t have to think about most of the time with MySQL

Are PostgreSQL’s other strengths enough?

Not for the systems I work on. These two problems seem extremely difficult for me to work around. I rely so heavily on MySQL’s replication and in-place upgrades that it feels too daunting to live without them.

What I’m trying to do here is give some psychological insight into what makes me feel happy with MySQL, and afraid of the thought of having to solve these problems with PostgreSQL. It may or may not apply broadly; my sense is that these are concerns for others as well, but I could be wrong.

If I were primarily a PostgreSQL user, I’m sure there would be similar feelings the other direction. This would explain why some people in the PostgreSQL camp seem to recoil away from MySQL. I’d be interested to hear why that is, too.

mysql, replication, upgrades

Hopefully you can’t get enough of the UC08 videos (and thanks to Sheeri for the link with the full Jonathan keynote video), so Zack has managed to get some most posted.

This morning, we learned what it meant to be a pirate in terms of patents, copyright and now politics with the Pirate Party. Don’t let the scary name put you off - these guys are about making all of us consumers (of software, video, audio, books, etc.) more in control of information. Please support these guys by visiting Piratpartiet.se.

Next we had the Scalability Panel with representatives from Facebook, Fotolog, Sun, YouTube, Flickr and Wikipedia talking about the problems (and some of the solutions) they have taken to approach the bane of any web company.

One option for scalability of course is just not to cover the problem yourself. Skip the issues of server rooms and use Amazon’s various services (EC2, S3, etc). If you need convincing, try Werner Vogels’ talk.

Yesterday, in a short but sweet visit to the podium, Rich Green talked about MySQL and Sun and it will all work. If you have concerns about the integration, this is a good place to get the situation from the guy who will make sure it doesn’t go wrong. And of course Rich will answer to Jonathan.

Marten talked about the significance of Storage engines and the significance of your data.

For a full list, try Zack’s profile page.

MySQL just gave me an award at this morning’s keynote, along with Sheeri Kritzer Cabral (for the second year in a row!) and Diego Medina, for my code contributions to the MySQL community, specifically Maatkit, which makes it easier to make MySQL reliable, fast, and robust. It’s an honor to be recognized. And while I could leave it at that, I’d like to say a word or two more.

The economy, community, and ecosystem that’s building around Free Software can often be very rewarding financially. This is a great motivation; being rewarded for your efforts is one of the chief virtues of a culture of entrepreneurship, along with the idea that to try and fail is just as noble as to succeed. But I find that isn’t enough. If I were only rewarded financially and with recognitions such as this morning’s, I would quickly become bankrupt at a deeper level. I would become focused on external measures of success, such as accolade and wealth.

That’s why it’s so important to be of service to others and to work for the good of all. This is one of the strongest counterbalances for me. It helps keep me humbler and more open.

In the end, Free Software is all about this. It reminds me always that we are all interconnected, and that to work for your highest good is to work for my own.

I believe we all need at least these three things deeply:

• To chart your own course in life.
• To be of service to others.
• To make the most of what you have.

Does proprietary software offer you the chance to do this? No, it does not. It makes you beholden and dependent, not free. To pursue these three goals to their maximum extent you need freedom. “Make the most of what you have” doesn’t imply that you have to just accept what’s given to you; you can also take some time to see what your choices are, and choose something that gives you more freedom if possible. That’s what I did years ago when I moved away from using proprietary software.

I hope you’ll give this a try yourself: contribute what you build internally in your company, and put in the extra effort to make it really high quality and useful for everyone. This is how Maatkit started. Don’t wait for others to make it happen: chart your own course.

This morning’s award is most important to me because it reinforces that I’m serving others well.

Diego Medina, Sheeri Kritzer Cabral

I’ve been working with the folks working on OpenSolaris for a few months now providing advice and input on getting MySQL and the connectors (C/ODBC and C/J) installed as a standard component. Having got the basics in, the team are now looking at adding both 32-bit and 64-bit packages.

The question raised at the end of last week was whether OpenSolaris should enable 64-bit builds by default in 64-bit installations, and whether there was a noticeable performance difference that would make this worthwhile.

I did some initial tests on Friday which showed that there was a small increase (10-15%) of the packaged 64-bit installations over 32-bit under x86 using snv_81. Tests were executed using the included sql-bench tool, and this was a single execution run of each package for 5.0.56. Transactions are missing because I hadn’t enabled transactions in the tests.

Test (x86, binary packages) 32-bit 64-bit +/-

ATIS	20	17	17.65%
alter-table	18	15	20.00%
big-tables	14	11	27.27%
connect	134	121	10.74%
create	348	348	0.00%
insert	1038	885	17.29%
select	399	257	55.25%
transactions
wisconsin	10	8	25.00%

There are some significant differences there (like the 55% increase on SELECT speeds, for example), but a single execution is never a good test. Also, it’s unclear whether the differences are between the compilations, the platform or just pure coincidence. This requires further investigation.

As a coincidence, Krish Shankar posted these notes on using SunStudio 11 and SunStudio 12 and the right compiler flags to get the best optimization.

I decided to do 10-pass iterations of sql-bench and compare both 32-bit and 64-bit standard builds, the 32-bit standard builds against Krish’s optimizations, and finally 32-bit and 64-bit optimized builds.

Some notes on all the tests:

• All builds are 5.0.56
• All tests are run on SunOS 5.11, snv_81
• Tests are executed on the same OS and machine running in 64-bit. The SPARC tests are on an UltraSPARC IIIi@1.28GHz Workstation with 1GB RAM; x86 are on a Dell T105, Opteron 1212 with 4GB RAM. Of course we’re not comparing machine speed, just 32-bit binaries over 64-bit.
• All results are in seconds; lower values mean faster performance.
• In all tests I’m using the built-in defaults (i.e. no my.cnf anywhere) so as to simulate a standardized installation.

Let’s first look at x86 and the 32-bit standard and 32-bit optimized builds:

Test (x86, 32-bit) 32-bit (standard) 32-bit (optimized) +/-

ATIS	15.4	21	-26.67%
alter-table	15	16.3	-7.98%
big-tables	13.7	12.5	9.60%
connect	77.6	133	-41.65%
create	343.7	350.6	-1.97%
insert	760.3	1043.8	-27.16%
select	394.8	384.2	2.76%
transactions	10.8	18.6	-41.94%
wisconsin	6.6	10.1	-34.65%

The standard build uses gcc instead of SunStudio, but I don’t get the same performance increases that Krish saw - in fact, I see reductions in performance, not improvements at all. I’m going to rebuild and retest, because I’m convinced there’s a problem here with the builds that I’m not otherwise seeing. I certainly don’t expect to get results that show a 27% reduction in insert speed. That said, a 10% big-table increase is interesting. I’ll redo these builds and find out if the slow down is as marked as it here.

Here’s the comparison for standard builds between 32-bit and 64-bit standard builds on x86:

Test (x86, standard) 32-bit

64-bit +/-
ATIS	15.4	13.5	14.07%
alter-table	15	10.6	41.51%
big-tables	13.7	10.6	29.25%
connect	77.6	76.4	1.57%
create	343.7	346	-0.66%
insert	760.3	681.6	11.55%
select	394.8	254.8	54.95%
transactions	10.8	10.7	0.00%
wisconsin	6.6	5.8	13.79%

There are some incredible differences here - more than 50% increase in SELECT, and 30% for the big-tables test show that there is some advantage to having the 64-bit builds on x86 enabled.

Unfortunately I’ve had problems with the 64-bit optimized builds on my machine, so I haven’t completed optimized test comparisons.

On SPARC, Sun Studio is used as the default compiler, and the standard 32-bit and 64-bit show little difference:

Test (SPARC, standard) 32-bit 64-bit +/-

ATIS	28.6	27.5	4.00%
alter-table	27	26.7	1.12%
big-tables	26.9	29.4	-8.50%
connect	166.3	173.6	-4.21%
create	155	143.1	8.32%
insert	1577.3	1572.3	0.32%
select	807.4	761.6	6.01%
transactions	19.5	18.75	4.00%
wisconsin	11.1	11.4	-2.63%

Overall, a pretty insignificant difference here.

Now let’s compare the standard and optimized builds using Krish’s flags on SPARC:

Test (SPARC) 32-bit (standard) 32-bit (optimized) +/-

ATIS	28.6	27.75	3.06%
alter-table	27	26.25	2.86%
big-tables	26.9	25	7.60%
connect	166.3	162.5	2.34%
create	155	145.25	6.71%
insert	1577.3	1551.5	1.66%
select	807.4	769.625	4.91%
transactions	19.5	16.875	15.561%
wisconsin	11.1	10.875	2.07%

The tests here show little significant difference between the standard and the optimized builds, although 6-7% would probably be enough to prefer an optimized build if you wanted to build your own.

Now let’s compare the optimized, Sun Studio 12 builds running in 32-bit and 64-bit:

Test (SPARC, optimized) 32-bit 64-bit +/-

ATIS	27.75	27.3	1.65%
alter-table	26.25	26.6	-1.32%
big-tables	25	25	0.00%
connect	162.5	162	0.31%
create	145.25	154.3	-5.87%
insert	1551.5	1535.1	1.07%
select	769.625	771.2	-0.20%
transactions	16.875	19.1	-11.65%
wisconsin	10.875	10.7	1.64%

The differences are virtually non-existent, and taking the reductions and increases in performance overall, there’s probably little difference.

The overall impression is that on x86 the improvement of 64-bit over 32-bit is significant enough that it’s probably a good idea to make 64-bit the default. On SPARC, the difference in the optimized builds is so slight that for compatibility reasons alone, 32-bit would probably make a better default.

I’ll probably be re-running these tests over the next week or so (particularly the x86 so I can get a true comparison of the 64-bit optimized improvements), and I’ll try the T1000 which I upgraded to snv_81 over the weekend, but I think indications are good enough to make a reasonable recommendation of 64-bit over 32-bit.