Emergency

Lack of free Swap Space

Lastest Forum Posts - August 18, 2015 - 1:25am
HI,

I'm having in 2 servers some problems with swap space. I've added more swap space, but it seems that mysql percona server is using it all.

If I restart mysql , everything goes ok again.

How can I solve this ?

Thanks.

Verify Node after error during shutdown

Lastest Forum Posts - August 17, 2015 - 3:50pm
I recently adopted 3 mysql servers and my back ground is in Oracle an mssql so please forgive my terminology or lack of knowledge of mysql. We recently shutdown three of our mysql nodes for maintenance. Nodes 2 and 3 shutdown just fine, but node 1 took 20+ minutes to shutdown with an error (sorry, I do not have the error message). These are synchronized nodes. My question is, what are the steps to:
1) Verify the database integrity on node 1
2) How do you join and sync node 1 to the remaining nodes
  • Database client version: libmysql - 5.5.41
  • Server type: Percona Server
  • CentOS
Thanks!

Question about sysdate() query

Lastest Forum Posts - August 17, 2015 - 1:03pm
Server version: 5.6.25-73.1-log Percona Server (GPL), Release 73.1, Revision 07b797f on Centos 6

I tried to upload a 10k screen capture unsuccessfully, but here is my question. when I use the CLI to run the query SELECT sysdate(), SLEEP(5), sysdate(); I get the same time and date to the second. even with a 60 second sleep.

when I manually run successive CLI queries of queries of SELECT sysdate();, I get identical results if run quickly, if I pause between individual queries of about 5 seconds then I get the results I expected to get different values.

Is this because the query is cached?

PXC 5.6.24 - All cluster nodes got signal 11 after mass update query

Lastest Forum Posts - August 17, 2015 - 6:10am
Our cluster have 6 nodes and 1 arbitrator.
Each server's average qps is 10000.
Basic environment: Ubuntu 14.04.2 TLS + Intel Xeon E5-2620v2 * 2 + 8 * 300GB SAS build as RAID 10 + Percona XtraDB Cluster 5.6.24-25.11 + galera 3.11.1 + 1G network

When somebody run a bad query like "UPDATE large_table SET some_column = 'new_value';", all nodes lock write query and wait for unlock.
After this query update finished, some nodes rejoin the cluster. Then mysqld got signal 11 one by one, finally every nodes died.

How to explain this problem?


mysqld got signal 11 log, and every nodes got the same result:
: Query (xxxx): is an invalid pointer detail log:
: 10:01:45 UTC - mysqld got signal 11 ; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. Please help us make Percona XtraDB Cluster better by reporting any bugs at https://bugs.launchpad.net/percona-xtradb-cluster key_buffer_size=8388608 read_buffer_size=131072 max_used_connections=2145 max_threads=4098 thread_count=57 connection_count=24 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 1640430 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. Thread pointer: 0x37fe0300 Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... stack_bottom = 7f6ca6a69e00 thread_stack 0x40000 /usr/sbin/mysqld(my_print_stacktrace+0x2c)[0x91b54c] /usr/sbin/mysqld(handle_fatal_signal+0x461)[0x698ba1] /lib/x86_64-linux-gnu/libpthread.so.0(+0x10340)[0x7f76d3600340] /lib/x86_64-linux-gnu/libc.so.6(strlen+0x2a)[0x7f76d2a93aea] /usr/sbin/mysqld(_Z21mysql_execute_commandP3THD+0x3e7)[0x71af47] /usr/sbin/mysqld(_ZN18Prepared_statement7executeEP6Stringb+0x3a5)[0x737085] /usr/sbin/mysqld[0x737371] /usr/sbin/mysqld(_Z19mysqld_stmt_executeP3THDPcj+0x32f)[0x7379af] /usr/sbin/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcj+0x1c8c)[0x725cfc] /usr/sbin/mysqld(_Z10do_commandP3THD+0x1e4)[0x727524] /usr/sbin/mysqld(_Z24do_handle_one_connectionP3THD+0x172)[0x6f0432] /usr/sbin/mysqld(handle_one_connection+0x40)[0x6f0620] /usr/sbin/mysqld(pfs_spawn_thread+0x143)[0xb42bd3] /lib/x86_64-linux-gnu/libpthread.so.0(+0x8182)[0x7f76d35f8182] /lib/x86_64-linux-gnu/libc.so.6(clone+0x6d)[0x7f76d2b0547d] Trying to get some variables. Some pointers may be invalid and cause the dump to abort. Query (7f693d99ad80): is an invalid pointer Connection ID (thread ID): 1073150170 Status: NOT_KILLED my.cnf
: # Default values. [mysqld_safe] flush_caches numa_interleave # # [mysqld] back_log = 65535 binlog_format = ROW character_set_server = utf8mb4 collation_server = utf8mb4_general_ci datadir = /var/lib/mysql default_storage_engine = InnoDB expand_fast_index_creation = 1 expire_logs_days = 7 innodb_autoinc_lock_mode = 2 innodb_buffer_pool_instances = 16 innodb_buffer_pool_populate = 1 innodb_buffer_pool_size = 32G innodb_data_file_path = ibdata1:64M;ibdata2:64M:autoextend innodb_file_format = Barracuda innodb_file_per_table innodb_flush_log_at_trx_commit = 2 innodb_flush_method = O_DIRECT innodb_io_capacity = 1600 innodb_large_prefix innodb_locks_unsafe_for_binlog = 1 innodb_log_file_size = 64M innodb_print_all_deadlocks = 1 innodb_read_io_threads = 64 innodb_stats_on_metadata = FALSE innodb_support_xa = FALSE innodb_write_io_threads = 64 log-bin = mysqld-bin log-queries-not-using-indexes log-slave-updates long_query_time = 1 max_allowed_packet = 64M max_connect_errors = 4294967295 max_connections = 4096 min_examined_row_limit = 1000 performance-schema-instrument='%=ON' port = 3306 relay-log-recovery = TRUE skip-name-resolve slow_query_log = 1 slow_query_log_timestamp_always = 1 table_open_cache = 4096 thread_cache = 1024 tmpdir = /srv/tmp transaction_isolation = REPEATABLE-READ updatable_views_with_limit = 0 user = mysql wait_timeout = 60 # # Galera wsrep_cluster_address = gcomm://ip1:4567,ip2:4567,ip3:4567 wsrep_cluster_name = XXX wsrep_provider = /usr/lib/libgalera_smm.so wsrep_provider_options = "gcache.size=4G" wsrep_slave_threads = 32 wsrep_sst_auth = "root:rootPASSWORD" wsrep_sst_method = xtrabackup-v2 # # XXX You *MUST* change! server-id =

MySQL is crashing: a support engineer’s point of view

Latest MySQL Performance Blog posts - August 17, 2015 - 4:38am

In MySQL QA Episode #12, “MySQL is Crashing, now what?,” Roel demonstrated how to collect crash-related information that will help Percona discover what the issue is that you are experiencing, and fix it.

