Buy Percona ServicesBuy Now!

pmm-server not collecting mysql metrics from client

Lastest Forum Posts - June 10, 2018 - 11:44pm
Hello,

I have installed pmm-server on RHEL 7.4 through docker and pmm-clients in two RHEL 6.6 servers.
I am able to collect OS metrics from clients, but not able to collect mysql metrics from clients.
I have telnet from the pmm-server to clients and from clients to pmm server.

pmm-admin list output
01:11:20 # pmm-admin list
pmm-admin 1.11.0

PMM Server | [server_ip]
Client Name | [client]
Client Address | [client_ip]
Service Manager | linux-upstart

-------------- ------------ ----------- -------- ----------------------------- --------------------------------------------------------------------------------------
SERVICE TYPE NAME LOCAL PORT RUNNING DATA SOURCE OPTIONS
-------------- ------------ ----------- -------- ----------------------------- --------------------------------------------------------------------------------------
mysql:queries [server] - YES root:***@tcp(127.0.0.1:3306) query_source=slowlog, query_examples=true, slow_log_rotation=true, retain_slow_logs=1
linux:metrics [server] 42000 YES -
mysql:metrics [server] 42002 YES root:***@tcp(127.0.0.1:3306) tablestats=OFF

pmm-admin check-network output

01:14:24 # pmm-admin check-network
PMM Network Status

Server Address | [server]:80
Client Address | [server]

* System Time
PMM Server | 2018-06-11 06:16:23 +0000 GMT
PMM Client | 2018-06-11 01:16:29 -0500 CDT
PMM Client to PMM Server Time Drift | OK

* Connection: Client --> Server
-------------------- -------
SERVER SERVICE STATUS
-------------------- -------
Consul API OK
Prometheus API OK
Query Analytics API OK

Connection duration | 712.884µs
Request duration | 6.964205ms
Full round trip | 7.677089ms


* Connection: Client <-- Server
-------------- ------------ ----------------- ------- ---------- ---------
SERVICE TYPE NAME REMOTE ENDPOINT STATUS HTTPS/TLS PASSWORD
-------------- ------------ ----------------- ------- ---------- ---------
linux:metrics [server] [server]:42000 OK YES -
mysql:metrics [server] [server]:42002 DOWN YES -

When an endpoint is down it may indicate that the corresponding service is stopped (run 'pmm-admin list' to verify).
If it's running, check out the logs /var/log/pmm-*.log

When all endpoints are down but 'pmm-admin list' shows they are up and no errors in the logs,
check the firewall settings whether this system allows incoming connections from server to addressort in question.

Also you can check the endpoint status by the URL: http://[server]/prometheus/targets


output of /var/log/pmm-mysql-metrics-42002.log

time="2018-06-08T11:56:02-05:00" level=info msg="Starting mysqld_exporter (version=, branch=, revision=)" source="mysqld_exporter.go:292"
time="2018-06-08T11:56:02-05:00" level=info msg="Build context (go=go1.9.4, user=, date=)" source="mysqld_exporter.go:293"
time="2018-06-08T11:56:02-05:00" level=info msg="HTTPS/TLS is enabled" source="mysqld_exporter.go:362"
time="2018-06-08T11:56:02-05:00" level=info msg="Enabled High Resolution scrapers:" source="mysqld_exporter.go:376"
time="2018-06-08T11:56:02-05:00" level=info msg=" --collect.info_schema.innodb_metrics" source="mysqld_exporter.go:378"
time="2018-06-08T11:56:02-05:00" level=info msg=" --collect.global_status" source="mysqld_exporter.go:378"
time="2018-06-08T11:56:02-05:00" level=info msg="Enabled Medium Resolution scrapers:" source="mysqld_exporter.go:382"
time="2018-06-08T11:56:02-05:00" level=info msg=" --collect.info_schema.processlist" source="mysqld_exporter.go:384"
time="2018-06-08T11:56:02-05:00" level=info msg=" --collect.slave_status" source="mysqld_exporter.go:384"
time="2018-06-08T11:56:02-05:00" level=info msg=" --collect.perf_schema.eventswaits" source="mysqld_exporter.go:384"
time="2018-06-08T11:56:02-05:00" level=info msg=" --collect.perf_schema.file_events" source="mysqld_exporter.go:384"
time="2018-06-08T11:56:02-05:00" level=info msg=" --collect.info_schema.query_response_time" source="mysqld_exporter.go:384"
time="2018-06-08T11:56:02-05:00" level=info msg="Enabled Low Resolution scrapers:" source="mysqld_exporter.go:388"
time="2018-06-08T11:56:02-05:00" level=info msg=" --collect.global_variables" source="mysqld_exporter.go:390"
time="2018-06-08T11:56:02-05:00" level=info msg=" --collect.binlog_size" source="mysqld_exporter.go:390"
time="2018-06-08T11:56:02-05:00" level=info msg=" --collect.info_schema.userstats" source="mysqld_exporter.go:390"
time="2018-06-08T11:56:02-05:00" level=info msg="Listening on [server]:42002" source="mysqld_exporter.go:399"
time="2018-06-08T11:56:33-05:00" level=info msg="Starting mysqld_exporter (version=, branch=, revision=)" source="mysqld_exporter.go:292"
time="2018-06-08T11:56:33-05:00" level=info msg="Build context (go=go1.9.4, user=, date=)" source="mysqld_exporter.go:293"
time="2018-06-08T11:56:33-05:00" level=info msg="HTTPS/TLS is enabled" source="mysqld_exporter.go:362"
time="2018-06-08T11:56:33-05:00" level=info msg="Enabled High Resolution scrapers:" source="mysqld_exporter.go:376"
time="2018-06-08T11:56:33-05:00" level=info msg=" --collect.info_schema.innodb_metrics" source="mysqld_exporter.go:378"
time="2018-06-08T11:56:33-05:00" level=info msg=" --collect.global_status" source="mysqld_exporter.go:378"
time="2018-06-08T11:56:33-05:00" level=info msg="Enabled Medium Resolution scrapers:" source="mysqld_exporter.go:382"
time="2018-06-08T11:56:33-05:00" level=info msg=" --collect.info_schema.processlist" source="mysqld_exporter.go:384"
time="2018-06-08T11:56:33-05:00" level=info msg=" --collect.slave_status" source="mysqld_exporter.go:384"
time="2018-06-08T11:56:33-05:00" level=info msg=" --collect.perf_schema.eventswaits" source="mysqld_exporter.go:384"
time="2018-06-08T11:56:33-05:00" level=info msg=" --collect.perf_schema.file_events" source="mysqld_exporter.go:384"
time="2018-06-08T11:56:33-05:00" level=info msg=" --collect.info_schema.query_response_time" source="mysqld_exporter.go:384"
time="2018-06-08T11:56:33-05:00" level=info msg="Enabled Low Resolution scrapers:" source="mysqld_exporter.go:388"
time="2018-06-08T11:56:33-05:00" level=info msg=" --collect.global_variables" source="mysqld_exporter.go:390"
time="2018-06-08T11:56:33-05:00" level=info msg=" --collect.binlog_size" source="mysqld_exporter.go:390"
time="2018-06-08T11:56:33-05:00" level=info msg=" --collect.info_schema.userstats" source="mysqld_exporter.go:390"
time="2018-06-08T11:56:33-05:00" level=info msg="Listening on [server]:42002" source="mysqld_exporter.go:399"
2018/06/08 11:58:19 http: TLS handshake error from [server]:52196: tls: first record does not look like a TLS handshake
2018/06/08 12:20:33 http: Accept error: accept tcp [server]:42002: accept4: too many open files; retrying in 5ms
2018/06/08 12:20:33 http: Accept error: accept tcp [server]:42002: accept4: too many open files; retrying in 10ms
2018/06/08 12:20:33 http: Accept error: accept tcp [server]:42002: accept4: too many open files; retrying in 20ms
2018/06/08 12:20:33 http: Accept error: accept tcp [server]:42002: accept4: too many open files; retrying in 40ms
2018/06/08 12:20:33 http: Accept error: accept tcp [server]:42002: accept4: too many open files; retrying in 80ms
2018/06/08 12:20:33 http: Accept error: accept tcp [server]:42002: accept4: too many open files; retrying in 160ms
2018/06/08 12:20:33 http: Accept error: accept tcp [server]:42002: accept4: too many open files; retrying in 320ms

In prometheus dashboard for mysql metrics status is down.
Telnet is working fine and OS metrics is working, only mysql metrics it is giving this error.

Please help me in solving this.



crash of 2 nodes

Lastest Forum Posts - June 10, 2018 - 3:45pm
Hello,

We are using a percona cluster with 3 nodes and today at the same time 2 nodes go down.

In the log I only see that they is an active transaction going in ROLL BACK and after a report about the crash

I have attached the log.

any idea ?

BR

Laurent



pt-online-schema-change error: Error creating new table ... duplicate key

Lastest Forum Posts - June 6, 2018 - 12:14pm
When using pt-online-schema-change 3.0.10 on a table that has previously been altered using the same tool, we get the "errno: 121 Duplicate key on write or update" error.
Googling & your bug tracker lead me to https://jira.percona.com/browse/PT-394, but this issue is marked as fixed while it appears that it still exists.

