Peter ZaitsevQ&A: Percona XtraDB Cluster as a MySQL HA solution for OpenStack (14.11.2014, 14:00 UTC)

Thanks to all who attended my Nov. 12 webinar titled, “Percona XtraDB Cluster as a MySQL HA Solution for OpenStack.” I had several questions which I covered at the end and a few that I didn’t. You can view the entire webinar and download the slides here.

Q&A: Percona XtraDB Cluster as a MySQL HA solution for OpenstackQ: Is the read,write speed reduced in Galera compared to normal MySQL?

For reads, it’s the same (unless you use the sync_wait feature, used to be called causal reads).

For writes, the cost of replication (~1 RTT to the worst node), plus the cost of certification will be added to each transaction commit.  This will have a non-trivial impact on your write performance, for sure.  However, I believe most OpenStack meta store use cases should not suffer overly by this performance penalty.

Q: Does state transfers affect a continuing transaction within the nodes?

Mostly, no.  The joining node will queue ongoing cluster replication while receiving its state transfer and use that to do its final ‘catchup’.  The node donating the state transfer may get locked briefly during a full SST, which could temporarily block writes on that node.  If you use the built-in clustercheck (or check the same things it checks), you can avoid this by diverting traffic away from a donor.

Q: Perhaps not the correct webinar for this question, but I was also expecting to hear something about using PXC in combination with OpenStack Trove. If you’ve got time, could you tell something about that?

Trove has not supported the concept of more than a single SQL target.  My understanding is a recent improvement here for MongoDB setups may pave the way for more robust Trove instances backed by Percona XtraDB Cluster.

Q: For Loadbalancing using the Java Mysql driver, would you suggest HA proxy or the loadbalancing connection in the java driver. Also how does things work in case of persistent connections and connection pools like dbcp?

Each node in a PXC cluster is just a mysql server that can handle normal mysql connections.  Obviously if a node fails, it’s easy to detect that you should probably not keep using that node, but in Percona XtraDB Cluster you need to watch out for things like cluster partitions where nodes lose quorum and stop taking queries (but still allow connections)  I believe it’s possible to configure advanced connection pooling setups to properly health check the nodes (unless the necessary features are not in the pool implementation), but I don’t have a reference architecture to point you to.

Q: Are there any manual solutions to avoid deadlocks within a high write context to force a query to execute on all nodes?

Yes, write to a single node, at least for the dataset that has high write volume.

Remember what I said in the webinar:  you can only update a given row once per RTT, so there’s an upper cap of throughput on the smallest locking granularity in InnoDB (i.e., a single row).

This manifests itself in two possible ways:

1) In a multi-node writing cluster by triggering deadlocks on conflicts.  Only approximately 1 transaction modifying a given row per RTT would NOT receive a deadlock

2) In a single-node writing cluster by experiencing longer lock waits.  Transaction times (and subsequently lock times) are extended by the replication and certification time, so other competing transactions will lock wait until the blocking transaction commit. There are no replication conflict deadlocks in this case, but the net effect is exactly the same:  only 1 transaction per row per RTT.

Galera offers us high data redundancy at the cost of some throughput.  If that doesn’t work for your workload, then asynchronous replication (or perhaps semi-sync in 5.7) will work better for you.

Note that there is wsrep_retry_autocommit.  However, this only works for autocommited transactions.  If your write volume was so high that you need to increase this a lot to get the confl

Truncated by Planet PHP, read more at the original (another 1376 bytes)

Link
Jean-Jerome Schmidt9 DevOps Tips for Going in Production with Galera Cluster for MySQL - Webinar Replay & Slides (14.11.2014, 12:45 UTC)
November 14, 2014
By Severalnines

Thanks to everyone who attended and participated in this week's webinar on '9 DevOps Tips for Going in Production with Galera Cluster for MySQL'. If you missed the sessions or would like to watch the webinar again & browse through the slides, they are now available online.

 

In this webinar, Severalnines CTO Johan Andersson discussed 9 key aspects to consider before taking Galera Cluster for MySQL into production:

  • 101 Sanity Check
  • Operating System
  • Backup Strategies
  • Galera Recovery
  • Query Performance
  • Schema changes
  • Security / Encryption
  • Reporting
  • Protecting from Disasters

 

Watch the replay

9 DevOps Tips for Going in Production with Galera Cluster for MySQL from Severalnines AB

 

Read the slides

9 DevOps Tips for Going in Production with Galera Cluster for MySQL - Slides from Severalnines AB

 

Operations is not so much about specific technologies, but about the techniques and tools you use to deploy and manage them. Monitoring, managing schema changes and pushing them in production, performance optimizations, configurations, version upgrades, backups; these are all aspects to consider – preferably before going live.

 

read more

Link
Shlomi NoachJoining Booking.com (14.11.2014, 12:04 UTC)

I'm excited to be joining Booking.com at the Netherlands in a couple weeks :)

I'm looking forward to be working with a great team and friendly people! I hope to contribute from my experience and of course be challenged by difficult problems.

Booking.com is a supporter of open source in multiple aspects, and I am looking forward to continue working with open source solutions as well as releasing open source code.

I am leaving my work at Outbrain feeling grateful for the opportunity of working at this wonderful company! I am awed and humbled by the amazing teams I've worked with, whose level of knowledge and insights I can only aspire to match. Thank you in particular to the Infrastructure team, of which I was proud to be part of.

Outbrain allowed me and others (and in fact encouraged and supported) to develop as much open source as we saw fit. This is not a minor thing: when you orient your code towards open source, you need to make generalizations which are not always providing direct benefit to the company, and which consume precious time. I hold Outbrain in the highest respect for their support for open source.

 

 

 

Link
Peter ZaitsevOptimizing MySQL for Zabbix (14.11.2014, 11:00 UTC)

dolphins making friends with zabbix
This blog post was inspired by my visit at the annual Zabbix Conference in Riga, Latvia this year, where I gave a couple of talks on MySQL and beyond.

It was a two day single-track event with some 200 participants, a number of interesting talks on Zabbix (and related technologies) and really well-organized evening activities. I was amazed how well organized the event was and hope to be invited to speak there next year as well. ;)  (Just in case you’re not sure what Zabbix is, it is an enterprise-class open source distributed monitoring solution for networks and applications)

I must secretly confess, it was also the first conference where I honestly enjoyed being on stage and connecting with the audience – I was even looking forward to it rather than being scared as hell (which is what typically happens to me)! I guess it was all about the positive atmosphere, so big thanks to all the speakers and attendees for that. It meant a lot to me.

If I had to mention one negative vibe I heard from attendees, it would be that there was not enough deeply technical content, however, I think this is slightly biased, because people I talked to most, were ones who enjoyed my technical talks and so they were craving for more.

And now, without further ado, let me get to the essence of this blog post.

Zabbix and MySQL

The very first thing I did when I arrived at the conference was to approach people who I knew use Zabbix on a large scale and tried to figure out what were the biggest challenges they face. Apparently, in all of the cases, it was MySQL and more specifically, MySQL disk IO.

With that in mind, I would like to suggest a few optimizations that will help your MySQL get the best out of your disks (and consequentially will help your Zabbix get the best out of MySQL) and the available hardware resources in general.

SSD is a game changer

“Will MySQL run better on SSDs?” I’ve been hearing this question over and over again, both publicly and privately.

I can tell you without a shadow of doubt, if IO is currently your bottle-neck – either because some queries take a long time to run and you see diskstats reporting 100-250 reads per second until the query completes (latency), or because you are overloading the disks with requests and wait time suffers (throughput), SSDs will definitely help and not just by little, by much!

