Oracle ACE Award - Baron Schwartz

A big congrats to Baron Schwartz for his Oracle ACE Award.

Baron is extremely respected in the MySQL community. He is the Chief Performance Architect at Percona, an independent services provider for the MySQL database server. He is the lead author of High Performance MySQL, and has created several popular open-source software projects for MySQL, including Maatkit.

Thank you Baron.

Just in case you missed the press release:

Oracle Provides Early Access to MySQL 5.6 New Features

At OSCON (the O’Reilly Open Source Convention) in Oregon, Oracle announced that early access to new features of MySQL 5.6 is available for the community to test, deploy and provide feedback.  Read more here

Take the time now for gains later.

Regardless of which data warehouse paradigm you follow or have heard of, Kimball or Inmon. We should all agree that the data warehouse is often a requirement for business. Different people want different things and they all want it from your data. The data warehouse is not a new concept and yet they are over looked at times. A warehouse is never complete, it is an evolving entity that adjusts with the requirements it is given. It is up to us to make sure that the access to enterprise data in an accurate and timely manner is easy and the standard. MySQL can handle a data warehouse perfectly.

MySQL databases are designed in numerous ways, some good some bad. A warehouse can take that data and organize it for the best use of others. What concerns or issues do you often hear when it comes to gathering data from your database? It is easy for all of your developers to query and get the same data? How many ways does your company slice and dice data? Who is your target audience for all of this data? Who wants just the important data? How well do you keep your results accurate?

If these are questions you can relate too but you do have not a warehouse then it is time to consider one. They can be considered to be, to big of a project for the current resources, they never work , poor data quality, always has data inconsistencies. These all are common complaints but those are not complaints of the warehouse but more of the execution behind it.

Use the tools available to you to keep the data updated. ETLs (extract , transform, load) are a requirement of course with data warehouse tables, use them to your advantage. Do not get lazy or let others get lazy. Fix data at the source and re-execute the etl. Do not let “quick fixes” that are not done at the source level. Remember, you do not have to back up the warehouse like you do your source, you can always rerun the ETLs.

Be ready to have eager users try to access your data before your ready to release. Be prepared for complaints. Keeping it accurate and easy to access is the key overall. Have it serve a need first, do not solve everything at once but make an impact with good quality fast data. As I said before a warehouses work is never done, you will continue to add data and available slices of data. Take the time to execute correctly now to have long lasting accurate data.

I am a very big supporter of data normalization but a warehouse also has a place and you can have big performance gains from using it.

There is a lot of resources on the net to help you with this:

http://www.mysql.com/why-mysql/data-warehouse.html

http://www.mysqlperformanceblog.com/2010/07/15/data-mart-or-data-warehouse/ by Justin Swanhart

http://www.mysql.it/why-mysql/white-papers/mysql_wp_for_data_warehousing.pdf

http://www.kimballgroup.com/

http://www.amazon.com/Data-Warehouse-Toolkit-Complete-Dimensional/dp/0471200247

http://www.inmoncif.com/home/

http://www.amazon.com/Building-data-warehouse-William-Inmon/dp/0894354043

http://download.oracle.com/docs/cd/B10500_01/server.920/a96520/concept.htm

http://en.wikipedia.org/wiki/Data_warehouse

http://en.wikipedia.org/wiki/Extract,_transform,_load

http://opensourceanalytics.com/2006/03/14/data-mart-vs-data-warehouse-the-great-debate/

GOSCON

Innovation Nation & GOSCON 2011

GOSCON returns to Washington D.C. on August 23, 2011

The list of speakers is available here: http://goscon.org/speakers.

I will be at the conference this year in support of MySQL and I am eagerly awaiting to meet everyone.

So please stop by and say hello ! 

Stored procedures and Triggers

Having multiple ways to achieve a task is something we all enjoy as developers and DBAs. We find, develop and learn new ways to do things better and faster all the time.

At the risk of starting a debate, I am curious on others opinions or practices when it comes to Stored Procedures and Triggers. To use them or not versus code based functions ? Best case use versus worst case use? There is no real wrong answer here as it depends on your development application. Certainly some lean one way over another and there are more than enough valid reasons on both sides of the debate.

Here are couple of my thoughts on the topic....

I come from the dot.com bubble era , and from that I rarely use stored procedures or triggers. Back then PHP was still new, Perl dominated websites with the cgi-bin and MySQL did not have stored procedures or triggers. Thank goodness things have changed. Developing in those days, forced developers to develop in certain ways. Hopefully, we all used functions and classes so we could reuse code as much as possible. Yet we all survived without stored procedures or triggers just fine by having the logic in our code. But did that make it better?