As a Support Engineer I (Sveta) am very happy to see this post – but as a person who better understands writing than recording – I’d like to have same information, in textual form. We discussed it, and decided to do a joint blog post. Hence, this post

If you haven’t seen the video yet, or you do not have any experience with gdb, core files and crashes, I highly recommend to watch it first.

Once you have an idea of why crashes happen, what to do after it happens in your environment, and how to open a Support issue and/or a bug report, you’re ready for the next step: which information do you need to provide? Note that the more complete and comprehensive information you provide, the quicker the evaluation and potential fix process will go – it’s a win-win situation!

At first we need the MySQL error log file. If possible, please send us the full error log file. Often users like to send only the part which they think is relevant, but the error log file can contain other information, recorded before the crash happened. For example, records about table corruptions, lack of disk space, issues with InnoDB dictionary, etc.

If your error log is quite large, please note it would compress very well using a standard compression tool like gzip. If for some reason you cannot send the full error log file, please sent all lines, written after the words “mysqld: ready for connections” (as seen the last time before the actual crash), until the end of the error log file (alternatively, you can also search for rows, started with word “Version:”). Or, if you use scripts (or mysqld_safe) which automatically restart MySQL Server in case of disaster, obviously please search for the one-previous server start after the crash.

An example which includes an automatic restart as mentioned above:

2015-08-03 14:24:03 9911 [Note] /home/sveta/SharedData/Downloads/5.6.25/bin/mysqld: ready for connections. Version: '5.6.25-73.1-log' socket: '/tmp/mysql_sandbox21690.sock' port: 21690 Percona Server (GPL), Release 73.1, Revision 07b797f 2015-08-03 14:24:25 7f5b193f9700 InnoDB: Buffer pool(s) load completed at 150803 14:24:25 11:25:12 UTC - mysqld got signal 4 ; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. Please help us make Percona Server better by reporting any bugs at http://bugs.percona.com/ key_buffer_size=268435456 read_buffer_size=131072 max_used_connections=1 max_threads=216 thread_count=1 connection_count=1 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 348059 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. Thread pointer: 0x0 Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... stack_bottom = 0 thread_stack 0x40000 /home/sveta/SharedData/Downloads/5.6.25/bin/mysqld(my_print_stacktrace+0x2e)[0x8dd38e] /home/sveta/SharedData/Downloads/5.6.25/bin/mysqld(handle_fatal_signal+0x491)[0x6a5dc1] /lib64/libpthread.so.0(+0xf890)[0x7f5c58ac8890] /lib64/libc.so.6(__poll+0x2d)[0x7f5c570fbc5d] /home/sveta/SharedData/Downloads/5.6.25/bin/mysqld(_Z26handle_connections_socketsv+0x1c2)[0x5f64c2] /home/sveta/SharedData/Downloads/5.6.25/bin/mysqld(_Z11mysqld_mainiPPc+0x1b5d)[0x5fd87d] /lib64/libc.so.6(__libc_start_main+0xf5)[0x7f5c57040b05] /home/sveta/SharedData/Downloads/5.6.25/bin/mysqld[0x5f10fd] You may download the Percona Server operations manual by visiting http://www.percona.com/software/percona-server/. You may find information in the manual which will help you identify the cause of the crash. 150803 14:25:12 mysqld_safe Number of processes running now: 0 150803 14:25:12 mysqld_safe mysqld restarted /home/sveta/SharedData/Downloads/5.6.25/bin/mysqld: /lib64/libssl.so.1.0.0: no version information available (required by /home/sveta/SharedData/Downloads/5.6.25/bin/mysqld) /home/sveta/SharedData/Downloads/5.6.25/bin/mysqld: /lib64/libcrypto.so.1.0.0: no version information available (required by /home/sveta/SharedData/Downloads/5.6.25/bin/mysqld) 2015-08-03 14:25:12 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details). 2015-08-03 14:25:12 0 [Note] /home/sveta/SharedData/Downloads/5.6.25/bin/mysqld (mysqld 5.6.25-73.1-log) starting as process 10038 ... 2015-08-03 14:25:12 10038 [Warning] Buffered warning: Changed limits: max_open_files: 1024 (requested 50005) 2015-08-03 14:25:12 10038 [Warning] Buffered warning: Changed limits: max_connections: 214 (requested 10000) 2015-08-03 14:25:12 10038 [Warning] Buffered warning: Changed limits: table_open_cache: 400 (requested 4096) 2015-08-03 14:25:12 10038 [Note] Plugin 'FEDERATED' is disabled. 2015-08-03 14:25:12 10038 [Note] InnoDB: Using atomics to ref count buffer pool pages 2015-08-03 14:25:12 10038 [Note] InnoDB: The InnoDB memory heap is disabled 2015-08-03 14:25:12 10038 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins 2015-08-03 14:25:12 10038 [Note] InnoDB: Memory barrier is not used 2015-08-03 14:25:12 10038 [Note] InnoDB: Compressed tables use zlib 1.2.3 2015-08-03 14:25:12 10038 [Note] InnoDB: Using Linux native AIO 2015-08-03 14:25:12 10038 [Note] InnoDB: Using CPU crc32 instructions 2015-08-03 14:25:12 10038 [Note] InnoDB: Initializing buffer pool, size = 4.0G 2015-08-03 14:25:13 10038 [Note] InnoDB: Completed initialization of buffer pool 2015-08-03 14:25:13 10038 [Note] InnoDB: Highest supported file format is Barracuda. 2015-08-03 14:25:13 10038 [Note] InnoDB: The log sequence numbers 514865622 and 514865622 in ibdata files do not match the log sequence number 514865742 in the ib_logfiles! 2015-08-03 14:25:13 10038 [Note] InnoDB: Database was not shutdown normally! 2015-08-03 14:25:13 10038 [Note] InnoDB: Starting crash recovery. 2015-08-03 14:25:13 10038 [Note] InnoDB: Reading tablespace information from the .ibd files... 2015-08-03 14:25:14 10038 [Note] InnoDB: Restoring possible half-written data pages 2015-08-03 14:25:14 10038 [Note] InnoDB: from the doublewrite buffer... InnoDB: Last MySQL binlog file position 0 150866, file name mysql-bin.000006 2015-08-03 14:25:16 10038 [Note] InnoDB: 128 rollback segment(s) are active. 2015-08-03 14:25:16 10038 [Note] InnoDB: Waiting for purge to start 2015-08-03 14:25:16 10038 [Note] InnoDB: Percona XtraDB (http://www.percona.com) 5.6.25-rel73.1 started; log sequence number 514865742 2015-08-03 14:25:16 7f67ceff9700 InnoDB: Loading buffer pool(s) from .//ib_buffer_pool 2015-08-03 14:25:16 10038 [Note] Recovering after a crash using mysql-bin 2015-08-03 14:25:16 10038 [Note] Starting crash recovery... 2015-08-03 14:25:16 10038 [Note] Crash recovery finished. 2015-08-03 14:25:17 10038 [Note] RSA private key file not found: /home/sveta/sandboxes/rsandbox_Percona-Server-5_6_25/master/data//private_key.pem. Some authentication plugins will not work. 2015-08-03 14:25:17 10038 [Note] RSA public key file not found: /home/sveta/sandboxes/rsandbox_Percona-Server-5_6_25/master/data//public_key.pem. Some authentication plugins will not work. 2015-08-03 14:25:17 10038 [Note] Server hostname (bind-address): '127.0.0.1'; port: 21690 2015-08-03 14:25:17 10038 [Note] - '127.0.0.1' resolves to '127.0.0.1'; 2015-08-03 14:25:17 10038 [Note] Server socket created on IP: '127.0.0.1'. 2015-08-03 14:25:17 10038 [Warning] 'proxies_priv' entry '@ root@thinkie' ignored in --skip-name-resolve mode. 2015-08-03 14:25:17 10038 [Note] Event Scheduler: Loaded 0 events 2015-08-03 14:25:17 10038 [Note] /home/sveta/SharedData/Downloads/5.6.25/bin/mysqld: ready for connections. Version: '5.6.25-73.1-log' socket: '/tmp/mysql_sandbox21690.sock' port: 21690 Percona Server (GPL), Release 73.1, Revision 07b797f

