Monday, November 30, 2009

Books for your Holiday reading

Once again the holidays season is upon us and many of you will be looking for gifts. I would like to recommended the following.

For many years I felt the best overall MySQL book was The MySQL 5.0 Study Guide. It is still a very good book but it does not cover third party tools and is getting slightly dated. My new favorite MySQL book is MySQL Administrators Bible . It covers everything from installing and monitoring MySQL servers to tuning and scaling. This book is written in a clear, easy to read style and deserves to be on the bookshelf of any MySQL DBA. This will be one of those books that you will dog-ear pages and festoon with PostIt notes.

The next book is for those of you wanting to expand your PHP knowledge . I have been using PHP back when it was called Personal Home Page (and using it with a free database from tcx.se called MySQL) and have a habit of skimming every PHP book I see in bookstores (and databases, Linux, and a few others; My family knows this and avoid my book store trips). Particularly interesting for those in the MySQL world is the information under Workload-driven Performance Tuning. I started skimming this book and realized that there was a lot of good information inside the covers that made it worthy of purchase.

Please note that I get no kickbacks from Amazon and use their links as anyone in the world seem to have ubiquitous access to Amazon. And that I have met half the authors pairs for each book. I worked with Morgan Tocker and have met Sheeri Cabral at the last few Users Conferences. Nothing against John Coggeshall or Keith Murphy!

And lastly I recommend Fool for light riding. It is a re-telling of King Lear as written by the court fool. There is nothing in this book that will make your code run better or your server return data more efficiently but it a very funny read.

Friday, November 13, 2009

North Texas MySQL Meeting -- now with pizza!

At the next North Texas MySQL Users Group meeting we will be featuring pizza, soft drinks and beer. We will also have TWO presenters. Do if you are planning to be in Dallas on December 7th, please drop by!

Sun Microsystems will host the next Dallas MySQL User Group Meeting

When: Monday, December 7, 2009 | 6:00 to 8:00 pm

Where: Sun Microsystems, 7th Floor, 16000 Dallas Tollway, Dallas, TX

Map: http://bit.ly/ZnYoS

Are you interested in learning more about MySQL's new server release cycle and how it impacts the MySQL roadmap? Or are you looking to get the latest update on storage engines or development and administration tools? Are you investigating data warehousing and analytic solutions?

Then come join the North Texas MySQL Users Group, Benjamin Wood, Senior MySQL Systems Engineer, and Kathy Guzman, Senior Account Manager, from MySQL and Robin Schumacher, VP Products/Calpont for an interactive discussion on these MySQL hot topics and more. Pizza, beer, and soda and the opportunity to connect with other local MySQL Users provided!

Agenda

* How MySQL's New Server Release Cycle works and what's next
* Storage Engine Update
* What's New with the MySQL Query Analyzer and MySQL Workbench
* Building High Performance Analytic Databases
* Q & A / Roundtable
* Beer/Soda/Pizza

Featured Speakers:

- Benjamin Wood is a Systems Engineer with the MySQL group of Sun Microsystems. He has spent the last 12 years working with database technologies, including 8 years as a production DBA.

- Robin Schumacher, VP Products/CalPont, will lead a discussion on building high performance analytic databases.

Learn more about thse North Texas MySQL Users Group »

Register now to reserve your spot! http://www.mysql.com//news-and-events/events/dallasmeetup.php

Friday, November 6, 2009

Oracle Express Edition first steps for MySQL DBAs

I have had a few MySQL DBAs ask about how to get started learning Oracle. I will admit that it has been on my to-do list for quite a while1. It never hurts to know more than one database system and a great deal of DBA help wanted ads mention Oracle. Someone once said that you must make sure your capabilities exceed your limitations2 and recently I have been feeling limited when others have started to talk about Oracle capabilities.

So what does it take for a MySQL DBA to get their hands on their own Oracle instance? I used my Ubuntu box to go to Oracle's web site to get the free Oracle XE software.


  1. Download and feed to package manager

  2. Add my account to dba group

  3. As root, /etc/init.d/oracle-xe configure to set passwords and ports



I pointed my web browser to http://127.0.01/apex and got the page you see in image with this blog. Now I need to find my copy of Hands-On Oracle Database 10g Express Edition for Linux.

1. I also have a stack of DB-2 and SQL Servers books that were picked up at Half Price Books to read through. I am always looking for better ways to express database concepts for MySql exams. Writing up something similar to this entry for DB2 and SQL Server is also on the list.

2. This quote has attributed to Bruce Lee and several others. And it always gota groan from my kids when I used it on them.

Tuesday, November 3, 2009

Cardinality

Last night I was asked about index cardinality. One of the members of the North Texas MySQL Users Group was using phpMyadmin and noticed an element marked 'cardinality' and asked me what it meant. And I will admit I was stumped.

The manual says:

ANALYZE TABLE determines index cardinality (as displayed in the Cardinality column of SHOW INDEX output) by doing ten random dives to each of the index trees and updating index cardinality estimates accordingly. Because these are only estimates, repeated runs of ANALYZE TABLE may produce different numbers. This makes ANALYZE TABLE fast on InnoDB tables but not 100% accurate because it does not take all rows into account.

MySQL uses index cardinality estimates only in join optimization. If some join is not optimized in the right way, you can try using ANALYZE TABLE. In the few cases that ANALYZE TABLE does not produce values good enough for your particular tables, you can use FORCE INDEX with your queries to force the use of a particular index, or set the max_seeks_for_key system variable to ensure that MySQL prefers index lookups over table scans. See Section 5.1.3, “Server System Variables”, and Section B.5.6, “Optimizer-Related Issues”.


Clear, huh? Well not 100% for me so I went back to searching. Other finds on the net say that cardinality is a measure of how accurate an index is and that a UNIQUE index would have the highest cardinality. So the more unique the index entries are, the more higher the cardinality.

Which brings up another question: How often do you maintain your indexes? Whay clues tell you to do maintenance? Please share your recommendations!


BTW we will have pizza at the next meeting of the North Texas Users Group, so see you December 7 at 7PM at the Sun offices, 16000 Dallas Tollway in suite 700!