Buy Percona ServicesBuy Now!

Percona Toolkit 3.0.10 Is Now Available

Lastest Forum Posts - May 24, 2018 - 6:13am
Percona announces the release of Percona Toolkit 3.0.10 on May 22, 2018.

Percona Toolkit is a collection of advanced open source command-line tools, developed and used by the Percona technical staff, that are engineered to perform a variety of MySQL®, MongoDB® and system tasks that are too difficult or complex to perform manually. With over 1,000,000 downloads, Percona Toolkit supports Percona Server for MySQL, MySQL®, MariaDB®, Percona Server for MongoDB and MongoDB.

Percona Toolkit, like all Percona software, is free and open source. You can download packages from the website or install from official repositories.

This release includes the following changes:

New Features:
  • PT-131: pt-table-checksum disables the QRT plugin
    The Query Response Time Plugin provides a tool for analyzing information by counting and displaying the number of queries according to the length of time they took to execute. This feature enables a new flag --disable-qrt-plugin that leverages Percona Server for MySQL’s new ability to disable QRT plugin at the session level. The advantage to enabling this Toolkit feature is that the QRT metrics are not impacted by the work that pt-table-checksum performs. This means that QRT metrics report only the work your Application is generating on MySQL, and not clouded by the activities of pt-table-checksum.
  • PT-118: pt-table-checksum reports the number of rows of difference between master and slave
    We’re adding support for pt-table-checksum to identify the number of row differences between master and slave. Previously you were able to see only the count of chunks that differed between hosts. This is helpful for situations where you believe you can tolerate some measure of row count drift between hosts, but want to be precise in understanding what that row count difference actually is.
Improvements
  • PT-1546: Improved support for MySQL 8 roles
  • PT-1543: The encrypted table status query causes high load over multiple minutes
    Users reported that listing encrypted table status can be very slow. We’ve enabled this functionality via --list-encrypted-tables and set it to default of disabled.
  • PT-1536: Added info about encrypted tablespaces in pt-mysql-summary
    We’ve improved pt-mysql-summary to now include information about encrypted tablespaces. This information is available by using --list-encrypted-tables .
Bug Fixes:
  • PT-1556: pt-table-checksum 3.0.9 does not change binlog_format to statement any more.
pt-show-grants has several known issues when working with MySQL 8 and roles, which Percona aims to address in subsequent Percona Toolkit releases: PT-1560, PT-1559, and PT-1558

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

Percona Server for MongoDB 3.6.4-1.2 Is Now Available

Lastest Forum Posts - May 24, 2018 - 6:12am
Percona announces the release ofPercona Server for MongoDB3.6.4-1.2 on May 23, 2018. Download the latest version from the Percona web site or the Percona Software Repositories.

Percona Server for MongoDB is an enhanced, open source, and highly-scalable database that is a fully-compatible, drop-in replacement for MongoDB 3.6 Community Edition. It supports MongoDB 3.6 protocols and drivers.

Percona Server for MongoDB extends MongoDB Community Edition functionality by including the Percona Memory Engine, as well as several enterprise-grade features. Percona Server for MongoDB requires no changes to MongoDB applications or code.

This release is based on MongoDB 3.6.4 and includes the following additional changes:
  • #PSMDB-205: mongod failed to initialize if audit filter was set to record Action type events specified with the $in expression.
  • #PSMDB-207: a premature initialization of the feature compatibility version in global parameters was fixed for the RocksDB storage engine.
  • #PSMDB-209: CentOS 6 and CentOS 7 RPM packages contained config file with a wrong link to the online Percona Memory Engine documentation.
Note: as mentioned in the Percona Server for MongoDB 3.6.3-1.1 Release Notes, MongoRocks is deprecated in Percona Server for MongoDB 3.6.

The Percona Server for MongoDB 3.6.4-1.2 release notes are available in the official documentation.

WSREP detected deadlock/conflict

Lastest Forum Posts - May 24, 2018 - 3:12am
I am getting WSREP detected deadlock/conflict with Percona latest version. I am using spring boot app to save my entity but some time this conflict occurs. It occurs at many places. Since I am testing I do not have much data but I hit database couple of times.
This is the log from node1 & node2
Code: 018-05-23T11:36:28.362290Z 127478 [Note] WSREP: --------- CONFLICT DETECTED -------- 2018-05-23T11:36:28.362338Z 127478 [Note] WSREP: cluster conflict due to certification failure for threads: 2018-05-23T11:36:28.362348Z 127478 [Note] WSREP: Victim thread: THD: 127478, mode: local, state: executing, conflict: cert failure, seqno: 2381 SQL: commit This is the exception from spring boot app
Code: org.springframework.orm.jpa.JpaSystemException: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: WSREP detected deadlock/conflict and aborted the transaction. Try restarting the transaction; nested exception is javax.persistence.PersistenceException: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: WSREP detected deadlock/conflict and aborted the transaction. Try restarting the transaction at org.springframework.orm.jpa.EntityManagerFactoryUtils.convertJpaAccessExceptionIfPossible(EntityManagerFactoryUtils.java:418) at org.springframework.orm.jpa.DefaultJpaDialect.translateExceptionIfPossible(DefaultJpaDialect.java:122) at org.springframework.orm.jpa.JpaTransactionManager.doCommit(JpaTransactionManager.java:521) at org.springframework.transaction.support.AbstractPlatformTransactionManager.processCommit(AbstractPlatformTransactionManager.java:761) at org.springframework.transaction.support.AbstractPlatformTransactionManager.commit(AbstractPlatformTransactionManager.java:730) at org.springframework.transaction.interceptor.TransactionAspectSupport.commitTransactionAfterReturning(TransactionAspectSupport.java:504) at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:292) at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:136) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) at org.springframework.data.jpa.repository.support.CrudMethodMetadataPostProcessor$CrudMethodMetadataPopulatingMethodInterceptor.invoke(CrudMethodMetadataPostProcessor.java:133) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:92) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) at org.springframework.data.repository.core.support.SurroundingTransactionDetectorMethodInterceptor.invoke(SurroundingTransactionDetectorMethodInterceptor.java:57) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:213) at com.sun.proxy.$Proxy153.save