Usually the error log file contains the actual query which caused the crash. If it does not and you know the query (for example, if your application logs errors / query problems), please send us this query too. Additionally, if possible, include the CREATE TABLE statements for any tables mentioned in the query. Actually working with the query is the first thing which you can do to resolve the issue: try to run this query (on a non-production/test server which is as close a copy to your production server as possible), to ensure it crashes MySQL Server consistently. If so, you can try and create a temporary workaround by avoiding this kind of queries in your application.

If you have doubts as to which query caused the crash, but have the general query log turned ON, you can use utilityparse_general_log.pl  from percona-qa to create a potential test case. Simply execute:

$ sudo yum install bzr $ cd ~ $ bzr branch lp:percona-qa $ cp /path_that_contains_your_general_log/your_log_file.sql ~ $ ~/percona-qa/parse_general_log.pl -i./your_log_file.sql -o./output.sql

And subsequently execute output.sql against mysqld on a non-production test server to see if a crash is produced. Alternatively, you may mail us the output.sql file (provided your company privacy etc. policies allow for this). If you want to try and reduce the testcase further, please see QA Episode #7 on reducing testcases.

The next thing which we need is a backtrace. You usually have a simple backtrace showing in the error log directly after crash. An example (extracted from an error log) of what this looks like:

stack_bottom = 0 thread_stack 0x40000 /home/sveta/SharedData/Downloads/5.6.25/bin/mysqld(my_print_stacktrace+0x2e)[0x8dd38e] /home/sveta/SharedData/Downloads/5.6.25/bin/mysqld(handle_fatal_signal+0x491)[0x6a5dc1] /lib64/libpthread.so.0(+0xf890)[0x7f5c58ac8890] /lib64/libc.so.6(__poll+0x2d)[0x7f5c570fbc5d] /home/sveta/SharedData/Downloads/5.6.25/bin/mysqld(_Z26handle_connections_socketsv+0x1c2)[0x5f64c2] /home/sveta/SharedData/Downloads/5.6.25/bin/mysqld(_Z11mysqld_mainiPPc+0x1b5d)[0x5fd87d] /lib64/libc.so.6(__libc_start_main+0xf5)[0x7f5c57040b05] /home/sveta/SharedData/Downloads/5.6.25/bin/mysqld[0x5f10fd]

Note that the above backtrace is mangled. You can send us the file like this (we can demangle it). However, if you want to work with it yourself more comfortably you can unmangle it with help ofc++filt  utility:

sveta@linux-85fm:~/sandboxes/rsandbox_Percona-Server-5_6_25> cat master/data/msandbox.err | c++filt ... terribly wrong... stack_bottom = 0 thread_stack 0x40000 /home/sveta/SharedData/Downloads/5.6.25/bin/mysqld(my_print_stacktrace+0x2e)[0x8dd38e] /home/sveta/SharedData/Downloads/5.6.25/bin/mysqld(handle_fatal_signal+0x491)[0x6a5dc1] /lib64/libpthread.so.0(+0xf890)[0x7f5c58ac8890] /lib64/libc.so.6(__poll+0x2d)[0x7f5c570fbc5d] /home/sveta/SharedData/Downloads/5.6.25/bin/mysqld(handle_connections_sockets()+0x1c2)[0x5f64c2] /home/sveta/SharedData/Downloads/5.6.25/bin/mysqld(mysqld_main(int, char**)+0x1b5d)[0x5fd87d] /lib64/libc.so.6(__libc_start_main+0xf5)[0x7f5c57040b05] /home/sveta/SharedData/Downloads/5.6.25/bin/mysqld[0x5f10fd] ...

Now the backtrace looks much nicer. When sending error log reports to us, please to not use c++filt on them before sending. We have a list of known bugs, and to scan the known bugs list we need to receive the error log unaltered.

You can also turn core files ON. Core files are memory dump files, created when a process crashes. They are very helpful for debugging, because they contain not only the backtrace of crashing thread, but backtraces of all threads, and much of what was in memory at the time the crash occurred.

Sidenote: Please note it is always a good idea to have the debuginfo (for example Percona-Server-56-debuginfo.x86_64 from the Percona Repository) package installed. This package provides the debugging symbols for Percona server (there are similar packages for other distributions) and ensures that stack traces are more readable and contain more information. It is important to ensure that you have the right package version etc. as symbols are different for each version of mysqld. If you have installed Percona Server from our repository, you can simply install the debuginfo package, the version will be correct, and the package will be auto-updated when Percona Server is updated.

By default the MySQL server does not create core files. To let it do so, you can follow instructions from the “GDB Cheat sheet” (page 2 under header ‘Core Files Cheat Sheet’). In short:

  • Add the option core-file under the [mysqld] section of your configuration file
  • Tune your OS options, so it allows mysqld to create core files as described in the cheat sheet
    echo "core.%p.%e.%s" > /proc/sys/kernel/core_pattern ulimit -c unlimited sudo sysctl -w fs.suid_dumpable=2

    Note: some systems use ‘kernel’ instead of ‘fs’: use kernel.suid_dumpable=2 instead if you get key or file warnings/errors.
  • Restart the MySQL server

Besides the core file which is generated by the MySQL server, you can also setup the operating system to dump a core file. These are two different core files (for a single crash of the mysqld binary), and the amount of information contained within may differ. The procedure above shows how to setup the one for the MySQL Server alone.

If you like your operating system to dump a core file as well, please see the MySQL QA Episode #12 video. Also, please note that changing the ulimit and fs.suid_dumpable settings may alter the security of your system. Please read more about these options online before using them or leaving them permanently on a production system.

Once a core file is generated, you can use the GDB utility to debug the core file (also called a ‘coredump’). GDB allows you to better resolve backtraces (also called ‘stack traces’ or ‘stacks’), for example by taking a back trace of all threads instead of only the crashing threads. This is off-course better then the single backtrace available in the error log file. To use GDB, you need to first start it:

gdb /path_to_mysqld /path_to_core

/path_to_core is usually your data directory (for coredumps produced by mysqld as a result of using the –core-file option in your my.cnf file), or sometimes in the same directory where the crashing binary is (for coredumps produced by the OS) – though you can specify an alternate fixed location for OS coredumps as the cheat sheet. Note that OS generated dumps are sometimes written with very few privileges and so you may have to use chown/chmod/sudo to access it.

Once you’re into GDB, and all looks fine, run the commandsbt(backtrace) and  bt thread apply all(get backtrace for all threads) to get the stacktraces. bt should more or less match the backtrace seen in the error log, but sometimes this may differ.

For us, ideally you would run the following commands in GDB (as seen in the cheat sheet):

set trace-commands on set pagination off set print pretty on set print array on set print array-indexes on set print elements 4096 set logging file gdb_standard.txt set logging on thread apply all bt set logging off set logging file gdb_full.txt set logging on thread apply all bt full

After you run these commands and have existed ( quit ) GDB, please send us the ./gdb_standard.txt  and./gdb_full.txt files.

Finally, we would be happy to receive the actual core file from you. In terms of security and privacy, please note that a core file often contains fragments, or sections, or even the full memory of your server.

However, a core file without mysqld is useless, thus please add the mysqld binary together with the core file. If you use our compiled binaries you can also specify the exact package and file name which you downloaded, but if you use a self-compiled version of the server, the mysqld binary is required for us to resolve backtrace and other necessary information (like varialbes) from your core file. Generally speaking, it’s easier just to sent mysqld along.

Also, it would be really nice, if you send us library files which are dynamically linked with mysqld you use. You can get them by using a tool, calledldd_files.sh  from the percona-qa suite. Just create a temporary directory, copy yourmysqld  binary to it and run the tool on it:

sveta@thinkie:~/tmp> wget http://bazaar.launchpad.net/~percona-core/percona-qa/trunk/download/head:/ldd_files.sh-20150713030145-8xdk0llrd3skfsan-1/ldd_files.sh sveta@thinkie:~/tmp> mkdir tmp sveta@thinkie:~/tmp> cd tmp/ sveta@thinkie:~/tmp/tmp> cp /home/sveta/SharedData/Downloads/5.6.25/bin/mysqld . # Copy of your mysqld sveta@thinkie:~/tmp/tmp> ../ldd_files.sh mysqld # Run the tool on it cp: cannot stat ‘./mysqld: /lib64/libssl.so.1.0.0: no version information available’: No such file or directory # Ignore cp: cannot stat ‘./mysqld: /lib64/libcrypto.so.1.0.0: no version information available’: No such file or directory # Ignore sveta@thinkie:~/tmp/tmp> ls ld-linux-x86-64.so.2 libaio.so.1 libcrypto.so.1.0.0 libcrypt.so.1 libc.so.6 libdl.so.2 libgcc_s.so.1 libm.so.6 libpthread.so.0 librt.so.1 libssl.so.1.0.0 libstdc++.so.6 libz.so.1 mysqld # Files to supply in combination with mysqld

These library files are needed if case some of the frames from the stacktrace are system calls, so that our developers can resolve/check those frames also.

Summary

If you hit a crash, please send us (in order of preference, but even better ‘all of these’):

  • The error log file (please sent it unaltered – i.e. before c++filt was executed – which allows us to scan for known bugs)
  • The crashing query (from your application logs and/or extracted from the core file – ref the query extraction blog post)
    • Please include the matching CREATE TABLE statements
  • A resolved backtrace (and/or preferably the ./gdb_standard.txt and ./gdb_full.txt files)
  • The core file together with the mysqld binary and preferably the ldd files

Thank you!

 

The post MySQL is crashing: a support engineer’s point of view appeared first on MySQL Performance Blog.

pt checksum differente number of chunks in the replica

Lastest Forum Posts - August 17, 2015 - 1:26am
Hello, i'm running pt checksum regularly and i noticed for some tables there is a different number of chunks in the master and in the replica.
I'm using --replicate heartbeat.checksums and i see in checksums a differet number of rows for some table, if i compare the crc column i don't see any difference.
also pt-table-sync doesn't show any difference.
can you explain ?
thank you
Marco

Cluster seems properly synced, but clustercheck script fails

Lastest Forum Posts - August 15, 2015 - 10:15am
Hello,

I have a completely new install of Percona Cluster 5.6 on CentOS 6.6 along the lines of the setup in the following blog post:

https://www.percona.com/blog/2012/06...-with-haproxy/

There are three hosts: each has a MySQL node, plus an HAProxy instance using the standard "clustercheck" script to health-check the local nodes via xinetd. All three hosts share a floating ip address via keepalived.

Every part of the stack except the clustercheck script seems to be working properly.

I.e, I get output like the following on all three hosts:

$ mysql -e "SHOW GLOBAL STATUS LIKE 'wsrep_cluster_%'"
+--------------------------+--------------------------------------+
| Variable_name | Value |
+--------------------------+--------------------------------------+
| wsrep_cluster_conf_id | 9 |
| wsrep_cluster_size | 3 |
| wsrep_cluster_state_uuid | dca6d39f-42dc-11e5-946f-b7d45ebb1bba |
| wsrep_cluster_status | Primary |
+--------------------------+--------------------------------------+

I also created users on one of the nodes, and I can see that it propagated as it should to mysql.user on all cluster members:

mysql> SELECT Host, User FROM mysql.user;
+----------------------+----------+
| Host | User |
+----------------------+----------+
| % | agunn |
| % | ahough |
| % | bharden |
| % | clott |
| % | gloy |
| % | joomla |
| % | mcdba |
| 127.0.0.1 | root |
| ::1 | root |
| localhost | mysqlchk |
| localhost | root |
| localhost | sstuser |
+----------------------+----------+
12 rows in set (0.00 sec)

In HAProxy, all three nodes are failing the health check via xinetd.

I know the xinetd configuration is working because I can reproduce the failed health checks via curl:

$ curl http://joomdb01:9200/
Percona XtraDB Cluster Node is not synced or non-PRIM.

And when I run the clustercheck script manually at the command line outside of xinted, it returns the same output:

$ /usr/bin/clustercheck [actual_password_redacted] 1 /var/log/mysqlchk 0 /etc/my.cnf
HTTP/1.1 503 Service Unavailable
Content-Type: text/plain
Connection: close
Content-Length: 57

Percona XtraDB Cluster Node is not synced or non-PRIM.

Basically, it looks like everything except the clustercheck script is working as intended. Any ideas as to where I should be looking?

Thanks,

-Cal

Can Percona 5.5 be installed on Windows?

