Fall Conferences

I am pleased and proud to announce that I have been accepted to speak at the MySQL Connect and Zend Conference this fall.

The MySQL Connect conference is being held Sept 29-30th in San Francisco and I will present on my beginners journey into NoSQLand with MySQL’s NoSQL implementation . Much of the technology for this talk I was not overly familiar with, so it is a lot of fun and very enlightening for me.

This years Zendcon is October 22 – 25th in Santa Clara and I will be presenting on MySQL replication and server variable tuning. Both subjects that I enjoy and plan to update for the latest versions of MySQL.

So if you plan to be at either conference – be sure to find me so we can sit down for a chat and a drink or 2.

updating MySQL statistics

MySQL statistics are used by the optimizer for query plans. Having up to date statistics helps the optimizer “choose” the least expensive way to execute a query.

I know that if I have a query using an index that it should not be and/or performing poorly, I can always run ANALYZE TABLE to update the statistics. But I also wanted to learn when MySQL implicitly updates its internal statistics. So since I can never remember anything – here is a consolidated list taken from a variety of places for me to try and remember things.

1. 1. ANALYZE TABLE [1]
2. 2. OPTIMIZE TABLE [2]
3. 3. When a table is first opened [3]
4. 4. the mysql client starts if the auto-rehash setting is set on (the default) [3]
5. 5. Metadata commands (SHOW INDEX, SHOW TABLE STATUS and SHOW [FULL] TABLES) or the matching INFORMATION_SCHEMA tables/stats [4]
6. 6. When 1 / 16th of the table or 2Billion rows has been modified, whichever comes first. ./row/row0mysql.c:row_update_statistics_if_needed [4]
7. 7. It is implied that some DDL for a table that causes it to be rebuilt can also trigger an update of statistics. (Ex: ALTER, CREATE INDEX, etc.) [1][2][6]
8. 8. Execute myisamchk –stats_method=method_name –analyze [5]

For more information on how to control the collection of the table statistics see
– http://dev.mysql.com/doc/refman/5.5/en/myisam-index-statistics.html
– http://dev.mysql.com/doc/innodb/1.1/en/innodb-other-changes-statistics-estimation.html

I am sure I am forgetting other triggers for an update of the table statistics. If anyone knows of any others, please let me know so I can add it to the listing. And if I am incorrect on any of them – be sure to let me know that also.

References:
[1] http://dev.mysql.com/doc/refman/5.5/en/analyze-table.html
[2] http://dev.mysql.com/doc/refman/5.5/en/optimize-table.html
[3] http://dev.mysql.com/doc/innodb/1.1/en/innodb-other-changes-statistics-estimation.html
[4] http://www.mysqlperformanceblog.com/2011/10/06/when-does-innodb-update-table-statistics-and-when-it-can-bite/
[5] http://dev.mysql.com/doc/refman/5.5/en/myisam-index-statistics.html
[6] http://dev.mysql.com/doc/refman/5.6/en/innodb-performance.html#innodb-persistent-stats

Jax PHP User Group Presentation

For all the people that were looking for the presentation I gave last night to the Jacksonville PHP user group – I have uploaded it to my presentations page as a PDF. Included are all my speaker notes and references as well.

Oracle ACE

I pleased and proud to announce that I have been accepted to be an Oracle ACE for MySQL.

For those not familiar with it, Oracle ACEs are community members that are nominated and eventually chosen for their (in my case) MySQL community involvement. To learn more about the Oracle ACE program, check out the Oracle page or the FAQ’s.

And just cause I want to show it off a little – my ACE page

A huge thank you to Keith and Dave for submitting my name. I am truly humbled to have been chosen.

Cha, Cha, Changes

As some know I left MySQL Support last week and started working for Kaplan Professional as their MySQL DBA.

To all my old colleagues at MySQL Support – I miss you. You have been my friends, teachers, mentors and co-conspirators. Thank you for everything and don’t be strangers.

To my new colleagues at Kaplan, thank you for the wonderful opportunity you have given me. I look forward to learning and working with you

Tek

Man time goes by fast. Only about a week left before Tek. I have to admit to being rather excited about it. I will be giving a tutorial on Optimizing MySQL Essentials that will cover many areas including queries and the server. I also have a regular session called “Why be Normal” that will be an intro into data normalization.

Both of these talks are new for me, so it has been quite a lot of fun putting things together. I hope you will come out and join me and others of the PHP Community at Tek. If you haven’t registered for it yet, there is still time. So get out there!

Voices of the Elephpant

Crazy but true – I am on Voices of the Elephpant this week. I had a great time talking with Cal about PHPWomen, so be sure to check it out!

And if you know someone who is one of the unsung members of the PHP Community, be sure to nominate them for an interview at http://voicesoftheelephpant.com/nominate-a-voice/

MySQL’s undo log

Short Braindump:
– undo log == rollback segment
– everything kept within the tablespace
– primarily for removing data changes that had been written to disk when a crash occurred, but should not have been written, because they were for uncommitted transactions.”
– “History list length 6 is number of unpurged transactions in undo space. It is increased as transactions which have done updates are commited and decreased as purge runs. ” – comes from http://www.mysqlperformanceblog.com/2006/07/17/show-innodb-status-walk-through/
– number of undo log entries for a specific transaction can also be found in the TRANSACTION section of INNODB STATUS

References:
1) http://forge.mysql.com/wiki/MySQL_Internals_Files_In_InnoDB_Sources – “Some Notes About Structures”
2) http://dev.mysql.com/doc/refman/5.1/en/innodb-multi-versioning.html
3) http://dev.mysql.com/doc/refman/5.5/en/innodb-multiple-rollback-segments.html

Dutch PHP Conference 2011

I am pleased and proud to announce that I will be presenting at this years Dutch PHP Conference from May 19th to the 21st in Amsterdam, Netherlands.

I will be giving a tutorial on the basics of optimizing MySQL along with a session on character sets. I, in particular, look forward to the tutorial since it covers areas that I find fascinating – query and server optimization.

On a personal note, neither I or my husband have ever been to Amsterdam before. So since we have never yet had the opportunity to play tourist there, my husband plans to join me on the trip for the conference. We hope to play tourist while there and at least see the Van Gogh museum. Though hopefully we will get to see much more then just that.

BarCamp Orlando is announced!

I missed last years BarCamp Orlando since it was being held Easter weekend. This year I am hoping to make it!

This year it will be Saturday, April 2, 2011 from 9:30 am to 6:00 pm at:

Wall St. Cantina
19 N Orange Ave
Orlando, FL 32801

Not sure if I will present yet.. But thinking about it.