During this time I had to interact with a SQL server and the DBA that ran it. My LAMP stack used MySQL except for the one call to the finance department's SQL server for a yes/no value. ( I actually used FreeTDS for this connection and it worked like a champ, my side of things anyway. No comment on the speed or other issues of the SQL server needed here. ) I did not need or care about the table structures and layout of the finance SQL system. I only needed a yes or no answer from it. It was the finance teams system and they protected it, rightfully so. So in that case it made perfect sense, for a stored procedure. I handled the code on my side , made a call into the SQL server got the answer and got right out.

Now days of course we have stored procedures in MySQL, do we use them? Is it just an old school development versus modern development in terms of who uses them? I have seen them used at times when it just made it harder to track down a problem. Tracing code across six different functions and cursing the entire way at the missing comments in the code only to find a CALL statement. So we end up going back to the DB to find the query and hopefully do not break the site as we test changes. At the same time, I think they are great if you do need to compartmentalize your logic away from others.

It really all comes down to your application. Where do you want your logic to live? The biggest thing we need to take into account when we decide this is, what do your benchmarks say about your query and do you value the results? Do you want to take advantage of not passing the data across a network and keeping it local to the database? Are you ok with the a separate cache per connection to a stored procedure versus a globally cached query? Depending on the query and how often it is used plays a big part in that decision.

A simple example of stored procedure using the “menagerie database”  is below:

DROP PROCEDURE IF EXISTS addnewpet;

DELIMITER //

CREATE PROCEDURE addnewpet(IN petname VARCHAR(20) , ownername VARCHAR(20), speciestype VARCHAR(20), gender CHAR(1) , bday date )

BEGIN

INSERT INTO pet (name,owner,species,sex,birth) VALUES (petname,ownername,speciestype,gender,bday);

INSERT INTO event VALUES (petname, date_format(NOW(),'%Y-%m-%d'), 'first visit', NULL) ;

END //

DELIMITER ;

CALL addnewpet('Clifford','Emily','dog','m',date_format(NOW(),'%Y-%m-%d') );

This simple call will add the dog name to both tables when used.

You will notice the one of the problems with stored procedures is that you might end up having to pass a lot of variables that might already be defined in your class.

Triggers are another example where history has had developers learn to query data after insert and/or have scripts check the status of data to then make changes as needed. Triggers avoid this for you and should be used whenever possible. Let MySQL handle the adjustments for you immediately and avoid having to write code just for a simple update. Granted as situations get more complex the need might arise for function based code as well. Make sure that all code is optimized before used in the trigger as trigger options are limited.

Replication concerns with triggers are addressed here:

http://dev.mysql.com/doc/refman/5.5/en/replication-features-triggers.html

A quick example can be shown using the “menagerie database”

mysql> SELECT TRIGGER_NAME, EVENT_MANIPULATION, EVENT_OBJECT_TABLE, ACTION_STATEMENT

FROM INFORMATION_SCHEMA.TRIGGERS

WHERE TRIGGER_SCHEMA='menagerie';

No triggers so we will create one. In this example we can assume that people forget to update the death of pets in the pet table. They note it in events but that is it.

So a simple trigger will keep this updated for us :

delimiter ;;

CREATE TRIGGER ed_update AFTER INSERT ON event

for each row

begin

UPDATE pet p

INNER JOIN event e ON p.name = e.name

SET p.death = e.date

WHERE e.remark = 'death' AND p.death IS NULL;

end;;

delimiter ;

This trigger just updated the pets table for us quickly.

To boil down my thoughts on stored procedures and triggers.... When you are considering a stored procedure make sure to benchmark and test to confirm your ok with the cache results. A stored procedure is best when the global cache is not going to be more effective overall. If tests show that the results are faster handling large data sets with stored procedures versus code base changes then also a good option to use stored procedures. Testing all options is critical.

Triggers can be a great addition to clean up additional scripting work that can be done immediately upon data insert. Again test and confirm to be sure your aware of all the options of using them. Options with triggers are limited so do all the explains ahead of time.

More links here:

http://dev.mysql.com/doc/refman/5.5/en/triggers.html

http://dev.mysql.com/doc/refman/5.5/en/create-procedure.html

http://dev.mysql.com/doc/refman/5.5/en/stored-routines.html

http://dev.mysql.com/doc/refman/5.5/en/explain.html

The MySQL Librarian

The MySQL community team wants to ensure that our community engagement tools remain relevant, popular and above all gives you, the user, a clear strong voice. We are always here for your opinions, comments and feedback. We have started the process of examining what is useful, what could be enhanced and even what could be deprecated. 

 