Lastest Forum Posts - August 13, 2015 - 10:00pm
I have windows computer. I'm wondering if percona 5.5 can be installed in windows computer too?

TokuDB -> Percona Server upgrade path?

Lastest Forum Posts - August 13, 2015 - 9:58pm
Hi,
Now that Tokutek's TokuDB builds are no longer available, what is the proper upgrade path? We have servers running some old TokuDB versions, and some newer versions (but none running the latest). Is it a drop-in replacement, or something else altogether?

The language of compression

Latest MySQL Performance Blog posts - August 13, 2015 - 12:19pm

Leif Walsh will talk about the language of compression at Percona Live Amsterdam

Storage. Everyone needs it. Whether your data is in MySQL, a NoSQL, or somewhere in the cloud, with ever-growing data volumes – along with the need for SSDs to cut latency and replication to provide insurance – an organization’s storage footprint is an important place to look for savings. That’s where compression comes in (squeeze!) to save disk space.

Two Sigma software engineer Leif Walsh speaks the language of compression. Fluently. In fact, he’ll be speaking on

that exact subject September 22 during the Percona Live conference in Amsterdam.

I asked him about his talk, and about Amsterdam, the other day. Here’s what he had to say.

* * *

Tom: Hi Leif, how will your talk help IT decision-makers cut through the marketing mumbo-jumbo on what’s important to focus on and what is not
Leif: My talk will have three lessons aimed at those making storage decisions for their company:

  1. What are the key factors to consider when evaluating storage options, and how can they affect your bottom line?  This is not only how storage tech influences your hardware, operations, and management costs, but also how it can facilitate new development initiatives and cut time-to-market for your products.
  2. How should you read benchmarks and marketing materials about storage technology?  You’ll learn what to look for in promotional material, and how to think critically about whether that material is applicable to your business needs.
  3. What’s the most effective way to communicate with storage vendors about your application’s requirements?  A lot of time can be spent in the early stages of a relationship in finding a common language for users and vendors to have meaningful discussions about users’ needs and vendors’ capacity to meet those needs.  With the tools you’ll learn in my talk, you’ll be able to accelerate quickly to the high-bandwidth conversations you need to have in order to make the right decision, and consequently, you’ll be empowered to evaluate more choices to find the best one faster.

Tom: In addition to IT decision-makers, who else should attend your session and what will they take away afterward?
Leif: My talk is primarily about the language that everyone in the storage community should be using to communicate. Therefore, storage vendors should attend to get ideas for how  to express their benchmarks and their system’s properties more effectively, and application developers and operations people will learn strategies for getting better support and for making a convincing case to the decision makers in their own company.

Tom: Which session(s) are you most looking forward to besides your own?
Leif: Sam Kottler is a good friend and an intensely experienced systems engineer with a dynamic and boisterous personality, so I can’t wait to hear more about his experiences with Linux tuning.

As one of the original developers of TokuMX, I’ll absolutely have to check out Stephane’s talk about it, but I promise not to heckle. Charity Majors is always hilarious and has great experiences and insights to share, so I’ll definitely check out her talk too.

The post The language of compression appeared first on MySQL Performance Blog.

MySQL Quality Assurance: A Vision for the Future by Roel Van de Paar (Final Episode 13)

Latest MySQL Performance Blog posts - August 13, 2015 - 10:13am

Welcome to the final – but most important – episode in the MySQL QA Series.

In it, I present my vision for all MySQL Quality Assurance – for all distributions – worldwide.

Episode 13: A Better Approach to all MySQL Regression, Stress & Feature Testing: Random Coverage Testing & SQL Interleaving

1. pquery Review
2. Random Coverage Testing
3. SQL Interleaving
4. The past & the future

Presented by Roel Van de Paar. Full-screen viewing @ 720p resolution recommended

 

The post MySQL Quality Assurance: A Vision for the Future by Roel Van de Paar (Final Episode 13) appeared first on MySQL Performance Blog.

"Disk Read/Write Time per IO Request (ms)" - problem

Lastest Forum Posts - August 13, 2015 - 5:16am
Fresh Cacti 0.8.8f.
Fresh percona-monitoring-plugins-1.1.5

>>>>>>>>>>>>>>>>
RRDTool Command:

/usr/bin/rrdtool graph - \
--imgformat=PNG \
--start='-86400' \
--end='-300' \
--title='aten.test.rmv - Disk Read/Write Time per IO Request (ms)' \
--base='1024' \
--height='120' \
--width='500' \
--alt-autoscale-max \
--lower-limit='0' \
--vertical-label='' \
--slope-mode \
--font TITLE:10: \
--font AXIS:7: \
--font LEGEND:8: \
--font UNIT:7: \
DEF:a='/srv/www/cacti.test.rmv/htdocs/cacti-0.8.8f/rra/aten_test_rmv_disk_reads_122.rrd':'DISK_reads':AVE RAGE \
DEF:b='/srv/www/cacti.test.rmv/htdocs/cacti-0.8.8f/rra/aten_test_rmv_disk_reads_122.rrd':'DISK_writes':AV ERAGE \
CDEF:cdefa='c,a,/' \
CDEF:cdefe='d,b,/,-1,*' \
CDEF:cdeff='d,b,/' \
AREA:cdefa#75637EFF:'Read Time per IO' \
GPRINT:cdefa:LAST:' Cur\:%6.1lf%s' \
GPRINT:cdefa:AVERAGE:'Avg\:%6.1lf%s' \
GPRINT:cdefa:MAX:'Max\:%6.1lf%s\n' \
AREA:cdefe#81BFE0FF:'Write Time per IO' \
GPRINT:cdeff:LAST:'Cur\:%6.1lf%s' \
GPRINT:cdeff:AVERAGE:'Avg\:%6.1lf%s' \
GPRINT:cdeff:MAX:'Max\:%6.1lf%s\n'

RRDTool Says:
ERROR: invalid rpn expression in: c,a,/
>>>>>>>>>>>>>>>>>>>>>

Data Source [DISK_time_spnt_rdng]
The data source to use for this graph item.
and
Data Source [DISK_tme_spnt_wrtng]
The data source to use for this graph item.
is "none"


Other graphs are okay.

Master Slave Replication Issue - Seconds behind master

Lastest Forum Posts - August 13, 2015 - 1:04am
We have Master Slave Mysql Setup, Master & slave hardware configuration is same (8 Core,30 GB Memory). These servers are Hosted on AWS.

My slave frequently is getting behind master . The current value for Seconds_Behind_Master is 114701, Not sure on how to get this sync master & slave Immediately. I can see that .

Master status - mysql-bin.000262 | 514064392
Slave Status - mysql-bin.000258 | 285395655



Below is my my.cnf configuration from Mysql Slave.

[mysql]

# CLIENT #
port = 3306
socket = /var/lib/mysql/mysql.sock

[mysqld]

# GENERAL #
user = mysql
default-storage-engine = InnoDB
socket = /var/lib/mysql/mysql.sock
pid-file = /srv/mysql/mysql.pid
skip_name_resolve

