September 2012 - Open Query five year anniversary, training for MySQL and MariaDB
Hi Visitor, and welcome to the September 2012 issue of the Open Query Newsletter.
In this issue:
- short news
- training with "BYO laptop"
- upcoming events
- tip: TEXT and VARCHAR
Open Query has just celebrated its fifth birthday! This milestone coincided with training days in Brisbane, and apart from sharing cake with our training clients we had a little staff meeting of all our engineers who are based in Australia. Always good!
We've done well despite the volatile economic climate, and we reckon that's thanks to our sane pricing and way of working. Unfortunately a few of our engineers (Walter and Guillaume) have independently moved on to other things, but we've also welcomed a few new faces over the past year - and we are looking for one or two more at this time.
Tribily, our external monitoring provider, has informed us that they're ceasing operations. Ironically, Tribily started as a spin-off from Open Query's Zabbix monitoring installation. Peter has been busy working on a new solution using Munin, which we're deploying on our own servers and rolling out for our clients in the coming weeks. Our focus with monitoring is mainly tracking trends of various statistical details, as that enables us to catch issues before they become big problems. Alerts can also be triggered, but as you know our focus is on preventing emergencies rather than having to act as fire fighters.
Following us via the Open Query blog or Twitter (@openquery) is a good way to catch new posts and other topical items we identify. After all, the maximum frequency of this newsletter is only monthly, as to not clutter your inbox from our end.
Training with "BYO Laptop"
We've been offering "BYO laptop" course days lately which has received good feedback at our recent Brisbane training days. For our public courses we've generally worked in training facilities and preconfigured computers. While that still works best for some of the modules (for instance our backup & replication workshop), it's not always necessary. As we then pay less for the facilities, this works out up to 40% cheaper for you! It does of course mean that for those days, bringing your own laptop is not optional but a pre-requisite. You have to make sure you have a working basic MySQL setup on your laptop to be able to participate in the course day.
We currently have a number of Developer and DBA course days scheduled in Sydney. These topics were very popular in Brisbane earlier this month, the days were pretty much booked out! MariaDB and related enhancements are of course covered as well as stock MySQL Server. You can register for Open Query course days/modules individually to suit your time, budget and current needs. Your trainers for these days are Daniel and Arjen.
Sydney (BYO laptop)
- Wed 24 Oct 2012: MySQL Optimisation by Design (AUD 275 + GST)
- Thu 25 Oct 2012: MySQL Locking, Transactions, Storage Engines (AUD 275 + GST)
- Fri 26 Oct 2012: MySQL Server Performance Analysis and Tuning (AUD 375 + GST)
We will shortly be scheduling more course days in various cities for the new year. If you have specific needs, please let us know.
For bookings and questions, contact us today! All prices excluding GST.
- Sydney NSW, 4-7 Dec 2012: Open Source Developers Conference. Arjen will be presenting a session "Understanding SQL joins in less than 20 minutes".
- Brisbane QLD, first Tuesday of the month: Brisbane Web Tech meetup, incorporating MySQL and many other relevant technologies. Arjen co-organises.
Tip: TEXT and VARCHAR
You've made it this far, so now to our freebie newsletter tip!
We often see people using VARCHAR(200) and VARCHAR(255). For us, this indicates that a developer has chosen a large arbitrary size, or possibly doesn't know what the maximum length of the string might be. However, did you know that 255 is not actually the upper limit? Depending on your character set (see last month's newsletter) it's over 20K. So, VARCHAR(20480) is entirely valid in UTF-8. This has been the case since MySQL 5.0, but interestingly little known.
Naturally, we're not suggesting to you to define all your "I don't know how big the string will be" columns this large, as there are performance and memory consequences to this related to grouping, sorting and temporary tables. But it does help with something else: TEXT columns. While TEXT and BLOB handling in MySQL is pretty good, some aspects of its storage, retrieval and handling is more costly as these columns can get very big. Now, if you're currently using a TEXT column because you do have more than 255 characters, but less than 20K, you could convert the column to a VARCHAR. From the application's perspective it'll work identically, but there can be performance gains as it may allow the server to perform certain operations in memory rather than on disk. This will depend on other settings as well (sort_buffer_size, tmp_table_size, max_heap_table size, to name a few).
If you want to learn more about this nifty stuff, ask us or sign up for Open Query training days. We can also (help) analyse and optimise your database, that's all part of our service.
Until next time!
Feedback welcome through http://openquery.com/contact
You can also access this issue online: http://openquery.com.au/newsletter/2012-09, other issues can be viewed at http://openquery.com.au/category/newsletter/open-query-newsletter-2014news1l6
-- Arjen & the OQ team
We aim to keep our newsletter in plain text, apart from links. If you're reading the Open Query Newsletter online or received it via someone else, you can subscribe for your own copy through http://openquery.com/user/register and http://openquery.com.au/newsletter/confirm/add/