The server quit without updating PID file (/var/lib/mysql/db-1.pid) [fail]

Lastest Forum Posts - May 24, 2018 - 2:52am
Hi All,

Anybody help..

I Can't start bootsrap-pxc.. error it's:
https://paste.fedoraproject.org/past...g2q83FDcsOrTDw

root@db-1:/etc/mysql/conf.d# /etc/init.d/bootmisc.sh status root@db-1:/etc/mysql/conf.d#


my error log:
https://paste.fedoraproject.org/past...pxSuVvPWoUYGpQ


Thank you,

riki

Querying Buffer Pool of InnoDB directly. Is it possible?

Lastest Forum Posts - May 24, 2018 - 1:00am
For us, it is very important to get high response time for our website php script which queries a large database. It is essentially getting user information for the user who visited the page. There may be millions of users in the db.

We would like to not bother using the user data at all if the record is not already in the InnoDB Buffer Pool of the MySQL database, and use information only if present in the above cache.

Is there a way to directly ask Mysql/InnoDB whether the record is in cache?

Percona Monitoring and Management 1.11.0 Is Now Available

Latest MySQL Performance Blog posts - May 23, 2018 - 1:37pm

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.

In PMM Release 1.11.0, we deliver the following changes:

  • Configurable MySQL Slow Log Rotation – enable or disable rotation, and specify how many files to keep on disk
  • Predictable Graphs – we’ve updated our formulas to use aggregation functions over time for more reliable graphs
  • MySQL Exporter Parsing of my.cnf – we’ve improved how we read my.cnf
  • Annotation improvements – passing multiple strings results in single annotation being written

The issues in the release includes 1 new features & improvements, and 9 bugs fixed.

MySQL Slow Log Rotation Improvements

We spent some time this release going over how we handle MySQL’s Slow Log rotation logic. Query Analytics requires that slow logging be enabled (either to file, or to PERFORMANCE_SCHEMA) and we found that users of Percona Server for MySQL overwhelmingly choose logging to a file in order to take advantage of log_slow_verbosity which provides enhanced InnoDB Usage information. However, the challenge with MySQL’s Slow Log is that it is very verbose and thus the number one concern is disk space. PMM strives to do no harm and so MySQL Slow Log Rotation was a natural fit, but until this release we were very strict and hadn’t enabled any configuration of these parameters.

Percona Server for MySQL Users have long known about Slow Query Log Rotation and Expiration, but until now had no way of using the in-built Percona Server for MySQL feature while ensuring that PMM wasn’t missing any queries from the Slow Log during file rotation. Or perhaps your use case is that you want to do Slow Log Rotation using logrotate or some other facility. Today with Release 1.11 this is now possible!

We’ve made two significant changes:

  1. You can now specify the number of Slow Log files to remain on disk, and let PMM handle deleting the oldest files first. Default remains unchanged – 1 Slow Log to remain on disk.
  2. Slow Log rotation can now be disabled, for example if you want to manage rotation using logrotate or Percona Server for MySQL Slow Query Log Rotation and Expiration. Default remains unchanged – Slow Log Rotation is ON.

Number of Slow Logs Retained on Disk

Slow Logs Rotation – On or Off

You specify each of these two new controls when setting up the MySQL service. The following example specifies that 5 Slow Log files should remain on disk:

pmm-admin add mysql ... --retain-slow-logs=5

While the following example specifies that Slow Log rotation is to be disabled (flag value of false), with the assumption that you will perform your own Slow Log Rotation:

pmm-admin add mysql ... --slow-log-rotation=false

We don’t currently support modifying option parameters for an existing service definition. This means you must remove, then re-add the service and include the new options.

We’re including a logrotate script in this post to get you started, and it is designed to keep 30 copies of Slow Logs at 1GB each. Note that you’ll need to update the Slow Log location, and ensure a MySQL User Account with SUPER, RELOAD are used for this script to successfully execute.

Example logrotate /var/mysql/mysql-slow.log {     nocompress     create 660 mysql mysql     size 1G     dateext     missingok     notifempty     sharedscripts     postrotate        /bin/mysql -e 'SELECT @@global.long_query_time INTO @LQT_SAVE; SET GLOBAL long_query_time=2000; SELECT SLEEP(2); FLUSH SLOW LOGS; SELECT SLEEP(2); SET GLOBAL long_query_time=@LQT_SAVE;'     endscript     rotate 30 } Predictable Graphs

We’ve updated the logic on four dashboards to better handle predictability and also to allow zooming to look at shorter time ranges.  For example, refreshing PXC/Galera graphs prior to 1.11 led to graphs spiking at different points during the metric series. We’ve reviewed each of these graphs and their corresponding queries and added in <aggregation>_over_time() functions so that graphs display a consistent view of the metric series. This improves your ability to drill in on the dashboards so that no matter how short your time range, you will still observe the same spikes and troughs in your metric series. The four dashboards affected by this improvement are:

  • Home Dashboard
  • PXC/Galera Graphs Dashboard
  • MySQL Overview Dashboard
  • MySQL InnoDB Metrics Dashboard
