Introducing PostgreSQL Day at Percona Live Europe, Amsterdam 2016.

As modern open source database deployments change, often including more than just a single open source database, Percona Live has also changed. We changed our model from being a purely MySQL-focused conference (with variants) to include a significant amount of MongoDB content. We’ve also expanded our overview of the open source database landscape and included introductory talks on many other technologies. These included practices we commonly see used in the world, and new up and coming solutions we think show promise.

In getting Percona Live Europe 2016 ready, something unexpected happened: we noticed the PostgreSQL …

Ever wanted to just look at QPS in real time while logged into your server?

Well here’s a little command line hackery to do it quick and dirty.

[user@yourserver ~] $ LASTVAL=0; while true; do CURVAL=`mysql --batch -N -e "show status like 'Quer%';" | awk '{print $2}'`; QPS=`expr $CURVAL - $LASTVAL`; if [ $LASTVAL -ne 0 ]; then echo "$CURVAL $QPS"; fi; LASTVAL=$CURVAL; sleep 1; done

The output looks like this:

65549603430 2439
65549605421 1991
65549606912 1491
65549611219 4307
65549614186 2967
65549618048 3862
65549620853 2805

The first column is just the Query counter value. The second column is the QPS.

This script requires a .my.cnf to exist in your home directory (or that you do something nastily insecure and supply -u user -ppassword to the mysql command in the example above).

This only works if you’re using innodb_file_per_table.

Purpose: import this csv quickly into google sheets (or other spreadsheet) and compare MySQL’s internal data size to the container size on disk to determine tables needing to be optimized (or “null altered”) to reclaim disk space and maybe increase performance due to defragmentation. Rule of thumb is probably something like >=10% difference may warrant action.

I wrote this loop as a one-liner dynamically / ad-hoc on the command line a couple weeks ago but made it into a configurable, yet quick-and-dirty shell script, below.

Add -u and -p arguments to MySQL CLI command if you need to, or just place a .my.cnf in your home directory and use the script as-is.

#!/bin/bash

DATADIR="/path/to/datadir" # ex: /var/lib/mysql
SCHEMANAME="yourschema"

for x in `mysql --batch -n -e "select concat(concat(table_name,'.ibd'),',',(data_length + index_length)) as …

Learn how to set up and use Percona's clustercheck script in a MySQL Galera Cluster.

The post Percona’s Clustercheck Script for MySQL Galera appeared first on Datavail.

Want to easily find out about the MySQL sessions taking place at Oracle OpenWorld? Check out our Focus on MySQL @ OpenWorld page. Conference sessions, Tutorials, Hands-on Labs, Birds-of-a- Feather as well as the MySQL General Session are listed with their time and location.

Not registered for Oracle OpenWorld yet? Do it Now! You'll learn about the latest MySQL developments and plans, meet the engineers and get all your questions answered. You'll get the chance to network with you peers and learn best practices from MySQL power users. And, you'll get access to the extensive Oracle OpenWorld content and programs. Don't miss this once a year …

I have been fired for speaking out about the GPL and MariaDB actions that have caused great harm to our ecosystem.

It has been pointed out that I have a non-compete agreement. None of my tools compete with MariaDB and I have no non-public knowledge of MariaDB technology. GPLScale remains free software under the GNU GPL license and it is my right to fork a github repo. I am not paid to work on GPLScale and I don't intend to get paid to maintain it by anyone. All my projects are labors of love.


Who wants to hire me? I'm dedicated, honest, open, and I have integrity. I'm willing to risk everything for what I believe in.

Email me at:
greenlion at gmail dot com


----


I have been a proponent of GPL for a long time, and I don't need publicity.
…

I have been fired for speaking out about the GPL and MariaDB actions that have caused great harm to our ecosystem.

It has been pointed out that I have a non-compete agreement. None of my tools compete with MariaDB and I have no non-public knowledge of MariaDB technology. GPLScale remains free software under the GNU GPL license and it is my right to fork a github repo. I am not paid to work on GPLScale and I don't intend to get paid to maintain it by anyone. All my projects are labors of love.


Who wants to hire me? I'm dedicated, honest, open, and I have integrity. I'm willing to risk everything for what I believe in.

Email me at:
greenlion at gmail dot com


----


I have been a proponent of GPL for a long time, and I don't need publicity.
…

When I wrote this blog about “bash on Windows” a few days ago I omitted one issue, that I already knew about. This is because it needs some elaboration that did not ‘fit in’ the previous blog. So I will do it here.

It is about Unicode. Unicode always was a pain in “cmd” and with the arrival of “bash” in Windows, this has become more significant and important. Actually on any recent *nix platform user will not do anything to make Unicode work ‘out of the box’ in the console and display all or almost all scripts (though I have noticed that the completeness of the ‘monospace’ font mostly used in the Linux console varies between Linux distros – with …

When I wrote this blog about “bash on Windows” a few days ago I omitted one issue, that I already knew about. This is because it needs some elaboration that did not ‘fit in’ the previous blog. So I will do it here.

It is about Unicode. Unicode always was a pain in “cmd” and with the arrival of “bash” in Windows, this has become more significant and important. Actually on any recent *nix platform user will not do anything to make Unicode work ‘out of the box’ in the console and display all or almost all scripts (though I have noticed that the completeness of the ‘monospace’ font mostly used in the Linux console varies between Linux distros – …

This blog discusses how to find and address badly written queries using ProxySQL.

All of us are very good in writing good queries. We know this to always be true!

But sometimes a bad query escapes our control and hits our database. There is the new guy, the probie, who just joined the company and is writing all his code using SELECT * instead of WHERE. We’ve told him “STOP” millions of times, but he refuses to listen. Or a there is a new code injection, and it will take developers some time to fix and isolate the part of the code that is sending killing queries to our database.

The above are true stories; things that happen every day in at least few environments.

Isolating the bad query isn’t the main problem: that is something that we can do very fast. The issue is identifying the code that is generating the query, and disabling that code without killing the whole application.

That …