Author: sheeri

MySQL Puppet Module and Slides

During yesterday’s MySQL Connect conference, Brandon Johnson and I gave a joint talk about how Mozilla uses puppet to manage hundreds of MySQL servers efficiently. We also released our mysql puppet module on github, so that all can benefit. The … Continue reading

During yesterday’s MySQL Connect conference, Brandon Johnson and I gave a joint talk about how Mozilla uses puppet to manage hundreds of MySQL servers efficiently. We also released our mysql puppet module on github, so that all can benefit. The slides for the talk are at http://bit.ly/puppet-mysql-slides.

Video: Getting Started with Performance Schema

Recently I gave a new talk to both the Tokyo and Boston MySQL User Groups about how to get started using performance schema. I have put some resources online for those interested: Performance Schema talk video PDF slides The feedback … Continue reading

Recently I gave a new talk to both the Tokyo and Boston MySQL User Groups about how to get started using performance schema. I have put some resources online for those interested:

Performance Schema talk video

PDF slides

The feedback has been excellent, so I hope that you find this video useful when trying to learn how to use performance schema and dive into the depth of the information it provides you.

Indexing Talk Online

I am doing a quick blog post to announce that I have put an indexing talk online*. Most recently, I delivered this indexing talk at Confoo and Scale 11x. The talk is on YouTube at Are You Getting the Best … Continue reading

I am doing a quick blog post to announce that I have put an indexing talk online*. Most recently, I delivered this indexing talk at Confoo and Scale 11x.

The talk is on YouTube at Are You Getting the Best Out of Your MySQL Indexes? There are also PDF slides.
From the official conference description, if you want to know more:
MySQL indexes are often used to make performance better. However, they can make performance suffer if you are not using them properly. Oracle ACE Director Sheeri Cabral explains the pitfalls to avoid with indexes and how to utilize compound indexes to maximize index availability with the least amount of write overhead.

*I know I have not been posting blogs for a long time. This was a very busy year, and I took March through July off from conferences in order to buy a house and move.

2013 SkySQL and MariaDB Solutions Videos Are Online!

I do not recall seeing an announcement about it, but I went looking for the videos today and lo and behold, they were up! Forgive me if I missed a post about it….but if you also missed it, here they … Continue reading

I do not recall seeing an announcement about it, but I went looking for the videos today and lo and behold, they were up! Forgive me if I missed a post about it. But if you also missed it, here they are:

2013 SkySQL and MariaDB Solutions Day for the MySQL Database videos

A Different Spin On the max_allowed_packet Problem

Back in November, I filed MySQL bug 67448, talking about a different type of max_allowed_packet problem. See, an application had put data into the database, but could not retrieve it without getting max_allowed_packet. With the help of some really smart … Continue reading

Back in November, I filed MySQL bug 67448, talking about a different type of max_allowed_packet problem.

See, an application had put data into the database, but could not retrieve it without getting max_allowed_packet. With the help of some really smart community folks (named Jesper Hansen, Brandon Johnson and Shane Bester), we determined that MySQL actually has 2 different max_allowed_packet settings: client and server.

When you change the max_allowed_packet variable, you are changing the server variable if it is in [mysqld] and the client variable if it is in [client] or [mysql] or whatever client you have. As far as we can tell, theres no way to actually view what the client variable is, as looking at both the session and global max_allowed_packet variable shows you the server variable.

If max_allowed_packet is not set by the client, it defaults to 16M. The proposed solution is to allow it to be increased for non-interactive clients, and the bug has been verified as a feature request, though it has not been implemented yet.

ulimits and upgrading from Oracle MySQL 5.0 to Percona patched MySQL 5.1