MySQL Exporter parsing of my.cnf

In earlier releases, the MySQL Exporter expected only key=value type flags. It would ignore options without values (i.e. disable-auto-rehash), and could sometimes read the wrong section of the my.cnf file.  We’ve updated the parsing engine to be more MySQL compatible.

Annotation improvements

Annotations permit the display of an event on all dashboards in PMM.  Users reported that passing more than one string to pmm-admin annotate would generate an error, so we updated the parsing logic to assume all strings passed during annotation creation generates a single annotation event.  Previously you needed to enclose your strings in quotes so that it would be parsed as a single string.

Issues in this release New Features & Improvements
  • PMM-2432 – Configurable MySQL Slow Log File Rotation
Bug fixes
  • PMM-1187 – Graphs breaks at tight resolution 
  • PMM-2362 – Explain is a part of query 
  • PMM-2399 – RPM for pmm-server is missing some files 
  • PMM-2407 – Menu items are not visible on PMM QAN dashboard 
  • PMM-2469 – Parsing of a valid my.cnf can break the mysqld_exporter 
  • PMM-2479 – PXC/Galera Cluster Overview dashboard: typo in metric names 
  • PMM-2484 – PXC/Galera Graphs display unpredictable results each time they are refreshed 
  • PMM-2503 – Wrong InnoDB Adaptive Hash Index Statistics 
  • PMM-2513 – QAN-agent always changes max_slowlog_size to 0 
  • PMM-2514 – pmm-admin annotate help – fix typos
  • PMM-2515 – pmm-admin annotate – more than 1 annotation 
How to get PMM

PMM is available for installation using three methods:

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

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

Percona Server for MongoDB 3.6.4-1.2 Is Now Available

Latest MySQL Performance Blog posts - May 23, 2018 - 10:04am

Percona announces the release of Percona Server for MongoDB 3.6.4-1.2 on May 23, 2018. Download the latest version from the Percona web site or the Percona Software Repositories.

Percona Server for MongoDB is an enhanced, open source, and highly-scalable database that is a fully-compatible, drop-in replacement for MongoDB 3.6 Community Edition. It supports MongoDB 3.6 protocols and drivers.

Percona Server for MongoDB extends MongoDB Community Edition functionality by including the Percona Memory Engine, as well as several enterprise-grade features. Percona Server for MongoDB requires no changes to MongoDB applications or code.

This release is based on MongoDB 3.6.4 and includes the following additional changes:

  • #PSMDB-205: mongod failed to initialize if audit filter was set to record Action type events specified with the $in expression.
  • #PSMDB-207: a premature initialization of the feature compatibility version in global parameters was fixed for the RocksDB storage engine.
  • #PSMDB-209: CentOS 6 and CentOS 7 RPM packages contained config file with a wrong link to the online Percona Memory Engine documentation.

Note: as mentioned in the Percona Server for MongoDB 3.6.3-1.1  Release Notes,  MongoRocks is deprecated in Percona Server for MongoDB 3.6.

The Percona Server for MongoDB 3.6.4-1.2 release notes are available in the official documentation.

The post Percona Server for MongoDB 3.6.4-1.2 Is Now Available appeared first on Percona Database Performance Blog.

PXC on Ubuntu 18.04 is missing Key Vault plugin. It's present on 16.04 distro

Lastest Forum Posts - May 23, 2018 - 8:01am
Hello,

I have found out that on Ubuntu 18.04, the package:
Code: percona-xtradb-cluster-server-5.7 is missing the Key Vault plugin binary Code: keyring_vault.so .

This happens installing either
Code: percona-release_0.1-6.bionic_all.deb or
Code: percona-release_0.1-5.bionic_all.deb The same packages on an Ubuntu 16.04 install said plugin.

If I go on "regular" Percona Server download wizard,

the suggested percona-server-server-5.7_5.7.21-21-3.bionic_amd64.deb

comes with that plugin.

Needless to say, if I extract Code: keyring_vault.so from that regular Percona Server package and copy it into the PXC plugins directory, when I try restarting the server I get all sorts of typical errors due to mismatching binary versions.

In fact, even apt updating the packages, the PXC mysqld version stays as follows:

Code: mysqld Ver 5.7.20-18-18 for debian-linux-gnu on x86_64 (Percona XtraDB Cluster (GPL), Release rel18, Revision e19a6b7, WSREP version 29.24, wsrep_29.24) whereas the regular Percona Server Package installs 5.7.21-21-3.


What can I do to get a functional key vault plugin in my PXC?

Deploy a MongoDB Replica Set with Transport Encryption (Part 2)

Latest MySQL Performance Blog posts - May 23, 2018 - 7:42am

In this article series, we will talk about the basic high availability architecture of a MongoDB: the MongoDB replica set.

  • Part 1 : We introduced basic replica set concepts, how it works and what its main features
  • Part 2 (this post): We’ll provide a step-by-step guide to configure a three-node replica set
  • Part 3: We’ll talk about how to configure transport encryption between the nodes

In part 1 we introduced and described the main features of a MongoDB replica set. In this post, we are going to present a step-by-step guide to deploy a basic and fully operational 3-nodes replica set. We’ll use just regular members, all with priority=1, no arbiter, no hidden or delayed nodes.

The environment

Our example environment is 3 virtual hosts with Ubuntu 16.04 LTS, although the configuration is the same with CentOS or other Linux distributions.

