Wednesday, 27 June 2012

Pythian at MySQL NoSQL & Cloud Conference & Expo, Buenos Aires

It’s with great pleasure that I announce that my friend and colleague, Francisco Bordenave, will be talking about MySQL Replication at 2:10pm on the 28th of June 2012. This is the 1st Latin American MySQL, NoSQL and Cloud Conference in Buenos Aires being held at the Hilton Hotel.
Replication is at the heart of many highly available MySQL installations. Francisco will be giving an introduction to replication, the fundamentals behind the feature and he will be disclosing some of his tips and tricks for monitoring and troubleshooting. He will also be talking about some of the awesome features that Oracle are releasing in the next major version, 5.6.

If you use replication for your product then this is one not to miss!

There are some other great talks going on this week at the conference. If I was at the conference I would be keen to see;

How Mozilla uses Puppet to manage MySQLBrian Hourigan – Mozilla Foundation
Puppet remains on my list of ‘things-to-do’ so this would certainly keep me engaged. Mozilla are no stranger to larger operations so their take on the tool should be an interesting talk.

High Availability solutions for MySQL - Massimo Brignoli – SkySQL
Highly available systems are generally important in our line of work so some further insight from Massimo from SkySQL would be a session worth attending.

Improving Performance with Better IndexesRonald Bradford – MySQL Expert
I would recommend this for anyone tuning or writing SQL code. Ronald is the author of the recent Oracle Press book, “Effective MySQL Optimizing SQL Statements“. Rest assured that this is full of sound advice (from an Oracle Ace Director no less!).

Write Optimization is the Right Read Optimization - Martin Farach Colton – Tokutek
The guys at Tokutek are doing some wonderful work on their TokuDB storage engine. Catch up with them for an explaination of fractal indexes and what their storage engine does to enhance the performance of your MySQL instances.

Not forgetting that Oursql Podcast star, Sheeri K. Cabral of Mozilla fame, is going to be presenting a session on MySQL Security at the same time as Francisco’s talk, so you can attend that if you’re a MySQL Replication guru already but want to brush up on your security fu.

If you’re attending, we hope you have a wonderful time and if you’re not there then make sure you catch up with all the latest news via Twitter hashtag #mysqlnosqlcloud

Thursday, 5 January 2012

MySQL pager

There are a vast amount of tips and trick ode to the MySQL command line interface. One thing you'll be quick to notice with MySQL's client program is that the stdout formatting isn't always as readable as you may want. Also the fact that when viewing the list of variables set on the server or the statuses you'll surely find yourself scrolling and searching for the values you're specifically seeking. Well take the pain out of listing by making use of the mysql pager functionality. I'm not sure the right word for it, 'macro'?

From your mysql prompt change your pager by issuing the likes of;

Step through the INNODB STATUS output
mysql> pager more

grep-ing your output;

Sleeping Connections

mysql> pager grep -i sleep
PAGER set to 'grep -i sleep'
mysql> show processlist;
| 152 | squeezecenter | localhost | squeezecenter | Sleep | 36 | | NULL |
3 rows in set (0.02 sec)

Grouping variables/status counters together for easy eye-balling

mysql> pager grep -i 'key'
PAGER set to 'grep -i key'
| Com_assign_to_keycache | 0 |
| Com_preload_keys | 0 |
| Com_show_keys | 0 |
| Handler_read_key | 611 |
| Key_blocks_not_flushed | 0 |
| Key_blocks_unused | 14343 |
| Key_blocks_used | 10 |
| Key_read_requests | 4197 |
| Key_reads | 13 |
| Key_write_requests | 144 |
| Key_writes | 140 |
291 rows in set (0.03 sec)

There is the potential to do some really cool things with this;

Using Maatkit/Percona toolkit or custom scripts

mysql> pager mk-visual-explain
PAGER set to 'mk-visual-explain'
mysql> EXPLAIN select a.title, from albums a join contributors c on a.contributor = where LIKE '%Monkeys%';
+- Filter with WHERE
| +- Bookmark lookup
| +- Table
| | table c
| | possible_keys PRIMARY
| +- Unique index lookup
| key c->PRIMARY
| possible_keys PRIMARY
| key_len 4
| ref db.a.contributor
| rows 1
+- Table scan
rows 483
+- Table
table a
2 rows in set (0.00 sec)

Don't forget to revert to normal;

mysql> nopager
PAGER set to stdout