Ideas, releases, practical guides, and perspectives from the people building with MySQL.
So, within MySQL it's not 'allowed' to release or name anything
prefixed with 'My' (but MySQL of course). An example is MyODBC
doesn't exists anymore, it's Connector/ODBC.
But you see lots of successful MyThis and MyThats.
Bah, anyway, there are lots of them of course. What's 'yours' on
the net?
Back around November 2005, I started working on query profiling in MySQL via the SHOW PROFILE and SHOW PROFILES commands. It’s been an interesting ride, but profiling support is finally available in public releases of MySQL starting with MySQL Community 5.0.37!
I had a few thoughts on the process and the feature that I’d like to share:
It’s been rough — Although everyone at MySQL who had seen the patch had wildly positive feedback about it, it took almost a year and a half to get things committed. Chad Miller took up my cause (back in December?) with the profiling patch as well as many others, and things actually started making progress. …
[Read more]
I am a fan of using GUIDs as primary keys. They are
database
independent, object independent and I can either create them in
my
application or in the database (MySQL since 5.0 has had a
UUID()
function but since it does not replicate I don't use it
normally...
this has been fixed in 5.1 though).
What I have heard for years was that Innodb didn't handle them
very
well. I have had my doubts, but never taken the time to see if
this
was true or not.
This week I finally got around to putting this to a test.
/mysqlslap --concurrency=1,50,100,150,200,250,300 --iterations=10
--
number-int-cols=2 --number-char-cols=3 --auto-generate-sql
--auto-
generate-sql-add-autoincrement
--auto-generate-sql-load-type=update --
auto-generate-sql-execute-number=10000
--auto-generate-sql-unique-
write-number=10 --auto-generate-sql-write-number=100000 …
[Update 16/03/2007]: Wrapped up a short post about the proceedings.
Next Thrsday, March 15th 2007, I'll give a short presentation on HTTP Load Balancing & High Availability with Wackamole at the PHP User Group Dortmund. We can use eZ System's office (cheers!) at Emil-Figge-Strasse 80. If you're in the area consider saying hello. We meet at 18:30 (that's 6:30pm :-) and I'll start with the presentation as soon as everybody is there and everything is set up.
You'll learn about how to reliably spread a lot of HTTP requests over a set of machines and when and why it is a good idea to do so. I'll shed some light on the confusion (even if you're not aware of any) about Load Balancing and High Availability and how they relate to scaling HTTP. That done, …
[Read more]This is not about how to write a Stored Procedure (SP), But how to efficiently write a SP.
MySQL Query Browser
The easiest way to write a SP is to use a MySQL Query Browser, Just select the database and right click the dB and "Create Stored Routite..." This shall help you do easily modify, edit and create procedures.
Command Line
Unfortunately, not many of the dB guys could have access to GUI and create/edit access permissions, they may have to rely on the CLI. This is where create / edit of SPs is the most tedious. Lets set something in our system, before we log into MySQL.
~/.bashrc add the following...export EDITOR=vim VISUAl=vim
~/.vimrc file, and add the
following...set noai set nonu
delimiter //
This is not about how to write a Stored Procedure (SP), But how to efficiently write a SP.
MySQL Query Browser
The easiest way to write a SP is to use a MySQL Query Browser, Just select the database and right click the dB and "Create Stored Routite..." This shall help you do easily modify, edit and create procedures.
Command Line
Unfortunately, not many of the dB guys could have access to GUI and create/edit access permissions, they may have to rely on the CLI. This is where create / edit of SPs is the most tedious. Lets set something in our system, before we log into MySQL.
~/.bashrc add the following...export EDITOR=vim VISUAl=vim
~/.vimrc file, and add the
following...set noai set nonu
delimiter //
If you count the lines of code in the MySQL Cluster (NDB) test suite (mysql-5.1/storage/ndb/test - and exclude the old ODBC stuff) you come up with about 104000 lines of code. This is in contrast to the approximate other 350,000 lines of code for the NDB engine (excluding the handler, which is an additional 12,000 lines - this isn’t tested much by the NDB test suite… mysql-test-run.pl is meant to take care of a lot of that).
If you go and check the MyISAM tree, it’s only 40545 lines of code - for the entire engine. That’s right, the MySQL Cluster test suite is about 2.5 times the size of MyISAM.
If you look at mysql-test-run.pl tests, which are just lists of SQL commands with static data, it comes up at 250,000 lines (that excludes result files). The NDB tests do things programmatically - so can generate large amounts of data and different loads quite easily.
The architecture of the NDB tests (commonly referred to …
[Read more]This week I talk about the MySQL Query Cache.
Direct play the podcast here:
http://tinyurl.com/22ju9q
Subscribe to the podcast by clicking:
http://phobos.apple.com/WebObjects/MZStore.woa/wa/viewPodcast?id=206806301
You can Direct download all the oursql podcasts at:
http://technocation.org/podcasts/oursql/
Show notes:
Listener Feedback:
http://dev.mysql.com/doc/refman/4.1/en/mysql-commands.html
News:
Daylight Savings Time and how to check your system:
…
Thanks to Guilhem Bichot, we are adding some support for Query Cache to Prepared Statements. Should become public in one of the future 5.1 versions. It took only 3 years of procrastination and a few days of diligence to do it.
A couple of buddies pointed me toward Sys-Con Media's Top 150 Information Technology Heroes, and I have to admit it was fun (once you click past the obstructing ad anyway). While my first reaction was to want to point out omissions (see below), what's not to like about a list that mixes Dennis Ritchie, Luca Cardelli, Edsger Dijkstra, and Charles Babbage?
However, you have to get a bit cranky with the fact that Jim Gray was left out. (His omission is even more surprising given recent events should have kept him on the authors' mind.) Jim's work on transactions was foundational for both relational databases and transaction processing (TP) monitors. Moreover, the whole web/Java application server category (WebLogic, WebSphere, .NET, ...) is really just a TP monitor rearchitected for web processing. (By the way, the reason it was not called a web/Java TP monitor in the first …
[Read more]