We have installed Percona Server for MongoDB on each node. Hostnames and IPs are:

  • psmdb1 : 192.168.56.101
  • psmdb2 : 192.168.56.102
  • psmdb3 : 192.168.56.103

It is not the goal of this post to provide installation details, but in case you need them you can follow this guide: https://www.percona.com/doc/percona-server-for-mongodb/LATEST/install/index.html MongoDB installation from the repository is very easy.

Connectivity

Once we have all the nodes with MongoDB installed, we just need to be sure that each one is accessible by all the others on port 27017, the default port.

Since our members are on the same network we can simply try to test the connectivity between each pair of nodes, connecting the mongo client from one node to each of the others.

psmdb1> mongo --host 192.168.56.102 --port 27017 psmdb1> mongo --host 192.168.56.103 --port 27017 psmdb2> mongo --host 192.168.56.101 --port 27017 psmdb2> mongo --host 192.168.56.103 --port 27017 psmdb3> mongo --host 192.168.56.101 --port 27017 psmdb3> mongo --host 192.168.56.102 --port 27017

If the mongo client is not able to connect, we need to check the network configuration, or to configure or disable the firewall.

Hostnames

Configuring the hostnames into our hosts is not mandatory for the replica set. In fact you can configure the replica set using just the IPs and it’s fine. But we need to define the hostnames because they will be very useful when we discuss how to configure internal encryption in Part 3.

We need to ensure that each member is accessible by way of resolvable DNS or hostnames.

Set up each node in the /etc/hosts file

root@psmdb1:~# cat /etc/hosts 127.0.0.1 localhost 192.168.56.101 psmdb1 192.168.56.102 psmdb2 192.168.56.103 psmdb3 Choose a name for the replica set

We are now close to finalizing the configuration.

Now we have to choose a name for the replica set. We need to choose one and put t on each member’s configuration file. Let’s say we decide to use rs-test.

Put the replica set name into /etc/mongod.conf (the MongoDB configuration file) on each host. Enter the following:

replication: replSetName: "rs-test"

Restart the server:

sudo service mongod restart

Remember to do this on all the nodes.

That’s all we need to do to configure the replication at its most basic. There are obviously other configuration parameters we could set, but maybe we’ll talk about them in another post when discussing more advanced features. For this basic deployment we can assume that all the default values are good enough.

Initiate replication

Now we need to connect to one of the nodes. It doesn’t matter which, just choose one of them and launch mongo shell to connect to the local mongod instance.

Then issue the rs.initiate() command to let the replica set know what all the members are.

mongo> rs.initiate( { ... _id: “rs-test”, ... members: [ ... { _id: 0, host: “psmdb1:27017” }, ... { _id: 1, host: “psmdb2:27017” }, ... { _id: 2, host: “psmdb3:27017” } ... ] })

After issuing the command, MongoDB initiates the replication process using the default configuration. A PRIMARY node is elected and all the documents will be created by now will be asynchronously replicated on the SECONDARY nodes.

We don’t need to do any more. The replica set is now working.

We can verify that the replication is working by taking a look at the mongo shell prompt. Once the replica set is up and running the prompt should be like this on the PRIMARY node:

rs-test:PRIMARY>

and like this on the SECONDARY nodes:

rs-test:SECONDARY>

MongoDB lets you know the replica role of the node that you are connected to.

A couple of useful commands

There are several commands to investigate and to do some administrative tasks on the replica set. Here are a couple of them.

To investigate the replica set configuration you can issue rs.conf() on any node

rs-test:PRIMARY> rs.conf() { "_id" : "rs-test", "version" : 68835, "protocolVersion" : NumberLong(1), "members" : [ { "_id" : 0, "host" : "psmdb1:27017", "arbiterOnly" : false, "buildIndexes" : true, "hidden" : false, "priority" : 1, "tags" : { }, "slaveDelay" : NumberLong(0), "votes" : 1 }, { "_id" : 1, "host" : "psmdb2:27017", "arbiterOnly" : false, "buildIndexes" : true, "hidden" : false, "priority" : 1, "tags" : { }, "slaveDelay" : NumberLong(0), "votes" : 1 }, { "_id" : 2, "host" : "psmdb3:27017", "arbiterOnly" : false, "buildIndexes" : true, "hidden" : false, "priority" : 1, "tags" : { }, "slaveDelay" : NumberLong(0), "votes" : 1 } ], "settings" : { "chainingAllowed" : true, "heartbeatIntervalMillis" : 2000, "heartbeatTimeoutSecs" : 10, "electionTimeoutMillis" : 10000, "catchUpTimeoutMillis" : 60000, "getLastErrorModes" : { }, "getLastErrorDefaults" : { "w" : 1, "wtimeout" : 0 }, "replicaSetId" : ObjectId("5aa2600d377adb63d28e7f0f") } }

We can see information about the configured nodes, whether arbiter or hidden, the priority, and other details regarding the heartbeat process.

To investigate the replica set status you can issue rs.status() on any node

