For an online shop, the website is the cash register. It has to be open for business 24 hours a day.

As the ops person on duty, when you get a call at 3am after your website went down, your priority number one is to restore the service asap. But why can we not have our application stack automatically recover, and not have the pager wake us at all? Why do we still stick to tedious manual processes, which take up time and resources, and hinder future growth?

Infrastructure automation isn’t easy, but it’s not rocket science either, says Riaan Nolan. Riaan has been in operations for the past decade, and has built over a dozen eCommerce properties. Automation is a worthwhile investment for retailers serious about eCommerce, but deciding on which tools to invest in can be a confusing and overwhelming process.

Join us for this webinar to understand the key pain points that online retailers experience which indicate it’s time …

Thanks to everyone who attended and participated in last week’s webinar on 'Galera Cluster for MySQL vs MySQL (NDB) Cluster: A High Level Comparison'. If you missed the sessions or would like to watch the webinar again & browse through the slides, they are now available online.

In this webinar, Severalnines VP of Products, Alex Yu, who was part of the team at Ericsson who originally developed the NDB storage engine in 2001, gave an overview of the two clustering architectures and discussed their respective strengths and weaknesses: 

1. MySQL Cluster architecture: strengths and limitations
2. Galera Architecture: strengths and limitations
3. Deployment scenarios
4. Data migration
5. Read and write workloads (Optimistic/pessimistic locking)
6. WAN/Geographical replication
7. Schema changes
8. Management and monitoring …

upload on 2014.12 [ten important tips of MySQL database design for better performance] Download this PDF

Installing Apache2 With PHP5 And MySQL Support On Fedora 21 (LAMP)

LAMP is short for Linux, Apache, MySQL, PHP. This tutorial shows how you can install an Apache2 webserver on a Fedora 21 server with PHP5 support (mod_php) and MySQL support.

There are few easy ticks to see what is stuck inside MySQL instance. All these techniques will not give you whole picture, but might help to find queries that block performance. Let’s start from what exactly doing your MySQL server right now.

Which queries are running now?

This will give you an idea what’s running right now so you can find long running queries which slowing down MySQL and/or causing replication lag:

mysql -e "SHOW PROCESSLIST" | grep -v -i "sleep"

It is more convenient than just run “SHOW PROCESSLIST” as it’s hiding all connected threads in “Sleep” state so you’ll get a clean output. Also you can get same output but updating each second:

watch -n1 'mysql -e "SHOW FULL PROCESSLIST" | grep -v -i "Sleep"'

What to look for? This is complex output but you can start with Time and State columns. When you see a query running for more …

When I talk about troubleshooting I like to repeat: "Don't grant database access to everybody!" This can sound a bit weird having one can give very limited read-only access.

But only if ignore the fact what even minimal privileges in MySQL allows to change session variables, including those which control server resources. My favorite example is "Kill MySQL server with join_buffer_size". But before version 5.7 I could only recommend this, but not demonstrate. Now, with help of memory summary tables in Performance Schema, I can show how unprivileged user can let your server to use great amount of swap.

At first lets create a user account with minimal privileges and login.

$../client/mysql -ulj -h127.0.0.1 -P13001
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 5.7.6-m16-debug-log Source distribution

…

Here is a list of my most useful tools that I use when doing performance audits.
Please note, I am writing this mainly for myself, because I sometimes end up trying to find them in my other blog post about mastering indexing and this may save me time as well as a few changes that have happened over the years.
Regular Slow Log Report pt-query-digest slow_query.log  >slow.txt
All Queries (that use indexes) for a certain table pt-query-digest slow_query.log  --filter '($event->{fingerprint} =~ m/^(!?select|update|delete)/) &&  ($event->{arg} =~ m/mytable /) ' --limit=100% >mytable.txt
Longest Running Select Queries - most painful queries with response time % right next to them. pt-query-digest slow_query.log  --filter '($event->{fingerprint} =~ …

Disclaimer: Do this at your own risk! It doesn’t apply if you’re using Pluggable authentication and certainly won’t be usable if/when MySQL system tables are stored on InnoDB

Recover your root password with care!

What is the situation?

The situation is the classic “need to recover MySQL root password” but you cannot restart MySQL (because it is the master production server, or any other reason), which makes the –skip-grant-tables solution as a no-no possibility.

 What can I do?

There is a workaround, which is the following:

•  Launch another instance of mysqld, a small one (without innodb).
•  Copy your user.[frm|MYD|MYI] files from the original datadir to the datadir of the new instance.
• Modify them and then copy …

Oracle DBAs have has the luxury of their V$ variables for a long time while we MySQL DBAs pretended we were not envious. With MySQL 5.6 and 5.7 we were gifted with the PERFORMANCE_SCHEMA tables. But there is such a wealth of information in those tables that it is intimidating to plunge in to them.  Thankfully Mark Leith has given us the SYS Schema. The SYS Schema is a collection of views, functions and procedures to help MySQL administrators get insight in to MySQL Database usage.

The first step is to get a copy of the SYS SCHEMA files.
git clone https://github.com/MarkLeith/mysql-sys


Next install the SYS Schema (here for MySQL 5.7)
mysql -u root -p < ./sys_57.sql

Now run MySQL and look at this …

I had an enjoyable time last night at Twitter with local MySQL DBAs and developers. We had an attendee who has no experience with SQL or programming at all. She is interested in organizing her collection of recipes and had heard a rumor that MySQL was a good tool to use for this task. She indicated that her desktop runs Windows 7. I think I’m going to encourage her to turn her concept in to a community project, as she is not the first person I’ve met who wants to organize recipes!

We were hosted by Rob at Twitter, who used to work with Lisa back before she retired. He’s a member of the site reliability team and keeps the fail whale from rearing its blubbery head.

Pizza was provided by my dear friend and long-time open source buddy Gerry Narvaja with the assistance of the folks in the kitchen at Zeek’s.

We discussed new techniques in the areas of load …