|Showing entries 1 to 11|
MySQL is needlessly slow at accepting new connections. People usually work around that by having various sorts of connection pools, but there’s always a scale at which connection pools are not feasible. Sometimes connection avalanches come unexpected, and even if MySQL would have no trouble dealing with queries, it will have problems letting clients in. Something has to be done about it.
Lots of these problems have been low hanging fruits for years – it ‘was not detected’ by benchmarks because everyone who benchmarks MySQL would know that persistent connections are much faster and therefore wouldn’t look at connection speeds anymore.
Usually people attribute most of slowness to the LOCK_thread_count mutex – they are only partially right. This mutex does not just handle the counter of active running connections, but pretty much every operation that deals with[Read more...]
There are multiple metrics that are really useful for read workload analysis, that should all be tracked and looked at in performance-critical environments.
The most commonly used is of course Questions (or ‘Queries’, ‘COM_Select’) – this is probably primary finger-pointing metric that can be used in communication with different departments (“why did your qps go up by 30%?”) – it doesn’t always reveal actual cost, it can be increase of actual request rates, it can be new feature, it can be fat fingers error somewhere in the code or improperly handled cache failure.
Another important to note is Connections – MySQL’s costly bottleneck. Though most of users won’t be approaching ~10k/s area – at that point connection pooling starts actually making sense – it is[Read more...]
I always have difficulties with complex analysis schemes, so fall back to something that is somewhat easier. Or much easier. Here I will explain the super-powerful method of database write workload analysis.
Doing any analysis on master servers is already too complicated, as instead of analyzing write costs one can be too obsessed with locking and there’s sometimes uncontrollable amount of workload hitting the server beside writes. Fortunately, slaves are much better targets, not only because writes there are single-threaded, thus exposing every costly I/O as time component, but also one can drain traffic from slaves, or send more in order to cause more natural workload.
Also, there can be multiple states of slave load:
SHOW PROFILES command and its profiling support is something that I can’t believe I hadn’t spotted before today.
It allows you to enable profiling for a session and then record performance information about the queries executed. It shows details of the different stages in the query execution (as usually displayed in the thread state output of
SHOW PROCESSLIST) and how long each of these stages took.
I’ll demonstrate using an example. First within our session we need to enable profiling, you should only do this in sessions that you want to profile as there’s some overhead in performing/recording the profiling information:
mysql> SET profiling=1; Query OK, 0 rows affected (0.00 sec)
Now let’s run a couple of regular SELECT queries
mysql> SELECT[Read more...]
In the past I’ve used the profiling features (e.g. SHOW PROFILES) in MySQL to help with timing SQL statements, especially those in the
Out of habit I did use this to time all SQL statements however in MySQL 5.5.8 GA I've found this no longer to be representative.
As you can see, the query takes some 50+ms longer with profiling enabled, not to mention they have broken the Source_file column which I've actually used to troll the source code with.
mysql> set profiling=1; 4 rows in set (1.14 sec) 4 rows in set (1.15 sec) 4 rows in set (1.17 sec) mysql> set profiling=0; 4 rows in set (0.37 sec) 4 rows in set (0.37 sec) 4 rows in set (0.37 sec)
Investigating further showed the cause. There appears to be some new overhead that causes profiling to log excessive amount of information.
mysql> show profile source for[Read more...]
Lately we have been especially enjoying the opportunities that Poor Man’s Profiler provides us – but also the technology has improved a lot too – there have been few really useful mutations.
One mutation (hyper-pmp) was Ryan Mack’s approach of having somewhat more efficient sampling – instead of firing gdb each time, he instructed gdb to get backtraces every time monitored process gets a signal (SIGUSR2 for example). This allows to maintain a persistent debugger attachment – and then signal periodically to get stacks analyzed.
Other mutation was auto-pmp – high frequency polling of process state (e.g. how many threads are running), and when a certain threshold is exceeded – obtaining stacks for further[Read more...]
Sometimes people ask why do I use MacOSX as my main work platform (isn’t that something to do with beliefs?). My answer is “good foundation with great user interface”. Though that can be treated as “he must like unix kernel and look&feel!”, it is not exactly that.
What I like is that I can have good graphical stable environment with some mandatory tools (yes, I used OS-supplied browser, mail, etc), but beside that maintain the bleeding edge open-source space (provided by MacPorts).
Also what I like, is OS-supplied development and performance tools. DTrace included is awesome, yes, but Apple did put some special touch on it too. This is visualization environment for dtrace probes and other profiling/debugging tools:[Read more...]
It's been a while since I've written about progress on the book. I actually stopped working on it as much at the beginning of the month, because on October 31(st) I managed to finish a first draft of the last big chapter! Now I'm back to full-time work at my employer, and I'm working on the book in the evenings and weekends only. Read on for details of what I've been working on and what's next in the pipeline.
This release of MySQL Toolkit fixes some minor bugs and adds new functionality to four of the tools. Some of the changes I made were in response to feedback I got at the recent MySQL Camp. I'm still working on some of the feature requests, such as daemon-izing certain tools.
For those who requested features for MySQL Query Profiler, the tab-separated format should give you the desired output: no zero rows, and variables are not renamed.
|Showing entries 1 to 11|