# MyISAM #
key-buffer-size = 32M
myisam-recover = FORCE,BACKUP

# SAFETY #
max-allowed-packet = 16M
max-connect-errors = 1000000
# DATA STORAGE #
datadir = /srv/mysql/

# BINARY LOGGING #
log-bin = /srv/mysqllogs/mysql-bin
max_binlog_size = 512M
expire-logs-days = 4
sync-binlog = 1

# REPLICATION #
server-id = 2
read-only = 1
log-slave-updates = 1
relay-log = /srv/mysqllogs/relay-bin
slave-net-timeout = 60
sync-master-info = 1
sync-relay-log = 1
sync-relay-log-info = 1
slave-skip-errors = 1062
# CACHES AND LIMITS #
tmp-table-size = 128M
max-heap-table-size = 128M
query-cache-type = 1
query-cache-size = 512M
max-connections = 4096
thread-cache-size = 100
open-files-limit = 65535
table-definition-cache = 4096
table-open-cache = 10240
#slave_parallel_workers = 3


# INNODB #
innodb-flush-method = O_DIRECT
innodb-log-files-in-group = 2
innodb-log-file-size = 256M
innodb-flush-log-at-trx-commit = 0
innodb-file-per-table = 1
innodb-buffer-pool-size = 24G
# LOGGING #
log-error = /srv/mysqllogs/mysql-error.log
#general_log_file = /srv/mysqllogs/mysql.log
#general_log = 1
log_warnings = 2
#log-queries-not-using-indexes = 1
#slow-query-log = 1
#slow-query-log-file = /srv/mysqllogs/mysql-slow.log

#tmpdir = /media/ephemeral0/tmp/


Could some one please help me in identifying & resolving the issue.

CPU Spikes every 5 minutes with MySQL on RDS

Lastest Forum Posts - August 13, 2015 - 12:04am
Under a relatively write intensive load we are experiencing CPU spikes every 5 minutes using MySQL on RDS. Has anyone experienced something similar or have any idea what might be causing it?

MySQL Version: 5.6.22
Storage Engine: InnoDB

We experience the spikes on both M3 Medium and M3 Large instance types, with both magnetic and SSD disks, and they occur whether or not we have multi AZ enabled.

The more writes we have, the steeper the spikes are. However, the number of writes does not seem to impact the frequency. They continue a little while after we finish writing. They do not appear under a read only load.

Thanks!

Percona Live Amsterdam Discounted Pricing and Community Dinner!

Latest MySQL Performance Blog posts - August 12, 2015 - 2:37pm

The countdown is on for the annual Percona Live Data Performance Conference and Expo in Europe! This year the conference will be taking place in the great city of Amsterdam September 21-23rd. This three day conference will focus on the latest trends, news and best practices in the MySQL, NoSQL and Data in the Cloud markets, while looking forward to what’s on the long-term horizon within the global Data Performance industry. With 84 breakout sessions, 8 tutorial sessions and 6 keynotes, including a Data in the Cloud keynote panel, there will certainly be no lack of content.
Advanced Rate Registration ENDS August 16th so make sure to register now to secure the best price possible.

As it is a Percona Live Conference, there will certainly be no lack of FUN either!!!!

As tradition holds, there will be a Community Dinner. Tuesday night, September 22, Percona Live Diamond Sponsor Booking.com will be hosting the Community Dinner of the year at their very own headquarters located in historic Rembrandt Square in the heart of the city. After breakout sessions conclude, attendees will be picked up right outside of the venue and taken to booking.com’s headquarters by canal boats! This gives all attendees the opportunity to play “tourist” while viewing the beauty of Amsterdam from the water. Attendees will be dropped off right next to Booking.com’s office! Come and show your support for the community while enjoying dinner and drinks! Space is limited so make sure to sign up ASAP!

So don’t forgetregister for the conference and sign up for the community dinner before space is gone!
See you in Amsterdam!

The post Percona Live Amsterdam Discounted Pricing and Community Dinner! appeared first on MySQL Performance Blog.

upgrade percona 5.5 to latest version of 5.5

Lastest Forum Posts - August 12, 2015 - 8:06am
I am writing since we have a percona 5.5 master -slave setup on our system. We had performed the package upgrade on ubuntu and it upgraded the percona 5.5 client and server on the slave before the Master. Once the master was upgraded it could not be accessed unless direclty on the command line. If we stopped the slave mysql then it would connect to the Master. I am wondering what would be the best recommendations on how to upgrade the client and server of the same major version with updates.

master-master replication fails on "duplicate partition name" error

Lastest Forum Posts - August 12, 2015 - 1:05am
Hi,

Server version: 5.6.25-73.1-log Percona Server (GPL), Release 73.1, Revision 07b797f
High load database, master-master cross replication between two nodes on innodb tables, innodb_file_per_table is on, binary format is row.
At least one time at day, the both slaves fail to run on statement "Error 'Duplicate partition name p20150818' on query. Default database: 'db'. Query: 'ALTER TABLE fc_ticks REORGANIZE PARTITION pmax INTO ( PARTITION p20150818 VALUES LESS THAN(1439931600), PARTITION pmax VALUES LESS THAN( MAXVALUE ) )'
It's similar to https://bugs.mysql.com/bug.php?id=47343 and I can reproduce this error on my test nodes, but replication does not fail in my case.
Any ideas? I'm in stuck.



MySQL replication primer with pt-table-checksum and pt-table-sync

Latest MySQL Performance Blog posts - August 12, 2015 - 12:00am

MySQL replication is a process that allows you to easily maintain multiple copies of MySQL data by having them copied automatically from a master to a slave database.

It’s essential to make sure the slave servers have the same set of data as the master to ensure data is consistent within the replication stream. MySQL slave server data can drift from the master for many reasons – e.g. replication errors, accidental direct updates on slave, etc.

Here at Percona Support we highly recommend that our customers periodically run the pt-table-checksum tool to verify data consistency within replication streams. Specifically, after fixing replication errors on slave servers to ensure that the slave has identical data as its master. As you don’t want to put yourself in a situation where you need to failover to a slave server for some reason and you find different data on that slave server.

In this post, I will examine the pt-table-checksum and pt-table-sync tools usage from Percona Toolkit on different replication topologies. We often receive queries from customers about how to run these tools and I hope this post will help.

Percona Toolkit is a free collection of advanced command-line tools to perform a variety of MySQL server and system tasks that are too difficult or complex to perform manually.

One of those tools is pt-table-checksum, which works by dividing table rows into chunks of rows. The size of a chunk changes dynamically during the operation to avoid overloading the server. pt-table-checksum has many safeguards including variation into chunk size to make sure queries run in a desired amount of time.

pt-table-checksum verifies chunk size by running EXPLAIN query on each chunk. It also monitors slave server’s continuously in order to make sure replicas not falls too far behind and in this case tool pauses itself to allow slave to catch up. Along with that there are many other safeguards builtin and you can find all the details in this documentation

In my first example case, I am going to run pt-table-checksum against pair of replication servers – i.e. master having only one slave in replication topology. We will run pt-table-checksum tool on master server to verify data integrity on slave and in case If differences found by pt-table-checksum tool we will sync those changes on slave server via pt-table-sync tool.

I have created a dummy table under test database and inserted 10 records on master server as below:

mysql-master> create table dummy (id int(11) not null auto_increment primary key, name char(5)) engine=innodb; Query OK, 0 rows affected (0.08 sec) mysql-master> insert into dummy VALUES (1,'a'), (2,'b'), (3,'c'), (4,'d'), (5,'e'), (6,'f'), (7,'g'), (8,'h'), (9,'i'), (10,'j'); Query OK, 10 rows affected (0.00 sec) Records: 10 Duplicates: 0 Warnings: 0 mysql-master> select * from dummy; +------+------+ | id | name | +------+------+ | 1 | a | | 2 | b | | 3 | c | | 4 | d | | 5 | e | | 6 | f | | 7 | g | | 8 | h | | 9 | i | | 10 | j | +------+------+ 10 rows in set (0.00 sec)

Then I intentionally deleted a few records from the slave server to make it inconsistent with the master for the purpose of this post.

mysql-slave> delete from dummy where id>5; Query OK, 5 rows affected (0.03 sec) mysql-slave> select * from dummy; +----+------+ | id | name | +----+------+ | 1 | a | | 2 | b | | 3 | c | | 4 | d | | 5 | e | +----+------+ 5 rows in set (0.00 sec)

Now, in this case the master server has 10 records on the dummy table while the slave server has only 5 records missing records from id>5 – we will run pt-table-checksum at this point on the master server to see if the pt-table-checksum tool catches those differences.

[root@master]# pt-table-checksum --replicate=percona.checksums --ignore-databases mysql h=localhost,u=checksum_user,p=checksum_password TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE 07-11T18:30:13 0 1 10 1 0 1.044 test.dummy

This needs to be executed on the master. The user and password you specify will be used to not only connect to the master but the slaves as well. You need the following privileges for the pt-table-checksum mysql user:

mysql-master> GRANT REPLICATION SLAVE,PROCESS,SUPER, SELECT ON *.* TO `checksum_user`@'%' IDENTIFIED BY 'checksum_password'; mysql-master> GRANT ALL PRIVILEGES ON percona.* TO `checksum_user`@'%';

Earlier, in pt-table-checksum command, I used –replicate option which writes replication queries to mentioned table percona.checksums. Next I passed  –ignore-databases option which accepts comma separated list of databases to ignore. Moreover, –create-replicate-table and —empty-replicate-table options are “Yes” by default and you can specify both options explicitly if you want to create database table different then percona.checksums.

pt-table-checksum reported 1 DIFF which is number of chunks which are different from master on one or more slaves. You can find details about tabular columns e.g. TS, ERRORS and so on on documentation of pt-table-checksum. After that, I ran next command to identify which table has difference on slave.

[root@master]# pt-table-checksum --replicate=percona.checksums --replicate-check-only --ignore-databases mysql h=localhost,u=checksum_user,p=checksum_password Differences on slave TABLE CHUNK CNT_DIFF CRC_DIFF CHUNK_INDEX LOWER_BOUNDARY UPPER_BOUNDARY test.dummy 1 -5 1

In this command I used –replicate-check-only option which only reports the tables with having differences vice versa only checksum differences on detected replicas are printed. It doesn’t checksum any tables. It checks replicas for differences found by previous checksumming, and then exits.

You may also login to the slave and also execute below query to find out which tables have inconsistencies.

mysql-slave> SELECT db, tbl, SUM(this_cnt) AS total_rows, COUNT(*) AS chunks FROM percona.checksums WHERE ( master_cnt <> this_cnt OR master_crc <> this_crc OR ISNULL(master_crc) <> ISNULL(this_crc)) GROUP BY db, tbl;

pt-table-checksum identified test.dummy table is different on slave now we are going to use pt-table-sync tool to synchronize table data between MySQL servers.

[root@slave]# pt-table-sync --print --replicate=percona.checksums --sync-to-master h=localhost,u=checksum_user,p=checksum_password REPLACE INTO `test`.`dummy`(`id`, `name`) VALUES ('6', 'f') /*percona-toolkit src_db:test src_tbl:dummy src_dsn:P=3306,h=192.168.0.130,p=...,u=checksum_user dst_db:test dst_tbl:dummy dst_dsn:h=localhost,p=...,u=checksum_user lock:1 transaction:1 changing_src:percona.checksums replicate:percona.checksums bidirectional:0 pid:24683 user:root host:slave*/; REPLACE INTO `test`.`dummy`(`id`, `name`) VALUES ('7', 'g') /*percona-toolkit src_db:test src_tbl:dummy src_dsn:P=3306,h=192.168.0.130,p=...,u=checksum_user dst_db:test dst_tbl:dummy dst_dsn:h=localhost,p=...,u=checksum_user lock:1 transaction:1 changing_src:percona.checksums replicate:percona.checksums bidirectional:0 pid:24683 user:root host:slave*/; REPLACE INTO `test`.`dummy`(`id`, `name`) VALUES ('8', 'h') /*percona-toolkit src_db:test src_tbl:dummy src_dsn:P=3306,h=192.168.0.130,p=...,u=checksum_user dst_db:test dst_tbl:dummy dst_dsn:h=localhost,p=...,u=checksum_user lock:1 transaction:1 changing_src:percona.checksums replicate:percona.checksums bidirectional:0 pid:24683 user:root host:slave*/; REPLACE INTO `test`.`dummy`(`id`, `name`) VALUES ('9', 'i') /*percona-toolkit src_db:test src_tbl:dummy src_dsn:P=3306,h=192.168.0.130,p=...,u=checksum_user dst_db:test dst_tbl:dummy dst_dsn:h=localhost,p=...,u=checksum_user lock:1 transaction:1 changing_src:percona.checksums replicate:percona.checksums bidirectional:0 pid:24683 user:root host:slave*/; REPLACE INTO `test`.`dummy`(`id`, `name`) VALUES ('10', 'j') /*percona-toolkit src_db:test src_tbl:dummy src_dsn:P=3306,h=192.168.0.130,p=...,u=checksum_user dst_db:test dst_tbl:dummy dst_dsn:h=localhost,p=...,u=checksum_user lock:1 transaction:1 changing_src:percona.checksums replicate:percona.checksums bidirectional:0 pid:24683 user:root host:slave*/;

I ran the pt-table-sync tool from an opposite host this time i.e. from the slave as I used the –sync-to-master option which treats DSN as slave and syncs to master. Again, pt-table-sync will use the mysql username and password you specify to connect to the slave as well as to its master. –replicate option here examines the specified table to find out the data differences and –print just prints the SQL  (REPLACE queries) not actually executes it.

You may audit the queries before executing to sync data between master/slave.  You may see it printed only missing records on the slave. Once you are happy with the results, you can substitute –print with –execute to do actual synchronization.

