To have more visibility of our systems I needed a method to quickly query large sets of MySQL servers. Though there are few methods like ‘xargs -P’ and dsh, they are overkill for the SQL-only job, and simply not fast enough.
So, I hacked pmysql (274 lines of C) – a tool that is able to run queries on multiple servers in parallel. It can run SQL query on 1000 servers in under 0.1s, and scales well with larger sets of servers.
Use cases can be various – from configuration/schema checks (I really enjoyed using information_schema), to ‘global processlist’ analysis.
Ability to crunch hundreds of megabytes of processlist info in few seconds – priceless, so is schema verification of millions of tables.
It is fast enough to use it as backend for some of real-time health check CGI scripts :-)
To get the source:
bzr co lp:mysqlatfacebook/tools/pmysql
P.S. Oh, I know that nearly nobody needs it, but thats not a reason for keeping it closed :)
21 thoughts on “pmysql (multi-server mysql client)”
This is great!
I want to argue with your concluding words (“…nearly nobody needs it…”)
I can think right away of a use many may need this for:
A web page executes a long query. It’s taking a looong time. But – where is it issued? Queries are load-balanced between master and 6 slaves.
Your tool can first find the slave on which this query performs on.
if you have a query that is taking a long time and you don’t use some sort of slow query log analysis and you have more than 1 slave you’re doing your reads from, you’re doing it wrong :)
Not really, I prefer global processlist snapshots to slow query logs on thousands of machines (or even aggregated) at any time :)
It has that awesome feeling of ‘now’
Shlomi, for that one can use simple ‘for i in …’ loop ;-) I mean, the performance needs to scale to thousands of boxes isn’t that necessary :)
What platform are you building this on, and how?
I tried (on CentOS 5.5):
But get a raft of errors:
I have the mysql-devel package installed (which provides mysql.h), but I’m more of a sysadmin than coder so am not sure what the problem is.
Ok, that’s cool but how different is that from OmniSQL http://code.google.com/p/omnisql/
Well, quite different, did you look at OmniSQL?
Perl (with unpackaged deps) vs C, single-threaded vs multi-threaded, XML configuration-file vs dynamic server lists.
pmysql does not have concept of shards, though.
There’s makefile in the directory, and README – it also needs glib2-devel besides mysql-devel.
Aaahh, I didn’t browse the directory – just grabbed the single source file!
I deal with gobs of servers and real estate data / trends.
Thank you for this.
Are all lines 80 chars or less?
Not a bad idea … I guess that you can run N connections on 1 server too. Can you ???
I had a version of mysqlgui that could have a GUI interface to N servers.
I will work on it as well as on my XEmacs mode for MySQL, that can connect to N servers, once when I retire …
Sinisa, if you mention same server twice, it will get the queries twice. I haven’t really optimized for this – it builds the server list in memory, so better not to flood with millions of entries (though currently jobs get passed to threads just as server name strings).
Where is ppmp?
I am still waiting for turbo pmp.
You are very cool for having written this! I’m trying like hell to debug a stored proc that *may* have a race condition. No matter what I’ve used with test programs – forks, threads, timing tricks, I can’t get the clients calling it to execute the proc without there being some sort of order to when they run. Maybe your tool will help!
Hello Domas, I don’t use bzr client.
Do you have any binary for pmysql? which i can download.
Please let me know the exact url to download it. I need it for 32 and 64 bit both.
dunno, maybe some distros have it! I don’t do binary builds for any common distro :(
Is there any other way to download the source besides using bazzar? thanks
You can try to download the files individually (maybe use wget -r?) @ http://bazaar.launchpad.net/~mysqlatfacebook/mysqlatfacebook/tools/files/head:/pmysql/
Comments are closed.