Consider this: the fastest spinning disk (15k rpm) can do 250 random IO operations per second tops (at this point it is limited by physics) and single query will only ever read from one disk even if you have RAID10 made of 16 disks, so if you need to read 15,000 data points to display a graph, reading those data points from disk will take 60s.

Enterprise-class SSD disk, on the other hand, can do 15,000 or even more 16k random reads per second with a single-thread (16k is the size of an InnoDB block). And as you increase the throughput, it only gets better! So that means that the query in the previous example would take 1s instead of 60s, which is a significant difference. Plus you can run more requests on the same SSD at the same time and the total number of IO operations will only increase, while a single spinning disk would have to share the available 250 IO operations between multiple requests.

The only area where SSDs don’t beat spinning disks (yet) is sequential operation, especially single-threaded sequential writes. If that is your typical workload (which might be the case if you’re mostly collecting data and rarely if ever reading it), then you may want to consider other strategies.

MySQL configuration

Besides improving your disk IO subsystem, there’s ways to reduce the pressure on IO and I’m going to cover a few my.cnf variables that will help you with that (and with other things such as internal contention).

Note, most of the tunables are common for any typical high-performance MySQL setup, though some are explicitly suited for Zabbix because you can relax a few parameters for great effect at the price of, in the worst case, loosing up to 1s worth of collected data which, from discussions during the conf

Truncated by Planet PHP, read more at the original (another 8707 bytes)

Link
Peter ZaitsevPercona Live London 2014 Wrap Up (13.11.2014, 14:16 UTC)

Percona Live London 2014 SummaryThe 2014 edition of Percona Live London brought together attendees from 30 countries to hear insightful talks from leaders in the MySQL community. The conference kicked off on Monday with a full day of tutorials followed by the very popular Community Dinner featuring a double decker bus shuttle from the conference to the event.

Tuesday started with keynote talks by representatives from MySQL, VMware, HGST, Codership, and Percona. I particularly enjoyed the talks by Tomas Ulin of MySQL (which highlighted the upcoming MySQL 5.7 release) and Robert Hodges of VMware (which focused on the evolution of MySQL). The remainder of the day was filled by six time slots of breakout sessions (30 sessions in all) broken into 10 tracks. The day wrapped up with the always popular Community Networking Reception. Attesting to the quality of the conference, 4 out of 5 respondents to our post conference survey indicate they are likely to attend the conference again in 2015.

The session slides are available by visiting the Percona Live London 2014 conference website (look for the “Session Slides” button in the right hand column). Slides are added as they come in from the speakers so please check back if the slides are not yet available for a specific talk that interests you.

Special thanks goes out to the Percona Live London 2014 Conference Committee which put together such a great program:

  • Cedric Peintre of Dailymotion
  • David Busby of Percona
  • Colin Charles of MariaDB
  • Luis Motta Campos of the ebay Classified Group
  • Nicolai Plum of Booking.com
  • Morgan Tocker of Oracle
  • Art van Scheppingen of Spil Games

Percona Live London 2014 Attendee Survey

Percona Live London 2014 Attendee SurveyThis year we collaborated with ComputerworldUK to run a short survey at the conference which should appear in that publication in the near future. We had 64 responses, all of them being form MySQL professionals who attended the conference. The results were interesting:

Do you agree with the statement that “Oracle has been a good steward of MySQL over the past twelve months”?
YES = 81%
NO = 19%

Are you currently running a production OpenStack environment in your organization?
YES = 17%
NO = 83%

Have you evaluated OpenStack within the past twelve months?
YES = 25%
NO = 75%

Do you plan to evaluate OpenStack in the next twelve months?
YES = 48%
NO = 52%

Are you currently using an AWS product to run MySQL in the Cloud?
YES = 28%
NO = 72%

Are you more likely to switch to a non-MySQL open source database now than you were twelve months ago?
YES = 35%
NO = 65%

The sentiment about Oracle’s stewardship of MySQL compares favorably with the comments by our own Peter Zaitsev in a recent ZDNet article titled “MySQL: Why the open source database is better off under Oracle“.

