Percona Monitoring and Management (PMM) is a free and open-source platform for managing and monitoring MySQL® and MongoDB® performance. You can run PMM in your own environment for maximum security and reliability. It provides thorough time-based analysis for MySQL® and MongoDB® servers to ensure that your data works as efficiently as possible.

We focused mainly on two features in 1.10.0, but there are also several notable improvements worth highlighting:

• Annotations – Record and display Application Events as Annotations using pmm-admin annotate
• Grafana 5.0 – Improved visualization effects
• Switching between Dashboards – Restored functionality to preserve host when switching dashboards
• New Percona XtraDB Cluster Overview graphs – Added Galera Replication Latency graphs on Percona XtraDB Cluster Overview dashboard with consistent colors

The issues in the release include four new features & improvements, and eight bugs fixed.

Annotations

pmm-admin annotate "Application deploy v1.2" --tags "UI, v1.2"

Help us improve our software quality by reporting any Percona Monitoring and Management bugs you encounter using our bug tracking system.

The post Percona Monitoring and Management (PMM) 1.10.0 Is Now Available appeared first on Percona Database Performance Blog.

Join Percona Chief Evangelist Colin Charles as he covers happenings, gives pointers and provides musings on the open source database community.

Percona Live is just over a week away — there’s an awesome keynote lineup, and you really should register. Also don’t forget to save the date as Percona Live goes to Frankfurt, Germany November 5-7 2018! Prost!

In acquisitions, we have seen MariaDB acquire MammothDB and Idera acquire Webyog.

Some interesting Amazon notes: Amazon Aurora Continues its Torrid Growth, More than Doubling the Number of Active Customers in the Last Year (not sure I’d describe it as torrid but this is great for MySQL and PostgreSQL), comes with a handful of customer mentions. In addition, there have already been 65,000 database migrations on AWS. For context, in late November 2017, it was 40,000 database migrations.

Releases

• Zstandard v1.3.4 – you can now tune compression parameters to go from near lz4 speeds, to near lzma ratios. Should this now be your go to compression format? Relevant from a MyRocks standpoint.
• DBdeployer – now a GA, at version 1. Goodbye MySQL Sandbox. Thanks for the awesome work Giuseppe Maxia! As he says, over 6,000+ tests performed.
• MariaDB 10.2.14 with MyRocks being Gamma (RC), bugs fixed. MariaDB 10.1.32 with bugs fixed. I guess both releases also have a new plugin, called DISKS, so the INFORMATION_SCHEMA now has a DISKS table.
• ProxySQL 1.4.8 just keeps getting better, with improvements even for ClickHouse backends.
• Percona Monitoring & Management (PMM) 1.9.1 now with faster loading of the index page with gzip/HTTP2, improvements for deployments on Amazon AWS, and something useful for Percona customers: the Percona Snapshot Server.
• Percona XtraBackup 2.4.10 with bug fixes, based on MySQL 5.7.19.
• Database Flow – self-describing, “Database Flow is an open source self-hosted SQL client, GraphQL server, and charting application that works with your database.”

Link List

• Amazon DynamoDB Continuous Backups and Point-In-Time Recovery (PITR) – continuous backup and restore with DynamoDB
• Managing db schema changes without downtime – from the co-founder of Discourse.
• All AWS Services GDPR ready
• What a Difference a Decade Makes – Stephen O’Grady on open source database adoption.
• 10 Steps to Develop an Incident Response Plan You’ll ACTUALLY Use
• Incident Post-Mortem and Security Advisory: Data Exposure After travis-ci.com Outage – be careful of those database queries
• PostgreSQL Begins Landing LLVM JIT Support For Faster Performance – incidentally have you also seen the work done by Vitesse Data?
• Monitoring PostgreSQL VACUUM processes
• textql – Execute SQL against structured text like CSV or TSV
• CockroachDB 2.0 Performance Makes Significant Strides
• GraphQL-to-MongoDB, or how I learned to stop worrying and love generated query APIs
• How Kustomer uses MongoDB and AWS to help fill in the gaps in the customer journey – I really enjoy reading these case studies that MongoDB are doing lately
• Enable your Devs to do Ops
• How to Speed-Up MongoDB Regex Queries by a Factor of up-to 10
• Building a Highly Available Web Application with Web Processing and Storing Capabilities Using MongoDB and Elk Stack

Upcoming appearances

