Showing entries 1 to 10 of 18
8 Older Entries »
Displaying posts with tag: Statistics (reset)
Some Notes on Index Statistics in InnoDB

In MySQL 5.6 we introduced a huge improvement in the way that index and table statistics are gathered by InnoDB and subsequently used by the Optimizer during query optimization: Persistent Statistics. Some aspects of the way that Persistent Statistics work could be improved further though, and we’d really like your input on that.

How much to sample?

The statistics are gathered by picking some pages semi-randomly, analyzing them, and deriving some conclusions about the entire table and/or index from those analyzed pages. The number of pages sampled can be specified on a per-table basis with the STATS_SAMPLE_PAGES clause. For example:

ALTER TABLE t STATS_SAMPLE_PAGES=500;


This …

[Read more]
What the Mean Really Means

When analyzing response time, or latency, you need much more information than an average provides. The average, commonly the arithmetic mean, shows the index of central tendency. But, as I found in earlier posts, the tendency is often not central, but may be skewed by outliers, or split by multiple modes. How often these factors occur was determined quantitatively, using tests and a survey of hundreds of production servers and different types of latency: over 95% had six-sigma outliers, and at least 20% had multiple modes. While these numerical results are useful, nothing beats a visualization, such as a histogram, …

[Read more]
Modes and Modality

It is a truth universally acknowledged that the average is the index of central tendency. But what if the tendency isn’t central?

I’ve worked many performance issues where the latency or response time was multimodal, and higher-latency modes turned out to be the cause of the problem. Their existence isn’t shown by the average – the arithmetic mean; it could only be seen by examining the distribution as a histogram, density plot, heat map, or frequency trail. Once you know that more than one mode is present, it’s often straightforward to determine what causes the slower mode, by seeing what parameters of …

[Read more]
Fun with Bugs #14 - InnoDB in MySQL 5.6

InnoDB improvements in MySQL 5.6 are well known. One of the key reasons to upgrade to MySQL 5.6 for most users is to get the benefits of improved performance, scalability, new monitoring features and fulltext indexes support in InnoDB.

Is there anything to double check before assuming that InnoDB in MySQL 5.6 is just better than any older version for any practical purposes? Let's review known public InnoDB-specific bug reports. Here is my "Top 10" list, as of MySQL 5.6.12, starting with most recent reports:

  1. Bug #69424  - maybe I miss something (I am not the only one though), but I see no way to continue using raw devices (on Linux at least) to store InnoDB data. You had working raw device in 5.5.32, then you upgrade to 5.6.12 and just can not start MySQL any more. Check this bug for the details and maybe you'll find …
[Read more]
Statistical functions in MySQL

Even in times of a growing market of specialized NoSQL databases, the relevance of traditional RDBMS doesn't decline. Especially when it comes to the calculation of aggregates based on complex data sets that can not be processed as a batch like Map&Reduce. MySQL is already bringing in a handful of aggregate functions that can be useful for a statistical analysis. The best known of this type are certainly:

Read the rest »

Saving time and increasing service availability with MySQL Enterprise Monitor 2.3

Nowadays MySQL Databases are encapsulated into many mission critical software solutions. Lots of companies host one or many MySQL databases in their data center, sometimes even without knowing it except when the MySQL service is no more available. In order to increase this service availability it is mandatory to have a monitoring solution. Regardles of you are using MySQL Server, MySQL replication or cluster the Oracle/MySQL monitoring solution is called MySQL Enterprise Monitor.

Persistent index statistics for InnoDB

In browsing the BZR tree for lp:mysql-server, I noticed some rather exciting code had been merged into the Innobase code.

You may be aware that InnoDB will do some index dives when opening a table to get some statistics about the indexes that can help the optimiser make good query plans.

The problem being that this is many disk seeks. It means that on server restart, you have to spend a whole bunch of time seeking around the disk reading index pages.

Not any more.

There is now code merged in to store the calculated statistics in a table inside InnoDB so that these index dives don’t have to happen on startup.

Originally, this looked like it was going to make it into InnoDB+. The good news is that it’s now in a public …

[Read more]
Kontrollbase gets more environment statistics

More stats for your consumption! Here are the latest additions to the environment overview reporting features. On the overview page, as of revision 238, you will find the following data for your servers. Update your revision now using ’svn update’ to get the new features or wait until the next formal release if you are [...]

OpenSQLCamp Videos online!

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.

Drizzle Client Rewrite – Clark Boylan leads the requirements and design discussion for rewriting the Drizzle Client Drizzle Plugin Hacking[Read more]
OpenSQLCamp Lightning Talk Videos

OpenSQLCamp was a huge success! Not many folks have blogged about what they learned there….if you missed it, all is not lost. We did take videos of most of the sessions (we only had 3 video cameras, and 4 rooms, and 2 sessions were not recorded).

All the videos have been processed, and I am working on uploading them to YouTube and filling in details for the video descriptions. Not all the videos are up right now….right now all the lightning talks are up.

[Read more]
Showing entries 1 to 10 of 18
8 Older Entries »