English
| Showing entries 1 to 12 |
Read the original article at Book Review – Effective MySQL
Effective MySQL: Optimizing SQL Statements by Ronald Bradford No Nonsense, Readable, Practical, and Compact I like that this book is small; 150 pages means you can carry it easily. It’s also very no nonsense. It does not dig too deeply into theory unless it directly relates to your day-to-day needs. And those needs probably cluster [...]
For more articles like these go to iHeavy, Inc +1-212-533-6828
The 95 percentile for query response times is and old concept; Peter and Roland blogged about it in 2008. Since then, MySQL tools have calculated the 95 percentile by collecting all values, either exactly or approximately, and returning all_values[int(number_of_values * 0.95)] (that’s an extreme simplification). But recently I asked myself*: must we save all values? The answer is no. I created a new algorithm** for calculating the 95 percentile that is faster, more accurate, and saves only 100 values.***
Firstly, my basis of comparison is the 95 percentile algo used by
[Read more...]
-0
Last week I wrote about my experience compiling Drizzle 7 on Mac OS X 10.6. Then David Shrewsbury informed me of his nearly identical blog post: Installing Drizzle from source on OS X. Once Drizzle 7 was running on my box, I immediately looked to see what plugins where available because Drizzle uses a lot of plugins and they are one of its notable differences from MySQL. In my humble opinion, Drizzle’s plugins will primarily influence how database professionals evaluate and decide whether or not to use Drizzle because so many of Drizzle’s features are plugins. Therefore, let’s look briefly at some the plugins included with Drizzle 7.
The plugin directory of the Drizzle 7
The Maatkit tools provide a suite of additional MySQL commands. There is one command I use constantly and that is mk-query-digest.
Unfortunately the documentation does leave a lot to be desired for usability. While throughout, it is a man page and not a user guide. Several of us have discussed writing better documentation however it’s always a matter of time. I have however learned a number of tips and I’d like to share them in smaller digests.
The first is showing additional display. Maatkit works on truncating per line output to a reasonable length of 73 characters?
One of those lines is the list of hosts that connected to MySQL for a query, for example.
# Hosts 4 192.168.40... (2), 192.168.40... (2)... 2[Read more...]
While I use this tcpdump command for MySQL query analysis with mk-query-digest, I found recently that it didn’t work on FreeBSD
$ tcpdump -i bge0 port 3306 -s 65535 -x -n -q -tttt -c 5 tcpdump: syntax error
It left me perplexed and reading the man page seemed to indicate my options were valid. I tried a few variances just to be sure without success.
$ tcpdump -i bge0 -c 5 port 3306 -x tcpdump: syntax error $ tcpdump -i bge0 -c 5 port 3306 -q tcpdump: syntax error $ tcpdump -i bge0 -c 5 port 3306 -tttt tcpdump: syntax error
The solution was actually quite simple in the end, it had nothing to do with the commands, it had everything to do with the order of them. Placing port as the last option solved the problem.
$[Read more...]
Here’s a sneak peek at a video matrix — this is all the videos that include Pythian Group employees at the MySQL conference. I hope to have all the rest of the videos processed and uploaded within 24 hours, with a matrix similar to the one below (but of course with many more sessions).
TitlePresenterSlidesVideo link
(hr:min:sec)Details (Conf. site link)
OpenSQLCamp was a huge success! I took videos of most of the sessions (we only had 3 video cameras, and 4 rooms, and 2 sessions were not recorded). Unfortunately, I was busy doing administrative stuff for opensqlcamp for the opening keynote and first 15 minutes of the session organizing, and when I got to the planning board, it was already full….so I was not able to give a session.
Like I said earlier, every tiny change that the test suite reveals after code changes is significant. I caught a very subtle “bug” today in recent changes to mk-query-digest (a.k.a. mqd). If you like to read about subtle bugs, read on.
An mqd test on sample file slow023.txt began to differ after some pretty extensive code changes of late:
---
> # Query 1: 0 QPS, 0x concurrency, ID 0x2CFD93750B99C734 at byte 0 ________
The ID which depends on the query’s fingerprint has changed. It’s very important that we don’t suddenly change these on users because these IDs are pivotal in trend analyses with mqd’s --review-history option. First some background info on the recent code changes and then the little story
-0
I made changes to mk-query-digest yesterday that I didn’t expect to cause any adverse affects. On the contrary, several tests began to fail because a single new but harmless line began to appear in the expected output: “Databases 0″. Perhaps I’m preaching to the choir, as you are all fantastic, thorough and flawless programmers, but as for myself I’ve learned to never take a single failed test for granted.
One time a test failed because some values differed by a millisecond or two. Being curious I investigated and found that our standard deviation equation was just shy of perfect. I fixed it and spent hours cross-checking the myriad tiny values with my TI calculator. Probably no one cared about 0.023 vs. 0.022 but it’s the cultivation of a disposition towards perfection that matters.
My innocuous changes yesterday introduced a case of
[Read more...]I originally posted this on the Maatkit discussion list:
A little while ago a user asked in http://groups.google.com/group/maatkit-discuss/browse_thread/thread/256b6c780bdb066d if it was possible to use mk-query-digest to analyze queries per hour. I responded with a skeleton script for use with –filter, but I didn’t actually test this. Today, I filled out the script and tested it and found that it works. The script is available from trunk at:
http://maatkit.googlecode.com/svn/trunk/mk-query-digest/t/samples/filter-add-ymdh-attribs.txt
Q: What SQL is running on your MySQL database server now?
A: The bane of pain for MySQL DBA’s when there is no official MySQL instrumentation that is dynamic and fine grained sufficiently to solve this problem at the SQL interface.
While hybrid solutions exist, the lack of dynamic and real-time are the issues. There is however great work being done by Baron and others on Maatkit mk-query-digest and packet sniffing the MySQL TCP packets.
$ sudo tcpdump -i eth0 port 3306 -s 65535 -x -n -q -tttt | ./mk-query-digest --type tcpdump tcpdump: verbose output suppressed, use -v or -vv for full protocol decode listening on eth0, link-type EN10MB (Ethernet), capture size 65535 bytes # Caught SIGINT. 5444 packets captured 8254 packets received by filter 2809 packets dropped by kernel #[Read more...]
| Showing entries 1 to 12 |