As a reminder, these queries always executed on the master as this is the only safe way to do the changes on slave. However, on the master it’s no-op changes as these records already exists on master but then falls to slave via replication stream to sync it with master.

If you find lots of differences on your slave server it may lag during synchronization of those changes. As I mentioned earlier, you can use –print option to go through your queries which are going to be executed to sync slave with master server. I found this post useful if you see a huge difference in the table between master/slave(s).

Note, you may use the –dry-run option initially which only analyzes print information about the sync algorithm and then exits. It shows verbose output; it doesn’t do any changes though. –dry-run parameter will basically instruct pt-table-sync to not actually do the sync, but just perform some checks.

Let me present another replication topology, where the master has two slaves where slave2 is running on non-default port 3307 while master and slave1 running on port 3306. Further, slave2 is out of sync with master and I will show you how to sync slave2 which running on port 3307 with master.

mysql-master> SELECT * FROM dummy; +----+------+ | id | name | +----+------+ | 1 | a | | 2 | b | | 3 | c | | 4 | d | | 5 | e | +----+------+ 5 rows in set (0.00 sec) mysql-slave1> SELECT * FROM test.dummy; +----+------+ | id | name | +----+------+ | 1 | a | | 2 | b | | 3 | c | | 4 | d | | 5 | e | +----+------+ 5 rows in set (0.00 sec) mysql-slave2> SELECT * FROM test.dummy; +----+------+ | id | name | +----+------+ | 1 | a | | 2 | b | | 3 | c | +----+------+

Let’s run pt-table-checksum tool on master database server.

[root@master]# pt-table-checksum --replicate percona.checksums --ignore-databases=mysql h=192.168.0.130,u=checksum_user,p=checksum_password --recursion-method=dsn=D=percona,t=dsns TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE 07-23T13:57:39 0 0 2 1 0 0.310 percona.dsns 07-23T13:57:39 0 1 5 1 0 0.036 test.dummy

I used –recursion-method parameter this time which is method to use find slaves in replication stream and it’s pretty useful when your servers run on non-standard port i.e. other than 3306. I created dsns table under percona database with following entries. You may find dsns table structure in documentation.

mysql> SELECT * FROM dsns; +----+-----------+------------------------------------------------------------+ | id | parent_id | dsn | +----+-----------+------------------------------------------------------------+ | 1 | 1 | h=192.168.0.134,u=checksum_user,p=checksum_password,P=3306 | | 2 | 2 | h=192.168.0.132,u=checksum_user,p=checksum_password,P=3307 | +----+-----------+------------------------------------------------------------+

Next I ran below pt-table-checksum command to identify which slave server has differences on test.dummy table.

[root@master]# pt-table-checksum --replicate=percona.checksums --replicate-check-only --ignore-databases=mysql h=192.168.0.130,u=checksum_user,p=checksum_password --recursion-method=dsn=D=percona,t=dsns Differences on slave2 TABLE CHUNK CNT_DIFF CRC_DIFF CHUNK_INDEX LOWER_BOUNDARY UPPER_BOUNDARY test.dummy 1 -2 1

This shows that slave2 has different data on test.dummy table as compared to the master. Now let’s run pt-table-sync tool to sync those differences and make slave2 identical as the master.

[root@slave2] ./pt-table-sync --print --replicate=percona.checksums --sync-to-master h=192.168.0.132,u=checksum_user,p=checksum_password REPLACE INTO `test`.`dummy`(`id`, `name`) VALUES ('4', 'd') /*percona-toolkit src_db:test src_tbl:dummy src_dsn:P=3306,h=192.168.0.130,p=...,u=checksum dst_db:test dst_tbl:dummy dst_dsn:h=192.168.0.132,p=...,u=checksum lock:1 transaction:1 changing_src:percona.checksums replicate:percona.checksums bidirectional:0 pid:1514 user:root host:slave2*/; REPLACE INTO `test`.`dummy`(`id`, `name`) VALUES ('5', 'e') /*percona-toolkit src_db:test src_tbl:dummy src_dsn:P=3306,h=192.168.0.130,p=...,u=checksum dst_db:test dst_tbl:dummy dst_dsn:h=192.168.0.132,p=...,u=checksum lock:1 transaction:1 changing_src:percona.checksums replicate:percona.checksums bidirectional:0 pid:1514 user:root host:slave2*/;

It shows 2 rows are different on slave2. Substituting –print with –execute synchronized the differences on slave2 and re-running pt-table-checksum tool shows no more differences.

Conclusion:
pt-table-checksum and pt-table-sync are the finest tools from Percona Toolkit to validate data between master/slave(s). With the help of these tools you can easily identify data drifts and fix them. I mentioned a couple of replication topologies above about how to check replication consistency and how to fix it in case of data drift. You may script pt-table-checksum / pt-table-sync steps and cron checksum script to periodically check the data consistency within replication stream.

This procedure is only safe for a single level master-slave(s) hierarchy. I will discuss the procedure for other topologies in future posts – i.e. I will describe more complex scenarios on how to use these tools in chain replication i.e. master -> slave1 -> slave2 pair and in Percona XtraDB Cluster setup.

The post MySQL replication primer with pt-table-checksum and pt-table-sync appeared first on MySQL Performance Blog.

pt-table-sync and SSH tunnels

Lastest Forum Posts - August 11, 2015 - 3:00pm
Not sure if I've diagnosed this properly, but here is what I'm running into. We have a master slave setup, with the master residing on server A, slave on server B. The slave communicates with the master on a different port, let's call it 3307. This shows up properly in SHOW SLAVE STATUS, and when I look at the DSN output of the pt-table-sync command, it reflects that:

# TableSyncer:5950 27054 Binlog trace message: src_db:my_db src_tbl: promotions src_dsn: D=my_db,P=3307,h=localhost,t=promotions dst_db:my_db dst_tbl: promotions dst_dsn: D=my_db,h=localhost,t=promotions lock:0 transaction:0 changing_src:1 replicate:0 bidirectional:0 pid:27054

However, the way that the servers communicate is via an SSH tunnel.

When I connect to server B with SqlPro, and tell it to use port 3306, I am querying the slave. No problem.
When I connect to server B with SqlPro, and tell it to use port 3307, I in fact, am talking to master, and see the query results from master.

However, when pt-table-sync attempts to connect to master, via port 3307 on the slave machine, it seems to end up talking to the slave, not the master.

Is this because the DBs are connecting using SSH tunnels, rather than port/sockets? Is there any way around this?

Thanks,
-Cam

Node IP address not obtained from bind_addres

Lastest Forum Posts - August 11, 2015 - 11:02am
Hi Friends,

Please find the below error log information. we are getting this error while start first node PXC server.

2015-08-11 16:34:45 22259 [ERROR] WSREP: Node IP address not obtained from bind_address, trying alternate methods

Please suggest to resolve this issues.
Thanks,
Periyannan N.