Learning Something New

It has been quite a while since I wrote on my blog and even longer since I had something to consistently write about.

You know how it is, there is only so much time in a day to do things and so many things to do… and work and family have taken priority. But I keep having this ‘itch’ – to stretch a little. Learn something completely new. Something that forces me to grow and play on my computer again just because it fascinates me.

For quite a while now I have been hearing about “Big Data” and “data science”. The idea of taking raw data and looking at it in ways that bring new understanding and knowledge intrigues me.

It reminds me of when I took my GIS class at University. How changing the presentation of data from simple rows and columns and instead placing it “over” a map completely alters how you perceive the data.

I admit, I have been talking a good game up until now, but have not actually done much about it.

So here I am, publicly admitting to the Internet that I am actually going to do something about it.

I will be expecting you to keep me honest about this.

So my plan of action:
1) I have started my paperwork to go back to school for Mathematics. Since my BS is in Computer Information Systems (and not CS) and I remember mostly nothing about mathematics, I have to go all the way back to the beginning (Freshman Algebra) and work my way back up.
* Actionable item: finish my paperwork within 2 weeks so I can be admitted back into school and actually get started.
2) I am currently reading O’Reilly’s “Doing Data Science” by Cathy O’Neil and Rachel Schutt. I at at the end of Chapter 2 and have hit the EDA exercise only to realize I honestly know jack about R.
* Actionable item: Learn at least the fundamentals of R so I can do the simple exercise.
3) Learning the basics of R. Yeah – I luckily bought O’Reilly’s book “Learning R” by Richard Cotton at the same time I bought “Doing Data Science”. Guess I should have started with this book instead.
* Actionable item: to learn the fundamentals of R I will be going through Part 1 of the book. Each chapter in that of course has its own quiz and exercises as well.

I’ll try to keep the blog up to date on how my progress with the books and school go.

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 opened [3]. (Don’t forget FLUSH TABLES and FLUSH TABLES WITH READ LOCK)
  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!

Tek Speaker Badge

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.

←Older