• Percona Live 2018 – Santa Clara, California, USA – April 23-25 2018
• rootconf.in 2018 – Bangalore, India – May 10-11 2018
• Open Source Data Centre Conference – Berlin, Germany – June 12-13 2018
• DataOps Barcelona – Barcelona, Spain – June 21-22 2018
• OSCON – Portland, Oregon, USA – July 16-19 2018

Feedback

I look forward to feedback/tips via e-mail at colin.charles@percona.com or on Twitter @bytebot.

The post This Week in Data with Colin Charles 35: Percona Live 18 final countdown and a roundup of recent news appeared first on Percona Database Performance Blog.

Percona announces the release of Percona Monitoring and Management 1.9.1. PMM (Percona Monitoring and Management) is a free and open-source platform for managing and monitoring MySQL and MongoDB performance. You can run PMM in your own environment for maximum security and reliability. It provides thorough time-based analysis for MySQL and MongoDB servers to ensure that your data works as efficiently as possible.

This release contains bug fixes only and supersedes Percona Monitoring and Management 1.9.0. This release effectively solves the problem in QAN when the Count column actually displayed the number of queries per minute, not per second, as the user would expect. The following screenshot demonstrates the problem. The value of the Count column for the TOTAL row is 649.38 QPS (queries per second). The total number 38.96 k (38960) is only sixty times greater than the reported value of QPS. Thus, queries were counted for each minute within the selected time range of Last 1 hour.

Query Analytics in PMM version 1.9.0.

The corrected version of QAN in PMM 1.9.1 shows that queries are now counted per second. The total number of queries is 60 * 60 greater than the value of QPS, as should be expected for the chosen time range.

Query Analytics in PMM version 1.9.1.

Bug fixes

• PMM-2364: QPS are wrong in QAN

Help us improve our software quality by reporting any bugs you encounter using our bug tracking system.

The post Percona Monitoring and Management 1.9.1 Is Now Available appeared first on Percona Database Performance Blog.

PMM (Percona Monitoring and Management) is a free and open-source platform for managing and monitoring MySQL® and MongoDB® performance. You can run PMM in your own environment for maximum security and reliability. It provides thorough time-based analysis for MySQL® and MongoDB® servers to ensure that your data works as efficiently as possible.

There are a number of significant updates in 1.9.0 that we hope you will like, some of the key highlights include:

• Faster loading of the index page: We have enabled performance optimizations using gzip and HTTP2.
• AWS improvements: We have added metrics from CloudWatch RDS to 6 dashboards, as well as changed our AWS add instance workflow, and made some changes to credentials handling.
• Percona Snapshot Server: If you are a Percona customer you can now securely share your dashboards with Percona Engineers.
• Exporting PMM Server logs: Retrieve logs from PMM Server for troubleshooting using single button-click, avoiding the need to log in manually to the docker container.
• Low RAM support: We have reduced the memory requirement so PMM Server will run on systems with 512MB
• Dashboard improvements: We have changed MongoDB instance identification for MongoDB graphs, and set maximum graph Y-axis on Prometheus Exporter Status dashboard

AWS Improvements

METRICS_MEMORY_MULTIPLIED=$(( (${MEMORY_AVAIABLE} - 256*1024*1024) / 100 * 40 ))
if [[ $METRICS_MEMORY_MULTIPLIED < $((128*1024*1024)) ]]; then
    METRICS_MEMORY_MULTIPLIED=$((128*1024*1024))
fi

Help us improve our software quality by reporting any bugs you encounter using our bug tracking system.

The post Percona Monitoring and Management 1.9.0 Is Now Available appeared first on Percona Database Performance Blog.

In this blog post, we’ll show that you can plot MySQL data in real time using Percona Monitoring and Management (PMM).

In my previous blog post, I showed how we could load into any metrics, benchmarks into MySQL and visualize it with PMM. But that’s not all! We can even visualize most any kind of data from MySQL in real time. I am falling in love with the MySQL plugin for Grafana — it just makes things so easy and smooth.

This graph shows us the number of visitors to a website in real time (refreshing in every 5 seconds).

We have the following table in MySQL:

CREATE TABLE `page_stats` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `visitors` int(11) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `time` (`time`)
) ENGINE=InnoDB AUTO_INCREMENT=9232 DEFAULT CHARSET=latin1

We store the number of visitors every second. I am not saying you have to update this table hundreds or thousands of times, it depends on how many visitors you have. You could use the example of Redis to store and increase this counter and save it into MySQL every second. Here are my metrics:

mysql> select * from page_stats order by id desc limit 10;
+------+---------------------+----------+
| id   | time                | visitors |
+------+---------------------+----------+
| 9446 | 2018-02-27 21:44:12 |      744 |
| 9445 | 2018-02-27 21:44:11 |      703 |
| 9444 | 2018-02-27 21:44:10 |      791 |
| 9443 | 2018-02-27 21:44:09 |      734 |
| 9442 | 2018-02-27 21:44:08 |      632 |
| 9441 | 2018-02-27 21:44:07 |      646 |
| 9440 | 2018-02-27 21:44:06 |      656 |
| 9439 | 2018-02-27 21:44:05 |      678 |
| 9438 | 2018-02-27 21:44:04 |      673 |
| 9437 | 2018-02-27 21:44:03 |      660 |
+------+---------------------+----------+

We can easily add my MySQL query to Grafana, and it will visualize it for us:

You might ask “what is $__timeFilter?” I discussed that in the previous post, but let me copy the manual here as well:

Time series:
- return column named time_sec (UTC in seconds), use UNIX_TIMESTAMP(column)
- return column named value for the time point value
- return column named metric to represent the series name
Table:
- return any set of columns
Macros:
- $__time(column) -> UNIX_TIMESTAMP(column) as time_sec
- $__timeFilter(column) ->  UNIX_TIMESTAMP(time_date_time) ? 1492750877 AND UNIX_TIMESTAMP(time_date_time) ? 1492750877
- $__unixEpochFilter(column) ->  time_unix_epoch > 1492750877 AND time_unix_epoch < 1492750877
- $__timeGroup(column,'5m') -> (extract(epoch from "dateColumn")/extract(epoch from '5m'::interval))::int
Or build your own conditionals using these macros which just return the values:
- $__timeFrom() ->  FROM_UNIXTIME(1492750877)
- $__timeTo() ->  FROM_UNIXTIME(1492750877)
- $__unixEpochFrom() ->  1492750877
- $__unixEpochTo() ->  1492750877

What can I visualize?

It’s true! Basically, if you can write a query, you can graph it. For example, let’s count all the visitors in every minute. Here is the query:

select
      UNIX_TIMESTAMP(ps.time) as time_sec,
      sum(visitors) as value,
      'visitors' as metric
   from
   page_stats as ps
   WHERE $__timeFilter(time)
   GROUP BY DATE_FORMAT(`time`, '%Y-%m-%d %H:%i')
    ORDER BY ps.time ASC;

And it gives us the following graph:

See, it’s easy!

Conclusion

There is no more excuse why you can not visualize your data! Percona Monitoring and Management lets you plot MySQL data in real time. You do not have to move it anywhere or change anything! Just grant read access from PMM, and you can start to create your own graphs!

The post Plot MySQL Data in Real Time Using Percona Monitoring and Management (PMM) appeared first on Percona Database Performance Blog.

In this post, we’ll look at the MySQL 8.0 innodb_dedicated_server variable.

MySQL 8.0 introduces a new variable called innodb_dedicated_server. When enabled, it auto tunes innodb_buffer_pool_size, innodb_log_file_size and innodb_flush_method at startup (if these variables are not explicitly defined in my.cnf).

The new MySQL 8.0 variable automatically sizes the following variables based on the RAM size of the system:

innodb_buffer_pool_size:

• • <1G: 128M(default value if innodb_dedicated_server is OFF)
  • <=4G: Detected Physical RAM * 0.5
  • >4G: Detected Physical RAM * 0.75

innodb_log_file_size:

• • <1G: 48M(default value if innodb_dedicated_server is OFF)
  • <=4G: 128M
  • <=8G: 512M
  • <=16G: 1024M
  • >16G: 2G

The variable also sets the following:

innodb_flush_method: 

• • Set to O_DIRECT_NO_FSYNC if the setting is available on the system. If not, set it to the default InnoDB flush method

These new default values are very reasonable, and the changes to these three variables show considerable performance improvements from the get-go than using the old default values. As stated in the worklog of this feature, the current MySQL version (5.7) only uses around 512M RAM with the default settings. With the new feature, these variables can easily adapt to the amount of RAM allocated to the server for the convenience of the system/database administrator.

With that said, you can achieve the best setting for these three variables by tuning it to your workload and hardware.

For InnoDB buffer pool size (based on this article), consider allocating 80% of physical RAM for starters. You can increase it to as large as needed and possible, as long as the system doesn’t swap on the production workload.

