Instrumentation is having a profiling active in every operation the application is performing. In many cases, profiling the application might be easier and more useful as it might show problems early. InstrumentationĪll the previous only considered profiling queries on the MySQL level. The other type which is wait analysis is useful for I/O bound queries that spend the majority of its execution time waiting to access resources or read/write data. There are two types of profiling, execution time profiling which is useful if tasks are CPU bound, those types of queries would spend the majority of their execution time doing work. The first column shows the rank which is an indication of how important a task is (importance here is defined as its response time percentage of the total response time of all tasks), the second column is the aggregation of response time of all instances of a certain task, the third column shows the percentage of time all instances of this task take compared to the total execution time which determines its rank, the fourth column shows the number of instances of this task, the sixth column shows the average response time for each instance of the task and the sevenths column shows the task itself. The able above shows a subset of the columns the profile would normally have for clarity. To make this clearer, let's take a look at a simplified profile produced for a SELECT query against a table called InvitesNew: It's also useful to be able to aggregate similar tasks in single items and sort tasks so that the important ones bubble up. This structure is useful to construct call graphs and understand what the server actually does to execute a query. Profiling a task is not as simple as measuring how much time a task takes to conclude, rather it requires measuring how much time the task takes broken down to individual child tasks that it needs to execute. So for example, if a query only runs 5% of the time, any performance improvements however significant will at most improve the overall performance by 5% so other minor improvements in queries that run 50% of the time will be more worthy of our time. or it can tell us that everything that server is doing is actually necessary and that it's best we can do which saves us a lot of time before we start optimizing something that's already at its maximum speed.Īnother important rule the book mentions is to carefully pick your battles, that is to say, you need to learn how to decide on which queries to optimize and which to not. This will enable us to know if the server is doing work that can be eliminated by maybe re-writing the query, adding an index, etc. The book defines optimization as reducing response time, so naturally, it makes sense to be able to understand why exactly the server needs a certain amount of time to respond to a query in as detailed way as possible. Troubleshooting intermittent problems that might appear as stalls or temporary system freezes happening sporadically.Finding out why a certain query takes so much time.Finding out if the server is doing its work optimally.Profiling Server PerformanceĪs the book authors say, profiling server performance is a way to answer the most three common questions asked pertaining to a database server performance which are: Chapter 3 gives a good introduction on profiling MySQL queries and diagnosing intermittent problems. So in the previous article we talked about chapter 2 of High Performance MySQL which discussed MySQL Benchmarking, this article discusses chapter 3 which discusses Profiling Server Performance.īefore starting a discussion on the internals of MySQL or any optimization techniques, it's essential to learn how to benchmark a server performance and how to profile for performance bottlenecks.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |