zorkian: Icon full of binary ones and zeros in no pattern. (Default)
I spent half of today working on rewriting my Perl show-top-queries script in Go and renamed it mysql-sniffer. It works now, so I'm happy to link it, in case anybody really wants to be a bleeding edge user. There are a few kinks to work out and options to add.


In short, the program uses pcap to sniff packets destined to MySQL. It extracts the queries from those packets and then does some canonicalization, producing output that should help you track down your most popular queries on the fly. Given that it uses pcap, you can turn it on and off with little impact to your running database.

On the big database at StumbleUpon, it parses 3000 queries per second and takes up 10% of one core on the machine. I can't show that output, but instead, I've got some data from Dreamwidth's master database.

Sample output. )

I'm not hosting compiled versions right now. If you want to play with it, you'll have to have a working Go setup (it's easy) and then check out the code. Yes, this means you'll need both Mercurial and Git on your machine. Sorry.

funky Perl

Dec. 14th, 2009 12:55 am
zorkian: Icon full of binary ones and zeros in no pattern. (Default)
I've learned how to express something in Perl I don't think I've ever used before. To express the highest index of an arrayref, the nomenclature is $#$foo. Nice.
zorkian: Icon full of binary ones and zeros in no pattern. (Default)
I've thought for a while now it'd be cool to remove Apache from the process of serving data on LJ (and now Dreamwidth). I spent an hour or two today hacking experimentally (which mostly means, looking at files, examining where things would hook together) and I think I know how I would implement it.

Basically, create a new module in Perlbal, "Perlbal::BackendGearman". This will correspond to being able to create a Gearman pool object, populating it with ip:ports (Gearman servers), and then pointing the reverse_proxy service at this pool. The service/client infrastructure doesn't have to have any idea it's talking to Gearman servers, which is great. The module would construct HTTP::Engine::Request objects, serialize them, and send those.

Then, the actual Gearman jobs are simply handlers that deserialize the request object, construct a HTTP::Engine::Response object, and return it. (Also, I'm not set on using this particular Request/Response object pairs... we might be able to just do something custom. There are some advantages to using these modules in particular...)

Something else that's interesting, the Perlbal modifications can even just skip the Gearman step entirely. In theory, we could make the entire site run out of Perlbal. That'd be great for development servers and small installations. (And it makes it very conceivable that we can distribute the site as an executable PAR file, which lets people just download it and run it... bam, they're up and running.)

I do have one concern for implementing this via Gearman: large requests. Right now, Perlbal doesn't have to buffer things. It buffers up to some amount, but at some point it just starts spooling the data to the backend. (Or to disk...if you have that turned on.) This gives some protection to Perlbal to prevent it from running out of memory, which is good. Also, I'm not sure about Gearman's performance with huge requests. In practice, I think that it won't matter; it should be normal/OK for ~100KB payloads going back and forth.

Okay, so that's a bit of a meandering post. This is something I've been talking to [livejournal.com profile] tupshin some about, and they're interested in this project as well.


zorkian: Icon full of binary ones and zeros in no pattern. (Default)
Mark Smith

April 2017

91011121314 15


RSS Atom

Most Popular Tags

Active Entries

Style Credit

Expand Cut Tags

No cut tags
Page generated Apr. 23rd, 2019 04:48 pm
Powered by Dreamwidth Studios