I’ll be attending Percona Live NY 2012 next month, co-presenting a tutorial with Marcos Albe (also from Percona) and a couple of sessions with Marcos and Ryan Lowe (of Square). On Monday, Marcos and I will be doing a hands on tutorial on Percona Toolkit, and on Tuesday, Ryan will join us for a session …
Read More…Piping data to multiple processes
Here’s a simple shell script to stream data to multiple processes. It has many applications, but the reason I wrote it is to stream the same data to multiple netcat processes on remote machines. Here’s the code: #!/bin/bash usage() { cat <&2 usage : multi-fifo target0 [target1 [target2 …]] Where each targetN is a program …
Read More…High Availability MySQL Cookbook review
High Availability MySQL Cookbook (Alex Davies, Packt Publishing) presents different approaches to achieve high availability with MySQL. The bulk of the book is dedicated to MySQL Cluster, with shorter sections on: MySQL replication shared storage block level replication performance tuning The recipes are clear and well explained, based on a CentOS distribution, and it seems …
Read More…how to prevent explain from executing subqueries
Here’s a quick tip for using explain: You may know this already, but mysql will actually execute some subqueries when you invoke explain. Here’s an example: mysql> explain select id from projects where id = (select max(id) from projects where name like ‘en%’); +—-+————-+———-+——-+—————+———+———+——-+——-+————-+ | id | select_type | table | type | possible_keys | …
Read More…Generating data with dbmonster
In my last post I included some sample data which was useful for playing around with queries (once I published it, I realized it made my post look like some form of keyword stuffing, fortunately I don’t use adsense on my site so I hope I’m free of any suspicion ). That sample data was …
Read More…Indexing text columns in MySQL
This time, I’m talking about indexes for string typed columns. In particular, I’ll show a procedure I find useful while looking for good index length values for these columns. I’ll use a sample table called people. Here’s what it looks like: mysql> desc people; +————+——————+——+—–+———+—————-+ | Field | Type | Null | Key | Default …
Read More…iptables trick to limit concurrent tcp connections
This is sort of a self-documenting post, and a self-support group about ill-behaved tomcat apps. Sometimes, you have multiple nodes accesing your MySQL server (or any kind of server, for that matter) concurrently. Eventually, software in one or more of these nodes might do nasty things (you know who you are buddy:)) MySQL provides a …
Read More…SOUNDEX(), triggers, and stored procedures
MySQL provides a SOUNDEX() function, which returns the soundex of a given string. For details, refer to the manual, but to put it simply, it allows you to compare strings based on how they sound, hence letting you do proximity searches on your database. If you’re just querying for a word, it’s usage is pretty …
Read More…Extending procedure_analyse
My previous post explored a stored procedure that extended procedure_analyse with the intent of helping DBAs optimize table structure. Here’s an improved version. I’ve followed Arjen Lentz‘s suggestion and added support for the max_elements and max_memory parameters. I also added a new Indexed column to the output, which is an ENUM(‘No’,’Yes’,’Overindexed’). Yes and No are …
Read More…Making use of procedure analyse()
SELECT Field0[,Field1,Field2,...] FROM TABLE PROCEDURE ANALYSE() is a nice tool to find out more about your table’s columns. Still, it could be improved in a lot of ways, and the stored procedure below is a starting point. It makes use of procedure analyse (though with ‘SELECT * FROM’), and modifies it’s output to include the …
Read More…