Percona Live MySQL Conference and OpenStack Live Conference

Percona Live London 2014The ratings related to OpenStack mirror our experience with the strong growth in interest in that technology. In response, we are launching the inaugural OpenStack Live 2015 conference in Silicon Valley which will focus on making attendees more successful with OpenStack with a particular emphasis on the role of MySQL and

Truncated by Planet PHP, read more at the original (another 1228 bytes)

Link
Peter ZaitsevLog rotate and the (deleted) MySQL log file mystery (12.11.2014, 15:41 UTC)

Did your logging stop working after you set up logrotate? Then this post might be for you.

Archive

Archive your log files!

Some time ago, Peter Boros wrote about Rotating MySQL Slow Logs safely, explaining the steps of a “best practice” log rotate/archive. This post will add more info about the topic.

When running logrotate for MySQL (after proper setting the /etc/logrotate.d/mysql conf file) from anacron, there’s a situation that you might potentially face if the user and password used to execute the “flush logs” command is stored in, for example, /root/.my.cnf file.

The situation:

You might find out that you have a new MySQL log file ready to receive data, but nothing is being written to it.

Why did this happen?

The logrotate script is executed, but the postrotate fails to successfully flush logs. If this happened to you, you might think, “I’ve lost my slow log file!” The good news: You didn’t lose it. What just happened is that your MySQL log file is no longer visible from the filesystem perspective, but the file still exists and is still receiving data.

So where is it? How can I find it again?

Through the file descriptor. If your mysqld still running, you can find your log under /proc/[pid of mysqld process]/fd path:

[root@hostname]# cd /proc/$(/sbin/pidof mysqld)/fd
[root@hostname fd]# ls -lh | grep deleted
lrwx------ 1 root root 64 Oct 21 11:39 131 -> /tmp/MLQKbznR (deleted)
lrwx------ 1 root root 64 Oct 21 11:39 26 -> /tmp/ib95UPJ8 (deleted)
lrwx------ 1 root root 64 Oct 21 11:39 5 -> /tmp/ib9nYywT (deleted)
lrwx------ 1 root root 64 Oct 21 11:39 501 -> /var/log/mysql/log-slow-queries.log.1 (deleted)

And how big is it? lsof can give us the answer with the file descriptor number, which for this example is 501:

[root@hostname fd]# /usr/sbin/lsof -p $(/sbin/pidof mysqld) -ad 501
COMMAND  PID  USER   FD   TYPE DEVICE  SIZE/OFF     NODE NAME
mysqld  2813 mysql  501u   REG  253,0 976746174 70516762 /var/log/mysql/log-slow-queries.log.1 (deleted)

The output of lsof tell us that this file size is 976746174 bytes, which is 931MB aprox.

Can I recover the file contents?

Yes, you can. You just need to use the “cat” command and knowing the File Descriptor number. In this case, is 501:

cat /proc/$(/sbin/pidof mysqld)/fd/501 > /path/to/new/logfile.log

Remember that once you execute a success “flush logs” commands on the MySQL client, the old contents will disappear, so do this prior any further log rotation.

How did this happen?

Let’s examine the logrotate script:

/var/log/mysql/log-slow-queries.log {       
   create 600 mysql mysql       
   daily       
   rotate 3       
   missingok       
   compress       
   sharedscripts   
   postrotate       
      if test -x /usr/bin/mysqladmin &&  
           /usr/bin/mysqladmin ping &>/dev/null       
      then
           /usr/bin/mysql -e 'select @@global.long_query_time into @lqt_save; set global long_query_time=2000; set global slow_query_log = 0; select sleep(2); FLUSH LOGS; select sleep(2); set global long_query_time=@lqt_save; set global slow_query_log = 1;' > /var/log/mysqladmin.flush-logs 2>&1
        fi   
   endscript
}