I found an online blog-post detailing the issue and the fix, but I'm very uncomfortable with patching the software myself.

Is there any progress or news about the issue, or do I need to assume the tool works as intended and we need to find another workaround?

Deadlock with &amp;quot;single-write&amp;quot; node!

Lastest Forum Posts - June 6, 2018 - 10:31am

Cluster: 3-nodes at 1 server(no LAN/WAN lag)
OS: Debian Stretch
Mysql version: 5.7.21-20 (xtradb-cluster)
Average qps: 50q/s


Using this script for testing cluster(php7.0 + pdo plugin):
https://pastebin.com/05nvKtb9

It creates table and then just use random queries like "INSERT", "UPDATE", "DELETE". :

Code: CREATE TABLE IF NOT EXISTS test ( id int(10) UNSIGNED NOT NULL AUTO_INCREMENT, field_int int(10) NOT NULL, field_float float NOT NULL, field_varchar varchar(100) NOT NULL, field_text longtext NOT NULL, field_datetime datetime NOT NULL, PRIMARY KEY (id) ) ENGINE = INNODB CHARACTER SET utf8 COLLATE utf8_unicode_ci;

When running one copy of script on singe mysql, percona server, mariadb - all is ok
When running multiple copies of script(parallel): on singe mysql, percona server, mariadb - all is ok

When running one copy of script on pxc and to one single node - all is ok
When running multiple multiple copies of script(parallel) on pxc and more and they connect to different nodes - deadlock on INSERT or UPDATE. (no problem we can use "single-node write mode", but)​​
When running multiple multiple copies of script on pxc and more and they connect to one node(!) - deadlock on INSERT or UPDATE.


PXC can't even hold multiple parallel "write" queries even on single node?


Tested with different options:
wsrep_retry_autocommit - from 0 to 10
wsrep_sync_wait - from 0 to 7 (7 is "helping", but speed is greatly reduced, so I think it just harder to emulate deadlock)

Cluster 3 nodes but wsrep_cluster_size = 1

Lastest Forum Posts - June 5, 2018 - 7:43am
Dear community,

I did the past year maybe 2 install of percona but since may i'm not able to perform an installation who works..
I already tried everything I could but i'm still blocked..

Maybe some of you can help me ?

I have followed the official doc

Here is what I have and tryed:
3 Ubuntu 16.04 freshly installed
SQL-NODE01 192.168.1.101/24
SQL-NODE02 192.168.1.102/24
SQL-NODE03 192.168.1.103/24
Every server are able to communicate to each other

Apparmor services stopped (Also tried with the services running or removed it but same conclusion)
Code: iptables --append INPUT --in-interface ens18 --protocol tcp --match tcp --dport 3306 --source 192.168.1.0/24 --jump ACCEPT iptables --append INPUT --in-interface ens18 --protocol tcp --match tcp --dport 4567 --source 192.168.1.0/24 --jump ACCEPT iptables --append INPUT --in-interface ens18 --protocol tcp --match tcp --dport 4568 --source 192.168.1.0/24 --jump ACCEPT iptables --append INPUT --in-interface ens18 --protocol tcp --match tcp --dport 4444 --source 192.168.1.0/24 --jump ACCEPT iptables --append INPUT --in-interface ens18 --protocol udp --match udp --dport 4567 --source 192.168.1.0/24 --jump ACCEPT Repo configured as described here : https://www.percona.com/doc/percona-...fig/index.html
Right after "apt-get install percona-xtradb-cluster-57"

The installation goes well, I stop the mysql services on the 3 nodes "sudo service mysql stop"

Here is my my.cnf for the NODE01:

Code: [mysqld] wsrep_provider=/usr/lib/libgalera_smm.so wsrep_cluster_name=db-cluster wsrep_cluster_address=gcomm://192.168.1.101,192.168.1.102,192.168.1.103 wsrep_node_name=SQL-NODE01 wsrep_node_address=192.168.1.101 wsrep_sst_method=xtrabackup-v2 wsrep_sst_auth=sstuser:passw0rd pxc_strict_mode=ENFORCING binlog_format=ROW default_storage_engine=InnoD innodb_autoinc_lock_mode=2 # # The Percona XtraDB Cluster 5.7 configuration file. # # # * IMPORTANT: Additional settings that can override those from this file! # The files must end with '.cnf', otherwise they'll be ignored. # Please make any edits and changes to the appropriate sectional files # included below. # !includedir /etc/mysql/conf.d/ !includedir /etc/mysql/percona-xtradb-cluster.conf.d/ Time to bootstrap the first node "/etc/init.d/mysql bootstrap-pxc"
Everything seems well except the error of PID on the syslog :

Code: Jun 5 15:33:03 SQL-NODE01 systemd[1]: Stopping LSB: AppArmor initialization... Jun 5 15:33:03 SQL-NODE01 apparmor[2107]: * Clearing AppArmor profiles cache Jun 5 15:33:03 SQL-NODE01 apparmor[2107]: ...done. Jun 5 15:33:03 SQL-NODE01 apparmor[2107]: All profile caches have been cleared, but no profiles have been unloaded. Jun 5 15:33:03 SQL-NODE01 apparmor[2107]: Unloading profiles will leave already running processes permanently Jun 5 15:33:03 SQL-NODE01 apparmor[2107]: unconfined, which can lead to unexpected situations. Jun 5 15:33:03 SQL-NODE01 apparmor[2107]: To set a process to complain mode, use the command line tool Jun 5 15:33:03 SQL-NODE01 apparmor[2107]: 'aa-complain'. To really tear down all profiles, run the init script Jun 5 15:33:03 SQL-NODE01 apparmor[2107]: with the 'teardown' option." Jun 5 15:33:03 SQL-NODE01 systemd[1]: Stopped LSB: AppArmor initialization. Jun 5 15:37:04 SQL-NODE01 systemd[1]: Starting Cleanup of Temporary Directories... Jun 5 15:37:04 SQL-NODE01 systemd-tmpfiles[3052]: [/usr/lib/tmpfiles.d/var.conf:14] Duplicate line for path "/var/log", ignoring. Jun 5 15:37:10 SQL-NODE01 systemd[1]: Started Cleanup of Temporary Directories. Jun 5 15:38:54 SQL-NODE01 systemd[1]: Reloading. Jun 5 15:38:54 SQL-NODE01 systemd[1]: Started ACPI event daemon. Jun 5 15:42:37 SQL-NODE01 systemd[1]: Reloading. Jun 5 15:42:37 SQL-NODE01 systemd[1]: Started ACPI event daemon. Jun 5 15:42:37 SQL-NODE01 systemd[1]: Reloading. Jun 5 15:42:37 SQL-NODE01 systemd[1]: Started ACPI event daemon. Jun 5 15:42:37 SQL-NODE01 systemd[1]: Starting LSB: Start and stop the mysql (Percona XtraDB Cluster)daemon... Jun 5 15:42:37 SQL-NODE01 mysql[4312]: * Starting MySQL (Percona XtraDB Cluster) database server mysqld Jun 5 15:42:37 SQL-NODE01 /etc/init.d/mysql[4357]: MySQL PID not found, pid_file detected/guessed: /var/run/mysqld/mysqld.pid Jun 5 15:42:43 SQL-NODE01 mysql[4312]: ...done. Jun 5 15:42:43 SQL-NODE01 systemd[1]: Started LSB: Start and stop the mysql (Percona XtraDB Cluster) daemon. Jun 5 15:42:50 SQL-NODE01 systemd[1]: Reloading. Jun 5 15:42:50 SQL-NODE01 systemd[1]: Started ACPI event daemon. Jun 5 15:49:37 SQL-NODE01 systemd[1]: Stopping LSB: Start and stop the mysql (Percona XtraDB Cluster)daemon... Jun 5 15:49:37 SQL-NODE01 mysql[4859]: * Stopping MySQL (Percona XtraDB Cluster) mysqld Jun 5 15:49:53 SQL-NODE01 /etc/init.d/mysql[4987]: MySQL PID not found, pid_file detected/guessed: /var/run/mysqld/mysqld.pid Jun 5 15:49:53 SQL-NODE01 /etc/init.d/mysql[4991]: MySQL PID not found, pid_file detected/guessed: /var/run/mysqld/mysqld.pid Jun 5 15:49:53 SQL-NODE01 mysql[4859]: ...done. Jun 5 15:49:53 SQL-NODE01 systemd[1]: Stopped LSB: Start and stop the mysql (Percona XtraDB Cluster) daemon. Jun 5 15:54:37 SQL-NODE01 /etc/init.d/mysql[5047]: ERROR: The partition with /var/lib/mysql is too full! Jun 5 15:55:00 SQL-NODE01 /etc/init.d/mysql[5109]: MySQL PID not found, pid_file detected/guessed: /var/run/mysqld/mysqld.pid Here is the result of "show status like 'wsrep%';" : (The first line got some value, it's because I did this copy after trying to add a the second node)
+----------------------------------+------------------+
| Variable_name | Value |
+----------------------------------+------------------+
| wsrep_local_state_uuid | 508226c7-68c6-11e8-b4e3-2faae9ed4ea1 |
| wsrep_protocol_version | 8 |
| wsrep_last_applied | 3 |
| wsrep_last_committed | 3 |
| wsrep_replicated | 3 |
| wsrep_replicated_bytes | 728 |
| wsrep_repl_keys | 3 |
| wsrep_repl_keys_bytes | 96 |
| wsrep_repl_data_bytes | 425 |
| wsrep_repl_other_bytes | 0 |
| wsrep_received | 2 |
.....
| wsrep_ist_receive_seqno_current | 0 |
| wsrep_ist_receive_seqno_end | 0 |
| wsrep_incoming_addresses | 192.168.1.101:3306 |
| wsrep_desync_count | 0 |
| wsrep_evs_delayed | |
| wsrep_evs_evict_list | |
| wsrep_evs_repl_latency | 0/0/0/0/0 |
| wsrep_evs_state | OPERATIONAL |
| wsrep_gcomm_uuid | 0a96d502-68c8-11e8-87ae-12c3077b44a0 |
| wsrep_cluster_conf_id | 1 |
| wsrep_cluster_size | 1 |
| wsrep_cluster_state_uuid | 508226c7-68c6-11e8-b4e3-2faae9ed4ea1 |
| wsrep_cluster_status | Primary |
| wsrep_connected | ON |
| wsrep_local_bf_aborts | 0 |
| wsrep_local_index | 0 |
| wsrep_provider_name | Galera |
| wsrep_provider_vendor | Codership Oy <info@codership.com> |
| wsrep_provider_version | 3.26(rac090bc) |
| wsrep_ready | ON |
+----------------------------------+--------------------------------------+
68 rows in set (0.01 sec)

So for the moment everything seems to be fine I'll create the sst users
mysql@pxc1> CREATE USER 'sstuser'@'localhost' IDENTIFIED BY 'passw0rd';
mysql@pxc1> GRANT RELOAD, LOCK TABLES, PROCESS, REPLICATION CLIENT ON *.* TO 'sstuser'@'localhost'; mysql@pxc1> FLUSH PRIVILEGES;

Time to bootsrap an another node via "/etc/init.d/mysql start"
The services start properly and there is what I got :
mysql> show status like 'wsrep%';
+----------------------------------+---------------------+
| Variable_name | Value |
+----------------------------------+---------------------+
| wsrep_local_state_uuid | 3bb3744c-68c6-11e8-8782-e32c0e432b82 |
| wsrep_protocol_version | 8 |
| wsrep_last_applied | 0 |
| wsrep_last_committed | 0 |
| wsrep_replicated | 0 |
| wsrep_replicated_bytes | 0 |
| wsrep_repl_keys | 0 |
| wsrep_repl_keys_bytes | 0 |
| wsrep_repl_data_bytes | 0 |
| wsrep_repl_other_bytes | 0 |
| wsrep_received | 2 |
| wsrep_received_bytes | 155 |
....
| wsrep_ist_receive_seqno_current | 0 |
| wsrep_ist_receive_seqno_end | 0 |
| wsrep_incoming_addresses | 192.168.1.102:3306 |
| wsrep_desync_count | 0 |
| wsrep_evs_delayed | |
| wsrep_evs_evict_list | |
| wsrep_evs_repl_latency | 4.391e-06/9.1114e-06/1.9822e-05/5.63194e-06/5 |
| wsrep_evs_state | OPERATIONAL |
| wsrep_gcomm_uuid | 440afb89-68c8-11e8-b82c-976d9c2e0aa3 |
| wsrep_cluster_conf_id | 1 |
| wsrep_cluster_size | 1 |
| wsrep_cluster_state_uuid | 3bb3744c-68c6-11e8-8782-e32c0e432b82 |
| wsrep_cluster_status | Primary |
| wsrep_connected | ON |
| wsrep_local_bf_aborts | 0 |
| wsrep_local_index | 0 |
| wsrep_provider_name | Galera |
| wsrep_provider_vendor | Codership Oy <info@codership.com>|
| wsrep_provider_version | 3.26(rac090bc) |
| wsrep_ready | ON |
+----------------------------------+-----------------------------------------------+
68 rows in set (0.01 sec)

Did someone already encountered this error ? Did I do something wrong ?
I'll wait for your answer

Does pmm-client work on Ubuntu 18.04?

Lastest Forum Posts - June 5, 2018 - 2:04am
Hello.
Could you please confirm that pmm-client was tested and working fine on Ubuntu 18.04?
Thank you.

Mysql Master eat up swap space and doesn't release it ... why?

Lastest Forum Posts - June 4, 2018 - 2:35pm
I am running a mysql 5.5 master
on a dedicated mysql master host.
it has 26Gb swap space and now alert on it's 93% used
it was 24GB a week ago and I added a tmp file a 2gb and now it's eaten up as well.
Swapoff/on doesn't work cause it isn't enough free mem avaliable.
What can I do ? ( besides reboot the master and wait until it all chewed up again )
What causes this ? bug in 5.5 ? memory leak ?
thx


swapoff -a; sleep 2; swapon -a
swapoff: /dev/sda3: swapoff failed: Cannot allocate memory

| innodb_buffer_pool_size | 30064771072

cat /proc/sys/vm/swappiness
1

cat /proc/sys/vm/dirty_ratio
20

RAM_ Size HD_Size HD_Used HD_Free Used% Speed Cache HT MySQL OSbits Linux_Version Model
49408748 689G 307G 348G 47% 2.40GHz 12288 16 5.5.32 x86_64 CentOS6.9(Final) R610f

DB_Type SwapSize SwapUsed SwapFree Usage% Cpu_1m Cpu_5m Cpu15m
master 26869752 25055192 1814560 93.247 2.23 2.30 2.30

free -m
total used free shared buffers cached
Mem: 48250 47649 600 0 8 101
-/+ buffers/cache: 47539 710
Swap: 26239 24699 1540

./find-out-what-is-using-your-swap.sh
..............................................
Overall swap used: 25019700 kB
========================================
kB pid name
========================================
24816896 7087 mysqld
145968 4636 messaging_local
28228 4311 puppet
7016 3226 snmpd
2444 30865 rsyslogd
2420 7746 nscd
2308 7789 nslcd

Use of uninitialized value in join or string at /bin/pt-online-schema-change

Lastest Forum Posts - June 4, 2018 - 6:07am
Hi everybody

we are trying to apply migrations to a small forum database but sadly get confusing error messages.

Database:
percona xtradb - 5.7.16-10-57-log on Centos 7.4


The table:
+------------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------+--------------+------+-----+---------+-------+
| conversationID | int(10) | NO | MUL | NULL | |
| participantID | int(10) | YES | MUL | NULL | |
| username | varchar(255) | NO | | | |
| hideConversation | tinyint(1) | NO | | 0 | |
| isInvisible | tinyint(1) | NO | | 0 | |
| lastVisitTime | int(10) | NO | | 0 | |
| joinedAt | int(10) | NO | | 0 | |
| leftAt | int(10) | NO | | 0 | |
| lastMessageID | int(10) | YES | MUL | NULL | |
+------------------+--------------+------+-----+---------+-------+


The original query:
ALTER TABLE wcf1_conversation_to_user ADD COLUMN conversationToUserID INT(10) UNSIGNED PRIMARY KEY AUTO_INCREMENT;


That should work:
PTDEBUG=1 pt-online-schema-change --execute "ADD COLUMN conversationToUserID INT(10) UNSIGNED PRIMARY KEY AUTO_INCREMENT" D=board_wcf,t=wcf1_conversation_to_user


But doesnt:
<snip lots of seemingly unimportend stuff to get under the 10000 char limit>
# /usr/bin/perl 5.016003
# Linux exampledb01.not.real 3.10.0-693.21.1.el7.x86_64 #1 SMP Wed Mar 7 19:03:37 UTC 2018 x86_64 x86_64 x86_64 GNU/Linux
# Arguments: _[--execute]_ _[--alter]_ _[ADD COLUMN conversationToUserID INT(10) UNSIGNED PRIMARY KEY AUTO_INCREMENT]_ _[D=board_wcf,t=wcf1_conversation_to_user]_
...
# NibbleIterator:5971 11747 EXPLAIN SELECT * FROM `board_wcf`.`wcf1_conversation_to_user` WHERE 1=1
# NibbleIterator:5973 11747 $VAR1 = {
# extra => undef,
# filtered => '100.00',
# id => '1',
# key => undef,
# key_len => undef,
# partitions => undef,
# possible_keys => undef,
# ref => undef,
# rows => '6059',
# select_type => 'SIMPLE',
# table => 'wcf1_conversation_to_user',
# type => 'ALL'
# };
...
# NibbleIterator:5521 11747 Ascend params: $VAR1 = {
# boundaries => {
# '<' => '((((? IS NOT NULL AND `participantid` IS NULL) OR (`participantid` < ?))) OR (((? IS NULL AND `participantid` IS NULL) OR (`participantid` = ?)) AND `conversationid` < ?))',
# '<=' => '((((? IS NOT NULL AND `participantid` IS NULL) OR (`participantid` < ?))) OR (((? IS NULL AND `participantid` IS NULL) OR (`participantid` = ?)) AND `conversationid` <= ?))',
# '>' => '((((? IS NULL AND `participantid` IS NOT NULL) OR (`participantid` > ?))) OR (((? IS NULL AND `participantid` IS NULL) OR (`participantid` = ?)) AND `conversationid` > ?))',
# '>=' => '((((? IS NULL AND `participantid` IS NOT NULL) OR (`participantid` > ?))) OR (((? IS NULL AND `participantid` IS NULL) OR (`participantid` = ?)) AND `conversationid` >= ?))'
# },
# cols => [
# 'conversationid',
# 'participantid',
# 'username',
# 'hideconversation',
# 'isinvisible',
# 'lastvisittime',
# 'joinedat',
# 'leftat',
# 'lastmessageid'
# ],
# index => 'participantid',
# scols => [
# 'participantid',
# 'participantid',
# 'participantid',
# 'participantid',
# 'conversationid'
# ],
# slice => [
# 1,
# 1,
# 1,
# 1,
# 0
# ],
# where => '((((? IS NULL AND `participantid` IS NOT NULL) OR (`participantid` > ?))) OR (((? IS NULL AND `participantid` IS NULL) OR (`participantid` = ?)) AND `conversationid` > ?))'
# };
#
# NibbleIterator:5538 11747 First lower boundary statement: SELECT /*!40001 SQL_NO_CACHE */ `participantid`, `participantid`, `participantid`, `participantid`, `conversationid` FROM `board_wcf`.`wcf1_conversation_to_user` FORCE INDEX(`participantid`) ORDER BY `participantid`, `conversationid` LIMIT 1 /*first lower boundary*/
# NibbleIterator:5563 11747 Last upper boundary statement: SELECT /*!40001 SQL_NO_CACHE */ `participantid`, `participantid`, `participantid`, `participantid`, `conversationid` FROM `board_wcf`.`wcf1_conversation_to_user` FORCE INDEX(`participantid`) ORDER BY `participantid` DESC,`conversationid` DESC LIMIT 1 /*last upper boundary*/
# NibbleIterator:5574 11747 Upper boundary statement: SELECT /*!40001 SQL_NO_CACHE */ `participantid`, `participantid`, `participantid`, `participantid`, `conversationid` FROM `board_wcf`.`wcf1_conversation_to_user` FORCE INDEX(`participantid`) WHERE ((((? IS NULL AND `participantid` IS NOT NULL) OR (`participantid` > ?))) OR (((? IS NULL AND `participantid` IS NULL) OR (`participantid` = ?)) AND `conversationid` >= ?)) ORDER BY `participantid`, `conversationid` LIMIT ?, 2 /*next chunk boundary*/
...
# NibbleIterator:5603 11747 Initial chunk size (LIMIT): 999
# NibbleIterator:5983 11747 Preparing statement handles
# NibbleIterator:6012 11747 First lower boundary: $VAR1 = [
# undef,
# undef,
# undef,
# undef,
# '1'
# ];
#
# NibbleIterator:6028 11747 Next lower boundary: $VAR1 = [
# undef,
# undef,
# undef,
# undef,
# '1'
# ];
#
2018-06-04T13:07:38 Copying approximately 6059 rows...
# IndexLength:6678 11747 SELECT /*!40001 SQL_NO_CACHE */ `participantid`, `conversationid` FROM `board_wcf`.`wcf1_conversation_to_user` FORCE INDEX (`participantid`) WHERE `participantid` IS NOT NULL AND `conversationid` IS NOT NULL ORDER BY `participantid`, `conversationid` LIMIT 1 /*key_len*/
# IndexLength:6645 11747 EXPLAIN SELECT /*!40001 SQL_NO_CACHE */ * FROM `board_wcf`.`wcf1_conversation_to_user` FORCE INDEX (`participantid`) WHERE `participantid` = ? AND `conversationid` >= ? /*key_len*/ params: 1 1
# IndexLength:6649 11747 Range scan: $VAR1 = {
# extra => 'Using index condition',
# filtered => '100.00',
# id => '1',
# key => 'participantID',
# key_len => '9',
# partitions => undef,
# possible_keys => 'participantID',
# ref => undef,
# rows => '8',
# select_type => 'SIMPLE',
# table => 'wcf1_conversation_to_user',
# type => 'range'
# };
#
# NibbleIterator:5657 11747 init callback returned 1
# pt_online_schema_change:11489 11747 EXPLAIN SELECT /*!40001 SQL_NO_CACHE */ `participantid`, `participantid`, `participantid`, `participantid`, `conversationid` FROM `board_wcf`.`wcf1_conversation_to_user` FORCE INDEX(`participantid`) WHERE ((((? IS NULL AND `participantid` IS NOT NULL) OR (`participantid` > ?))) OR (((? IS NULL AND `participantid` IS NULL) OR (`participantid` = ?)) AND `conversationid` >= ?)) ORDER BY `participantid`, `conversationid` LIMIT ?, 2 /*next chunk boundary*/ params: undef undef undef undef 1 999
# pt_online_schema_change:11498 11747 EXPLAIN plan: $VAR1 = {
# extra => 'Using where; Using index',
# filtered => '100.00',
# id => '1',
# key => 'participantID',
# key_len => '9',
# partitions => undef,
# possible_keys => 'participantID',
# ref => undef,
# rows => '3236',
# select_type => 'SIMPLE',
# table => 'wcf1_conversation_to_user',
# type => 'range'
# };
#
# NibbleIterator:6083 11747 next_boundaries callback returned 1
# CleanupTask:6551 11747 Calling cleanup task CODE(0x3159090)
....
2018-06-04T13:07:38 Error copying rows from `board_wcf`.`wcf1_conversation_to_user` to `board_wcf`.`_wcf1_conversation_to_user_new`: Use of uninitialized value in join or string at /bin/pt-online-schema-change line 6092.

...
# Cxn:4096 11747 Destroying cxn
# Cxn:4105 11747 DBI::db=HASH(0x3091470) Disconnecting dbh on exampledb01.not.real

--------


It seems the script stumbles over the 'undefined' results from the boundry queries:

Query:
SELECT /*!40001 SQL_NO_CACHE */ `participantid`, `participantid`, `participantid`, `participantid`, `conversationid` FROM `board_wcf`.`wcf1_conversation_to_user` FORCE INDEX(`participantid`) ORDER BY `participantid`, `conversationid` LIMIT 1
Result:
$VAR1 = [
# undef,
# undef,
# undef,
# undef,
# '1'
# ];

But we don't really know what to do with this information.
We tried with Percona toolkit 2.2.10 and 3.0.10, same problem.

Does anyone has an idea whats amiss?

Thanks
Tarwin



xtrabackup prepare is very slow

Lastest Forum Posts - June 4, 2018 - 3:05am
hi team,
i am using xtrabackup 2.4 with mysql 5.7 in centos 6 . initially preparing was very fast. after exporting and importing some databases it is very slow.
please can somebody explain me why it is very slow?????

Streaming backup

Lastest Forum Posts - June 4, 2018 - 1:23am
Hi all,

I'm designing my backup solution for Percona MySQL. Currently I have a (working) setup which does a full backup with xtrabackup to a remote location. These backups are then stored 4 weeks and rotated.

Now comes the part I have some trouble with; During the week I would like to stream binlogs to a remote location in order to be able to roll forward the full backups and get point in time recovery (PITR) capability. I say streaming because I want to minimize the gap between binlogs being send to a remote location and a possible disaster. I'm having a hard time finding a tool which manages receiving binlogs and stores them for backup purposes (and also does rotation and such). Am I trying to do something here that doesn't make sense?

This setup is basically the same as what pgbarman does for PostgreSQL; Take full backups and then supplement those by pretending to be part of PostgreSQL replication topology and receive WAL's.

xtrabackup has messed up my MySQL installation

Lastest Forum Posts - June 4, 2018 - 12:02am
I'm posting here at the recommendation of Lorraine@Percona, after a 1st post at StackExchange.

Last Thursday I experimented a bit with Percona xtrabackup on a MySQL 5.5.23 server (we're stuck at this very old version, that's another discussion). I installed the Percona repo as part of the task. I uninstalled the tool before the end of the day, but didn't uninstall the repo.

On Friday I noticed that when I ran "mysql -u root -p" I got a segmentation fault!

I investigated and found (using the rpm -qa --last command) that at 5:19am Friday morning, when we were all asleep, somehow, Percona-Server-Shared was installed (by itself??), and /usr/lib64/libmysqlclient.so.16.0.0 is now part of that package, as I confirm with the rpm -qf command. I suspect that's why the plain vanilla mysql client is dumping core, it can't find the libraries it expects.

When I attempt to uninstall Percona server, I see that it cascades to remove the mysql-server package itself (!), so I can't do it.

At StackExchange I was told that this is a known bug that plagues the 5.5.23 MySQL version and I received the recommendation to uninstall Percona-Server-Shared using the --nodeps option, and then install mysql-libs.

I must fix this problem ASAP, the server is a staging server (no I didn't do it on a production server :-) ) It's running Redhat 6.9.

Thanks for your help,

George

After upgrading to MySQL 5.7 queries on Views not using underlying indexes of tables

Lastest Forum Posts - June 2, 2018 - 7:59pm


select final.grad_year_flag ,
final.registration_id ,
final.first_name ,
final.last_name ,
final.grad_year ,
final.photo_id ,
final.photo_type ,
final.photo_image_path
from
(
select B.grad_year_flag,
B.registration_id,
B.first_name,
B.last_name,
B.grad_year,
B.photo_id,
B.photo_type,
B.photo_image_path,
@rn1 := if(@grad_flag1=grad_year_flag, @rn1+1,1) as rn1,
@grad_flag1 := grad_year_flag
from
(
select A.*,
@rn := if(@grad_flag=grad_year_flag and @reg_flag=registration_id, @rn+1,1) as rn,
@grad_flag := grad_year_flag,
@reg_flag := registration_id
from
(
select distinct
CASE WHEN rk.grad_year= NAME_CONST('i_grad_year',1975)THEN 0
ELSE rk.grad_year- NAME_CONST('i_grad_year',1975)END grad_year_flag,
rpd.photo_type,
rpd.registration_id,
rpd.first_name,
rpd.last_name,
rk.grad_year,
rpd.photo_id,
rpd.photo_image_path
from db_cache.reg_k12_grad_year rk,
db_cache.reg_photo_details rpd
where rk.registration_id=rpd.registration_id
and rpd.photo_type in(2,3)
and rk.school_id = NAME_CONST('i_commId',9183)and rk.grad_year between ( NAME_CONST('i_grad_year',1975)-3) and ( NAME_CONST('i_grad_year',1975)+3)
and rk.registration_id!= NAME_CONST('i_regId',7490338311)order by grad_year_flag,rpd.registration_id,rpd.photo_type
) A
) B
where rn=1
ORDER BY grad_year_flag, rand()
)final
WHERE case when grad_year_flag = 0
then rn1 <= 15
else rn1 <=5
end ;

Explain plan from 5.6

+----+-------------+---------------------+-------+-------------------------------------------------------+------------------------+---------+----------------------------------------------+------+----------+-----------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------------------+-------+-------------------------------------------------------+------------------------+---------+----------------------------------------------+------+----------+-----------------------------------------------------------+
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 10 | 100.00 | Using where |
| 2 | DERIVED | <derived3> | ref | <auto_key0> | <auto_key0> | 9 | const | 10 | 100.00 | Using temporary; Using filesort |
| 3 | DERIVED | <derived4> | ALL | NULL | NULL | NULL | NULL | 1755 | 100.00 | NULL |
| 4 | DERIVED | reg_k12_grad_year_b | range | PRIMARY,REG_K12_GRAD_YEAR_B_N1,REG_K12_GRAD_YEAR_B _N2 | REG_K12_GRAD_YEAR_B_N1 | 10 | NULL | 1755 | 100.00 | Using where; Using index; Using temporary; Using filesort |
| 4 | DERIVED | reg_photo_details_k | ref | reg_photo_details_k_n1 | reg_photo_details_k_n1 | 8 | db_cache.reg_k12_grad_year_b.registration_id | 1 | 100.00 | Using index condition; Distinct |
+----+-------------+---------------------+-------+-------------------------------------------------------+------------------------+---------+----------------------------------------------+------+----------+-----------------------------------------------------------+

Explain plan from 5.7
+----+-------------+---------------------+------------+------+---------------+-------------+---------+--------------------+----------+----------+----------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------------------+------------+------+---------------+-------------+---------+--------------------+----------+----------+----------------------------------------------+
| 1 | PRIMARY | <derived2> | NULL | ALL | NULL | NULL | NULL | NULL | 10 | 100.00 | Using where |
| 2 | DERIVED | <derived3> | NULL | ref | <auto_key0> | <auto_key0> | 9 | const | 10 | 100.00 | Using temporary; Using filesort |
| 3 | DERIVED | <derived4> | NULL | ALL | NULL | NULL | NULL | NULL | 217730 | 100.00 | NULL |
| 4 | DERIVED | <derived5> | NULL | ref | <auto_key0> | <auto_key0> | 8 | const | 907299 | 10.00 | Using where; Using temporary; Using filesort |
| 4 | DERIVED | <derived6> | NULL | ref | <auto_key1> | <auto_key1> | 8 | rk.registration_id | 12 | 20.00 | Using where |
| 6 | DERIVED | reg_photo_details_i | NULL | ALL | NULL | NULL | NULL | NULL | 79803766 | 100.00 | NULL |
| 5 | DERIVED | reg_k12_grad_year_b | NULL | ALL | NULL | NULL | NULL | NULL | 90729926 | 100.00 | NULL |
+----+-------------+---------------------+------------+------+---------------+-------------+---------+--------------------+----------+----------+----------------------------------------------+

Same query took less than a sec on 5.6 and is taking 24 mins on 5.7, i tried recreating views on underlying table with ALGORITHM=MERGE but no change in explain plan, any help would be appreciated.

This Week in Data with Colin Charles 40: a Peak at Blockchain, Lots of MariaDB News, then Back on the Road

Latest MySQL Performance Blog posts - June 1, 2018 - 9:45am

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

Shortly after the last dispatch, I jetted off for a spot of vacation (which really meant I was checking out the hype behind Blockchain with a database developer lens at the Blockchain Week NYC), and then some customer visits in Seoul, which explains the short hiatus. Here’s to making this more regular as the summer approaches.

I am about to embark on a fairly long trip, covering a few upcoming appearances: Lisbon for the Percona Engineering meeting, SouthEastLinuxFest in Charlotte, the Open Source Data Centre Conference in Berlin and then the DataOps Barcelona event. I have some discount codes: 50% discount for OSDC with the code OSDC_FOR_FRIENDS, and 50% discount for DataOps Barcelona with the code dataopsbcn50. Expect this column to reflect my travels over the next few weeks.

There has been a lot of news on the MariaDB front: MariaDB 10.3.7 went stable/GA! You might have noticed more fanfare around the release name MariaDB TX 3.0, but the reality is you can still get this download from your usual MariaDB Foundation site. It is worth noting that the MariaDB Foundation 2017 financials have also been released. Some may have noticed a couple months back there was a press release titled Report “State of the Open-Source DBMS Market, 2018” by Gartner Includes Pricing Comparison With MariaDB. This led to a Gartner report on the State of the Open-Source DBMS Market, 2018; although the report has since been pulled. Hopefully we see it surface again.

In the meantime, please do try out MariaDB 10.3.7 and it would be great to hear feedback. I also have an upcoming Percona webinar on MariaDB Server 10.3 on June 26 2018 — when the sign up link appears, I will be sure to include it here.

Well written, and something worth discussing: Should Red Hat Buy or Build a Database?. The Twitter discussion is also worth looking at.

Releases Link List Upcoming appearances Feedback

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

The post This Week in Data with Colin Charles 40: a Peak at Blockchain, Lots of MariaDB News, then Back on the Road appeared first on Percona Database Performance Blog.

Percona Server for MySQL 5.7.22-22 Is Now Available

Latest MySQL Performance Blog posts - May 31, 2018 - 11:06am

Percona announces the GA release of Percona Server for MySQL 5.7.22-22 on on May 31, 2018. 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.7.22, including all the bug fixes in it, Percona Server for MySQL 5.7.22-22 is the current GA release in the Percona Server for MySQL 5.7 series. Percona provides completely open-source and free software.

New Features:
  • A new --encrypt-tmp-files option turns on encryption for the temporary files which Percona Server may create on disk for filesort, binary log transactional caches and Group Replication caches.
Bugs Fixed:
  • Executing the SHOW GLOBAL STATUS expression could cause “data drift” on global status variables in case of a query rollback: the variable, being by its nature a counter and allowing only an increase, could return to its previous value. Bug fixed #3951 (upstream #90351).
  • NUMA support was improved in Percona Server, reverting upstream implementation back to the original one,due to upstream variant being less effective in memory allocation. Now  innodb_numa_interleave variable not only enables NUMA interleave memory policy for the InnoDB buffer pool allocation, but forces NUMA interleaved allocation at the buffer pool initialization time. Bug fixed #3967.
  • audit_log_include_accounts variable did not take effect if placed in my.cnf configuration file, while still working as intended if set dynamically. Bug fixed #3867.
  • 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.
  • Fixes were introduced to remove GCC 8 compilation warnings for the Percona Server build. Bug fixed #3950.
  • An InnoDB Memcached Plugin code clean-up was backported from MySQL 8.0. Bug fixed  #4506.
  • Percona Server could not be built with -DWITH_LZ4=system option on Ubuntu 14.04 (Trusty) because of too old LZ4 packages. Bug fixed #3842.
  • A regression brought during TokuDB code clean-up in 5.7.21-21 was causing assertion in cases when the FT layer returns an error during an alter table operation. Bug fixed #4294.
MyRocks Changes and fixes:
  • UPDATE statements were returning incorrect results because of not making a full table scan on tables with unique secondary index. Bug fixed #4495 (upstream facebook/mysql-5.6#830).
Other Bugs Fixed:
  • #4451 “Implement better compression algo testing”
  • #4469 “variable use out of scope bug in get_last_key test detected by ASAN in clang 6”
  • #4470 “the cachetable-simple-pin-nonblocking-cheap test occasionally fails due to a locking conflict with the cachetable evictor”
  • #4488 “-Werror is always disabled for innodb_memcached”
  • #1114 “Assertion `inited == INDEX’ failed”
  • #1130 “RBR Replication with concurrent XA in READ-COMMITTED takes supremum pseudo-records and breaks replication”

Find the release notes for Percona Server for MySQL 5.7.22-22 in our online documentation. Report bugs in the Jira bug tracker.

The post Percona Server for MySQL 5.7.22-22 Is Now Available appeared first on Percona Database Performance Blog.

Don’t Drown in your Data Lake

Latest MySQL Performance Blog posts - May 31, 2018 - 10:37am

A data lake is “…a method of storing data within a system or repository, in its natural format, that facilitates the collocation of data in various schemata and structural forms…”1. Many companies find value in using a data lake but aren’t clear that they need to properly plan for it and maintain it in order to prevent issues.

The idea of a data lake rose from the need to store data in a raw format that is accessible to a variety of applications and authorized users. Hadoop is often used to query the data, and the necessary structures for querying are created through the query tool (schema on read) rather than as part of the data design (schema on write). There are other tools available for analysis, and many cloud providers are actively developing additional options for creating and managing your data lake. The cloud is often viewed as an ideal place for your data lake since it is inherently elastic and can expand to meet the needs of your data.

Data Lake or Data Swamp?

One of the key components of a functioning data lake is the continuing inflow and egress of data. Some data must be kept indefinitely but some can be archived or deleted after a defined period of time. Failure to remove stale data can result in a data swamp, where the out of date data is taking up valuable and costly space and may be causing queries to take longer to complete. This is one of the first issues that companies encounter in maintaining their data lake. Often, people view the data lake as a “final resting place” for data, but it really should be used for data that is accessed often, or at least occasionally.

A natural spring-fed lake can turn into a swamp due to a variety of factors. If fresh water is not allowed to flow into the lake, this can cause stagnation, meaning that plants and animals that previously were not able to be supported by the lake take hold. Similarly, if water cannot exit the lake at some point, the borders will be breached, and the surrounding land will be inundated. Both of these conditions can cause a once pristine lake to turn into a fetid and undesirable swamp. If data is no longer being added to your data lake, the results will become dated and eventually unreliable. Also, if data is always being added to the lake but is not accessed on a regular basis, this can lead to unrestricted growth of your data lake, with no real plan for how the data will be used. This can become an expensive “cold storage” facility that is likely more expensive than archived storage.

If bad or undesirable items, like old cars or garbage, are thrown into a lake, this can damage the ecosystem, causing unwanted reactions. In a data lake, this is akin to simply throwing data into the data lake with no real rules or rationale. While the data is saved, it may not be useful and can cause negative consequences across the whole environment since it is consuming space and may slow response times. Even though a basic concept of a data lake is that the data does not need to conform to a predefined structure, like you would see with a relational database, it is important that some rules and guidelines exist regarding the type and quality of data that is included in the lake. In the absence of some guidelines, it becomes difficult to access the relevant data for your needs. Proper definition and tagging of content help to ensure that the correct data is accessible and available when needed.

Unrestricted Growth Consequences

Many people have a junk drawer somewhere in their house; a drawer that is filled with old receipts, used tickets, theater programs, and the like. Some of this may be stored for sentimental reasons, but a lot of it is put into this drawer since it was a convenient dropping place for things. Similarly, if we look to the data lake as the “junk drawer” for our company, it is guaranteed to be bigger and more expensive than it truly needs to be.

It is important that the data that is stored in your data lake has a current or expected purpose. While you may not have a current use for some data, it can be helpful to keep it around in case a need arises. An example of this is in the area of machine learning. Providing more ancillary data enables better decisions since it provides a deeper view into the decision process. Therefore, maintaining some data that may not have a specific and current need can be helpful. However, there are cases where maintaining a huge volume of data can be counterproductive. Consider temperature information delivered from a switch. If the temperature reaches a specific threshold, the switch should be shut down. Reporting on the temperature in an immediate and timely manner is important to make an informed decision, but stable temperature data from days, week, or months ago could be summarized and stored in a more efficient manner. The granular details can then be purged from the lake.

So, where is the balance? If you keep all the data, it can make your data lake unwieldy and costly. If you only keep data that has a specific current purpose, you may be impairing your future plans. Obviously, the key is to monitor your access and use of the data frequently, and purge or archive some of the data that is not being regularly used.

Uncontrolled Access Concerns

Since much of the data in your data lake is company confidential, it is imperative that access to that data be controlled. The fact that the data in the lake is stored in its raw format means that it is more difficult to control access. The structures of a relational database provide some of the basis for access control, allowing us to limit who has access to specific queries, tables, fields, schemas, databases, and other objects. In the absence of these structures, controlling access requires more finesse. Determining who has access to what parts of the data in the lake must be handled, as well as isolating the data within your own network environment. Many of these restrictions may already be in place in your current environment, but they should be reviewed before being relied on fully, since the data lake may store information that was previously unavailable to some users. Access should be regularly reviewed to identify potential rogue activities. Encryption options also exist to further secure the data from unwanted access, and file system security can be used to limit access. All of these components must be considered, implemented, and reviewed to ensure that the data is secure.

User Considerations

In a relational database, the data structure inherently determines some of the consistencies and format of the data. This enables users to easily query the data and be assured that they are returning valid results. The lack of such structures in the data lake means that users must be more highly skilled at data manipulation. Having users with less skill accessing the data is possible, but it may not provide the best results. A data scientist is better positioned to access and query the complete data set. Obviously, users with a higher skill set are rare and cost more to hire, but the return may be worth it in the long run.

So What Do I Do Now?

This is an area where there are no hard and fast rules. Each company must develop and implement processes and procedures that make sense for their individual needs. Only with a plan for monitoring inputs, outputs, access patterns, and the like are you able to make a solid determination for your company’s needs. Percona can help to determine a plan for reporting usage, assess security settings, and more. As you are using the data in your data lake, we can also provide guidance regarding tools used to access the data.

1 Wikipedia, May 22, 2018

The post Don’t Drown in your Data Lake appeared first on Percona Database Performance Blog.

MongoDB: deploy a replica set with transport encryption (part 3/3)

Latest MySQL Performance Blog posts - May 31, 2018 - 7:59am

In this third and final post of the series, we look at how to configure transport encryption on a deployed MongoDB replica set. Security vulnerabilities can arise when internal personnel have legitimate access to the private network, but should not have access to the data. Encrypting intra-node traffic ensures that no one can “sniff” sensitive data on the network.

In part 1 we described MongoDB replica sets and how they work.
In part 2 we provided a step-by-step guide to deploy a simple 3-node replica set, including information on replica set configuration.

Enable Role-Based Access Control

In order for the encryption to be used in our replica set, we need first to activate Role-Based Access Control (RBAC). By default, a MongoDB installation permits anyone to connect and see the data, as in the sample deployment we created in part 2. Having RBAC enabled is mandatory for encryption.

RBAC governs access to a MongoDB system. Users are created and assigned privileges to access specific resources, such as databases and collections. Likewise, for carrying out administrative tasks, users need to be created with specific grants. Once activated, every user must authenticate themselves in order to access MongoDB.

Prior to activating RBAC, let’s create an administrative user. We’ll connect to the PRIMARY member and do the following:

rs-test:PRIMARY> use admin switched to db admin rs-test:PRIMARY> db.createUser({user: 'admin', pwd: 'secret', roles:['root']}) Successfully added user: { "user" : "admin", "roles" : [ "root" ] }

Let’s activate the RBAC in the configuration file /etc/mongod.conf on each node

security: authorization: enabled

and restart the daemon

sudo service mongod restart

Now to connect to MongoDB we issue the following command:

mongo -u admin -p secret --authenticationDatabase "admin"

Certificates

MongoDB supports X.509 certificate authentication for use with a secure TLS/SSL connection. The members can use X.509 certificates to verify their membership of the replica set.

In order to use encryption, we need to create certificates on all the nodes and have a certification authority (CA) that signs them. Since having a certification authority can be quite costly, we decide to use self-signed certificates. For our purposes, this solution ensures encryption and has no cost. Using a public CA is not necessary inside a private infrastructure.

To proceed with certificate generation we need to have openssl installed on our system and certificates need to satisfy these requirements:

  • any certificate needs to be signed by the same CA
  • the common name (CN) required during the certificate creation must correspond to the hostname of the host
  • any other field requested in the certificate creation should be a non-empty value and, hopefully, should reflect our organization details
  • it is also very important that all the fields, except the CN, should match those from the certificates for the other cluster members

The following guide describes all the steps to configure internal X.509 certificate-based encryption.

1 – Connect to one of the hosts and generate a new private key using openssl openssl genrsa -out mongoCA.key -aes256 8192

We have created a new 8192 bit private key and saved it in the file mongoCA.key
Remember to enter a strong passphrase when requested.

2 – Sign a new CA certificate

Now we are going to create our “fake” local certification authority that we’ll use later to sign each node certificate.

During certificate creation, some fields must be filled out. We could choose these randomly but they should correspond to our organization’s details.

root@psmdb1:~# openssl req -x509 -new -extensions v3_ca -key mongoCA.key -days 365 -out mongoCA.crt Enter pass phrase for mongoCA.key: You are about to be asked to enter information that will be incorporated into your certificate request. What you are about to enter is what is called a Distinguished Name or a DN. There are quite a few fields but you can leave some blank For some fields there will be a default value, If you enter '.', the field will be left blank. ----- Country Name (2 letter code) [AU]:US State or Province Name (full name) [Some-State]:California Locality Name (eg, city) []:San Francisco Organization Name (eg, company) [Internet Widgits Pty Ltd]:My Company Ltd Organizational Unit Name (eg, section) []:DBA Common Name (e.g. server FQDN or YOUR name) []:psmdb Email Address []:corrado@mycompany.com 3 – Issue self-signed certificates for all the nodes

For each node, we need to generate a certificate request and sign it using the CA certificate we created in the previous step.

Remember: fill out all the fields requested the same for each host, but remember to fill out a different common name (CN) that must correspond to the hostname.

For the first node issue the following commands.

openssl req -new -nodes -newkey rsa:4096 -keyout psmdb1.key -out psmdb1.csr openssl x509 -CA mongoCA.crt -CAkey mongoCA.key -CAcreateserial -req -days 365 -in psmdb1.csr -out psmdb1.crt cat psmdb1.key psmdb1.crt > psmdb1.pem

for the second node

openssl req -new -nodes -newkey rsa:4096 -keyout psmdb2.key -out psmdb2.csr openssl x509 -CA mongoCA.crt -CAkey mongoCA.key -CAcreateserial -req -days 365 -in psmdb2.csr -out psmdb2.crt cat psmdb2.key psmdb2.crt > psmdb2.pem

and for the third node

openssl req -new -nodes -newkey rsa:4096 -keyout psmdb3.key -out psmdb3.csr openssl x509 -CA mongoCA.crt -CAkey mongoCA.key -CAcreateserial -req -days 365 -in psmdb3.csr -out psmdb3.crt cat psmdb3.key psmdb3.crt > psmdb3.pem

4 – Place the files

We could execute all of the commands in the previous step on the same host, but now we need to copy the generated files to the proper nodes:

  • Copy to each node the CA certifcate file: mongoCA.crt
  • Copy each self signed certifcate <hostname>.pem into the relative member
  • Create on each member a directory that only the MongoDB user can read, and copy both files there

sudo mkdir -p /etc/mongodb/ssl sudo chmod 700 /etc/mongodb/ssl sudo chown -R mongod:mongod /etc/mongodb sudo cp psmdb1.pem /etc/mongodb/ssl sudo cp mongoCA.crt /etc/mongodb/ssl

Do the same on each host.

5 – Configure mongod

Finally, we need to instruct mongod about the certificates to enable the encryption.

Change the configuration file /etc/mongod.conf on each host adding the following rows:

net: port: 27017 ssl: mode: requireSSL PEMKeyFile: /etc/mongodb/ssl/psmdb1.pem CAFile: /etc/mongodb/ssl/mongoCA.crt clusterFile: /etc/mongodb/ssl/psmdb1.pem security: authorization: enabled clusterAuthMode: x509

Restart the daemon

sudo service mongodb restart

Make sure to put the proper file names on each host (psmdb2.pem on psmdb2 host and so on)

Now, as long as we have made no mistakes, we have a properly configured replica set that is using encrypted connections.

Issue the following command to connect on node psmdb1:

mongo admin --ssl --sslCAFile /etc/mongodb/ssl/mongoCA.crt --sslPEMKeyFile /etc/mongodb/ssl/psmdb1.pem -u admin -p secret --host psmdb1

Access the first two articles in this series
  • Part 1: Introduces basic replica set concepts, how it works and what its main features
  • Part 2:  Provides a step-by-step guide to configure a three-node replica set

The post MongoDB: deploy a replica set with transport encryption (part 3/3) appeared first on Percona Database Performance Blog.

Percona Server for MySQL 5.6.40-84.0 Is Now Available

Lastest Forum Posts - May 31, 2018 - 3:24am
Percona announces the release of Percona Server for MySQL 5.6.40-84.0 on May 30, 2018 (downloads are available here and from the Percona Software Repositories). Based on MySQL 5.6.40, including all the bug fixes in it, Percona Server for MySQL 5.6.40-84.0 is now the current GA release in the 5.6 series. All of Percona’s software is open-source and free.

New Features
  • A new string variable version_suffix allows to change suffix for the Percona Server version string returned by the read-only version variable. This allows to append the version number for the server with a custom suffix to reflect some build or configuration specifics. Also version_comment (default value of which is taken from the CMake COMPILATION_COMMENT option) is converted from a global read-only to a global read-write variable and thereby it is now cutomizable.
  • Query response time plugin now can be disabled at session level with use of a new variable query_response_time_session_stats.
Bugs Fixed
  • Compilation warning was fixed for -DWITH_QUERY_RESPONSE_TIME=ON CMake compilation option, which makes QRT to be linked statically. Bug fixed #3841.
  • A code clean-up was done to fix clang 6 specific compilation warnings and errors (bug fixed #3893, upstream #90111).
  • 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 clean-up in Percona Server binlog-related code was made to avoid uninitialized memory comparison. Bug fixed #3925 (upstream #90238).
  • 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.
  • Percona Server Debian packages description included reference to /etc/mysql/my.cnf file, which is not actually present in these packages. Bug fixed #2046.
  • Fixes were introduced to remove GCC 8 compilation warnings for the Percona Server build, retaining compatibility with old compiler versions, including GCC 4.4. Bugs fixed #3950 and #4471.
  • A typo in plugin.cmake file prevented to compile plugins statically into the server. Bug fixed #3871 (upstream #89766).
  • -DWITH_NUMA=ON build option was silently ignored by CMake when NUMA development package was not installed, instead of exiting by error. Bug fixed #4487.
  • Variables innodb_buffer_pool_populate and numa_interleave mapped to the upstream innodb_numa_interleave variable in 5.6.27-75.0 were reverted to their original implementation due to upstream variant being less effective in memory allocation. Now buffer pool is allocated with MAP_POPULATE, forcing NUMA interleaved allocation at the buffer pool initialization time. Bug fixed #3967.
  • audit_log_include_accounts variable did not take effect if placed in my.cnf configuration file, while still working as intended if set dynamically. Bug fixed #3867.
  • Synchronization between between innodb_kill_idle_transaction and kill_idle_transaction system variables was broken because of the regression in Percona Server 5.6.40-83.2. Bug fixed #3955.
  • Executing the SHOW GLOBAL STATUS expression could cause “data drift” on global status variables in case of a query rollback: the variable, being by its nature a counter and allowing only an increase, could return to its previous value. Bug fixed #3951 (upstream #90351).
  • ALTER TABLE … COMMENT = … statement caused TokuDB to rebuild the whole table, which is not needed, as only FRM metadata should be changed. The fix was provided as a contribution by Fungo Wang. Bugs fixed #4280 and #4292.
  • A number of Percona Server 8.0 TokuDB fixes have been backported to Percona Server 5.6 in preparation for using MySQL 8.0. Bugs fixed #4379, #4380, #4387, #4378, #4383, #4384, #4386, #4382, #4391, #4390, #4392, and #4381.
TokuDB Changes and Fixes
  • Two new variables, tokudb_enable_fast_update and tokudb_enable_fast_upsert, were introduced to facilitate the TokuDB fast updates feature, which involves queries optimization to avoid random reads during their execution. Bug fixed #4365.
  • A data race was fixed in minicron utility of the PerconaFT, as a contribution by Rik Prohaska. Bug fixed #4281.
  • Row count and cardinality decrease to zero took place after long-running REPLACE load, ending up with full table scans for any action.
Other Bugs Fixed
  • #3818 “Orphaned file mysql-test/suite/innodb/r/percona_innodb_kill_idle_trx.result”
  • #3926 “Potentially truncated bitmap file name in log_online_open_bitmap_file_read_only() (storage/innobase/log/log0online.cc)”
  • #2204 “Test main.audit_log_default_db is unstable”
  • #3767 “Fix compilation warnings/errors with clang”
  • #3773 “Incorrect key file for table frequently for tokudb”
  • #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”
  • #3887 “TokuDB does not compile with -DWITH_PERFSCHEMA_STORAGE_ENGINE=OFF”
  • #4388 “5.7 code still has TOKU_INCLUDE_OPTION_STRUCTS which is a MariaDB specific construct”
  • #4265 “TDB-114 (Change use of MySQL HASH to unordered_map) introduces memory leak”
  • #4277 “memory leaks in TDB-2 and TDB-89 tests”
  • #4276 “Data race on cache table attributes detected by the thread sanitizer”
  • #4451 “Implement better compression algo testing”
  • #4469 “variable use out of scope bug in get_last_key test detected by ASAN in clang 6”
  • #4470 “the cachetable-simple-pin-nonblocking-cheap test occasionally fails due to a locking conflict with the cachetable evictor”
  • #1131 “User_var_log_event::User_var_log_event(const char*, uint, const Format_description_log_event*): Assertion `(bytes_read == (data_written – ((old_pre_checksum_fd || (description_event->checksum_alg == BINLOG_CHECKSUM_ALG_OFF)) ? 0 : 4))) || ((“.
Find the release notes for Percona Server for MySQL 5.6.40-84.0 in our online documentation. Report bugs in the Jira bug tracker.

Innobackupex get maximum speed out of backup to remote server?

Lastest Forum Posts - May 30, 2018 - 5:18pm
Hi I'm running a live backup and trying to configure innobackupex to get the maximum speed out of the backup. I'm using threads and compression but can't seem to get any more speed out of the backup. The source server is a linux 20 CPU's and 130 GB RAM and the receiving server is linux with 16 cpus and 60 GB RAM. Both servers are not reaching their maximum load during the backup. I have 2 Gbit/s network capacity but the backup is only using 500Mbit/s. I've adjusted compression and threads to tweak things but it doesn't seem to change much. Below is my current configuration. Are there suggested compression and thread settings to get maximum throughput, or another method that I should be using for a live backup? Thanks.

innobackupex --defaults-file=/mnt/data/slave/my.cnf --host=127.0.0.1 --safe-slave-backup --no-lock --skip-secure-auth --user=backup --password=xxxxxx --stream=xbstream --compress --compress-threads=10 --parallel=30 /mnt/data/slave | ssh backup@192.168.0.10 "cat - > /mnt/data/databases/slave/slave.xbstream"

Percona Server for MySQL 5.6.40-84.0 Is Now Available

Latest MySQL Performance Blog posts - May 30, 2018 - 11:55am

Percona announces the release of Percona Server for MySQL 5.6.40-84.0 on May 30, 2018 (downloads are available here and from the Percona Software Repositories). Based on MySQL 5.6.40, including all the bug fixes in it, Percona Server for MySQL 5.6.40-84.0 is now the current GA release in the 5.6 series. All of Percona’s software is open-source and free.

New Features
  • A new string variable version_suffix allows to change suffix for the Percona Server version string returned by the read-only version variable. This allows to append the version number for the server with a custom suffix to reflect some build or configuration specifics. Also version_comment (default value of which is taken from the CMake COMPILATION_COMMENT option) is converted from a global read-only to a global read-write variable and thereby it is now cutomizable.
  • Query response time plugin now can be disabled at session level with use of a new variable query_response_time_session_stats.
Bugs Fixed
  • Compilation warning was fixed for -DWITH_QUERY_RESPONSE_TIME=ON CMake compilation option, which makes QRT to be linked statically. Bug fixed #3841.
  • A code clean-up was done to fix clang 6 specific compilation warnings and errors (bug fixed #3893, upstream #90111).
  • 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 clean-up in Percona Server binlog-related code was made to avoid uninitialized memory comparison. Bug fixed #3925 (upstream #90238).
  • 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.
  • Percona Server Debian packages description included reference to /etc/mysql/my.cnf file, which is not actually present in these packages. Bug fixed #2046.
  • Fixes were introduced to remove GCC 8 compilation warnings for the Percona Server build, retaining compatibility with old compiler versions, including GCC 4.4. Bugs fixed #3950 and #4471.
  • A typo in plugin.cmake file prevented to compile plugins statically into the server. Bug fixed #3871 (upstream #89766).
  • -DWITH_NUMA=ON build option was silently ignored by CMake when NUMA development package was not installed, instead of exiting by error. Bug fixed #4487.
  • Variables innodb_buffer_pool_populate and numa_interleave mapped to the upstream innodb_numa_interleave variable in 5.6.27-75.0 were reverted to their original implementation due to upstream variant being less effective in memory allocation. Now buffer pool is allocated with MAP_POPULATE, forcing NUMA interleaved allocation at the buffer pool initialization time. Bug fixed #3967.
  • audit_log_include_accounts variable did not take effect if placed in my.cnf configuration file, while still working as intended if set dynamically. Bug fixed #3867.
  • Synchronization between between innodb_kill_idle_transaction and kill_idle_transaction system variables was broken because of the regression in Percona Server 5.6.40-83.2. Bug fixed #3955.
  • Executing the SHOW GLOBAL STATUS expression could cause “data drift” on global status variables in case of a query rollback: the variable, being by its nature a counter and allowing only an increase, could return to its previous value. Bug fixed #3951 (upstream #90351).
  • ALTER TABLE … COMMENT = … statement caused TokuDB to rebuild the whole table, which is not needed, as only FRM metadata should be changed. The fix was provided as a contribution by Fungo Wang. Bugs fixed #4280 and #4292.
  • A number of Percona Server 8.0 TokuDB fixes have been backported to Percona Server 5.6 in preparation for using MySQL 8.0. Bugs fixed  #4379#4380#4387#4378#4383#4384#4386#4382, #4391#4390#4392, and #4381.
TokuDB Changes and Fixes
  • Two new variables, tokudb_enable_fast_update and tokudb_enable_fast_upsert, were introduced to facilitate the TokuDB fast updates feature, which involves queries optimization to avoid random reads during their execution. Bug fixed #4365.
  • A data race was fixed in minicron utility of the PerconaFT, as a contribution by Rik Prohaska. Bug fixed #4281.
  • Row count and cardinality decrease to zero took place after long-running REPLACE load, ending up with full table scans for any action.
Other Bugs Fixed
  • #3818 “Orphaned file mysql-test/suite/innodb/r/percona_innodb_kill_idle_trx.result”
  • #3926 “Potentially truncated bitmap file name in log_online_open_bitmap_file_read_only() (storage/innobase/log/log0online.cc)”
  • #2204 “Test main.audit_log_default_db is unstable”
  • #3767 “Fix compilation warnings/errors with clang”
  • #3773 “Incorrect key file for table frequently for tokudb”
  • #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”
  • #3887 “TokuDB does not compile with -DWITH_PERFSCHEMA_STORAGE_ENGINE=OFF”
  • #4388 “5.7 code still has TOKU_INCLUDE_OPTION_STRUCTS which is a MariaDB specific construct”
  • #4265 “TDB-114 (Change use of MySQL HASH to unordered_map) introduces memory leak”
  • #4277 “memory leaks in TDB-2 and TDB-89 tests”
  • #4276 “Data race on cache table attributes detected by the thread sanitizer”
  • #4451 “Implement better compression algo testing”
  • #4469 “variable use out of scope bug in get_last_key test detected by ASAN in clang 6”
  • #4470 “the cachetable-simple-pin-nonblocking-cheap test occasionally fails due to a locking conflict with the cachetable evictor”
  • #1131 “User_var_log_event::User_var_log_event(const char*, uint, const Format_description_log_event*): Assertion `(bytes_read == (data_written – ((old_pre_checksum_fd || (description_event->checksum_alg == BINLOG_CHECKSUM_ALG_OFF)) ? 0 : 4))) || ((“.

Find the release notes for Percona Server for MySQL 5.6.40-84.0 in our online documentation. Report bugs in the Jira bug tracker.

The post Percona Server for MySQL 5.6.40-84.0 Is Now Available appeared first on Percona Database Performance Blog.

Unrecoverable encrypted data corruption if key vault mount point ends with a slash.

Lastest Forum Posts - May 30, 2018 - 6:16am
Hello again,

while I am in "issues reporting Zen mode", I'll add this.

It's not a real Percona Server error, but if a system administrator mistakenly forgets a slash at the end of the secret mount point, a disaster happens and all encrypted data on the whole server is immediately lost.
I'd classify it as "small usability issue with large consequences". Since it's very easy to add one or so line of code to check for a trailing slash, I suggest you do it and save admins some very bad days.

Let me show by example. Here are two anonymised plugin configurations. Spot the difference!

Code: vault_url = https://kv01.domain.tld:8200 secret_mount_point = secret/mysql/customer01/lxsrv01/ token = 0188a1a9-<censored> vault_ca = /usr/share/ca-certificates/mycerts/priv-cert-rootCA.crt Code: vault_url = https://kv01.domain.tld:8200 secret_mount_point = secret/mysql/customer01/lxsrv01 token = 0188a1a9-<censored> vault_ca = /usr/share/ca-certificates/mycerts/priv-cert-rootCA.crt The first configuration will destroy all data on next server reboot. The last works fine.

The difference and what it does
The difference is tiny and happened to me for real: I pasted the secret_mount_point (by the way, Linux shell tends to add a traling slash to paths) and it ends with a "/".
Then I happily encrypted tables. No error reported or logged by the key vault whatsoever.
Then one day I rebooted the server and the key vault plugin put errors on the log, stating it could not retrieve the keys from the HashiCorp Key Vault server.
Whatever you do, you'll never recover the data, because the tables were encrypted, the keys were generated but never stored on the key vault server.

By studying HashiCorp Key Vault logs, at next server / Percona daemon reboot, you can see the key vault plug in opening a connection and reading exactly that path. Sadly, due to the trailing slash, the Key Vault server never wrote the keys. Even querying the same path, it'll return an empty keys list and thus the "link" between Percona server and Key Vault server is severed.

Now, I don't know whether it should be the Key Vault server or the Percona plugin to show "hey dude, you passed a trailing slash, don't do it!" but as of now, it silently fails and then hell happens.
Visit Percona Store


General Inquiries

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