emGee Software Solutions Custom Database Applications

Share this

Web Technologies

Percona Live 2019 – Save the Date!

Planet MySQL - Thu, 10/11/2018 - 05:38

After much speculation following the announcement in Santa Clara earlier this year, we are delighted to announce Percona Live 2019 will be taking place in Austin, Texas.

Save the dates in your diary for May, 28-30 2019!

The conference will take place just after Memorial Day at The Hyatt Regency, Austin on the shores of Lady Bird Lake.

This is also an ideal central location for those who wish to extend their stay and explore what Austin has to offer! Call for papers, ticket sales and sponsorship opportunities will be announced soon, so stay tuned!

In other Percona Live news, we’re less than 4 weeks away from this year’s European conference taking place in Frankfurt, Germany on 5-7 November. The tutorials and breakout sessions have been announced, and you can view the full schedule here. Tickets are still on sale so don’t miss out, book yours here today!

 

Register Now for Percona Live Europe 2018

 

Categories: Web Technologies

FOSDEM’19 MySQL, MariaDB & Friends DEVROOM Call for Papers is now open !

Planet MySQL - Thu, 10/11/2018 - 04:17

Good news ! The MySQL, MariaDB & Friends Devroom has been accepted for FOSDEM’19 ‘s edition as announced earlier!

This event is a real success story for the MySQL ecosystem; the content, the speakers and the attendees are growing every year.

The first big change for this 2019 edition is that the MariaDB Foundation (Ian) is joining my efforts to build this Devroom. Don’t forget that FOSDEM takes place in Belgium, and our motto is “l’Union fait la Force” [“Unity is Strength”].

FOSDEM 2019’s edition will take place 2nd & 3rd February in Brussels and our MySQL, MariaDB & Friends devroom will run on Saturday 2nd (may change). FOSDEM & MySQL/MariaDB is a love story started 19 years ago !

The committee selecting the content for our devroom is not yet created and if you want to be part of this experience, just send me an email (candidate at mysqlmariadbandfriends dot eu) before Oct 29th.

If you want to join the Committee you have to align with the following conditions:

  • planning to be present at FOSDEM
  • having a link with MySQL & MariaDB Ecosystem
  • have some time to review and rate talks
  • be an ambassador for the event by promoting it

The Call for Paper is now offcialy open and ends November 15th. You can submit now() your proposal using FOSDEM’s submission tool.

Marketing and Sales speeches are not welcome, focus on the engineering, the operations and of course the developers.

Don’t forget to specify the track (MySQL, MariaDB and Friends devroom) and set the duration to 20 mins (short but intense ;-) )!

Thank you, and see you soon in Brussels !

lefred

Categories: Web Technologies

PHP 7.1.23 Released - PHP: Hypertext Preprocessor

Planet PHP - Wed, 10/10/2018 - 17:00
The PHP development team announces the immediate availability of PHP 7.1.23. This is a bugfix release.All PHP 7.1 users are encouraged to upgrade to this version.For source downloads of PHP 7.1.23 please visit our downloads page, Windows source and binaries can be found on windows.php.net/download/. The list of changes is recorded in the ChangeLog.
Categories: Web Technologies

PHP 7.3.0RC3 Released - PHP: Hypertext Preprocessor

Planet PHP - Wed, 10/10/2018 - 17:00
The PHP team is glad to announce the next PHP 7.3.0 pre-release, PHP 7.3.0RC3. The rough outline of the PHP 7.3 release cycle is specified in the PHP Wiki. For source downloads of PHP 7.3.0RC3 please visit the download page. Windows sources and binaries can be found on windows.php.net/qa/. Please carefully test this version and report any issues found in the bug reporting system. THIS IS A DEVELOPMENT PREVIEW - DO NOT USE IT IN PRODUCTION! For more information on the new features and other changes, you can read the NEWS file, or the UPGRADING file for a complete list of upgrading notes. Internal changes are listed in the UPGRADING.INTERNALS file. These files can also be found in the release archive. The next release would be RC4, planned for October 25th. The signatures for the release can be found in the manifest or on the QA site. Thank you for helping us make PHP better.
Categories: Web Technologies

PHP 7.2.11 Released - PHP: Hypertext Preprocessor

Planet PHP - Wed, 10/10/2018 - 17:00
The PHP development team announces the immediate availability of PHP 7.2.11. This is a bugfix release.All PHP 7.2 users are encouraged to upgrade to this version.For source downloads of PHP 7.2.11 please visit our downloads page, Windows source and binaries can be found on windows.php.net/download/. The list of changes is recorded in the ChangeLog.
Categories: Web Technologies

The dialog element

CSS-Tricks - Wed, 10/10/2018 - 13:28

Chris Manning digs into <dialog>:

A dialog element provides:

  • An element that is easy to show and hide, including an open boolean attribute on the element itself.
  • Two versions: a standard popover or modal version.
  • A ::backdrop pseudo-element for modal types.
  • Built-in focus: see dialog focusing steps.
  • ARIA role support (dialog is the implied default). Also accepts the alertdialog role.
  • A pending stack for multiple dialogs.
  • A DOM interface with the open boolean and methods show, showModal, and close.

And those are just some highlights! Showing content on top of other content has never been easier.

This is the evolution of the web at it's best. Identifying a major developer struggle and helping solve it.

Direct Link to ArticlePermalink

The post The dialog element appeared first on CSS-Tricks.

Categories: Web Technologies

Percona Monitoring and Management (PMM) 1.15.0 Is Now Available

Planet MySQL - Wed, 10/10/2018 - 11:31

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.

This release offers two new features for both the MySQL Community and Percona Customers:

  • MySQL Custom Queries – Turn a SELECT into a dashboard!
  • Server and Client logs – Collect troubleshooting logs for Percona Support

We addressed 17 new features and improvements, and fixed 17 bugs.

MySQL Custom Queries

In 1.15 we are introducing the ability to take a SQL SELECT statement and turn the result set into metric series in PMM.  The queries are executed at the LOW RESOLUTION level, which by default is every 60 seconds.  A key advantage is that you can extend PMM to profile metrics unique to your environment (see users table example), or to introduce support for a table that isn’t part of PMM yet. This feature is on by default and only requires that you edit the configuration file and use vaild YAML syntax.  The configuration file is in /usr/local/percona/pmm-client/queries-mysqld.yml.

Example – Application users table

We’re going to take a fictional MySQL users table that also tracks the number of upvotes and downvotes, and we’ll convert this into two metric series, with a set of seven labels, where each label can also store a value.

Browsing metrics series using Advanced Data Exploration Dashboard

Lets look at the output so we understand the goal – take data from a MySQL table and store in PMM, then display as a metric series.  Using the Advanced Data Exploration Dashboard you can review your metric series. Exploring the metric series  app1_users_metrics_downvotes we see the following:

MySQL table

Lets assume you have the following users table that includes true/false, string, and integer types.

SELECT * FROM `users` +----+------+--------------+-----------+------------+-----------+---------------------+--------+---------+-----------+ | id | app | user_type | last_name | first_name | logged_in | active_subscription | banned | upvotes | downvotes | +----+------+--------------+-----------+------------+-----------+---------------------+--------+---------+-----------+ | 1 | app2 | unprivileged | Marley | Bob | 1 | 1 | 0 | 100 | 25 | | 2 | app3 | moderator | Young | Neil | 1 | 1 | 1 | 150 | 10 | | 3 | app4 | unprivileged | OConnor | Sinead | 1 | 1 | 0 | 25 | 50 | | 4 | app1 | unprivileged | Yorke | Thom | 0 | 1 | 0 | 100 | 100 | | 5 | app5 | admin | Buckley | Jeff | 1 | 1 | 0 | 175 | 0 | +----+------+--------------+-----------+------------+-----------+---------------------+--------+---------+-----------+

Explaining the YAML syntax

We’ll go through a simple example and mention what’s required for each line.  The metric series is constructed based on the first line and appends the column name to form metric series.  Therefore the number of metric series per table will be the count of columns that are of type GAUGE or COUNTER.  This metric series will be called app1_users_metrics_downvotes:

app1_users_metrics: ## leading section of your metric series. query: "SELECT * FROM app1.users" ## Your query. Don't forget the schema name. metrics: ## Required line to start the list of metric items - downvotes: ## Name of the column returned by the query. Will be appended to the metric series. usage: "COUNTER" ## Column value type. COUNTER will make this a metric series. description: "Number of upvotes" ## Helpful description of the column.

Full queries-mysqld.yml example

Each column in the SELECT is named in this example, but that isn’t required, you can use a SELECT * as well.  Notice the format of schema.table for the query is included.

--- app1_users_metrics: query: "SELECT app,first_name,last_name,logged_in,active_subscription,banned,upvotes,downvotes FROM app1.users" metrics: - app: usage: "LABEL" description: "Name of the Application" - user_type: usage: "LABEL" description: "User's privilege level within the Application" - first_name: usage: "LABEL" description: "User's First Name" - last_name: usage: "LABEL" description: "User's Last Name" - logged_in: usage: "LABEL" description: "User's logged in or out status" - active_subscription: usage: "LABEL" description: "Whether User has an active subscription or not" - banned: usage: "LABEL" description: "Whether user is banned or not" - upvotes: usage: "COUNTER" description: "Count of upvotes the User has earned. Upvotes once granted cannot be revoked, so the number can only increase." - downvotes: usage: "GAUGE" description: "Count of downvotes the User has earned. Downvotes can be revoked so the number can increase as well as decrease." ...

We hope you enjoy this feature, and we welcome your feedback via the Percona forums!

Server and Client logs

We’ve enhanced the volume of data collected from both the Server and Client perspectives.  Each service provides a set of files designed to be shared with Percona Support while you work on an issue.

Server

From the Server, we’ve improved the logs.zip service to include:

  • Prometheus targets
  • Consul nodes, QAN API instances
  • Amazon RDS and Aurora instances
  • Version
  • Server configuration
  • Percona Toolkit commands

You retrieve the link from your PMM server using this format:   https://pmmdemo.percona.com/managed/logs.zip

Client

On the Client side we’ve added a new action called summary which fetches logs, network, and Percona Toolkit output in order to share with Percona Support. To initiate a Client side collection, execute:

pmm-admin summary

The output will be a file you can use to attach to your Support ticket.  The single file will look something like this:

summary__2018_10_10_16_20_00.tar.gz New Features and Improvements
  • PMM-2913 – Provide ability to execute Custom Queries against MySQL – Credit to wrouesnel for the framework of this feature in wrouesnel/postgres_exporter!
  • PMM-2904 – Improve PMM Server Diagnostics for Support
  • PMM-2860 – Improve pmm-client Diagnostics for Support
  • PMM-1754 – Provide functionality to easily select query and copy it to clipboard in QAN
  • PMM-1855 – Add swap to AMI
  • PMM-3013 – Rename PXC Overview graph Sequence numbers of transactions to IST Progress
  • PMM-2726 – Abort data collection in Exporters based on Prometheus Timeout – MySQLd Exporter
  • PMM-3003 – PostgreSQL Overview Dashboard Tooltip fixes
  • PMM-2936 – Some improvements for Query Analytics Settings screen
  • PMM-3029 – PostgreSQL Dashboard Improvements
Fixed Bugs
  • PMM-2976 – Upgrading to PMM 1.14.x fails if dashboards from Grafana 4.x are present on an installation
  • PMM-2969 – rds_exporter becomes throttled by CloudWatch API
  • PMM-1443 – The credentials for a secured server are exposed without explicit request
  • PMM-3006 – Monitoring over 1000 instances is displayed imperfectly on the label
  • PMM-3011 – PMM’s default MongoDB DSN is localhost, which is not resolved to IPv4 on modern systems
  • PMM-2211 – Bad display when using old range in QAN
  • PMM-1664 – Infinite loading with wrong queryID
  • PMM-2715 – Since pmm-client-1.9.0, pmm-admin detects CentOS/RHEL 6 installations using linux-upstart as service manager and ignores SysV scripts
  • PMM-2839 – Tablestats safety precaution does not work for RDS/Aurora instances
  • PMM-2845 – pmm-admin purge causes client to panic
  • PMM-2968 – pmm-admin list shows empty data source column for mysql:metrics
  • PMM-3043 – Total Time percentage is incorrectly shown as a decimal fraction
  • PMM-3082 – Prometheus Scrape Interval Variance chart doesn’t display data
How to get PMM Server

PMM is available for installation using three methods:

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

Categories: Web Technologies

Two More MySQL Books for 2018

Planet MySQL - Wed, 10/10/2018 - 09:14
Last time I mentioned four great MySQL books for 2018.  I was tactfully reminded of two books I overlooked. First is Dr. Charles Bell's Introducing InnoDB Cluster which I have not read (but it is on order).
Introducing InnoDB Cluster And last, but not least, is Mikael Ronstrum's MySQL Cluster 7.5 Inside and Out.  This is another book on NDB cluster and is a 'msut have' for those running NDB clusters.

MySQL Cluster 7.5 Inside and Out I apologize to both authors and take full blame for not mentioning these two find books.  Now I just have to wait for Amazon to send me the copies I ordered!


Categories: Web Technologies

Instrumenting Read Only Transactions in InnoDB

Planet MySQL - Wed, 10/10/2018 - 08:09

Probably not well known but quite an important optimization was introduced in MySQL 5.6 – reduced overhead for “read only transactions”. While usually by a “transaction” we mean a query or a group of queries that change data, with transaction engines like InnoDB, every data read or write operation is a transaction.

Now, as a non-locking read operation obviously has less impact on the data, it does not need all the instrumenting overhead a write transaction has. The main thing that can be avoided, as described by documentation, is the transaction ID. So, since MySQL 5.6, a read only transaction does not have a transaction ID. Moreover, such a transaction is not visible in the SHOW ENGINE INNODB STATUS output, though I will not go deeper on what really that means under the hood in this article. The fact is that this optimization allows for better scaling of workloads with many RO threads. An example RO benchmark, where 5.5 vs 5.6/5.7 difference is well seen, may be found here: https://www.percona.com/blog/2016/04/07/mysql-5-7-sysbench-oltp-read-results-really-faster/

To benefit from this optimization in MySQL 5.6, either a transaction has to start with the explicit START TRANSACTION READ ONLY clause or it must be an autocommit, non-locking SELECT statement. In version 5.7 and newer, it goes further, as a new transaction is treated as read-only until a locking read or write is executed, at which point it gets “upgraded” to a read-write one.

Information Schema Instrumentation

Let’s see how it looks like (on MySQL 8.0.12) by looking at information_schema.innodb_trx and information_schema.innodb_metrics tables. The second of these, by default, has transaction counters disabled, so before the test we have to enable it with:

SET GLOBAL innodb_monitor_enable = 'trx%comm%';

or by adding a parameter to the

[mysqld] section of the configuration file and restarting the instance:innodb_monitor_enable = "trx_%"

Now, let’s start a transaction which should be read only according to the rules:

mysql [localhost] {msandbox} (db1) > START TRANSACTION; SELECT count(*) FROM db1.t1; Query OK, 0 rows affected (0.00 sec) +----------+ | count(*) | +----------+ | 3 | +----------+ 1 row in set (0.00 sec mysql [localhost] {msandbox} (db1) > SELECT trx_id,trx_weight,trx_rows_locked,trx_rows_modified,trx_is_read_only,trx_autocommit_non_locking FROM information_schema.innodb_trx\G *************************** 1. row *************************** trx_id: 421988493944672 trx_weight: 0 trx_rows_locked: 0 trx_rows_modified: 0 trx_is_read_only: 0 trx_autocommit_non_locking: 0 1 row in set (0.00 sec)

Transaction started as above, did not appear in SHOW ENGINE INNODB STATUS, and its trx_id looks strangely high. And first surprise—for some reason, trx_is_read_only is 0. Now, what if we commit such a transaction—how do the counters change? (I reset them before the test):

mysql [localhost] {msandbox} (db1) > commit; Query OK, 0 rows affected (0.00 sec) mysql [localhost] {msandbox} (db1) > SELECT name, comment, status, count FROM information_schema.innodb_metrics WHERE name like 'trx%comm%'; +---------------------------+--------------------------------------------------------------------+---------+-------+ | name | comment | status | count | +---------------------------+--------------------------------------------------------------------+---------+-------+ | trx_rw_commits | Number of read-write transactions committed | enabled | 0 | | trx_ro_commits | Number of read-only transactions committed | enabled | 1 | | trx_nl_ro_commits | Number of non-locking auto-commit read-only transactions committed | enabled | 0 | | trx_commits_insert_update | Number of transactions committed with inserts and updates | enabled | 0 | +---------------------------+--------------------------------------------------------------------+---------+-------+ 4 rows in set (0.01 sec)

OK, so clearly it was a read-only transaction overall, just the trx_is_read_only property wasn’t set as expected. I had to report this problem here: https://bugs.mysql.com/bug.php?id=92558

What about an explicit RO transaction:

mysql [localhost] {msandbox} (db1) > START TRANSACTION READ ONLY; SELECT count(*) FROM db1.t1; Query OK, 0 rows affected (0.00 sec) +----------+ | count(*) | +----------+ | 3 | +----------+ 1 row in set (0.00 sec mysql [localhost] {msandbox} (db1) > SELECT trx_id,trx_weight,trx_rows_locked,trx_rows_modified,trx_is_read_only,trx_autocommit_non_locking FROM information_schema.innodb_trx\G *************************** 1. row *************************** trx_id: 421988493944672 trx_weight: 0 trx_rows_locked: 0 trx_rows_modified: 0 trx_is_read_only: 1 trx_autocommit_non_locking: 0 1 row in set (0.00 sec) mysql [localhost] {msandbox} (db1) > commit; Query OK, 0 rows affected (0.00 sec) mysql [localhost] {msandbox} (db1) > SELECT name, comment, status, count FROM information_schema.innodb_metrics WHERE name like 'trx%comm%'; +---------------------------+--------------------------------------------------------------------+---------+-------+ | name | comment | status | count | +---------------------------+--------------------------------------------------------------------+---------+-------+ | trx_rw_commits | Number of read-write transactions committed | enabled | 0 | | trx_ro_commits | Number of read-only transactions committed | enabled | 2 | | trx_nl_ro_commits | Number of non-locking auto-commit read-only transactions committed | enabled | 0 | | trx_commits_insert_update | Number of transactions committed with inserts and updates | enabled | 0 | +---------------------------+--------------------------------------------------------------------+---------+-------+ 4 rows in set (0.01 sec)

OK, both transactions are counted as the same type. Moreover, the two transactions shared the same strange trx_id, which appears to be a fake one. For a simple read executed in autocommit mode, the counters increase as expected too:

mysql [localhost] {msandbox} (db1) > select @@autocommit; SELECT count(*) FROM db1.t1; +--------------+ | @@autocommit | +--------------+ | 1 | +--------------+ 1 row in set (0.00 sec) +----------+ | count(*) | +----------+ | 3 | +----------+ 1 row in set (0.00 sec) mysql [localhost] {msandbox} (db1) > SELECT name, comment, status, count FROM information_schema.innodb_metrics WHERE name like 'trx%comm%'; +---------------------------+--------------------------------------------------------------------+---------+-------+ | name | comment | status | count | +---------------------------+--------------------------------------------------------------------+---------+-------+ | trx_rw_commits | Number of read-write transactions committed | enabled | 0 | | trx_ro_commits | Number of read-only transactions committed | enabled | 2 | | trx_nl_ro_commits | Number of non-locking auto-commit read-only transactions committed | enabled | 1 | | trx_commits_insert_update | Number of transactions committed with inserts and updates | enabled | 0 | +---------------------------+--------------------------------------------------------------------+---------+-------+ 4 rows in set (0.00 sec)

Now, let’s test how a transaction looks when we upgrade it to RW later:

mysql [localhost] {msandbox} (db1) > START TRANSACTION; SELECT count(*) FROM db1.t1; Query OK, 0 rows affected (0.00 sec) +----------+ | count(*) | +----------+ | 3 | +----------+ 1 row in set (0.00 sec) mysql [localhost] {msandbox} (db1) > SELECT trx_id,trx_weight,trx_rows_locked,trx_rows_modified,trx_is_read_only,trx_autocommit_non_locking FROM information_schema.innodb_trx\G *************************** 1. row *************************** trx_id: 421988493944672 trx_weight: 0 trx_rows_locked: 0 trx_rows_modified: 0 trx_is_read_only: 0 trx_autocommit_non_locking: 0 1 row in set (0.00 sec) mysql [localhost] {msandbox} (db1) > SELECT count(*) FROM db1.t1 FOR UPDATE; +----------+ | count(*) | +----------+ | 3 | +----------+ 1 row in set (0.00 sec) mysql [localhost] {msandbox} (db1) > SELECT trx_id,trx_weight,trx_rows_locked,trx_rows_modified,trx_is_read_only,trx_autocommit_non_locking FROM information_schema.innodb_trx\G *************************** 1. row *************************** trx_id: 4106 trx_weight: 2 trx_rows_locked: 4 trx_rows_modified: 0 trx_is_read_only: 0 trx_autocommit_non_locking: 0 1 row in set (0.00 sec) mysql [localhost] {msandbox} (db1) > commit; Query OK, 0 rows affected (0.00 sec) mysql [localhost] {msandbox} (db1) > SELECT name, comment, status, count FROM information_schema.innodb_metrics WHERE name like 'trx%comm%'; +---------------------------+--------------------------------------------------------------------+---------+-------+ | name | comment | status | count | +---------------------------+--------------------------------------------------------------------+---------+-------+ | trx_rw_commits | Number of read-write transactions committed | enabled | 1 | | trx_ro_commits | Number of read-only transactions committed | enabled | 2 | | trx_nl_ro_commits | Number of non-locking auto-commit read-only transactions committed | enabled | 1 | | trx_commits_insert_update | Number of transactions committed with inserts and updates | enabled | 0 | +---------------------------+--------------------------------------------------------------------+---------+-------+ 4 rows in set (0.00 sec)

OK, as seen above, after a locking read was done, our transaction has transformed: it got a real, unique trx_id assigned. Then, when committed, the RW counter increased.

Performance Schema Problem

Nowadays it may feel natural to use performance_schema for monitoring everything. And, indeed, we can monitor types of transactions with it as well. Let’s enable the needed consumers and instruments:

mysql [localhost] {msandbox} (db1) > UPDATE performance_schema.setup_consumers SET ENABLED = 'YES' WHERE NAME LIKE '%transactions%'; Query OK, 0 rows affected (0.00 sec) Rows matched: 3 Changed: 0 Warnings: 0 mysql [localhost] {msandbox} (db1) > UPDATE performance_schema.setup_instruments SET ENABLED = 'YES', TIMED = 'YES' WHERE NAME = 'transaction'; Query OK, 0 rows affected (0.01 sec) Rows matched: 1 Changed: 0 Warnings: 0 mysql [localhost] {msandbox} (db1) > SELECT * FROM performance_schema.setup_instruments WHERE NAME = 'transaction'; +-------------+---------+-------+------------+------------+---------------+ | NAME | ENABLED | TIMED | PROPERTIES | VOLATILITY | DOCUMENTATION | +-------------+---------+-------+------------+------------+---------------+ | transaction | YES | YES | | 0 | NULL | +-------------+---------+-------+------------+------------+---------------+ 1 row in set (0.00 sec) mysql [localhost] {msandbox} (db1) > SELECT * FROM performance_schema.setup_consumers WHERE NAME LIKE '%transactions%'; +----------------------------------+---------+ | NAME | ENABLED | +----------------------------------+---------+ | events_transactions_current | YES | | events_transactions_history | YES | | events_transactions_history_long | YES | +----------------------------------+---------+ 3 rows in set (0.01 sec) mysql [localhost] {msandbox} (db1) > SELECT COUNT_STAR,COUNT_READ_WRITE,COUNT_READ_ONLY FROM performance_schema.events_transactions_summary_global_by_event_name\G *************************** 1. row *************************** COUNT_STAR: 0 COUNT_READ_WRITE: 0 COUNT_READ_ONLY: 0 1 row in set (0.00 sec)

And let’s do some simple tests:

mysql [localhost] {msandbox} (db1) > START TRANSACTION; COMMIT; Query OK, 0 rows affected (0.01 sec) Query OK, 0 rows affected (0.00 sec) mysql [localhost] {msandbox} (db1) > SELECT COUNT_STAR,COUNT_READ_WRITE,COUNT_READ_ONLY FROM performance_schema.events_transactions_summary_global_by_event_name\G *************************** 1. row *************************** COUNT_STAR: 1 COUNT_READ_WRITE: 1 COUNT_READ_ONLY: 0 1 row in set (0.00 sec) mysql [localhost] {msandbox} (db1) > SELECT name, comment, status, count FROM information_schema.innodb_metrics WHERE name like 'trx%comm%'; +---------------------------+--------------------------------------------------------------------+---------+-------+ | name | comment | status | count | +---------------------------+--------------------------------------------------------------------+---------+-------+ | trx_rw_commits | Number of read-write transactions committed | enabled | 0 | | trx_ro_commits | Number of read-only transactions committed | enabled | 0 | | trx_nl_ro_commits | Number of non-locking auto-commit read-only transactions committed | enabled | 0 | | trx_commits_insert_update | Number of transactions committed with inserts and updates | enabled | 0 | +---------------------------+--------------------------------------------------------------------+---------+-------+ 4 rows in set (0.00 sec)

A void transaction caused an increase to this RW counter in Performance Schema view! Moreover, a simple autocommit select increases it too:

mysql [localhost] {msandbox} (db1) > SELECT count(*) FROM db1.t1; +----------+ | count(*) | +----------+ | 3 | +----------+ 1 row in set (0.01 sec) mysql [localhost] {msandbox} (db1) > SELECT COUNT_STAR,COUNT_READ_WRITE,COUNT_READ_ONLY FROM performance_schema.events_transactions_summary_global_by_event_name\G *************************** 1. row *************************** COUNT_STAR: 2 COUNT_READ_WRITE: 2 COUNT_READ_ONLY: 0 1 row in set (0.00 sec) mysql [localhost] {msandbox} (db1) > START TRANSACTION READ ONLY; COMMIT; Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) mysql [localhost] {msandbox} (db1) > SELECT COUNT_STAR,COUNT_READ_WRITE,COUNT_READ_ONLY FROM performance_schema.events_transactions_summary_global_by_event_name\G *************************** 1. row *************************** COUNT_STAR: 3 COUNT_READ_WRITE: 2 COUNT_READ_ONLY: 1 1 row in set (0.00 sec) mysql [localhost] {msandbox} (db1) > SELECT name, comment, status, count FROM information_schema.innodb_metrics WHERE name like 'trx%comm%'; +---------------------------+--------------------------------------------------------------------+---------+-------+ | name | comment | status | count | +---------------------------+--------------------------------------------------------------------+---------+-------+ | trx_rw_commits | Number of read-write transactions committed | enabled | 0 | | trx_ro_commits | Number of read-only transactions committed | enabled | 0 | | trx_nl_ro_commits | Number of non-locking auto-commit read-only transactions committed | enabled | 1 | | trx_commits_insert_update | Number of transactions committed with inserts and updates | enabled | 0 | +---------------------------+--------------------------------------------------------------------+---------+-------+ 4 rows in set (0.01 sec)

As seen above, with regard to monitoring transactions via Performance Schema, everything seems completely broken, empty transactions increase counters, and the only way to increase RO counter is to call a read-only transaction explicitly, but again, it should not count when no real read was done from a table. For this reason I filed another bug report: https://bugs.mysql.com/bug.php?id=92364

PMM Dashboard

We implemented a transactions information view in PMM, based on Information_schema.innodb_metrics, which—as presented above—is reliable and shows the correct counters. Therefore, I encourage everyone to use the innodb_monitor_enable setting to enable it and have the PMM graph it. It will look something like this:

Categories: Web Technologies

Arrays vs. Sets

Echo JS - Wed, 10/10/2018 - 03:27
Categories: Web Technologies

JavaScript Visualizer

Echo JS - Wed, 10/10/2018 - 03:27
Categories: Web Technologies

Hello, Create React App 2.0

Echo JS - Wed, 10/10/2018 - 03:27
Categories: Web Technologies

Pages