We all know that it's very important to test your backups. It's also quite important to use indexes. It's somewhat important to ensure that your users have only the privileges to do what they need to do. But it's extremely important to make sure you dine with your MySQL friends at Pedros on the 23rd of April 2013. Please goto this page --> MySQL Community Dinner <-- and add your name to the comments.
See you there!
Friday, 29 March 2013
Thursday, 21 March 2013
Yet another Percona Live 2013 Post
It was in doubt but today it was confirmed that I'll be in Santa Clara in April for the MySQL conference hosted by Percona. This will be my 4th Percona conference and I'm delighted to be attending again. The Percona guys put on a great conference, nobody can deny them that. Although I'm not speaking at this year's conference, my esteemed colleagues will be. The recently awarded Oracle Ace for MySQL, Marco Tusa, Danil Zburivsky, Francisco Bordenave and Ben Mildren will be delivering talks and I'm thrilled that Pythian founder, Paul Vallée is also going to be in town to talk about how Pythian are driving down human error.
I did submit some papers as I was eager to talk at this year's conference. By my own error I submitted some community flavour papers which were not selected for delivery at the conference. I was dis-heartened for a while and began to convince myself that it didn't matter if I miss Percona Live 2013. I planned to get back to the drawing board and produce some killer-prose for the next cfp. I had all but resigned to the fact PL2013 was off. I decided was going to re-focus my annual budget for other extra-curricular activities; maybe on something other then conference. Then it rained down reasons for me to attend, in fact it felt like monsoon season. There was a force unbeknown to me at work. As I stood in my kitchen, washing the dishes and listening to episode 129 of the OurSQL podcast (because that's how I roll) it was entirely about Percona Live; it hit me that I couldn't stave off this year. Shlomi Noach featured in the discussion about the plethora of great topics in the schedule for the week. It was too much, I couldn't handle it. I couldn't listen to the whole show (sorry Sheeri & Gerry!). All of these ideas/events/facts made it impossible for me to abstain from the transatlantic trip in the name of database geekery.
I did submit some papers as I was eager to talk at this year's conference. By my own error I submitted some community flavour papers which were not selected for delivery at the conference. I was dis-heartened for a while and began to convince myself that it didn't matter if I miss Percona Live 2013. I planned to get back to the drawing board and produce some killer-prose for the next cfp. I had all but resigned to the fact PL2013 was off. I decided was going to re-focus my annual budget for other extra-curricular activities; maybe on something other then conference. Then it rained down reasons for me to attend, in fact it felt like monsoon season. There was a force unbeknown to me at work. As I stood in my kitchen, washing the dishes and listening to episode 129 of the OurSQL podcast (because that's how I roll) it was entirely about Percona Live; it hit me that I couldn't stave off this year. Shlomi Noach featured in the discussion about the plethora of great topics in the schedule for the week. It was too much, I couldn't handle it. I couldn't listen to the whole show (sorry Sheeri & Gerry!). All of these ideas/events/facts made it impossible for me to abstain from the transatlantic trip in the name of database geekery.
- I won a conference pass from Shlomi Noach's blog (thank you, Shlomi)
- 2013 conference is a day longer then 2012 - that's great value!
- SO many potentially great talks and tutorials (too many for 1 person to attend)
- Oracle announced the GA release of 5.6
- Pythian are sponsors of the event, something that we all wanted in 2012
- Oracle announce that they are attending and talking!
- Colleagues I've worked with everyday but have never shared a beer with are attending
- Ex-colleagues and clients will also be in attendance
It's an expensive feat to travel to the States for the week, however after entering openark's competition for a ticket to the conference, I was afforded my pass and a catalyst to organise the trip. I spoke with Marco after Shlomi had announced I had won and he re-enforced that it was an important conference to attend. I couldn't disagree with this guy, after all he's usually right ;-)
As one of the only Pythian-ites not speaking at the event I've promised my team-lead to keep the rest of the team updated, so expect some tweets and blog posts through out the conference.
So maybe it's destiny, maybe it's just the strong magnetism of MySQL and it's ecosystem but I'm looking forward to the week and seeing all of the great people that make it what it is, both organisers and attendees.
See you all in approximately a month. Don't forget Pedro's on Tuesday evening for the MySQL community dinner some food and a great chance to mingle with the MySQL community. Download common schema if you didn't already, it's a really great project and if you're attending the conference be sure to attend Shlomi's talk for more info about QueryScript.
See you in April!
Saturday, 5 January 2013
My2013
So we made it around the sun again, despite the doubt cast by those pesky Mayans. I can't be angry at a culture that were unable to predict their own demise. I wanted to record some goals for the forthcoming 12 months...
- Improve time management skills.
- Learn more about SQL and the relational theory.
- Formal training on new programming language.
- Deeper understanding of innodb internals.
- Deeper understanding of Galera Replication.
Not too many. Realistic.
oh and as per usual - blog more!
oh and as per usual - blog more!
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 MySQL – Brian 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 Indexes – Ronald 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
grep-ing your output;
Sleeping Connections
Grouping variables/status counters together for easy eye-balling
There is the potential to do some really cool things with this;
Using Maatkit/Percona toolkit or custom scripts
Don't forget to revert to normal;
From your mysql prompt change your pager by issuing the likes of;
Step through the INNODB STATUS output
mysql> pager more
mysql> SHOW ENGINE INNODB STATUS \G
...
mysql> SHOW ENGINE INNODB STATUS \G
...
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)
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'
mysql> SHOW GLOBAL STATUS;
| 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)
PAGER set to 'grep -i key'
mysql> SHOW GLOBAL STATUS;
| 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, c.name from albums a join contributors c on a.contributor = c.id where c.name LIKE '%Monkeys%';
JOIN
+- 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)
PAGER set to 'mk-visual-explain'
mysql> EXPLAIN select a.title, c.name from albums a join contributors c on a.contributor = c.id where c.name LIKE '%Monkeys%';
JOIN
+- 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
PAGER set to stdout
Tuesday, 12 July 2011
Upcoming MySQL Events in the UK
As the song goes, "it's been a while".
I wanted to leave a note here for anyone that hasn't read it on other more active blogs but might stumble upon this in a freak search engine accident. There's a couple of notable dates for the UK MySQL scene over the next few months.
Firstly the `MySQL London Meetup` is hosting on the 18th of July 2011 and they have some MySQL rockstars talking and socialising. I'm looking forward to hearing both Zaitzev's Scaling MySQL talk and Andrew Hutchings' (@linuxjedi) words on Drizzle. There's loads of other good material too, so check out the event page.
Later in the year Peter returns to London for Percona Live in October. This is a one day intensive summit packed to the brim with expert talks, advice and all the tricks you can stuff a dolphin with.
There's also an 'open-bar' evening event which intriques me as I'm not sure if it's an excuse for a beer or something totally different. Head over to the Percona site for more details.
I should be attending both so hopefully see you there.
I wanted to leave a note here for anyone that hasn't read it on other more active blogs but might stumble upon this in a freak search engine accident. There's a couple of notable dates for the UK MySQL scene over the next few months.
Firstly the `MySQL London Meetup` is hosting on the 18th of July 2011 and they have some MySQL rockstars talking and socialising. I'm looking forward to hearing both Zaitzev's Scaling MySQL talk and Andrew Hutchings' (@linuxjedi) words on Drizzle. There's loads of other good material too, so check out the event page.
Later in the year Peter returns to London for Percona Live in October. This is a one day intensive summit packed to the brim with expert talks, advice and all the tricks you can stuff a dolphin with.
There's also an 'open-bar' evening event which intriques me as I'm not sure if it's an excuse for a beer or something totally different. Head over to the Percona site for more details.
I should be attending both so hopefully see you there.
Monday, 4 April 2011
The MySQL Community Podcast and other MySQL Resources
Should you find yourself learning MySQL for professional gain or as a hobbyist interest there are some resources that you would be best advised engrossing yourself in to gain that extra inch. Considered a prime cut of opensource software, MySQL is very accessible and well documented. As you peel back the layers you will find a busy and extremely welcoming community ready to assist, collaborate and discuss reams of subject matter.
In terms of official resource you will find a plethora of mysql.com subdomains. One of my regular stops is planet.mysql.com. The custom built blog/news aggregator built by Arjen Lentz, is a rollup of articles submitted and labeled with the `mysql` keyword. Pop on over to read the latest musings from those giving their $0.02 on various topics. You will find plenty of articles on MySQL and frequently articles on forks, tools, findings and guides from some of the loudest voices in the MySQL ecosystem. Names like Lenz Grimmer, Guiseppe Maxia, Baron Schwartz and Ronald Bradford will promptly become familiar names of the MySQL database rockstars. Simply put it's an ace dynamic for the community and a real way to have your voice heard.
There are few more channels that I won't explore too much as they are pretty self explanatory if you're familiar with any interactive web platforms. The forums.mysql.com do exactly as it says on the tin and lists.mysql.com allows you to join and read the MySQL mailing lists. Both are places that can fast become bookmarks in your fave browser. They certainly have made it into my Chrome Bookmark Bar. So besides the 'official' channels (official in appearance anyway) there are some other forms of connecting with the community in an educational or troubleshooting capacity. If you're an IRC user already you can point your fave IRC client at the Freenode MySQL channel. Here you will find many of the most experienced DBAs and DEVs hiding behind their carefully chosen guises. If you're struggling with something in realtime try the IRC channel for some friendly advice. But be forewarned, if you've not tried to solve your own problem before hitting the channel (RTFM) don't be shocked when you're hounded out. I've seen lazy newbies shot down fast by asking shallow questions born from thinking freenode == free support.
My next mention on this whistle-stop tour of MySQL resources is the countless amount of great books written about the product and it's implementations. Browsing a few of the search results on Amazon (or your favourite online book retailer) you will notice that there have been hundreds of books written for MySQL. There is usually a coupling of MySQL with PHP, the popular 'M' & 'P' of LAMP. So depending what you want to learn there's something out there for you. Should you visit my office you'll be sure to find something with Paul DuBois' name on the spine and also the awesome O'Reilly published offerings; "High Performance MySQL" & "MySQL High Availability". I've also got "Expert PHP and MySQL", co-authored by the one and only Ronald Bradford, awaiting my attention. [ ISBN: 9780596807306, 9780596101718 and 9780470563120 respectively ]
So onwards to a format of MySQL ingestion that I've been enjoying and certainly learning from lately. It is the sole contribution to the audio movement in the MySQL community. The OurSQL Podcast is delightfully brought to us weekly by two more MySQL stars, Sheeri Cabral of Palominodb and Sarah Novotny from Blue Gecko. Available from iTunes or the OurSQLcast website so you can copy it to your non-branded mp3 player. Sheeri and Sarah have vast experience with MySQL, working in consultancy roles on one hand and feeding the community with the other. From the haunting title music (trust me it will be bouncing around in your head hours after hearing the latest episode) to the 'Ear Candy' and out-takes, the podcast is a great way to learn from some bonafide experts in the field without the consultancy bills. Sheeri and Sarah guide their listeners through various subjects on the show and are often joined by community favourites such as Brian Aker of Data Differential, Patrick Galbraith of Membase fame and Lenz Grimmer recently retired MySQL Community Relations Specialist. I'm sure that their contact list is far from exhausted and they will brandish many more interesting special guests for our listening pleasure. All the show notes are posted online so if you miss anything first time around you can bet your primary key that they have posted it on the episode's page. As the podcast continues to build popularity with the community the ladies are asking for your feedback via iTunes or their phone line (which you will hear on the podcast) dangling a "swag" shaped carrot on front of our noses for encouragement. So if you listen, be sure to let them know what you think, you might be boasting a shiny new OurSQL mug by the end of the week. A cool little feature that they run on the show is "Where you can see us" which is a run-through of their travel and physical appearances (in actual human form, no less!) across this shiny little globe. This leads nicely into meet ups and conferences.
Conferences and meetups are sweet! Although light on the ground here in the west of England they are certainly another avenue to connect with other MySQL evangelists and gurus alike. Keep your eyes peeled on London if in the UK or if you're lucky enough to be in an established MySQL locality there's a wealth of MySQL tracks through various conferences in the States and Europe. A fine way to see what-is-what conference and meetup-wise is keeping abreast of developments on planet.mysql.com or by tuning in to the podcast. One of the largest MySQL conferences is the O'Reilly MySQL Conference which is like the MySQL version of Disney World and this year is 11th - 14th of April hosted in Santa Clara CA.
I can't say I've been remotely thorough today. I've missed out the superb documentation, MySQL Forge, the mysqlperfomanceblog.com, I didn't even mention Mark Callaghan's patching rants and probably loads more that you kind readers could mention in comments should you have the time to. I am going to sit in the dark and wait for the next installation of... "Sarah, Sheeri Optimise an SQL Query. Database is making me weary so I...." Ahhh get it out of my head!!!!!
In terms of official resource you will find a plethora of mysql.com subdomains. One of my regular stops is planet.mysql.com. The custom built blog/news aggregator built by Arjen Lentz, is a rollup of articles submitted and labeled with the `mysql` keyword. Pop on over to read the latest musings from those giving their $0.02 on various topics. You will find plenty of articles on MySQL and frequently articles on forks, tools, findings and guides from some of the loudest voices in the MySQL ecosystem. Names like Lenz Grimmer, Guiseppe Maxia, Baron Schwartz and Ronald Bradford will promptly become familiar names of the MySQL database rockstars. Simply put it's an ace dynamic for the community and a real way to have your voice heard.
There are few more channels that I won't explore too much as they are pretty self explanatory if you're familiar with any interactive web platforms. The forums.mysql.com do exactly as it says on the tin and lists.mysql.com allows you to join and read the MySQL mailing lists. Both are places that can fast become bookmarks in your fave browser. They certainly have made it into my Chrome Bookmark Bar. So besides the 'official' channels (official in appearance anyway) there are some other forms of connecting with the community in an educational or troubleshooting capacity. If you're an IRC user already you can point your fave IRC client at the Freenode MySQL channel. Here you will find many of the most experienced DBAs and DEVs hiding behind their carefully chosen guises. If you're struggling with something in realtime try the IRC channel for some friendly advice. But be forewarned, if you've not tried to solve your own problem before hitting the channel (RTFM) don't be shocked when you're hounded out. I've seen lazy newbies shot down fast by asking shallow questions born from thinking freenode == free support.
My next mention on this whistle-stop tour of MySQL resources is the countless amount of great books written about the product and it's implementations. Browsing a few of the search results on Amazon (or your favourite online book retailer) you will notice that there have been hundreds of books written for MySQL. There is usually a coupling of MySQL with PHP, the popular 'M' & 'P' of LAMP. So depending what you want to learn there's something out there for you. Should you visit my office you'll be sure to find something with Paul DuBois' name on the spine and also the awesome O'Reilly published offerings; "High Performance MySQL" & "MySQL High Availability". I've also got "Expert PHP and MySQL", co-authored by the one and only Ronald Bradford, awaiting my attention. [ ISBN: 9780596807306, 9780596101718 and 9780470563120 respectively ]
So onwards to a format of MySQL ingestion that I've been enjoying and certainly learning from lately. It is the sole contribution to the audio movement in the MySQL community. The OurSQL Podcast is delightfully brought to us weekly by two more MySQL stars, Sheeri Cabral of Palominodb and Sarah Novotny from Blue Gecko. Available from iTunes or the OurSQLcast website so you can copy it to your non-branded mp3 player. Sheeri and Sarah have vast experience with MySQL, working in consultancy roles on one hand and feeding the community with the other. From the haunting title music (trust me it will be bouncing around in your head hours after hearing the latest episode) to the 'Ear Candy' and out-takes, the podcast is a great way to learn from some bonafide experts in the field without the consultancy bills. Sheeri and Sarah guide their listeners through various subjects on the show and are often joined by community favourites such as Brian Aker of Data Differential, Patrick Galbraith of Membase fame and Lenz Grimmer recently retired MySQL Community Relations Specialist. I'm sure that their contact list is far from exhausted and they will brandish many more interesting special guests for our listening pleasure. All the show notes are posted online so if you miss anything first time around you can bet your primary key that they have posted it on the episode's page. As the podcast continues to build popularity with the community the ladies are asking for your feedback via iTunes or their phone line (which you will hear on the podcast) dangling a "swag" shaped carrot on front of our noses for encouragement. So if you listen, be sure to let them know what you think, you might be boasting a shiny new OurSQL mug by the end of the week. A cool little feature that they run on the show is "Where you can see us" which is a run-through of their travel and physical appearances (in actual human form, no less!) across this shiny little globe. This leads nicely into meet ups and conferences.
Conferences and meetups are sweet! Although light on the ground here in the west of England they are certainly another avenue to connect with other MySQL evangelists and gurus alike. Keep your eyes peeled on London if in the UK or if you're lucky enough to be in an established MySQL locality there's a wealth of MySQL tracks through various conferences in the States and Europe. A fine way to see what-is-what conference and meetup-wise is keeping abreast of developments on planet.mysql.com or by tuning in to the podcast. One of the largest MySQL conferences is the O'Reilly MySQL Conference which is like the MySQL version of Disney World and this year is 11th - 14th of April hosted in Santa Clara CA.
I can't say I've been remotely thorough today. I've missed out the superb documentation, MySQL Forge, the mysqlperfomanceblog.com, I didn't even mention Mark Callaghan's patching rants and probably loads more that you kind readers could mention in comments should you have the time to. I am going to sit in the dark and wait for the next installation of... "Sarah, Sheeri Optimise an SQL Query. Database is making me weary so I...." Ahhh get it out of my head!!!!!
Subscribe to:
Posts (Atom)
Posts
