Planet MySQL Planet MySQL: Meta Deutsch Español Français Italiano 日本語 Русский Português 中文
Showing entries 1 to 10 of 15 5 Older Entries

Displaying posts with tag: profiling (reset)

Profiling Stored Procedures in MySQL 5.7
Employee +1 Vote Up -0Vote Down

With the changes to performance_schema in MySQL 5.7 Development Milestone Release it is now possible to analyze and profile the execution of stored programs. This is highly useful if you develop more complex stored procedures and try to find the bottlenecks. The "old" performance_schema up to MySQL 5.6 only reported a CALL statement with a runtime, but no information on statements that were executed WITHIN the stored procedure. Now let's try this in the latest MySQL 5.7.6 DMR release. After creating some test table and a test stored procedure we need to activate the events_statements_history_long consumer, which is OFF by default:



  [Read more...]
Compiling & Debugging MariaDB(and MySQL) in Eclipse from scratch - Part 5: "Profiling in Eclipse with OProfile"
+1 Vote Up -0Vote Down

Section 6: "Profile a real case" 6.1 INTRODUCTION

Profiling & Debugging is an argument that would require an entire book, the aim of this(and the others) posts of this series is to give you the basic knowledge on how to work with these tools and techniques withing Eclipse. For instance if you want to learn to profile with OProfile you should study on the abundant and separate resources, you may start from: http://OProfile.sourceforge.net

6.2 ABOUT NAMING THE PROJECT

read more

Difference between DISTINCT and GROUP BY
+0 Vote Up -0Vote Down

Today we had an interesting situation where the same query was executed significantly slower when it was written with GROUP BY instead of DISTINCT and I saw many people still had the assumption that these two types of queries are actually equivalent which is simply not true. Although DISTINCT queries can be implemented using GROUP BY but not every GROUP BY query can be translated to DISTINCT. Depending on the brand and the optimizer the database server may actually use group by internally for the execution of distinct but that won’t make them equivalent. Let’s see why…

GROUP BY as the name suggest groups the result by some …

  [Read more...]
Unindexed queries can be really expensive
+0 Vote Up -0Vote Down

The story happened with a webshop application running on Amazon EC2 microinstances. Actually on two instance. Amazon business model is basically simple, they ask money for only three things: Cpu time, IOPS and network traffic. Everybody (including me) thinks for the first time network traffic will be the bottleneck until they got the first bill (it can be even after one year considering the free tier). Actually in this category the IOPS is the most expensive.

Symptoms

On the cacti diagrams I saw strange datas. The created temp tables on disk and created temp files were much higher than created temp tables. The 67% of temporary tables …

  [Read more...]
Profiling your slow queries using pt-query-digest and some love from Percona Server
+2 Vote Up -0Vote Down

This guide will get you up and running with how to identify the bottleneck queries using the excellent tool pt-query-digest. You will learn how to use and analyze the output returned by pt-query-digest. You will also learn some differences between slow query logging in various MySQL versions. Later on in the post I will also show you how to make use of the extra diagnostic data available with Percona Server.

The post Profiling your slow queries using pt-query-digest and some love from Percona Server appeared first on ovais.tariq.

On connections
+5 Vote Up -0Vote Down

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 …

  [Read more...]
MySQL metrics for read workloads
+4 Vote Up -0Vote Down

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 …

  [Read more...]
On database write workload profiling
+5 Vote Up -0Vote Down

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 …

  [Read more...]
MySQL Server’s built-in profiling support
+2 Vote Up -1Vote Down

MySQL’s 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 …

  [Read more...]
Changes in using Profiling in MySQL 5.5
+3 Vote Up -0Vote Down

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 < 10 millisecond range.

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 …
  [Read more...]
Showing entries 1 to 10 of 15 5 Older Entries

Planet MySQL © 1995, 2016, Oracle Corporation and/or its affiliates   Legal Policies | Your Privacy Rights | Terms of Use

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.