For InnoDB log file size, it should be able to handle one hour of writes to allow InnoDB to optimize writing the redo log to disk. You can calculate an estimate by following the steps here, which samples one minute worth of writes to the redo log. You could also get a better estimate from hourly log file usage with Percona Monitoring and Management (PMM) graphs.

Finally, for innodb_flush_method, O_DIRECT_NO_FSYNC prevents double buffering between the OS cache and disk, and works well with low-latency IO devices such as RAID subsystem with write cache. On the other hand, in high-latency IO devices, commonly found on deployments where MySQL is stored in SAN drives, having an OS cache with the default flush method fsync is more beneficial.

All in all, the MySQL 8.0 innodb_dedicated_server variable provides a fairly well-tuned InnoDB configuration at startup. But if it’s not enough, you can still tune these variables based on your workload and hardware. While MySQL 8.0 isn’t released yet, you can take a look at this article that helps you tune the current version (MySQL 5.7) right after installation.

The post New MySQL 8.0 innodb_dedicated_server Variable Optimizes InnoDB from the Get-Go appeared first on Percona Database Performance Blog.

This blog post contains a poll that helps us find out what Percona software the open source database community is using.

Nearly 20 years ago, Netscape released the source code for its Netscape Communicator web browser. This marked one of the biggest moments in “open source” history. The formation of The Open Source Initiative happened shortly after that. Bruce Perens, one of the working group’s founders, adapted his Free Software Guidelines as the official Open Source Definition.

Since then, open source software has gone from being the exception in large projects and enterprises, to being a normal part of huge deployments and daily business activities. Open source software is used by some of the biggest online companies: Facebook, YouTube, Twitter, etc. Many of these companies depend on open source software as part of their business model.

Percona’s mission is to champion unbiased open source database solutions. As part of this mission, we provide open source software, completely free of charge and for reuse. We developed our Percona Server for MySQL and Percona Server for MongoDB solutions to not only be drop-in replacements for existing open source software, but often incorporate “enterprise” features from upstream.

We’ve also recognized a need for a database clustering and backup solutions, and created Percona XtraDB Cluster and Percona XtraBackup to address those concerns.

Beyond database software, Percona has created management and monitoring tools like Percona Monitoring and Management that not only help DBAs with day-to-day tasks, but also use metrics to find out how best to configure, optimize and architect a database environment to best meet the needs of applications and websites.

What we’d like to know is which of our software products are you currently using in your database environment? Are you using just database software, just management and monitoring tools, or a combination of both? As Percona makes plans for the year, we’d like to know what the community is using, what they find helpful, and how we can best allocate our resources to address those needs. We are always looking for the best ways to invest in and grow the Percona software and tools people use.

Complete the survey below by selecting all the options that apply.

Note: There is a poll embedded within this post, please visit the site to participate in this post’s poll.

Thanks in advance for your responses – this helps us see which of our software is being deployed in the community.

The post Percona Blog Poll: What Percona Software Are You Using? appeared first on Percona Database Performance Blog.

In this blog post, we’ll look at how you can verify query performance using ProxySQL.

In the previous blog post, I showed you how many information can you get from the “stats.stats_mysql_query_digest” table in ProxySQL. I also mentioned you could even collect and graph these metrics. I will show you this is not just theory, it is possible.

These graphs could be very useful to understand the impact of the changes what you made on the query count or execution time.

I used our all-time favorite benchmark tool called Sysbench. I was running the following query:

UPDATE sbtest1 SET c=? WHERE k=?

There was no index on “k” when I started the test. During the test, I added an index. We expect to see some changes in the graphs.

I selected the “stats.stats_mysql_query_digest” into a file in every second, then I used Percona Monitoring and Management (PMM) to create graphs from the metrics. (I am going write another blog post on how can you use PMM to create graphs from any kind of metrics.)

Without the index, the update was running only 2-3 times per second. By adding the index, it went up to 400-500 hundred. We can see the results immediately on the graph.

Let’s see the average execution time:

Without the index, it took 600000-700000 microseconds, which is around 0.7s. By adding an index, it dropped to 0.01s. This is a big win, but most importantly we can see the effects on the query response time and query count if we are making some changes to the schema, query or configuration as well.

Conclusion

If you already have a ProxySQL server collecting and graphing these metrics, they could be quite useful when you are optimizing your queries. They can help make sure you are moving in the right direction with your tunings/modifications.

In this blog post, we’ll look at how to create PMM custom graphs and dashboards to track what you need to see in your database.

Percona Monitoring and Management (PMM)‘s default set of graphs is pretty complete: it covers most of the stuff a DBA requires to fully visualize database servers. However, sometimes custom information is needed in graphical form. Otherwise, you just feel your PMM deployment is a missing a graph.

