]]>
]]>

You are here

Feed aggregator

MySQL shell prompt vs MongoDB shell prompt

Recently Todd Farmer shared an interesting story about the mysql command line prompt in MySQL 5.7: how it was changed to provide more context and why the change was finally reverted. This made me think that after using the command line client for MongoDB for awhile, I would love seeing a much more modern mysql shell prompt. Here are a few examples of what a modern command line client can do.

Add dynamic information to the prompt

If you use replication with MongoDB, you have probably noticed a nice feature of the prompt: it is replication aware. What I mean is that for a standalone instance, the prompt is simply:

>

When you configure this instance to be the primary of a replica set named RS, the prompt automatically becomes:

RS:PRIMARY>

and for secondaries, you will see:

RS:SECONDARY>

And of course if an election is triggered and roles are switched, the prompt is updated accordingly to reflect the change.

Such a feature may not be easily transposed to MySQL as the allowed replication topologies are more flexible: for instance with master-master replication or for chained replication, some servers may be both a master and a slave.

However if you look a bit deeper how the MongoDB shell prompt can be customized, it is very flexible as you can set the prompt variable to the result of any Javascript function. So for instance if you write this:

var prompt=function() { return db.getName()+"/"+ISODate().toLocaleTimeString()+"> "; }

Your prompt will be changed to something like:

test/11:37:51>

And the change can be persisted by defining the prompt variable in a ~/.mongorc.js file.

If I could do something similar with MySQL, I would for instance be able to know the replication lag of a slave or the uptime of MySQL and so on and so forth. That would be incredibly useful in some situations.

Better help system

Let’s assume I want to manually fail over to a secondary in a MongoDB replica set. I can simply instruct the master that it should no longer be the master. But what is the exact command?

I know that it is rs.xxx because all replica sets functions have this format, so let’s simply open the shell and type rs. and all the available options show up:

> rs. rs.add( rs.constructor rs.propertyIsEnumerable( rs.syncFrom( rs.addArb( rs.debug rs.prototype rs.toLocaleString( rs.apply( rs.freeze( rs.reconfig( rs.toString( rs.bind( rs.hasOwnProperty( rs.remove( rs.valueOf( rs.call( rs.help( rs.slaveOk( rs.conf( rs.initiate( rs.status( rs.config( rs.isMaster( rs.stepDown(

Here again, I’d love to see something similar with MySQL. For instance each time I need to convert a timestamp to a human readable date, I have to look at the documentation to find the exact function name.

Actually I could have used the built-in help system to find the name of the function, but it is much more convenient to look at the documentation.

Conclusion

What’s your opinion on which new features a modern mysql shell should provide? If many people agree on a set of features, it could be worth filing a feature request to get a better mysql command line client in a future version of MySQL.

The post MySQL shell prompt vs MongoDB shell prompt appeared first on MySQL Performance Blog.

Percona XtraBackup 2.2.10 for MySQL hot backups is now available (free!)

Latest MySQL Performance Blog posts - March 31, 2015 - 8:34am

Percona is glad to announce the release of Percona XtraBackup 2.2.10 on March 31, 2015. Downloads are available from our download site or Percona Software Repositories.

Percona XtraBackup enables MySQL backups without blocking user queries, making it ideal for companies with large data sets and mission-critical applications that cannot tolerate long periods of downtime. Offered free as an open source solution, Percona XtraBackup drives down backup costs while providing unique features for MySQL backups.

Bugs Fixed:

  • Decrypting backup with the wrong key would make the backup unusable and unrecoverable. innobackupex doesn’t automatically delete the *.qp and *.xbcrypt files anymore, after --decrypt and --decompress are used. Bug fixed #1413044.
  • XtraDB Changed Page Tracking wasn’t working with innobackupex. Bug fixed #1436793.
  • Fixed Percona XtraBackup assertion caused by dirty pages remaining in the buffer pool after the log was fully applied. Bug fixed #1368846.
  • Backup will not be prepared and innobackupex will stop with an error if the transaction log file is corrupted and it wasn’t applied to the intended LSN. Previously this was showing only as a warning. Bug fixed #1414221.
  • New status log-applied is introduced for backup prepared with --redo-only to avoid making the backup unusable by preparing full or incremental backup without --redo-only and then applying next incremental on top of it. Incremental backup now can be applied only to backup in log-applied state, but not to full-prepared as it was earlier. Bug fixed #1436790.

Release notes with all the bugfixes for Percona XtraBackup 2.2.10 are available in our online documentation. Bugs can be reported on the launchpad bug tracker. Percona XtraBackup is an open source, free MySQL hot backup software that performs non-blocking backups for InnoDB and XtraDB databases.

The post Percona XtraBackup 2.2.10 for MySQL hot backups is now available (free!) appeared first on MySQL Performance Blog.

Error_code: 1677 on Rolling upgrade

Lastest Forum Posts - March 31, 2015 - 1:11am
Hi

I have a percona cluster of three servers and i am trying to perform rolling schema upgrade with RSU + node dropping method.
Basicaly what you do is:

1.remove the a server from the cluster,
2.change OSU method to RSU,
3.perform the schema change
4. Add server back to cluster

I did the above method on a small table and it all worked well how ever when i tried on a much bigger table (took like 5 hours to complete) when added the server back to cluster i got the following error:


150331 9:05:09 [Note] WSREP: Receiving IST: 278908 writesets, seqnos 42240-321148
150331 9:05:10 [ERROR] Slave SQL: Column 3 of table 'main_db.allRecords' cannot be converted from type 'varchar(120)' to type 'varchar(100)', Error_code: 1677
150331 9:05:10 [Warning] WSREP: RBR event 2 Write_rows apply warning: 3, 42266
150331 9:05:10 [Warning] WSREP: Failed to apply app buffer: seqno: 42266, status:1
at galera/src/replicator_smm.cpp:apply_wscoll():55
Weird thing is that the change i made was indeed on relevant table on Column 3 from VARCAHR(40) to VARCHAR(100)...
Any ideas?

Thanks

Percona XtraDB Cluster 5.5.41-25.11 is now available

Latest MySQL Performance Blog posts - March 30, 2015 - 9:22am

Percona is glad to announce the new release of Percona XtraDB Cluster 5.5 on March 30th 2015. Binaries are available from downloads area or from our software repositories.

Based on Percona Server 5.5.41-37.0 including all the bug fixes in it, Galera Replicator 2.11, and on Codership wsrep API 25.11, Percona XtraDB Cluster 5.5.41-25.11 is now the current 5.5 General Availability release. All of Percona‘s software is open-source and free, and all the details of the release can be found in the 5.5.41-25.11 milestone at Launchpad.

Bugs Fixed:

  • XtraBackup SST wouldn’t stop when MySQL was SIGKILLed. This would prevent MySQL to initiate a new transfer as port 4444 was already utilized. Bug fixed #1380697.
  • garbd was returning incorrect return code, ie. when garbd was already started, return code was 0. Bugs fixed #1308103 and #1422863.
  • wsrep_sst_xtrabackup-v2 script was causing innobackupex to print a false positive stack trace into the log. Bug fixed #1407599.
  • MyISAM DDL (CREATE TABLE only) isn’t replicated anymore when wsrep_replicate_myisam is OFF. Note, for older nodes in the cluster, wsrep_replicate_myisam should work since the TOI decision (for MyISAM DDL) is done on origin node. Mixing of non-MyISAM and MyISAM tables in the same DDL statement is not recommended with wsrep_replicate_myisam OFF since if any table in list is MyISAM, the whole DDL statement is not put under TOI (total order isolation). This also doesn’t work if default_storage_engine variable is set to MyISAM (which is not recommended for Percona XtraDB Cluster) and a table is created without the ENGINE option. Bug fixed #1402338.
  • Percona XtraDB Cluster now shows a warning in case additional utilities, like pv which may not affect critical path of SST, are not installed. Bug fixed #1248688.
  • wsrep_causal_reads variable was not honored when declared as global. Bug fixed #1361859.
  • garbd would not work when cluster address was specified without the port. Bug fixed #1365193.
  • garbd was running as root user on Debian. Bug fixed #1392388.
  • Errors in garbd init script stop/start functions have been fixed. Bug fixed #1367956.
  • If mysqld gets killed during the SST it will leave an unclean data directory behind. This would cause Percona XtraDB Cluster to fail when the server would be started next time because the data directory would be corrupted. This was fixed by resuming the startup in case wsrep-recover failed to recover due to corrupted data directory. The old behavior is still achievable through --exit-on-recover-fail command line parameter to mysqld_safe or exit-on-recover-fail under [mysqld_safe] in my.cnf. Bug fixed #1378578.
  • gvwstate.dat file was removed on joiner when XtraBackup SST method was used. Bug fixed #1388059.
  • xtrabackup-v2 SST did not clean the undo log directory. Bug fixed #1394836.
  • stderr of SST/Innobackupex is logged to syslog with appropriate tags if sst-syslog is in [sst] or [mysqld_safe] has syslog in my.cnf. This can be overridden by setting the sst-syslog to -1 in [sst]. Bug fixed #1399134.
  • clustercheck can now check if the node is PRIMARY or not, to allow for synced nodes which go out of PRIMARY not to take any writes/reads. Bug fixed #1403566.
  • Race condition between donor and joiner in Xtrabackup SST Configuration has been fixed. This caused XtraBackup SST to fail when joiner took longer to spawn the second listener for SST. Bug fixed #1405668.
  • SST will now fail early if the xtrabackup_checkpoints file is missing on the joiner side. Bug fixed #1405985.
  • socat utility was not properly terminated after a timeout. Bug fixed #1409710.
  • 10 seconds timeout in Xtrabackup SST Configuration script was not enough for the joiner to delete existing files before it started the socat receiver on systems with big datadir. Bug fixed #1413879.
  • Conflict between enforce_storage_engine and wsrep_replicate_myisam for CREATE TABLE has been fixed. Bug fixed #1435482.
  • SST processes are now spawned with fork/exec instead of posix_spawn to allow for better cleanup of child processes in event of non-graceful termination (SIGKILL or a crash etc.). Bug fixed #1382797.
  • Variable length arrays in WSREP code were causing debug builds to fail. Bug fixed #1409042.
  • Signal handling in mysqld has been fixed for SST processes. Bug fixed #1399175.
  • Inserts to a table with autoincrement primary key could result in duplicate key error if another node joined or dropped from the cluster during the insert processing. Bug fixed #1366997.

Other bugs fixed: #1391634 and #1396757.

Release notes for Percona XtraDB Cluster 5.5.41-25.11 are available in our online documentation along with the installation instructions.

Help us improve our software quality by reporting any bugs you encounter using our bug tracking system. As always, thanks for your continued support of Percona!

Please also note that Percona XtraDB Cluster 5.6 series is the latest General Availability series and current GA release is 5.6.22-25.8.

The post Percona XtraDB Cluster 5.5.41-25.11 is now available appeared first on MySQL Performance Blog.

TokuBD 7.5.6 Insert Performance

Lastest Forum Posts - March 30, 2015 - 6:02am
Hi

1. Is this TokuDB insert performance normal or slow?
2. It it possible to increase TokuDB insert performance?

Thank you

: num-threads=4, tokudb_commit_sync = ON, innodb_flush_log_at_trx_commit = 1 * MyISAM 16684 per sec * InnoDB 3379 per sec * TokuDB 1763 per sec num-threads=4, tokudb_commit_sync = OFF, innodb_flush_log_at_trx_commit = 2 * MyISAM 15485 per sec * InnoDB 22085 per sec * TokuDB 14956 per sec --- num-threads=2, tokudb_commit_sync = ON, innodb_flush_log_at_trx_commit = 1 * MyISAM 11848 per sec * InnoDB 2695 per sec * TokuDB 1251 per sec num-threads=2, tokudb_commit_sync = OFF, innodb_flush_log_at_trx_commit = 2 * MyISAM 14377 per sec * InnoDB 12283 per sec * TokuDB 9079 per sec --- num-threads=1, tokudb_commit_sync = ON, innodb_flush_log_at_trx_commit = 1 * MyISAM 6181 per sec * InnoDB 1618 per sec * TokuDB 922 per sec num-threads=1, tokudb_commit_sync = OFF, innodb_flush_log_at_trx_commit = 2 * MyISAM 6507 per sec * InnoDB 6518 per sec * TokuDB 5598 per sec ----------------------------------------------------------------- CPU: AMD A8-5600K (4 core) RAM: 8 Gb HDD: SATA 7200 rpm CentOS 7 x86_64 Percona 5.6.23-rel72.1.el7.x86_64 (tokudb 7.5.6) Sysbench 0.5 (snapshot 20150126) ----------------------------------------------------------------- my.cnf: [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock key_buffer_size = 2048M tokudb_cache_size = 2048M #tokudb_commit_sync = OFF innodb_buffer_pool_size = 2048M #innodb_flush_log_at_trx_commit = 2 [mysqld_safe] thp-setting=never log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid ----------------------------------------------------------------- sysbench.sh: #!/bin/bash function bench { engine=$1 shift sysbench \ --test=/usr/share/doc/sysbench/tests/db/insert.lua \ \ --db-driver=mysql \ --mysql-password= \ --mysql-host=localhost \ --mysql-table-engine=$engine \ --mysql-user=root \ \ --num-threads=4 \ --oltp-table-size=1000000 \ $* } function run { echo "=======================================================" echo " $1" echo "=======================================================" mysql -u root --password= -e "drop database sbtest" 2>/dev/null mysql -u root --password= -e "create database sbtest" 2>/dev/null bench $1 prepare > /dev/null bench $1 --max-time=60 --max-requests=0 run | grep 'requests:' } run 'MyISAM' run 'InnoDB' run 'TokuDB'

Fresh installation on Vagrant fails

Lastest Forum Posts - March 29, 2015 - 11:49am
Hi I did a clean installation on ubuntu64 via vagrant and every time i start it keeps failing. I'm not a pro so I would appreciate some noob friendly guidelines. I followed the guidelines from here https://www.digitalocean.com/communi...-replace-mysql
vagrant@precise64:/$ sudo service mysql stop
* Stopping MySQL (Percona Server) mysqld [ OK ]
vagrant@precise64:/$ sudo service mysql start
* Starting MySQL (Percona Server) database server mysqld [fail]
vagrant@precise64:/$

Has Shape-Shifting Spikes and Teen Angst

Lastest Forum Posts - March 29, 2015 - 8:12am
Cook and Other Tech CEOs Blast Indiana Religious https://www.reddit.com/r/ioposlas/comments/30p4n7/

OPTIMIZE, CHECK or REPAIR TABLE crashes tables and frequently server

Lastest Forum Posts - March 28, 2015 - 5:50am
We are using Percona 5.6.23-72.1 on Centos 7.0.1406 64bit (with TokuDB plugin) and migrated the tablespace from MySQL 5.5.27.


( 1 )

We see frequent table marked as crashed after OPTIMIZE, CHECK, REPAIR command (MyISAM)

Log entry:

2015-03-28 05:03:28 123161 [ERROR] Got an error from thread_id=7373, /mnt/workspace/percona-server-5.6-redhat-binary/label_exp/centos7-64/rpmbuild/BUILD/percona-server-5.6.23-72.1/storage/myisam/ha_myisam.cc:910
2015-03-28 05:03:29 123161 [ERROR] MySQL thread id 7373, OS thread handle 0x7f4f663bd700, query id 653385 192.168.0.*. * Checking table *** multiple tables ***
2015-03-28 05:03:30 123161 [ERROR] /usr/sbin/mysqld: Table '***' is marked as crashed and should be repaired

Fixing the table with REPAIR TABLE works for most cases - in some cases REPAIR TABLE fails and we are using myisamcheck, which works well.


( 2 )

In some cases we don't see the behaviour in (1) , but we see a server crash (only for multiple tables in CHECK TABLE or REPAIR TABLE). Error log:

05:06:41 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 Server better by reporting any
bugs at http://bugs.percona.com/

key_buffer_size=8589934592
read_buffer_size=2097152
max_used_connections=261
max_threads=5002
thread_count=135
connection_count=135
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 2641195005 K bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

Thread pointer: 0x7f4edb468000
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 = 7f4f23dcad40 thread_stack 0x40000
/usr/sbin/mysqld(my_print_stacktrace+0x3b)[0x8d9b8b]
/usr/sbin/mysqld(handle_fatal_signal+0x471)[0x658ab1]
/lib64/libpthread.so.0(+0xf130)[0x7f524e348130]
/usr/sbin/mysqld(_ZNK7handler22ha_statistic_incrementEM17sys tem_status_vary+0xc)[0x59a6ac]
/usr/sbin/mysqld(_ZN7handler16ha_external_lockEP3THDi+0x33)[0x5a02b3]
/usr/sbin/mysqld(_Z17mysql_lock_tablesP3THDPP5TABLEjj+0x75a)[0x7d5cea]
/usr/sbin/mysqld(_Z11lock_tablesP3THDP10TABLE_LISTjj+0x530)[0x690180]
/usr/sbin/mysqld(_Z20open_and_lock_tablesP3THDP10TABLE_LISTb jP19Prelocking_strategy+0xa2)[0x696e52]
/usr/sbin/mysqld[0x812853]
/usr/sbin/mysqld(_ZN20Sql_cmd_repair_table7executeEP3THD+0xc 8)[0x8140b8]
/usr/sbin/mysqld(_Z21mysql_execute_commandP3THD+0x16f0)[0x6dbd40]
/usr/sbin/mysqld(_Z11mysql_parseP3THDPcjP12Parser_state+0x5e 8)[0x6e1618]
/usr/sbin/mysqld(_Z16dispatch_command19enum_server_commandP3 THDPcj+0xfc8)[0x6e2d78]
/usr/sbin/mysqld(_Z24do_handle_one_connectionP3THD+0x172)[0x6afdf2]
/usr/sbin/mysqld(handle_one_connection+0x40)[0x6afef0]
/usr/sbin/mysqld(pfs_spawn_thread+0x143)[0x9119b3]
/lib64/libpthread.so.0(+0x7df3)[0x7f524e340df3]
/lib64/libc.so.6(clone+0x6d)[0x7f524c9b91ad]

Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (7f4ede41d010): is an invalid pointer
Connection ID (thread ID): 7367
Status: NOT_KILLED

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.
150328 06:06:42 mysqld_safe Transparent huge pages are already set to: never.
150328 06:06:42 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
2015-03-28 06:06:43 0 [Warning] The syntax 'pre-4.1 password hash' is deprecated and will be removed in a future release. Please use post-4.1 password hash instead.
2015-03-28 06:06:43 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2015-03-28 06:06:44 124453 [Note] Plugin 'FEDERATED' is disabled.
2015-03-28 06:06:44 124453 [Note] InnoDB: Using atomics to ref count buffer pool pages
2015-03-28 06:06:44 124453 [Note] InnoDB: The InnoDB memory heap is disabled
2015-03-28 06:06:44 124453 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2015-03-28 06:06:44 124453 [Note] InnoDB: Memory barrier is not used
2015-03-28 06:06:44 124453 [Note] InnoDB: Compressed tables use zlib 1.2.3
2015-03-28 06:06:44 124453 [Note] InnoDB: Using Linux native AIO
2015-03-28 06:06:44 124453 [Note] InnoDB: Using CPU crc32 instructions
2015-03-28 06:06:44 124453 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2015-03-28 06:06:44 124453 [Note] InnoDB: Completed initialization of buffer pool
2015-03-28 06:06:44 124453 [Note] InnoDB: Highest supported file format is Barracuda.
2015-03-28 06:06:44 124453 [Note] InnoDB: The log sequence numbers 1626265 and 1626265 in ibdata files do not match the log sequence number 1626285 in the ib_logfiles!
2015-03-28 06:06:44 124453 [Note] InnoDB: Database was not shutdown normally!
2015-03-28 06:06:44 124453 [Note] InnoDB: Starting crash recovery.
2015-03-28 06:06:44 124453 [Note] InnoDB: Reading tablespace information from the .ibd files...
2015-03-28 06:06:51 124453 [Note] InnoDB: Restoring possible half-written data pages
2015-03-28 06:06:51 124453 [Note] InnoDB: from the doublewrite buffer...
2015-03-28 06:06:51 124453 [Note] InnoDB: 128 rollback segment(s) are active.
2015-03-28 06:06:51 124453 [Note] InnoDB: Waiting for purge to start
2015-03-28 06:06:51 124453 [Note] InnoDB: Percona XtraDB (http://www.percona.com) 5.6.23-72.1 started; log sequence number 1626285
Sat Mar 28 06:06:51 2015 TokuFT recovery starting in env /var/lib/mysql/
Sat Mar 28 06:06:51 2015 TokuFT recovery scanning backward from 140611
Sat Mar 28 06:06:51 2015 TokuFT recovery bw_end_checkpoint at 140611 timestamp 1427519174964095 xid 140607 (bw_newer)
Sat Mar 28 06:06:51 2015 TokuFT recovery bw_begin_checkpoint at 140607 timestamp 1427519174964044 (bw_between)
Sat Mar 28 06:06:51 2015 TokuFT recovery turning around at begin checkpoint 140607 time 51
Sat Mar 28 06:06:51 2015 TokuFT recovery starts scanning forward to 140611 from 140607 left 4 (fw_between)
Sat Mar 28 06:06:51 2015 TokuFT recovery closing 2 dictionaries
Sat Mar 28 06:06:51 2015 TokuFT recovery making a checkpoint
Sat Mar 28 06:06:51 2015 TokuFT recovery done
2015-03-28 06:06:51 124453 [Note] Recovering after a crash using mysql-bin
2015-03-28 06:06:52 124453 [Note] Starting crash recovery...
2015-03-28 06:06:52 124453 [Note] Crash recovery finished.
2015-03-28 06:06:52 124453 [Note] RSA private key file not found: /var/lib/mysql//private_key.pem. Some authentication plugins will not work.
2015-03-28 06:06:52 124453 [Note] RSA public key file not found: /var/lib/mysql//public_key.pem. Some authentication plugins will not work.
2015-03-28 06:06:52 124453 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306
2015-03-28 06:06:52 124453 [Note] - '0.0.0.0' resolves to '0.0.0.0';
2015-03-28 06:06:52 124453 [Note] Server socket created on IP: '0.0.0.0'.
2015-03-28 06:06:52 124453 [Note] Event Scheduler: Loaded 0 events
2015-03-28 06:06:52 124453 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.6.23-72.1-log' socket: '/var/lib/mysql/mysql.sock' port: 3306 Percona Server (GPL), Release 72.1, Revision 0503478



Any suggestions?

Thanks,

Ralf

‘Woz on your mind?’ Share your questions for Steve Wozniak during his Percona Live keynote!

Latest MySQL Performance Blog posts - March 27, 2015 - 2:34pm

Here’s your chance to get on stage with Woz! Sort of. Apple co-founder and Silicon Valley icon and philanthropist Steve Wozniak will participate in a moderated Q&A on creativity and innovation April 14 during the Percona Live MySQL Conference and Expo in Santa Clara, California.

Woz once said that he never intended to change the world. That was the other Steve, Steve Jobs.

“I didn’t want to start this company,” Woz told the Seattle Times of Apple’s beginnings in a 2006 interview. “My goal wasn’t to make a ton of money. It was to build good computers. I only started the company when I realized I could be an engineer forever.”

What would you ask Woz if given the opportunity?

“Woz, what first sparked your interest in engineering?”
“Hey Woz, how did you come up with the design for the first Apple?”
“Woz, what do you see as the next big thing in personal computers?”
“Hi Woz, what’s the deal with your giant vacuum tube watch?”

Now it’s your turn! Ask a question in the comments below and be sure to include your Twitter handle – or your Facebook page or LinkedIn profile. If we use your question, then your profile and question will be displayed on the giant screen behind Woz on stage as it’s being asked during his big keynote! How cool is that?

Want to be there in person? See Woz speak for just $5! That’s $70 off the regular admission price! Just use the promo code “KEY” at registration under the “Expo Hall and Keynote Pass” selection. Following Woz’s keynote, be sure to stop by the Percona booth, say “hello, Tom,” and I’ll give you a limited-edition Percona t-shirt.

In the meantime, help spread the word! Please share this tweet:

“Woz on your mind?” Tweet @Percona your questions for Apple’s Steve Wozniak who speaks April 14 at #PerconaLive! http://ow.ly/KTmES

Do that, then follow @Percona and I’ll send a DM for your address and will ship a t-shirt right to your door. See you at the conference!

The post ‘Woz on your mind?’ Share your questions for Steve Wozniak during his Percona Live keynote! appeared first on MySQL Performance Blog.

XtraBackup depenencies for Centos 6.2 and 5.5

Lastest Forum Posts - March 27, 2015 - 11:31am
I'm wanting to install XtraBackup 2.2.9 as the backup up tool for my environment and it is mixed with 6.2 and 5.5.
I have been able to get the 6.2 without an issue but it seems that perl 5.10 is the minimum needed. Can somebody give me the minimums for Centos 5.5 and 6.2? I know I need DBI, DBD, IO-Socket-SSL, Time-Hi-Res. What else is needed and if I can't install 2.2.9 on Centos 5.5 what version would I be able to use? I'm not in a position to go upgrade all the 5.5 machines to 6.2 either.

Innobackupex - MySQL server has gone away when SET SESSION lock_wait_timeout=31536000

Lastest Forum Posts - March 27, 2015 - 9:39am
Hi,

I have mysql server 5.5 with > 300GB data.
I tried create a slave from this master server.

When i run innobackupex, it died at SET SESSION lock_wait_timeout=31536000

This is error:

: DBD::mysql::db do failed: MySQL server has gone away at /usr/bin/innobackupex line 3045. innobackupex: got a fatal error with the following stacktrace: at /usr/bin/innobackupex line 3048 main::mysql_query('HASH(0x19b4fed0)', 'SET SESSION lock_wait_timeout=31536000') called at /usr/bin/innobackupex line 3456 main::mysql_lock_tables('HASH(0x19b4fed0)') called at /usr/bin/innobackupex line 1991 main::backup() called at /usr/bin/innobackupex line 1601 innobackupex: Error: Error executing 'SET SESSION lock_wait_timeout=31536000': DBD::mysql::db do failed: MySQL server has gone away at /usr/bin/innobackupex line 3045. 150322 21:25:24 innobackupex: Waiting for ibbackup (pid=30331) to finish
I tried repeat 2 times but same errors.

I read tutorial at http://www.percona.com/doc/percona-x...ved_ftwrl.html
and set options same this tut but not success.

Sorry, my english is very poor

Thanks!

Innobackupex - MySQL server has gone away when SET SESSION lock_wait_timeout=31536000

Lastest Forum Posts - March 27, 2015 - 9:38am
Hi,

I have mysql server 5.5 with > 300GB data.
I tried create a slave from this master server.

When i run innobackupex, it died at SET SESSION lock_wait_timeout=31536000

This is error:

DBD::mysql::db do failed: MySQL server has gone away at /usr/bin/innobackupex line 3045.
innobackupex: got a fatal error with the following stacktrace: at /usr/bin/innobackupex line 3048
main::mysql_query('HASH(0x19b4fed0)', 'SET SESSION lock_wait_timeout=31536000') called at /usr/bin/innobackupex line 3456
main::mysql_lock_tables('HASH(0x19b4fed0)') called at /usr/bin/innobackupex line 1991
main::backup() called at /usr/bin/innobackupex line 1601
innobackupex: Error:
Error executing 'SET SESSION lock_wait_timeout=31536000': DBD::mysql::db do failed: MySQL server has gone away at /usr/bin/innobackupex line 3045.
150322 21:25:24 innobackupex: Waiting for ibbackup (pid=30331) to finish


I tried repeat 2 times but same errors.

I read tutorial at http://www.percona.com/doc/percona-x...ved_ftwrl.html
and set options same this tut but not success.

Sorry, my english is very poor

Thanks!

xtrabackup datadir must be empty - options?

Lastest Forum Posts - March 27, 2015 - 6:55am
In the Documentation for XtraBackup it says that

The datadir must be empty; Percona XtraBackup innobackupex --copy-back option will not copy
over existing files.

Is there any way round this?

My DataDir is /var/lib/mysql. I created my backup in /var/lib/mysql/backups/ as I don't have enough space anywhere else on the server. I removed everything from /var/lib/mysql except the `backups` directory but it still wont copy-back, giving error:

Error: Original data directory '/var/lib/mysql' is not empty! at /usr/bin/innobackupex line 2194.

FoundationDB is acquired by Apple: My thoughts

Latest MySQL Performance Blog posts - March 27, 2015 - 6:00am

TechCrunch reported yesterday that Apple has acquired FoundationDB. And while I didn’t see any mention if this news on the FoundationDB website, they do have an announcement saying: “We have made the decision to evolve our company mission and, as of today, we will no longer offer downloads.”

This is an unfortunate development – I have been watching FoundationDB technology for years and was always impressed in terms of its performance and features. I was particularly impressed by their demo at last year’s Percona Live MySQL and Expo. Using their Intel NUC-based Cluster, I remember Ori Herrnstadt showing me how FoundationDB handles single-node failure as well as recovery from complete power-down – very quickly and seamlessly. We have borrowed a lot of ideas from this setup for our Percona XtraDB Cluster Demos.

I think it was a great design to build a distributed, shared-nothing transaction aware key value store, and then have an SQL Layer built on top of it. I did not have a chance to test it hands-on, though. Such a test would have revealed the capabilities of the SQL optimizer – the biggest challenge for distributed relational database systems.

My hope was to see, over time, this technology becoming available as open source (fully or partially), which would have dramatically increased adoption by the masses. It will be interesting to see Apple’s long-terms plans for this technology.

In any case it looks like FoundationDB software is off limits. If you are an existing FoundationDB customer looking for alternatives, we here at Percona would be happy to help evaluate options and develop a migration strategy if necessary.

The post FoundationDB is acquired by Apple: My thoughts appeared first on MySQL Performance Blog.

Freshly installed mysql 5.5 unresponsive

Lastest Forum Posts - March 27, 2015 - 12:18am
Hi Guys,

I installed via apt-get mysql 5.5 and created following custom config that i placed in /etc/mysql/conf.d/custom.cnf

: [mysqld] #bind-address = 0.0.0.0 innodb_buffer_pool_size = 10G query_cache_limit = 80M query_cache_size = 64M tmp_table_size = 256M max_heap_table_size = 256M table_open_cache = 2000 join_buffer_size = 64M max_allowed_packet=512M log_slow_queries = /var/log/mysql/mysql-slow.log long_query_time = 2 #log-queries-not-using-indexes max_connections = 250 An apache web server in the same network is connecting to this server (roughly 70 max connections) and a couple of times the database would become unresponsive.

Now my fear is that there is something not okay with my config above.
The server has a SSD, 32GB of ram and 4 cores. All tables are InnoDB

Any input would be greatly appreciated.

Thanks in advance!

False negatives from pt-table-checksum

Lastest Forum Posts - March 26, 2015 - 1:59pm
I have come across a problem in which pt-table-checksum is reporting false negatives, i.e. failing to report differences in even very small tables.

The situation:

Customer has an existing MySQL cluster consisting of two MySQL 5.5.32 master-slave pairs, with master-master replication between the two masters. If we call the pairs A,B and C,D, then A and C are the masters and their replication topology is: B<-A<=>C->D
There is also a new five-node XtraDB 5.6.22-72 cluster, with a single asynchronous replication slave for backups. Node 1 of the cluster, for now, replicates asynchronously from node C in the above topology, and will do so until migration is accomplished. To hopefullu ensure compatibility with the cluster, replication on A-B-C-D has been set to ROW, since the cluster's internal replication is and must remain ROW. Due to the sheer volume of traffic the customer is processing, replication between A and C is by now routinely falling behind by as much as an hour during the day, with obvious impacts on the cleanliness of the data.

To validate that data on the cluster matches that on the production servers prior to attempting migration to the new cluster, the customer is running pt-table-checksum on node C. pt-table-checksum is of course setting SESSION BINLOG_FORMAT to STATEMENT; equally obviously, this is not propagating past node A, so checksums reported from B cannot be trusted. That's OK. We don't actually care about checksums from B. What we care about is that the data on C, which has been declared the authoritative copy of the data, and the cluster match. And that should be fine for pt-table-checksum, because there is only a single replication link between node C and cluster node 1, so checksums between C and cluster node 1 should be accurate.

Unfortunately, they are not. pt-table-checksum is reporting tables as having zero diffs and matching checksums between C and cluster node 1, when we can look at the two tables side by side and immediately see at a glance that they are different. This is alarming, because if pt-table-checksum is lying to us and failing to report diffs that we know exist, we cannot trust what it tells us about any of the other data. And we cannot manually compare almost a terabyte of DB data, and the production environment cannot be taken offline to check all of the data. (Nor can it be taken offline to update it.)

Can anyone shed any light on why pt-table-checksum, in this configuration, is throwing false negatives?

xtrabackup does not have access rights

Lastest Forum Posts - March 26, 2015 - 9:43am
I've just installed percona-xtrabackup, and so for my first test I have Created a Directory (testdata) in my Home directory:
`mkdir testdata`

changed permissions on it
`sudo chmod 777 testdata`

and ran innobackupex
`innobackupex --user=root --password=xxxx /home/user/testdata/`

But I then get:
`2015-03-26 16:37:54 7fee54ebd740 InnoDB: Operating system error number 13 in a file operation.
InnoDB: The error means mysqld does not have the access rights to
InnoDB: the directory.`


Is this the `datatest` directory, or my MySQL '/var/lib/mysql' directory, or somewhere else?



Trying to install innobackex

Lastest Forum Posts - March 26, 2015 - 5:07am
Hi, I tried installing Percona Toolkit using:
: dpkg -i percona-toolkit.deb apt-get install --fix-missing -f Eventually I got that to work, but looking in : /usr/bin there is no sign of : innobackupex .
I can see all the : pt-* files in there though.

I tried reinstalling using:
: apt-key adv --keyserver keys.gnupg.net --recv-keys 1C4CBDCDCD2EFD2A that gives me
: . . . gpgkeys: key 1C4CBDCDCD2EFD2A not found on keyserver gpg: no valid OpenPGP data found. gpg: Total number processed: 0 Then I try
: apt-get install xtrabackup which gives me
: Reading package lists... Done Building dependency tree Reading state information... Done E: Unable to locate package xtrabackup Am I doing something wrong?

I've also tried innobackupex, innobackupex_55, percona-toolkit, and they all have the same result.

creators discuss its neon

Lastest Forum Posts - March 26, 2015 - 2:20am
http://www.youtube.com/watch?v=YZN5Xa-0iCA
Hotline Miami makes you feel things that you don't want to feel. It shows you a veritable smorgasbord of ugly

binlog positions &amp;quot;off&amp;quot; a bit after restore

Lastest Forum Posts - March 25, 2015 - 8:52am
I have a few shards in an application that are approaching 600-800G on-disk, but aren't heavily used. I'm spinning up new off-site backup & reporting copies of all shards and let four streaming xtrabackup runs go last night. I have a script that I use frequently to clone out new slaves. Two of the shards, with active customer bases, started right up as normal (150-200G). The two shards with larger data sizes appear to have slightly wrong (behind) master coordinates in xtrabackup_slave_info.

So, when I do a streaming backup from an existing slave, at the end I get:
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.002238', MASTER_LOG_POS=263779110

overnight, the master moved through to
| mysql-bin.002239 | 30261034 |

So it passes a quick sanity check. Built a CHANGE MASTER with the correct ip/user/etc, fire it off, and start replication. Duplicate key error. Oops!

I verified from the error log that the expected slave statement was issued:
Slave SQL thread initialized, starting replication in log 'mysql-bin.002238' at position 263779110, relay log '/mysql/binlog/mysqld-relay-bin.000001' position: 4. I get:

Last_SQL_Error: Error 'Duplicate entry '68407820' for key 'PRIMARY'' on query.... This is against a Rails sessions table.
mysql> select id, created_at from sessions where id = '68407821';

+----------+---------------------+
| id | created_at |
+----------+---------------------+
| 68407821 | 2015-03-24 03:54:15 |
+----------+---------------------+

Using mysqlbinlog, I found that insert into the original binlogs. It seems to be halfway through a transaction for thread 841292:


#150325 2:37:43 server id 130161118 end_log_pos 263780113 CRC32 0xa869770f Query thread_id=841292 exec_time=0 error_code=0
SET TIMESTAMP=1427265463/*!*/;
INSERT INTO `sessions` (--- redacted --- )
/*!*/;

Thus, the xtrabackup_slave_info position should have been *at least* the next one:

#150325 2:37:43 server id 130161118 end_log_pos 263780693 CRC32 0x5b59698f Query thread_id=841292 exec_time=0 error_code=0

but as noted this appears to be splitting a transaction. So, the entire transaction for thread 841292 was committed to disk (I verified on the restore that the data is correct for the entire transaction) AND data from the next few transactions is present.

Info:

Source Slave:
:~$ dpkg -l|grep percona
ii libperconaserverclient18.1 5.6.19-67.0-618.wheezy amd64 Percona Server database client library
ii libperconaserverclient18.1-dev 5.6.19-67.0-618.wheezy amd64 Percona Server database development files
ii percona-server-client-5.6 5.6.19-67.0-618.wheezy amd64 Percona Server database client binaries
ii percona-server-common-5.6 5.6.19-67.0-618.wheezy amd64 Percona Server database common files (e.g. /etc/mysql/my.cnf)
ii percona-server-server 5.6.19-67.0-618.wheezy amd64 Percona Server database server
ii percona-server-server-5.6 5.6.19-67.0-618.wheezy amd64 Percona Server database server binaries
ii percona-xtrabackup 2.2.9-5067-1.wheezy amd64 Open source backup tool for InnoDB and XtraDB

Destination:
ii libperconaserverclient18.1 5.6.22-71.0-726.wheezy amd64 Percona Server database client library
ii libperconaserverclient18.1-dev 5.6.22-71.0-726.wheezy amd64 Percona Server database development files
ii percona-server-client-5.6 5.6.22-71.0-726.wheezy amd64 Percona Server database client binaries
ii percona-server-common-5.6 5.6.22-71.0-726.wheezy amd64 Percona Server database common files (e.g. /etc/mysql/my.cnf)
ii percona-server-server 5.6.22-71.0-726.wheezy amd64 Percona Server database server
ii percona-server-server-5.6 5.6.22-71.0-726.wheezy amd64 Percona Server database server binaries
ii percona-xtrabackup 2.2.9-5067-1.wheezy amd64 Open source backup tool for InnoDB and XtraDB


I feel like I'm missing something obvious here, like a failed roll-back or something. If it hadn't happened on 2/4 of the servers overnight, I probably wouldn't bother posting. What have I done wrong/misunderstood?

Thanks!

Wes

Pages

Subscribe to Percona aggregator
]]>