rs-test:SECONDARY> rs.status() { "set" : "rs-test", "date" : ISODate("2018-05-14T10:16:05.228Z"), "myState" : 2, "term" : NumberLong(47), "syncingTo" : "psmdb3:27017", "heartbeatIntervalMillis" : NumberLong(2000), "optimes" : { "lastCommittedOpTime" : { "ts" : Timestamp(1526292954, 1), "t" : NumberLong(47) }, "appliedOpTime" : { "ts" : Timestamp(1526292964, 1), "t" : NumberLong(47) }, "durableOpTime" : { "ts" : Timestamp(1526292964, 1), "t" : NumberLong(47) } }, "members" : [ { "_id" : 0, "name" : "psmdb1:27017", "health" : 1, "state" : 2, "stateStr" : "SECONDARY", "uptime" : 392, "optime" : { "ts" : Timestamp(1526292964, 1), "t" : NumberLong(47) }, "optimeDate" : ISODate("2018-05-14T10:16:04Z"), "syncingTo" : "psmdb3:27017", "configVersion" : 68835, "self" : true }, { "_id" : 1, "name" : "psmdb2:27017", "health" : 1, "state" : 1, "stateStr" : "PRIMARY", "uptime" : 379, "optime" : { "ts" : Timestamp(1526292964, 1), "t" : NumberLong(47) }, "optimeDurable" : { "ts" : Timestamp(1526292964, 1), "t" : NumberLong(47) }, "optimeDate" : ISODate("2018-05-14T10:16:04Z"), "optimeDurableDate" : ISODate("2018-05-14T10:16:04Z"), "lastHeartbeat" : ISODate("2018-05-14T10:16:04.832Z"), "lastHeartbeatRecv" : ISODate("2018-05-14T10:16:03.318Z"), "pingMs" : NumberLong(0), "electionTime" : Timestamp(1526292592, 1), "electionDate" : ISODate("2018-05-14T10:09:52Z"), "configVersion" : 68835 }, { "_id" : 2, "name" : "psmdb3:27017", "health" : 1, "state" : 2, "stateStr" : "SECONDARY", "uptime" : 378, "optime" : { "ts" : Timestamp(1526292964, 1), "t" : NumberLong(47) }, "optimeDurable" : { "ts" : Timestamp(1526292964, 1), "t" : NumberLong(47) }, "optimeDate" : ISODate("2018-05-14T10:16:04Z"), "optimeDurableDate" : ISODate("2018-05-14T10:16:04Z"), "lastHeartbeat" : ISODate("2018-05-14T10:16:04.832Z"), "lastHeartbeatRecv" : ISODate("2018-05-14T10:16:04.822Z"), "pingMs" : NumberLong(0), "syncingTo" : "psmdb2:27017", "configVersion" : 68835 } ], "ok" : 1 }

Here we can see for example if nodes are reachable and are running, but in particular we can see the role they have at this moment: which is the PRIMARY and which are SECONDARY

Test replication

Finally, let’s try to test that the replication process is really working as expected.

Connect to the PRIMARY node and create a sample document:

rs-test:PRIMARY> use test switched to db test rs-test:PRIMARY> db.foo.insert( {name:"Bruce", surname:"Dickinson"} ) WriteResult({ "nInserted" : 1 }) rs-test:PRIMARY> db.foo.find().pretty() { "_id" : ObjectId("5ae05ac27e6680071caf94b7") "name" : "Bruce" "surname" : "Dickinson" }

Then connect to a SECONDARY node and look for the same document.

Remember that you can’t connect to the SECONDARY node to read the data. By default reads and writes are allowed only on the PRIMARY. So, if you want to read data on a SECONDARY node, you first need to issue the rs.slaveOK() command. If you don’t do this you will receive an error.

rs-test:SECONDARY> rs.slaveOK() rs-test:SECONDARY> show collections local foo rs-test:SECONDARY> db.foo.find().pretty() { "_id" : ObjectId("5ae05ac27e6680071caf94b7") "name" : "Bruce" "surname" : "Dickinson" }

As we can see, the SECONDARY node has replicated the creation of the collection foo and the inserted document.

This simple test demonstrates that the replication process is working as expected.

There are more sophisticated features to investigate the replica set, and for troubleshooting, but discussing them it’s not in the scope of this post.

In Part 3, we’ll show how to encrypt the internal replication process we have deployed so far.

Read the first post of this series: Deploy a MongoDB Replica Set with Transport Encryption

The post Deploy a MongoDB Replica Set with Transport Encryption (Part 2) appeared first on Percona Database Performance Blog.

SQL_SLAVE_SKIP_COUNTER=1 does nothing, when master=5.7,slave=5.6

Lastest Forum Posts - May 23, 2018 - 7:14am
Has behavior of SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; changed in Percona5.7 (when the MASTER is 5.7)?
- Or, is it not supported/possible to slave a Percona 5.6 server from a Percona 5.7 server due to binlog modifications/incompatibilities?
- I can upgrade the slave if that will fix this, but before I do, I wanted to understand what is happening here