We are currently reviewing your feedback as we strive to enhance and grow our MySQL community. For example, if you have not yet taken the poll on “What content do you value most on planet.mysql.com” please do. Keep a lookout for exciting news and changes as we move forward with http://planet.mysql.com improvements as well as enhancements with news and events for our community. 

 

In the meantime, it would appear that one of our features has become redundant. The MySQL Librarian was originally created as a collection of the best links found on the web when it comes to MySQL. However, the same content is usually found easily, first and better presented on the community sites http://planet.mysql.com and http://forums.mysql.com . Traffic to the librarian has been on the steady decline as the community also uses the power of search engines to find the same information as well. 

 

Since the MySQL Librarian is only a collection of links, the code and other artifacts will all still be available for our community in their respected locations. However, the function of http://dev.mysql.com/librarian/ we only be available until the end of the month. 

As always, many thanks to the great MySQL community members. 

ATON || NOTA ? Ip address IPV4 ? IPV6 ?

IP address storage is something that some do right and others have done wrong for years. How many times have you seen or created a MySQL table having something very similar to the following.

CREATE TABLE `example_table` (
  `user_id` int(9)  NOT NULL AUTO_INCREMENT,
  `date_recorded` datetime DEFAULT NULL,
  `ip` varchar(15) DEFAULT NULL,
  PRIMARY KEY `user_id` (`user_id`),
  KEY `date_recorded` (`date_recorded`)
) ENGINE=InnoDB ;

Hopefully you never have and never run across it. What an optimized world it would be if that was the case.  It appears harmless, after all what could it hurt ? You need to store your ip address and it has those pesky periods in it, so why not varchar(15)?  

Human readable ips are just that, just for us. The database has no need to keep them in human readable form. So your field that requires a min 15 characters, to be able to store all of the variations of the ip, and another byte to store the length of the string, now requires ~16 bytes. Your wasting space by storing IP data in varchar fields, it is an integer , store it as an unsigned integer.  They are integers treat them that way.   

CREATE TABLE `table_example` (
  `user_id` int(9)  NOT NULL AUTO_INCREMENT,
  `date_recorded` datetime DEFAULT NULL,
  `ip_address`   INT UNSIGNED NOT NULL,
  PRIMARY KEY `user_id` (`user_id`),
  KEY `date_recorded` (`date_recorded`)
) ENGINE=InnoDB ;

Now just saved you ~6 bytes per row which will add up over time.   The ip address just needs to be inserted with the function INET_ATON. 
example below:

 INSERT INTO table_example VALUES (NULL ,NOW() , INET_ATON('213.136.52.29') ) ;

Pulled back out to test :

select ip_address ,INET_NTOA(ip_address) from table_example  ;

+------------+-----------------------+

| ip_address | INET_NTOA(ip_address) |

+------------+-----------------------+

| 3582473245 | 213.136.52.29 |

+------------+-----------------------+

As you can see the example is simple. As long as you enter and pull information from your tables wrapped accordingly you will save space, and gain speed.  But is it worth it just for 6 to 10 bytes? Yes and the reason is hopefully your database will scale and grow. Those wasted bytes will add up. If you wanted an Index on the ip address field then that will also now be smaller and also faster.

http://dev.mysql.com/doc/refman/5.5/en/miscellaneous-functions.html#function_inet-aton
http://dev.mysql.com/doc/refman/5.5/en/miscellaneous-functions.html#function_inet-ntoa

What about IPV6 though ? Ah those tricky ips...

Well this appears to be addressed in 5.6.3 

This example taken right from the 5.6 documentation:

SELECT INET6_NTOA(INET6_ATON('fdfe::5a55:caff:fefa:9089'));
        -> 'fdfe::5a55:caff:fefa:9089'

mysql> SELECT INET6_NTOA(INET6_ATON('10.0.5.9'));
        -> '10.0.5.9'

mysql> SELECT INET6_NTOA(UNHEX('FDFE0000000000005A55CAFFFEFA9089'));
        -> 'fdfe::5a55:caff:fefa:9089'

mysql> SELECT INET6_NTOA(UNHEX('0A000509'));
        -> '10.0.5.9'

http://dev.mysql.com/doc/refman/5.6/en/miscellaneous-functions.html#function_inet6-aton

http://dev.mysql.com/doc/refman/5.6/en/miscellaneous-functions.html#function_inet6-ntoa

SELF Shirts

The SELF Linux show ramps up to full swing tomorrow. We will have the new MySQL 5.5 shirts as well as Linux shirts. Please stop by say hi and get a shirt while they last!

I was glad to see the support for MySQL on day one, can't wait until tomorrow.