After upgrading to Percona’s patched MySQL 5.1*, end users were having connectivity problems, and reporting errors such as: OperationalError: (2003, “Can’t connect to MySQL server on ‘db-amo-ro’ (110)”) TimeoutError: Request timed out after 5.000000 seconds OperationalError: (1135, “Can’t create a … Continue reading

After upgrading to Perconas patched MySQL 5.1*, end users were having connectivity problems, and reporting errors such as:

OperationalError: (2003, "Can't connect to MySQL server on 'db-amo-ro' (110)")

TimeoutError: Request timed out after 5.000000 seconds

OperationalError: (1135, "Can't create a new thread (errno 11); if you are not out of available memory, you can consult the manual for a possible OS-dependent bug")

We had these same problems a while back, before increasing ulimit settings in /etc/sysconfig/mysqld. Oracles MySQL startup script specifically sources this file:

[ -e /etc/sysconfig/$prog ] . /etc/sysconfig/$prog

However, we saw these errors again when we upgraded to Perconas MySQL 5.1. At first we thought that it was because Oracles startup script is /etc/init.d/mysqld and Perconas is named /etc/init.d/mysql (so we would put ulimits in /etc/sysconfig/mysql). However, when we looked, we saw that Perconas startup script does NOT source anything in /etc/sysconfig.

So then we put the following in /etc/security/limits.d/99-nproc-mysql.conf:
root soft nproc 32768
root hard nproc 65535

We restarted MySQL and all was good. Even though we are long past having this problem, I thought it was important enough to blog about.

* We finished upgrading all of our servers to MySQL 5.1 at the end of 2012. We ran into this interesting snag that I wanted to blog about, even though were in the middle of upgrading to MySQL 5.5 right now (and by the end of the year, we will upgrade to MySQL 5.6 the performance schema stuff is definitely something we want to utilize).

MySQL User Group Video – Determinism and Databases

The May Boston MySQL User Group featured John Hugg of VoltDB talking about determinism and databases. I have uploaded the hour-long video to http://www.youtube.com/watch?v=mTDLyRauJtw. Seasoned MySQLers will nod their heads because the problems described are familiar, and those who are … Continue reading

The May Boston MySQL User Group featured John Hugg of VoltDB talking about determinism and databases. I have uploaded the hour-long video to http://www.youtube.com/watch?v=mTDLyRauJtw. Seasoned MySQLers will nod their heads because the problems described are familiar, and those who are not exactly sure what determinism and databases means will learn a lot.

Enjoy!

(As always, videos are free on YouTube with no login or attempt to solicit your e-mail address or any other information)

Upgrading support.mozilla.org databases

A while ago (November 2012 to be exact), we upgraded the support.mozilla.org databases from Percona 5.1 to MariaDB 5.5 (the next step, happening soon, is upgrading them to Oracle’s MySQL 5.6). One of the engineers and I had a conversation … Continue reading

A while ago (November 2012 to be exact), we upgraded the support.mozilla.org databases from Percona 5.1 to MariaDB 5.5 (the next step, happening soon, is upgrading them to Oracle’s MySQL 5.6). One of the engineers and I had a conversation where he mentioned that one of our worst performing views on SUMO is doing waaaayyy better with the upgraded databases, that it seems more stable and that I stopped receiving MySQL went away or disconnected emails which came in once in a while.

Its always nice to see upgrades actually making a difference. In our case we saw a lot less CPU wait, though that might also be partially due to tuning the memory settings on the machines and adding in another read slave to handle queries. As a result, network traffic throughput went from less than 1 Mb/sec to about 18 Mb/sec, because the machines were just handling more queries per second, period.

(I had this e-mail as a draft for a while and decided to clean it up and publish it now!)

Women in Science and Engineering (WISE) Computing Skills Boot Camp

Software Carpentry is running a 2-day software skills boot camp in Boston, June 24-25th 2013, for women in science, engineering, medicine, and related research areas. Registration is $20. Boot camps alternate short tutorials with hands-on practical exercises. You are taught … Continue reading

Software Carpentry is running a 2-day software skills boot camp in Boston, June 24-25th 2013,  for women in science, engineering, medicine, and related research areas. Registration is $20.

Boot camps alternate short tutorials with hands-on practical exercises. You are taught tools and concepts you can use immediately to increase your productivity and improve confidence in your results. Topics covered include the Unix shell, version control, basic Python programming, testing, and debugging — the core skills needed to write, test and manage research software.

This boot camp is open to women at all stages of their research careers, from graduate students, post-docs, and faculty to staff scientists at hospitals and in the public, private, and non-profit sectors.

Registration is $20; to sign up, or find out more, please visit the announcement at http://software-carpentry.org/blog/2013/04/announcing-wise-bootcamp.html. If you have questions, there is an e-mail link on the announcement page.

For those curious, they are using sqlite, not MySQL or PostgreSQL, and I will be helping out with the SQL parts. There are about 2 months left but the boot camp is about 2/3 full right now, so I wanted to make sure this opportunity was spread to as many people as possible so they do not hear about it too late.