Everything seems okay, except for one thing: When executing from cron, the HOME term environment variable will be blank. Meaning: /usr/bin/mysql won’t be able to find the file with the access credentials (user and password) and thus cannot execute the “flush logs” command.

What is the solution?

Add the HOME variable to the postscript line: env HOME=/root/

env HOME=/root/ /usr/bin/mysql -e 'select @@global.long_query_time into @lqt_save; set global long_query_time=2000; set global slow_query_log = 0; select sleep(2); FLUSH LOGS; select sleep(2); set global long_query_time=@lqt_save; set global slow_query_log = 1;'  > /var/log/mysqladmin.flush-logs 2>&1

Can I get an alert if this happens to me?

Yes! With the Percona Nagios Plugin pmp-check-mysql-deleted-files

Truncated by Planet PHP, read more at the original (another 975 bytes)

Link
Chris CalenderDiscussing the innodb_log_block_size variable (11.11.2014, 23:41 UTC)

Not a ground-breaking post here, but if you are interested in knowing more about the innodb_log_block_size variable, or if you use SSD cards and/or large InnoDB log files on ext4, then this is for you.

I’d read about it before briefly before, but didn’t give it too much thought until I ran across the following entry in an error log the other day:

InnoDB: Warning: innodb_log_block_size has been changed
from default value 512. (###EXPERIMENTAL### operation)

This got me wanting to know more.

Basically, this variable changes the size of transaction log records. Generally, the default of 512 is a good value. However, it has been found that setting it to 4096 has been beneficial when using SSD cards. (Note that while it is possible to set this to a value other than 512 or 4096, those are currently the only 2 values that make sense to use.)

Also, it has been found that 4096 is the best setting if you run ext4 along with innodb_flush_method=ALL_O_DIRECT (note both innodb_log_block_size and ALL_O_DIRECT are specific to XtraDB/XtraDB+; read: MariaDB and Percona server).

What is ALL_O_DIRECT and when it is useful?

“ALL_O_DIRECT: use O_DIRECT to open both data and log files, and use fsync() to flush the data files but not the log files. This option is recommended when InnoDB log files are big (more than 8GB), otherwise there might be even a performance degradation.”

http://www.percona.com/…/innodb_io.html#innodb_flush_method

Thus if you’re running large InnoDB log files (8G+) on ext4, you may want to consider ALL_O_DIRECT, and because you’re on ext4, you should set innodb_log_block_size=4096, the default log-block-size in ext4, in order to avoid the unaligned AIO/DIO warnings.

Likewise, if running with SSD cards, you’d likely see a performance improvement with innodb_log_block_size=4096 also.

If interested, there is a bit more about the innodb_log_block_size option here:

https://mariadb.com/…variables/#innodb_log_block_size
http://www.percona.com/…/innodb_io_55.html#innodb_log_block_size

Hope this helps.

 

Link
Chris CalenderMariaDB Launches Ambassadors Program (11.11.2014, 18:17 UTC)

It was announced today that the MariaDB Corporation is launching an Ambassadors Program on behalf of the MariaDB Foundation.

Colin Charles, Chief Evangelist at MariaDB Corporation said, “The MariaDB Ambassador Program is set up to recognize and support experienced contributors to the MariaDB and MySQL ecosystem who are responsible for representing, promoting and expanding the use of MariaDB and its ideals to the larger open source community and general public.”

He further added, “There are two distinct types of ambassadors, all of whom will be volunteers. Community Ambassadors will promote grassroots adoption in their specific region. Platform Ambassadors will contribute virtually through the creation of code, patches, features, and who lead MariaDB engineering efforts at their respective organizations. MariaDB Corporation will provide each Community Ambassador with funds to organize local MariaDB meetups and invite Platform Ambassadors to MariaDB Engineering meetings as guests.”

You can read the full press release here:

https://mariadb.com/news-events/press-releases/mariadb-launches-ambassadors-program

 

Link
Peter ZaitsevPercona Live MySQL Conference Call for Proposals closes Nov. 16 (11.11.2014, 17:40 UTC)

Percona Live MySQL Conference and ExpoThe Percona Live MySQL Conference in Silicon Valley (April 13-16, 2015) features a full day of tutorial sessions and three full days with keynote talks and breakout sessions from leading MySQL experts from around the world. Birds of a Feather sessions, two networking receptions, and a community dinner all offer great opportunities to network with the MySQL community. To make the Percona Live MySQL Conference 2015 even better than the great 2014 event, we need outstanding content from community members.

Submit your speaking proposals now for tutorials or breakout session on the Percona Live conference website. Accepted speakers receive a full conference pass and the sincere gratitude of attendees who will eagerly await your presentations. The Percona Live MySQL Conference call for proposals closes November 16.

We are currently seeking proposals for both breakout and tutorial sessions on the following topics:

  • High Availability
  • DevOps
  • Programming
  • Performance Optimization
  • Replication & Backup
  • MySQL in the Cloud
  • MySQL and NoSQL
  • MySQL Case Studies
  • Security
  • What’s New in MySQL

All submissions will be reviewed by the outstanding Conference Committee of community members:

  • Shlomi Noach from Outbrain
  • Tamar Bercovici from Box
  • Colin Charles from MariaDB
  • Sean Chighizola from Big Fish Games
  • Jeremy Cole from Google
  • Harrison Fisk from Facebook
  • Patrick Galbraith from HP
  • Jay Janssen from Percona
  • Chris Schneider from Ning.com
  • John Scott from Wellcentive
  • Gwen Shapira from Cloudera
  • Shivinder Singh from Verizon
  • Calvin Sun from Twitter
  • Morgan Tocker from Oracle
  • Peter Zaitsev from Percona

Submit your proposals now by visiting the Percona Live MySQL Conference 2015 website. The submission deadline is November 16.

If you plan to attend the conference but won’t be submitting, register now to get the lowest available rate. Visit the Percona Live MySQL Conference 2015 website for full details.

Percona Live MySQL Conference and Expo

The post Percona Live MySQL Conference Call for Proposals closes Nov. 16 appeared first on MySQL Performance Blog.

Link
Peter ZaitsevOpenStack Live Call for Proposals closes November 16 (11.11.2014, 15:20 UTC)

OpenStack Live 2015: Call for speakers open through Nov. 9The OpenStack Live conference in Silicon Valley (April 13-14, 2015) will emphasize the essential elements of making OpenStack perform better with emphasis on the critical role of MySQL and Trove. If you use OpenStack and have a story to share or a skill to teach, we encourage you to submit a speaking proposal for a breakout or tutorial session. The OpenStack Live call for proposals is your chance to put your ideas, case studies, best practices and technical knowledge in front of an intelligent, engaged audience of OpenStack Users. If you are selected as a speaker, you will receive one complimentary full conference pass. November 16th is the last day to submit.

We are seeking submissions for both breakout and tutorial sessions on the following topics:

  • Performance Optimization of OpenStack
  • OpenStack Operations
  • OpenStack Trove
  • Replication and Backup for OpenStack
  • High Availability for OpenStack
  • OpenStack User Stories
  • Monitoring and Tools for OpenStack

All submissions will be reviewed by our highly qualified Conference Committee:

  • Mark Atwood from HP
  • Rich Bowen from Red Hat
  • Andrew Mitty from Comcast
  • Jason Rouault from Time Warner
  • Peter Boros from Percona

If you don’t plan to submit a speaking proposal, now is a great time to purchase your ticket at the low Super Saver rates. Visit the OpenStack Live 2015 conference website for full details.

OpenStack Live 2015 April 13-14

The post OpenStack Live Call for Proposals closes November 16 appeared first on MySQL Performance Blog.

Link
LinksRSS 0.92   RDF 1.
Atom Feed