We used to use GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; successfully to skip bad entries. I am confident it worked for binlog_format=MIXED(, although I have not re-tested, we've used this technique for years).

On a Percona5.6 slave of a Percona5.7 master, I recently tried to use this method, and here was the result:
2018-05-23 09:59:43 10396 [Note] 'SQL_SLAVE_SKIP_COUNTER=1' executed at relay_log_file='./server-relay-bin.000020', relay_log_pos='500859725', master_log_name='master-bin.007892', master_log_pos='500859548' and new position at relay_log_file='./server-relay-bin.000020', relay_log_pos='500859725', master_log_name='master-bin.007892', master_log_pos='500859548'

The slave server recognized it should skip once, and then it didn't advance either the relay log position or the master log position at all.

To get past the objectionable query, I had to: SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 2;

After doing this, I recorded the former and new master_log_pos, and ran mysqlbinlog to see what was skipped. It appeared to be one statement.

I suppose I can just script around this, and first try SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;, thereafter if the bin log_pos does not change, I can try setting SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 2;, but, this seems a little odd, especially given the prior method worked for years and seems well-documented online, and I haven't seen anyone mention this feature.

Thanks for any help!

MASTER Server version: 5.7.21-20-log Percona Server (GPL), Release 20, Revision ed217b06ca3
SLAVE Server version: 5.6.25-73.0-log Percona Server (GPL), Release 73.0, Revision 5ccddf8

‘Error: mysql conflicts with MySQL’ when install Percona-XtraDB-Cluster-server-56

Lastest Forum Posts - May 23, 2018 - 12:34am
I use yum to install Percona-XtraDB-Cluster-server-56 on centos5
```
Loading "installonlyn" plugin
Setting up Install Process
Setting up repositories
percona-release-i386 100% |=========================| 2.5 kB 00:00
C5.7-base 100% |=========================| 1.1 kB 00:00
percona-release-noarch 100% |=========================| 2.5 kB 00:00
Reading repository metadata in from local files
Parsing package install arguments
Resolving Dependencies
--> Populating transaction set with selected packages. Please wait.
---> Package Percona-XtraDB-Cluster-server-56.i686 1:5.6.15-25.5.759.rhel5 set to be updated
--> Running transaction check
--> Processing Dependency: Percona-XtraDB-Cluster-shared-56 for package: Percona-XtraDB-Cluster-server-56
--> Processing Dependency: Percona-XtraDB-Cluster-galera-25 for package: Percona-XtraDB-Cluster-server-56
--> Processing Dependency: perl-DBD-MySQL for package: Percona-XtraDB-Cluster-server-56
--> Processing Dependency: percona-xtrabackup >= 2.1.6 for package: Percona-XtraDB-Cluster-server-56
--> Processing Dependency: Percona-XtraDB-Cluster-client-56 for package: Percona-XtraDB-Cluster-server-56
--> Restarting Dependency Resolution with new changes.
--> Populating transaction set with selected packages. Please wait.
---> Package Percona-XtraDB-Cluster-galera-2.i386 0:2.12-1.2682.rhel5 set to be updated
---> Package percona-xtrabackup.i386 0:2.3.9-1.el5 set to be updated
---> Package Percona-XtraDB-Cluster-client-56.i686 1:5.6.15-25.5.759.rhel5 set to be updated
---> Package Percona-XtraDB-Cluster-shared-56.i686 1:5.6.15-25.5.759.rhel5 set to be updated
---> Package perl-DBD-MySQL.i386 0:3.0007-2.el5 set to be updated
--> Running transaction check
--> Processing Dependency: libmysqlclient.so.15(libmysqlclient_15) for package: perl-DBD-MySQL
--> Processing Dependency: libev.so.4 for package: percona-xtrabackup
--> Processing Dependency: libmysqlclient.so.15 for package: perl-DBD-MySQL
--> Restarting Dependency Resolution with new changes.
--> Populating transaction set with selected packages. Please wait.
---> Package mysql.i386 0:5.0.77-4.el5_6.6 set to be updated
---> Package libev.i386 0:4.15-1.el5.rf set to be updated
--> Running transaction check
--> Processing Conflict: mysql conflicts MySQL
--> Finished Dependency Resolution
Error: mysql conflicts with MySQL

```
But I have uninstalled the package about mysql
When I execute `rpm qa|grep -i mysql` there is no output

```
[root@localhost /]# rpm qa|grep -i mysql
[root@localhost /]#
```
How can I solve this problem?
Thank you!

Percona Toolkit 3.0.10 Is Now Available

Latest MySQL Performance Blog posts - May 22, 2018 - 1:32pm

Percona announces the release of Percona Toolkit 3.0.10 on May 22, 2018.

Percona Toolkit is a collection of advanced open source command-line tools, developed and used by the Percona technical staff, that are engineered to perform a variety of MySQL®, MongoDB® and system tasks that are too difficult or complex to perform manually. With over 1,000,000 downloads, Percona Toolkit supports Percona Server for MySQL, MySQL®, MariaDB®, Percona Server for MongoDB and MongoDB.

Percona Toolkit, like all Percona software, is free and open source. You can download packages from the website or install from official repositories.

This release includes the following changes:

New Features:
  • PT-131: pt-table-checksum disables the QRT plugin
    The Query Response Time Plugin provides a tool for analyzing information by counting and displaying the number of queries according to the length of time they took to execute. This feature enables a new flag --disable-qrt-plugin  that leverages Percona Server for MySQL’s new ability to disable QRT plugin at the session level. The advantage to enabling this Toolkit feature is that the QRT metrics are not impacted by the work that pt-table-checksum performs. This means that QRT metrics report only the work your Application is generating on MySQL, and not clouded by the activities of pt-table-checksum.
  • PT-118: pt-table-checksum reports the number of rows of difference between master and slave
    We’re adding support for pt-table-checksum to identify the number of row differences between master and slave. Previously you were able to see only the count of chunks that differed between hosts. This is helpful for situations where you believe you can tolerate some measure of row count drift between hosts, but want to be precise in understanding what that row count difference actually is.
Improvements
  • PT-1546: Improved support for MySQL 8 roles
  • PT-1543: The encrypted table status query causes high load over multiple minutes
    Users reported that listing encrypted table status can be very slow.  We’ve enabled this functionality via --list-encrypted-tables and set it to default of disabled.
  • PT-1536: Added info about encrypted tablespaces in pt-mysql-summary
    We’ve improved pt-mysql-summary to now include information about encrypted tablespaces.  This information is available by using --list-encrypted-tables .
Bug Fixes:
  • PT-1556: pt-table-checksum 3.0.9 does not change binlog_format to statement any more.

pt-show-grants has several known issues when working with MySQL 8 and roles, which Percona aims to address in subsequent Percona Toolkit releases: PT-1560PT-1559, and PT-1558

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

The post Percona Toolkit 3.0.10 Is Now Available appeared first on Percona Database Performance Blog.

ProxySQL 1.4.8 and Updated proxysql-admin Tool Now in the Percona Repository

Latest MySQL Performance Blog posts - May 22, 2018 - 12:34pm

ProxySQL 1.4.8, released by ProxySQL, is now available for download in the Percona Repository along with an updated version of Percona’s proxysql-admin tool.

ProxySQL is a high-performance proxy, currently for MySQL and its forks (like Percona Server for MySQL and MariaDB). It acts as an intermediary for client requests seeking resources from the database. René Cannaò created ProxySQL for DBAs as a means of solving complex replication topology issues.

The ProxySQL 1.4.8 source and binary packages available at https://percona.com/downloads/proxysql include ProxySQL Admin – a tool, developed by Percona to configure Percona XtraDB Cluster nodes into ProxySQL. Docker images for release 1.4.8 are available as well: https://hub.docker.com/r/percona/proxysql/. You can download the original ProxySQL from https://github.com/sysown/proxysql/releases.

This release fixes the following bugs in ProxySQL Admin:

Usability improvement:

  • PR #84: Now proxysql-status tool dumps host_priority and proxysql-admin.cnf. Also output format was changed.

Other improvements and bug fixes:

  • PR #66: --syncusers option now makes ProxySQL-admin to update the user’s password in ProxySQL database if there is any password difference between ProxySQL user and MySQL user.
  • PSQLADM-45: it was unclear from the help screen, that --config-file option requires an argument.
  • PSQLADM-48:  ${PROXYSQL_DATADIR}/${CLUSTER_NAME}_mode file was not created at ProxySQL-admin upgrade (1.4.5 or before to 1.4.6 onwards).
  • PSQLADM-52: The  proxysql_galera_checker script was not checking empty query rules.
  • PSQLADM-54: proxysql_node_monitor did not change OFFLINE_HARD status properly for the coming back online nodes.

ProxySQL is available under OpenSource license GPLv3.

The post ProxySQL 1.4.8 and Updated proxysql-admin Tool Now in the Percona Repository appeared first on Percona Database Performance Blog.

Upcoming Webinar Thursday, 5/24: What’s New in MongoDB 3.6

Latest MySQL Performance Blog posts - May 22, 2018 - 11:59am

Please join Percona’s Senior Support Engineer, Adamo Tonete as he presents What’s New in MongoDB 3.6 on Thursday, May 24th, 2018, at 12:30 PM PDT (UTC-7) / 3:30 PM EDT (UTC-4).

In this webinar, Adamo will walk though what’s new in MongoDB 3.6, including:

  • Change streams for building reactive, real-time applications
  • Retryable writes for always-on write availability
  • Schema validation with JSON Schema for new data governance controls
  • Fully expressive array updates that perform complex array manipulations in a single atomic update operation
  • New security controls
  • End-to-end compression to create efficient, distributed architectures

This webinar is a summary and follow up to several published blog posts on MongoDB 3.6. More information can be found here.

Download the guide to MongoDB 3.6

Register Now

 

Adamo Tonete, Senior Technical Services Engineer

Adamo joined Percona in 2015, after working as a MongoDB/MySQL Database Administrator for three years. As the main database member of a startup, he was responsible for suggesting the best architecture and data flows for a worldwide company in a 7/24 environment. Before that, he worked as a Microsoft SQL Server DBA in a large e-commerce company, mainly on performance tuning and automation. Adamo has almost eight years of experience working as a DBA and in the past three years he has moved to NoSQL technologies without giving up relational databases. He likes to play video games and to study everything that is related to engines. Adamo lives with his wife in São Paulo, Brazil.

Register for the webinar

The post Upcoming Webinar Thursday, 5/24: What’s New in MongoDB 3.6 appeared first on Percona Database Performance Blog.

innobackupex deprecation, option question

Lastest Forum Posts - May 21, 2018 - 11:17pm
Question 1.
Im studying xtrabackup for sharing to my team members.

In 23 version release note says innobackup deprecated and will be removed in next major release. (It guess the next major version means 24)
But still exists as symlink of xtrabackup written in C.
Will innobackupex go together forever?

I have to decide which tool is better to share to team members.


Question 2.
I think xtrabackup tool support '--no-lock' option but can't find in parameter manual.
(https://www.percona.com/doc/percona-...reference.html)
I thinks it works well..(?)
Actually may I use it?.


Thanks to make xtrabackup.

Slow performance between two similar servers neither had an index for query

Lastest Forum Posts - May 21, 2018 - 1:49pm
General question, I'm inherited MySQL and learning about it. My background is mostly with SQL Server and a little Oracle.

We have a query in Production and Pre-Production. (same dataset size)

- Query runs several seconds (5-7) in Prod
- Query runs in a few hundred ms in pre-prod
- Both are on SSD.
- The query is not using an index performs table scan on both.


In MS SQL there are table statistics that help with queries even if there is not an index when stats are updated will improve performance.
Running "ANALYZE TABLE" had no affect.

I did end up suggesting adding the appropriate index which solved the problem.

HOWEVER, why would it have been an issue in the first place, the query without an index ran fine for past year...no issues. No config changes in past year. (of course without the index it probably didn't run optimally- but didn't run as slow as several seconds)

I suppose the dataset could have grown significantly, but then when do table statistics get updated. Also are statistics only captured
for index columns how about non indexed columns?


Thanks for any insight.

Percona Server for MongoDB 3.2.20-3.11 Is Now Available

Latest MySQL Performance Blog posts - May 21, 2018 - 1:32pm

Percona announces the release of Percona Server for MongoDB 3.2.20-3.11 on May 21, 2018. Download the latest version from the Percona web site or the Percona Software Repositories.

Percona Server for MongoDB is an enhanced, open source, and highly-scalable database that is a fully-compatible, drop-in replacement for MongoDB 3.2 Community Edition. It supports MongoDB 3.2 protocols and drivers.

Percona Server for MongoDB extends MongoDB Community Edition functionality by including the Percona Memory Engine and MongoRocks storage engine, as well as several enterprise-grade features. It requires no changes to MongoDB applications or code.

This release is based on MongoDB 3.2.20 and does not include any additional changes.

The Percona Server for MongoDB 3.2.20-3.11 release notes are available in the official documentation.

The post Percona Server for MongoDB 3.2.20-3.11 Is Now Available appeared first on Percona Database Performance Blog.

Webinar Wed, 5/23: Troubleshooting MySQL Concurrency Issues with Load Testing Tools

Latest MySQL Performance Blog posts - May 21, 2018 - 11:41am

Please join Percona’s Principal Support Escalation Specialist, Sveta Smirnova, as she presents Troubleshooting MySQL Concurrency Issues with Load Testing Tools on Wednesday, May 23, 2018 at 11:00 AM PDT (UTC-7) / 2:00 PM EDT (UTC-4).

Register Now

 

Normally, we use benchmarking tools when we are developing applications. When applications are deployed, benchmarks tests are usually too late to help.

This webinar doesn’t cover actual benchmarks, but it does look at how you can use benchmarking tools for troubleshooting. When you need to repeat a situation caused by concurrent client execution, they can be your best option. These types of issues include all kinds of locking and performance issues, along with stalls and crashes.

In this webinar Sveta will cover some of the main tools she uses, such as (but not limited to) SysBench and mysqlslap. She will show how to use the tools’ standard options while working with specific custom problems, and how to script them to develop test cases that are as close to real life scenarios as possible.

Register for the webinar.

Sveta Smirnova, Principal Support Escalation Specialist

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 MySQL Bugs Analysis Support Group in MySQL AB-Sun-Oracle. She is the author of book “MySQL Troubleshooting” and JSON UDF functions for MySQL.

The post Webinar Wed, 5/23: Troubleshooting MySQL Concurrency Issues with Load Testing Tools appeared first on Percona Database Performance Blog.

xtrabackup version 2.4.11 having --stream issue

Lastest Forum Posts - May 21, 2018 - 7:31am
We recently upgraded our xtrabackup version from 2.4.8 to 2.4.11 and our --stream=xbstream option seems to be failing.
Following it the message we got /bin/xtrabackup: /usr/lib64/libstdc++.so.6: version `GLIBCXX_3.4.15' not found (required by /bin/xtrabackup)

Although there is no version of available on https://www.gnu.org/software/libc/

Percona Server for MySQL 5.5.60-38.12 Is Now Available

Lastest Forum Posts - May 21, 2018 - 1:19am
Perconaannounces the release of Percona Server for MySQL 5.5.60-38.12 on May 18, 2018. Based on MySQL 5.5.60, including all the bug fixes in it, Percona Server for MySQL 5.5.60-38.12 is now the current stable release in the 5.5 series.

Percona Server for MySQL is open-source and free. Downloads are available here and from the Percona Software Repositories.

Bugs Fixed
  • mysqldump utility with --innodb-optimize-keys option was incorrectly working with foreign keys pointing to the same table, producing invalid SQL statements. Bugs fixed #1125 and #3863.
  • A typo in plugin.cmake file prevented to compile plugins statically into the server. Bug fixed #3871(upstream #89766).
  • Using -DWITHOUT_<PLUGIN>=ON CMake variable to exclude a plugin from the build didn’t work for some plugins, including a number of storage engines. Bug fixed #3901.
  • A fix was introduced to remove GCC 8 compilation warnings for the Percona Server build. Bug fixed #3950.
  • A code clean-up was done to fix compilation warnings and errors specific for clang 6. Bug fixed #3893(upstream #90111).
  • Percona Server Debian packages description included reference to /etc/mysql/my.cnf file, which is not actually present in these packages. Bug fixed #2046.
  • A clean-up in Percona Server binlog-related code was made to avoid uninitialized memory comparison. Bug fixed #3925 (upstream #90238).
  • The temporary file I/O was not instrumented for Performance Schema. Bug fixed #3937 (upstream #90264). A key_block_size value was set automatically by the Improved MEMORY Storage Engine, which resulted in warnings when changing the engine type to InnoDB, and constantly growing key_block_size during alter operations. Bugs fixed #3936, #3940, and #3943.
Other bugs fixed: #3767 “Fix compilation warnings/errors with clang”, #3778 “5.5 Tree received Percona-TokuBackup submodule where it should not”, #3794 “MTR test main.percona_show_temp_tables_stress does not wait for events to start”, #3798 “MTR test innodb.percona_extended_innodb_status fails if InnoDB status contains unquoted special characters”, and #3926 “Potentially truncated bitmap file name in log_online_open_bitmap_file_read_only() (storage/innobase/log/log0online.cc)”.

Find the release notes for Percona Server for MySQL 5.5.60-38.12 in our online documentation. Report bugs in the Jira bug tracker.
Visit Percona Store


General Inquiries

For general inquiries, please send us your question and someone will contact you.