If you've been reading enough database related forums, mailing lists or blogs you probably heard complains about MySQL being unable to handle more than 1.000.000 (or select any other number) rows by some of the users. On other hand it is well known with customers like Google, Yahoo, LiveJournal,Technocarati MySQL has installations with many billions of rows and delivers great performance. What could be the reason ?

The reason is normally table design and understanding inner works of MySQL. If you design your data wisely considering what MySQL can do and what it can't you will get great perfomance if not, you might become upset and become one of thouse bloggers. Note - any database management system is different in some respect and what works well for Oracle,MS SQL, PostgreSQL may not work well for MySQL and other way around. Even storage engines have very important differences which can affect performance dramatically.

The three …

I'm reading through an excellent JMP Securities research note called "Turning the Software Model Upside Down: The Proliferation of Open Source." [Subscription required.] It's a good overview of the open source business phenomenon - the how's and why's open source is increasingly dominating software - but I particularly like its analysis of proprietary vendor's likely responses to open source:

• Proliferation of enterprise license agreements ("ELAs"?). Large enterprise software vendors such as Oracle Corporation, Microsoft and IBM may aggressively pursue ELAs with customers. Our due diligence suggests Oracle has employed this practice over the past few years. ELAs reduce the cost advantages of open source software since they are often structured as "?all you can eat" type of deal. Effectively the …

There are other ways to do this, but I thought I'd demonstrate how you can use information_schema to build commands to send back to MySQL;

shell> mysql -N -e "select CONCAT('REPAIR TABLE ', TABLE_SCHEMA, '.', TABLE_NAME, ';') FROM information_schema.tables WHERE ENGINE='MyISAM' AND TABLE_SCHEMA!='mysql' AND TABLE_SCHEMA!='information_schema';" | mysql

All I've done is formatted the result to be valid SQL. If you needed to massage your resuts a little bit, you could use a scripting language in between as a filter before sending back to MySQL. You could also use xargs to throw the lines back to MySQL one at a time (although I'm still trying to fix a use for this):

mysql -N -e "select CONCAT('REPAIR TABLE ', TABLE_SCHEMA, '.', TABLE_NAME) FROM information_schema.tables WHERE ENGINE='MyISAM' AND TABLE_SCHEMA!='mysql' AND TABLE_SCHEMA!='information_schema';" | tee /tmp/repair_log.txt | xargs -d \\n …

ERROR: The requested URL could not be retrieved (yeah, site didn’t work when i clicked on it from RSS).

Matt uses this bit of ruby code to demonstrate that here you can’t ever not close the file handle:

File.open('something.txt') do |fd|
# Manipulate the file through 'fd'
end
# File handle is now closed

Which seems pretty cool. However, a good C++ programmer can also acheive the same (or better) things!

For example, in NDB (well, in the portability library we use inside NDB) we have a class called Guard. The constructor for Guard pthread_mutex_locks a mutex. The destructor unlocks it. So, for when you’ve got some simple mutual exclusion you need doing, you can have bits of code like this:

{
Guard g(m_config_mutex);

…

Last week I spoke to Lenz Grimmer, MySQL Community Relations Manager for Europe, ME and Africa, about the Users Conference, PBXT and the Community.

The interview has been featured under Articles on the MySQL home page.

For those of you that missed it, here is the link to the interview again:

Interview with Paul McCullagh, developer of the PrimeBase XT Storage Engine.

At this week I came across great blog of Peter Zaitsev and Vadim Tkachenko - two developers from High Performace Group at MySQL Inc. This blog’s name is MySQL Performance Blog and, as you probably assume, it is about misscelaneous sides of MySQL optimization and related stuff. So, if you are MySQL administrator or, even, developer, that works with MySQL, I would advise you to take a look at this blog - there are lots of useful MySQL optimization info.

Tags: mysql, high, performance, …

Do you has what it takes to join the MySQL Documentation team? The Guts? The Determination? The Five Bucks?

If you think you do, send me an email.

Those confused can click here.

Guiseppe Maxia recently posted on Filling Test Tables Quickly. However, he only gave examples for numbers, words and dates. This article will deal with randomly inserting images into a database — particularly useful for testing images stored in the database versus the filesystem. This article was inspired by Guiseppe’s article.

[note: there will be a post soon with my presentation on storing images in the MySQL filesystem, and there will be ample opportunity for discussion then, especially since everyone making a claim will have tested their system, given this code.]

Getting images into the database is not easy. All the tutorials I have read have used a programming language to get images into a database. Using cat and similar unix tools, I have attempted to be able to insert an image into a …

OK, so in case anyone didn't notice, I've had quite a few problems with spam comments and trackbacks on my blog. What does this have to do with the MySQL Forge Wiki? Well, Tim Bunce had an excellent comment on my post yesterday that in order to prevent the Forge Wiki from duplicating content of the documentation, I should put links to the MySQL Manual in the wiki pages, to show contributors that the Forge Wiki is a place to put community-driven content that enhances already existing documentation.

In fact, Tim's comment was so good, that I accidentally deleted it when removing the 100+ spam comments I received between yesterday and today. Sorry Tim!.

Anyway, I have gone ahead and updated the Server …