Buy Percona ServicesBuy Now!

PMM Sever Amazon EC2 Image

Lastest Forum Posts - August 31, 2017 - 3:44am
Hi,

I've been trying to set up the PMM Server as supplied in the Amazon images at https://www.percona.com/doc/percona-...erver/ami.html (I'm using EU (London)).

This fires up just fine, but I want to SSH into the server to diagnose what looks to be a firewall issue.

As normal, I've provisioned the server giving it RSA keypair via the Amazon EC2 web UI. The thing is, I cannot SSH into the server using this key. It may be because I don't know the default SSH username this key will refer to. I've tried root and ec2-user.

Does anyone know the SSH username that's configured in this image?

Thanks,

Marc

Percona Server for MySQL 5.6.37-82.2 Is Now Available

Lastest Forum Posts - August 31, 2017 - 2:19am
Percona announces the release of Percona Server for MySQL 5.6.37-82.2 on August 25, 2017. Download the latest version from the Percona web site or the Percona Software Repositories. You can also run Docker containers from the images in the Docker Hub repository.

Based on MySQL 5.6.37, and including all the bug fixes in it, Percona Server for MySQL 5.6.37-82.2 is now the current GA release in the Percona Server for MySQL 5.6 series. Percona Server for MySQL is open-source and free – this is the latest release of our enhanced, drop-in replacement for MySQL. Complete details of this release are available in the 5.6.37-82.2 milestone on Launchpad.

NOTE: Red Hat Enterprise Linux 5 (including CentOS 5 and other derivatives), Ubuntu 12.04 and older versions are no longer supported by Percona software. The reason for this is that these platforms reached end of life, will not receive updates and are not recommended for use in production.

Bugs Fixed
  • #1703105: Fixed overwriting of error log on server startup.
  • #1705729: Fixed the postinst script to correctly locate the datadir.
  • #1709834: Fixed the mysqld_safe script to correctly locate the basedir.
  • Other fixes: #1706262
TokuDB Changes
  • TDB-72: Fixed issue when renaming a table with non-alphanumeric characters in its name.
Platform Support
  • Stopped providing packages for RHEL 5 (CentOS 5) and Ubuntu 12.04.
Release notes for Percona Server for MySQL 5.6.37-82.2 are available in the online documentation. Please report any bugs on the launchpad bug tracker.

Percona Server for MySQL 5.5.57-38.9 Is Now Available

Lastest Forum Posts - August 31, 2017 - 2:18am
Perconaannounces the release of Percona Server for MySQL 5.5.57-38.9 on August 23, 2017. Based on MySQL 5.5.57, including all of its bug fixes, Percona Server for MySQL 5.5.57-38.9 is now the current stable release in the 5.5 series.

Percona Server for MySQL is open-source and free. You can find release details in the 5.5.57-38.9 milestone on Launchpad. Downloads are available here and from the Percona Software Repositories.

NOTE: Red Hat Enterprise Linux 5 (including CentOS 5 and other derivatives), Ubuntu 12.04 and older versions are no longer supported by Percona software. The reason for this is that these platforms reached the end of life, will not receive updates and are not recommended for use in production.

New Features
  • #1702903: Added support of OpenSSL 1.1.
Platform Support
  • Added support and packages for Debian 9 (stretch). Covers only the amd64 architecture.
  • Stopped providing packages for RHEL 5 (CentOS 5) and Ubuntu 12.04.
Bugs Fixed
  • #1622985: Downgraded diagnostic severity from warning to normal note for successful doublewrite buffer recovery.
  • #1661488: Fixed crash of debug server build when two clients connected, one of them enabled userstat and ran either FLUSH CLIENT_STATISTICS or FLUSH USER_STATISTICS, and then both clients exited.
  • #1673656: Added support for wildcards and Subject Alternative Names (SAN) in SSL certificates for --ssl-verify-server-cert. For more information, see the compatibility matrix at the end of this post.
  • #1705729: Fixed the postinst script to correctly locate the datadir.
  • #1709834: Fixed the mysqld_safe script to correctly locate the basedir.
  • Minor fixes: #1160986, #1684601, #1689998, #1690012.

Nested Data Structures in ClickHouse

Latest MySQL Performance Blog posts - August 30, 2017 - 11:21am

In this blog post, we’ll look at nested data structures in ClickHouse and how this can be used with PMM to look at queries.

Nested structures are not common in Relational Database Management Systems. Usually, it’s just flat tables. Sometimes it would be convenient to store unstructured information in structured databases.

We are working to adapt ClickHouse as a long term storage for Percona Monitoring and Management (PMM), and particularly to store detailed information about queries. One of the problems we are trying to solve is to count the different errors that cause a particular query to fail.

For example, for date 2017-08-17 the query:

"SELECT foo FROM bar WHERE id=?"

was executed 1000 times. 25 times it failed with error code “1212”, and eight times it failed with error code “1250”. Of course, the traditional way to store this in relational data would be to have a table "Date, QueryID, ErrorCode, ErrorCnt" and then perform a JOIN to this table. Unfortunately, columnar databases don’t perform well with multiple joins, and often the recommendation is to have de-normalized tables.

We can create a column for each possible ErrorCode, but this is not an optimal solution. There could be thousands of them, and most of the time they would be empty.

In this case, ClickHouse proposes Nested data structures. For our case, these can be defined as:

CREATE TABLE queries ( Period Date, QueryID UInt32, Fingerprint String, Errors Nested ( ErrorCode String, ErrorCnt UInt32 ) )Engine=MergeTree(Period,QueryID,8192);

This solution has obvious questions: How do we insert data into this table? How do we extract it?

Let’s start with INSERT. Insert can look like:

INSERT INTO queries VALUES ('2017-08-17',5,'SELECT foo FROM bar WHERE id=?',['1220','1230','1212'],[5,6,2])

which means that the inserted query during 2017-08-17 gave error 1220 five times, error 1230 six times and error 1212 two times.

Now, during a different date, it might produce different errors:

INSERT INTO queries VALUES ('2017-08-18',5,'SELECT foo FROM bar WHERE id=?',['1220','1240','1258'],[3,2,1])

Let’s take a look at ways to SELECT data. A very basic SELECT:

SELECT * FROM queries ┌─────Period─┬─QueryID─┬─Fingerprint─┬─Errors.ErrorCode───────┬─Errors.ErrorCnt─┐ │ 2017-08-17 │ 5 │ SELECT foo │ ['1220','1230','1212'] │ [5,6,2] │ │ 2017-08-18 │ 5 │ SELECT foo │ ['1220','1240','1260'] │ [3,16,12] │ └────────────┴─────────┴─────────────┴────────────────────────┴─────────────────┘

If we want to use a more familiar tabular output, we can use the ARRAY JOIN extension:

SELECT * FROM queries ARRAY JOIN Errors ┌─────Period─┬─QueryID─┬─Fingerprint─┬─Errors.ErrorCode─┬─Errors.ErrorCnt─┐ │ 2017-08-17 │ 5 │ SELECT foo │ 1220 │ 5 │ │ 2017-08-17 │ 5 │ SELECT foo │ 1230 │ 6 │ │ 2017-08-17 │ 5 │ SELECT foo │ 1212 │ 2 │ │ 2017-08-18 │ 5 │ SELECT foo │ 1220 │ 3 │ │ 2017-08-18 │ 5 │ SELECT foo │ 1240 │ 16 │ │ 2017-08-18 │ 5 │ SELECT foo │ 1260 │ 12 │ └────────────┴─────────┴─────────────┴──────────────────┴─────────────────┘

However, usually we want to see the aggregation over multiple periods, which can be done with traditional aggregation functions:

SELECT QueryID, Errors.ErrorCode, SUM(Errors.ErrorCnt) FROM queries ARRAY JOIN Errors GROUP BY QueryID, Errors.ErrorCode ┌─QueryID─┬─Errors.ErrorCode─┬─SUM(Errors.ErrorCnt)─┐ │ 5 │ 1212 │ 2 │ │ 5 │ 1230 │ 6 │ │ 5 │ 1260 │ 12 │ │ 5 │ 1240 │ 16 │ │ 5 │ 1220 │ 8 │ └─────────┴──────────────────┴──────────────────────┘

If we want to get really creative and return only one row per QueryID, we can do that as well:

SELECT QueryID, groupArray((ecode, cnt)) FROM ( SELECT QueryID, ecode, sum(ecnt) AS cnt FROM queries ARRAY JOIN Errors.ErrorCode AS ecode, Errors.ErrorCnt AS ecnt GROUP BY QueryID, ecode ) GROUP BY QueryID ┌─QueryID─┬─groupArray(tuple(ecode, cnt))──────────────────────────────┐ │ 5 │ [('1230',6),('1212',2),('1260',12),('1220',8),('1240',16)] │ └─────────┴────────────────────────────────────────────────────────────┘

Conclusion

ClickHouse provides flexible ways to store data in a less structured manner and variety of functions to extract and aggregate it – despite being a columnar database.

Happy data warehousing!

percona server 5.5.33 response very slow and many error in mysql error file

Lastest Forum Posts - August 30, 2017 - 4:59am
my database env:

Code: MySQL Server | Percona-Server-5.5.33-rel31.1 # Percona Toolkit System Summary Report ###################### Date | 2017-08-30 11:46:02 UTC (local TZ: CST +0800) Hostname | database9 Uptime | 370 days, 2:29, 2 users, load average: 0.08, 0.12, 0.16 System | Dell Inc.; PowerEdge R630; vNot Specified (Rack Mount Chassis) Service Tag | FTD5DF2 Platform | Linux Release | CentOS release 6.8 (Final) Kernel | 2.6.32-642.4.2.el6.x86_64 Architecture | CPU = 64-bit, OS = 64-bit Threading | NPTL 2.12 SELinux | Disabled Virtualized | No virtualization detected # Processor ################################################## Processors | physical = 2, cores = 20, virtual = 40, hyperthreading = yes Speeds | 40x2199.942 Models | 40xIntel(R) Xeon(R) CPU E5-2630 v4 @ 2.20GHz Caches | 40x25600 KB # Memory ################################################## ### Total | 125.9G
last sunday, one of my database response to application suddenly very slow, and the mysql thread running and cpu load is very high, such as:

HTML Code: top - 21:16:17 up 367 days, 4:00, 0 users, load average: 906.18, 623.71, 300. Tasks: 1149 total, 3 running, 1146 sleeping, 0 stopped, 0 zombie Cpu(s): 0.5%us, 0.3%sy, 0.0%ni, 99.2%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st Mem: 132042600k total, 131641800k used, 400800k free, 0k buffers Swap: 41929644k total, 3606772k used, 38322872k free, 24683400k cached PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 38391 mysql 20 0 111g 99g 5636 D 3403.4 78.8 147443:28 mysqld TS 1503839784.028255754 2017-08-27 21:16:24 MemTotal: 132042600 kB MemFree: 435984 kB Buffers: 0 kB Cached: 24602272 kB SwapCached: 383772 kB
and have many repeate message in error log, such as:
HTML Code: Aug 27 21:00:00 database9 mysqld-3309: Memory status: Aug 27 21:00:00 database9 mysqld-3309: Non-mmapped space allocated from system: 0 Aug 27 21:00:00 database9 mysqld-3309: Number of free chunks:#011#011#011 1 Aug 27 21:00:00 database9 mysqld-3309: Number of fastbin blocks:#011#011 0 Aug 27 21:00:00 database9 mysqld-3309: Number of mmapped regions:#011#011 0 Aug 27 21:00:00 database9 mysqld-3309: Space in mmapped regions:#011#011 0 Aug 27 21:00:00 database9 mysqld-3309: Maximum total allocated space:#011#011 0 Aug 27 21:00:00 database9 mysqld-3309: Space available in freed fastbin blocks: 0 Aug 27 21:00:00 database9 mysqld-3309: Total allocated space:#011#011#011 0 Aug 27 21:00:00 database9 mysqld-3309: Total free space:#011#011#011 0 Aug 27 21:00:00 database9 mysqld-3309: Top-most, releasable space:#011#011 0 Aug 27 21:00:00 database9 mysqld-3309: Estimated memory (with thread stack): 16515072 Aug 27 21:00:00 database9 mysqld-3309: Events status: Aug 27 21:00:00 database9 mysqld-3309: LLA = Last Locked At LUA = Last Unlocked At Aug 27 21:00:00 database9 mysqld-3309: WOC = Waiting On Condition DL = Data Locked Aug 27 21:00:00 database9 mysqld-3309: Aug 27 21:00:00 database9 mysqld-3309: Event scheduler status: Aug 27 21:00:00 database9 mysqld-3309: State : INITIALIZED Aug 27 21:00:00 database9 mysqld-3309: Thread id : 0 Aug 27 21:00:00 database9 mysqld-3309: LLA : n/a:0 Aug 27 21:00:00 database9 mysqld-3309: LUA : n/a:0 Aug 27 21:00:00 database9 mysqld-3309: WOC : NO Aug 27 21:00:00 database9 mysqld-3309: Workers : 0 Aug 27 21:00:00 database9 mysqld-3309: Executed : 0 Aug 27 21:00:00 database9 mysqld-3309: Data locked: NO Aug 27 21:00:00 database9 mysqld-3309: Aug 27 21:00:00 database9 mysqld-3309: Event queue status: Aug 27 21:00:00 database9 mysqld-3309: Element count : 0 Aug 27 21:00:00 database9 mysqld-3309: Data locked : NO Aug 27 21:00:00 database9 mysqld-3309: Attempting lock : NO Aug 27 21:00:00 database9 mysqld-3309: LLA : init_queue:139 Aug 27 21:00:00 database9 mysqld-3309: LUA : init_queue:149 Aug 27 21:00:00 database9 mysqld-3309: WOC : NO Aug 27 21:00:00 database9 mysqld-3309: Next activation : never Aug 27 21:00:00 database9 mysqld-3309: Aug 27 21:00:00 database9 mysqld-3309: Status information: Aug 27 21:00:00 database9 mysqld-3309: Aug 27 21:00:00 database9 mysqld-3309: Current dir: /web/mysql/node3309/data/ Aug 27 21:00:00 database9 mysqld-3309: Running threads: 61 Stack size: 262144 Aug 27 21:00:00 database9 mysqld-3309: Current locks: Aug 27 21:00:00 database9 mysqld-3309: lock: 0x7fcbe4c41e38: Aug 27 21:00:00 database9 mysqld-3309: Aug 27 21:00:00 database9 mysqld-3309: lock: 0x7fcc35bf82c0: read Aug 27 21:00:00 database9 mysqld-3309: read : 0x7fcc01689398 (29404920:2); Aug 27 21:00:00 database9 mysqld-3309: Aug 27 21:00:00 database9 mysqld-3309: lock: 0x7fcc35bf8180: read Aug 27 21:00:00 database9 mysqld-3309: read : 0x7fcc01687398 (29404920:2); Aug 27 21:00:00 database9 mysqld-3309: lock: 0x7fcc35bf7f00: read Aug 27 21:00:00 database9 mysqld-3309: read : 0x7fcd1bcc8398 (29404920:2); Aug 27 21:00:00 database9 mysqld-3309: Aug 27 21:00:00 database9 mysqld-3309: lock: 0x7fcc35bf7dc0: read Aug 27 21:00:00 database9 mysqld-3309: read : 0x7fcc017b2398 (29404920:2); it seems that mysql server receives many SIGHUP, but there is no operate to MySQL server by admin or application, I have no idea for this error message, thanks a lot for everyone help.
Visit Percona Store


General Inquiries

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