Recently, a customer request came in asking for a better understanding of a specific metric: table growth, or more specifically the daily table growth (in bytes) for the last 30 days.

The graph we came up with looks like this:
. . .which graphs the information that comes from this query:

increase(mysql_info_schema_table_size{instance="$host",component="data_length"}[1d])

But what does that query mean, and how do I create one myself? I’m glad you asked! Let’s go deep into the technical details!

Before creating any graph, we must ensure that we have the data that will represent graphically. So, the first step is to ensure data collection.

Data collection

This data is already collected by the Percona mysqld_exporter, as defined in the “Collector Flags” table from the GitHub repo: https://github.com/percona/mysqld_exporter/#collector-flags

Cool! Now we need a Prometheus query in order to get the relevant data. Luckily, the Prometheus documentation is very helpful and we came up with a query in no time.

Prometheus query

What do we need for the query? In this case, it is a metric, a label and a time range. Every PMM deployment has access to the Prometheus console by adding “/prometheus” to the URL. The console is incredibly helpful when playing with queries. The console looks like this:

The metric

The time series values collected by the exporter are stored in the metrics inside of Prometheus. For our case, the metric name is called mysql_info_schema_table_size, which I figured out by using the Prometheus console “Expression” text input and its autocomplete feature. This shows you the options available as you’re writing. All the metrics collected by mysqld_export start with “mysql”.

The label

Labels are different per metric, but they are intuitively named. We need the instance and component labels. Instance is the hostname and component is equivalent to the column name of a MySQL table. The component we need is “data_length”.

The time frame

This is easy: since is a daily value, the time frame is 1d. 

The time frame is not mandatory, but it is a parameter asked for by the function we’re going to use to calculate the increase, which is called increase().

That’s how we ended up with the query that feeds the metrics, which end up in here:

You will notice it’s using a variable: $host. We define that variable in the dashboard creation, explained below.

PMM dashboard

PMM best practice is to take a copy of the existing dashboard using Setting > Save as…, since edits to Percona-provided dashboards are not preserved during upgrades. In this example, we will start with an empty dashboard.

Adding a new dashboard is as easy as clicking the “New” button from the Grafana dropdown menu:

After that, you choose the type of element that you want on a new row, which is a Graph in this case:

We like to use variables for our graphs – changing which server we analyze, for example. To add variables to the dashboard, we need to head up to the Templating option and add the variables:

Make sure you put a meaningful name for your dashboard, and you’re all set! A good practice will be to export the JSON definition of your dashboard as a backup for future recovery, or to just share it with others.

The final dashboard is called “MySQL Table Size” and holds another graph showing the table size during the timeframe for the top ten biggest tables. It looks like this:

The top right of the screen has some drop down links, the ones that look like this:

You can add links on the “Link” tab of the dashboard settings:

In case you are wondering, the query for the “Table size” graph is:

topk(10,sort_desc(sum(mysql_info_schema_table_size{instance="$host",component=~".*length"}) by (schema, table)))

So next time you want to enhance PMM and you know that there is data already inside Prometheus, but PMM lacks the visualization you want, just add it! Create a new graph and put it to your own custom dashboard!

Please join Percona’s Principal Support Engineer, Sveta Smirnova, as she presents Basic External MySQL Troubleshooting Tools on March 15, 2018 at 10:00 am PDT (UTC-7) / 1:00 pm EDT (UTC-4).

In my troubleshooting webinar series, I normally like to discuss built-in instruments available via the SQL interface. While they are effective and help to understand what is going on, external tools are also designed to make life of a database administrator easier.

In this webinar, I will discuss the external tools, toolkits and graphical instruments most valued by Support teams and customers. I will show the main advantages of these tools, and provide examples on how to effectively use them.

I will cover Percona Toolkit, MySQL Utilities, MySQL Sandbox, Percona Monitoring and Management (PMM) and a few other instruments.

Register for the webinar now.

Sveta Smirnova, Principal Technical Services Engineer

Sveta joined Percona in 2015. Her main professional interests are problem-solving, working with tricky issues, bugs, finding patterns that can quickly solve typical issues, and teaching others how to deal with MySQL issues, bugs and gotchas effectively. Before joining Percona Sveta worked as Support Engineer in the MySQL Bugs Analysis Support Group in MySQL AB-Sun-Oracle. She is the author of the book “MySQL Troubleshooting” and JSON UDF functions for MySQL.