http://www.southeastlinuxfest.org/

Monitoring just a blink away...

I bring this up because of recent conversations I have found myself in.

Over the years I have gone back and forth between OSX & Linux laptops. Over these same years I was also monitoring several MySQL databases. I have found numerous server side solutions for you to be able to monitor your database, Nagios and of course MySQL Enterprise Monitor to list a couple. Both of these are great solutions and allow you to try and get some sleep and night.

Years ago, while using OS X, I was enlightened to learn about a free module called Geektool, it is not new, but I often find people are unaware of it.  I have had at least three conversations about it lately. While Nagios and enterprise can send me emails and sms messages, even some that I don't want. Geektool allowed me to have a pulse of whatever I wanted to monitor easily as I was busy working on other things.

Here is how it works, Geek Tool is a system preferences module for Mac OSX. It has the option to display url images and/or output from a script on top of your desktop background. So you can always have the results at a blink of your eye. I used the "shell mode" option and created Perl scripts that live locally in my LAMP database server environment. When these scripts are executed they output a range of information from a count of rows in the processlist , seconds behind master, InnoDB, stats as well as time stamps on the last run of key reports. I had key entities for the company db that I wanted available at a moments notice and displayed on top of my background and yet out of the way from desktop work. You could even tail your MySQL error logs if you wanted. Someone could ask a question and with a blink I could look to the corner of my desktop and have an answer. All of the data is dependent on the refresh thresholds that you determine. So you can have some checks every 10 seconds while another item checked every hour. It is all up to you.

Yes it would be just as easy to run the same Perl script via a terminal when asked a question. Having the results up in the corner of the screen though, I was able to see things becoming issues before they became issues. While I was off writing PHP or Perl scripts, I was also able to watch the database processlist slowly rise or slave start to fall behind and I knew someone was doing something that I needed to check on.

Was I able to catch everything and sleep well at night... No. This is just an additional monitor to keep we aware of more easily as I worked. I had Nagios, MySQL Enterprise Monitor and custom phone call alert system to handle the overnight monitoring.

The Database is of course critical, anyway to have stats and references easily, has always helped. You can use Geektool for numerous things. I happen to have used it to keep quick tabs on key stats for seven different databases, all with one look.

I have not yet found a Linux desktop replacement that I like as well yet, I am searching though.

A screenshot from their site can be seen here: http://projects.tynsoe.org/en/geektool/shots/shot02.jpg They have many more on their site.

URL here if your curious for more: http://projects.tynsoe.org/en/geektool/

Smart Selects for replicated systems

Over the last few years I have worked with a few different companies and their replication databases. Your replication choice is yours and what your needs are. I have enjoyed relayed replication and the reason being is that it gives you the chance to make your applications even smarter. 

One reason is that if you did loose a master your slave can become the new master and you still have replication active as you rebuild the down server.

Another reason is the options it can give you for selects. I have seen some systems that have a replicated slave that is never touched. Granted safe, but money spent on hardware and users complaining about application speed. Whichever scripting language you prefer, you can use all of your replicated databases to your advantage. To be safe all writes should to go the master unless you can control and confirm that nothing from the master would try to write to the same location. Selects however can be expanded.

Often users complaints are speed. Everything must be faster. If your database handle only connects to 1 database then your are limited to the performance from that once database. Java has connection pooling (http://java.sun.com/developer/onlineTraining/Programming/JDCBook/conpool.html) and PHP can be used to do the same task. Personally, although I bias, I feel you will have more control with PHP.

The concept is simple, of course you do not want to hurt performance by unnecessary overhead but these steps are and should be very fast. 

1. connect to the slave
2. execute SHOW SLAVE STATUS
3. check 'Seconds_Behind_Master'
4. Based on threshold you decide you choose which database your going to read from
   1. You can pass this to your connection handle so you have thresholds per query if needed.
   2. You can also create smart connections to never connect to slave if query does not start with select, options are up to you.

This allows you to keep slower data intense queries off the master server. Your options are up to you and what you want to do. How you choose your thresholds depends on your application. How you define what can select from master versus slave only is dependent on your application as well. This is not a replacement for optimization but just expanding your options to get the data your after.

We used three databases in a relayed replication InnoDB environment. We where able to check the third database first , based on thresholds move up to the second database if needed, and lastly run a select on the master if required. Typically the seconds behind master always 0 or under 5 seconds with this TB+ db. If something major was going on then the third database would be further back and the selects would roll to the second database naturally.

This worked very well for our application at the time, and I have used the concept with more than one company. Everything is of course dependent on your application but getting valid results fast to users is a common issue we all face.