In my last post, a tale of two MySQL upgrades, a few folks asked if I would outline the process we used to upgrade, and what kind of downtime we had.
Well, the processes were different for each upgrade, so I will tackle them in separate blog posts. The first step was to upgrade all our MySQL 5.1 machines to MariaDB 5.5. As mentioned in the previous post, MariaDB’s superior performance for subqueries is why we switched – and we switched back to MySQL for 5.6 to take full advantage of the performance_schema.
It is not difficult to blog about our procedure, as we have documentation on each process. My first tip would be to do that in your own environment. This also enables other folks to help, even if they are sysadmins and not normally DBAs. You may notice the steps[Read more...]
At the beginning of 2013, Mozilla’s MySQL databases were a mix of MySQL 5.0, Percona’s patched MySQL 5.1, Percona’s patched MySQL 5.5 and MariaDB 5.5. MySQL 5.1 was released in November 2008 – so at the beginning of the year, we still had databases with no new major features in 4 years. Currently we have almost all our databases at Oracle’s MySQL 5.6 – the only stragglers are our cluster running TokuDB and a few machines that are no longer in use. Here’s a graph showing the state of our machines – you can see that in the first half of the year we concentrated on upgrading our 5.0 and 5.1 servers to 5.5, and then in the second half of the year we upgraded everything to MySQL 5.6 (click on the image to get a larger[Read more...]
Within MySQL, there’s a piece called a storage engine that reads and writes to disk on your behalf when you execute a query. It controls the way that your data is stored on disk. With MySQL, you can change what storage engine you use, which is helpful since every engine has different advantages and downsides, and you can select which engine to use based on your workload. There are two main engines: MyISAM and InnoDB. MyISAM was the default engine before MySQL 5.5 and it’s been there since the beginning. It’s also not crash-proof, it doesn’t have foreign keys, and it’s not transactional. InnoDB, on the other hand, has all these features.[Read more...]
I recently came across an issue trying to connect to a MySQL server using the mysql client. It appeared as through the connection was hanging.
A subsequent connection using the -A option highlighted the problem with the previous connection stuck in the state “Waiting for table metadata lock”.
mysql> SHOW PROCESSLIST\G *************************** 1. row *************************** Id: 37 User: root Host: localhost db: tmp Command: Query Time: 90 State: preparing Info: create table missing as select id from AK where id not in (select id .. *************************** 2. row *************************** Id: 38 User: root Host: localhost db: tmp Command: Field List Time: 50 State: Waiting for table metadata lock Info: *************************** 3. row ***************************[Read more...]
Yesterday we had our third Madrid MySQL users group meeting. That was quite interesting. Thanks go to Juan for his presentation.
We plan the next meeting on January 16th after the New Year is out of the way. If you are interested in MySQL and happen to be in Madrid please consider coming to see us.
More information about the next meeting can be found on the group’s web page. Note: The meeting will be in Spanish. I look forward to seeing you.
Next week the US celebrates Thanksgiving Day. For those that are American or live here, this is a significant event. Three different experiences recently have lead me to write this request for ALL MySQL community members to give thanks to those that have contributed to the MySQL ecosystem. I have made a commitment to myself, and I would like to challenge others to write one book review per week in December, that’s 4 book reviews to the MySQL books that I have on my bookshelf that have made an impact in some way. I ask others to give it a go too.
It only takes a few minutes to pen a comment on Amazon, or a publishers site, but to authors it means so much more. I can only speak for myself, but any comment; good, bad or ugly; helps to know you are out there and you took the time to acknowledge somebody’s work of art (in this case a[Read more...]
Translating theory to practice is never easy. Morgan gives us the right steps in a play environment to move from dev.mysql.com native MySQL rpm’s to the new MySQL yum repository. I thought I would try it out.
A necessary step, however immediately I have more dependencies including Perl DBD (used in several utilities) including MHA.
$ sudo su - $ rpm -qa | grep -i mysql MySQL-devel-5.6.13-1.el6.x86_64 MySQL-test-5.6.13-1.el6.x86_64 MySQL-shared-compat-5.6.13-1.el6.x86_64 MySQL-server-5.6.13-1.el6.x86_64 perl-DBD-MySQL-4.013-3.el6.x86_64 MySQL-client-5.6.13-1.el6.x86_64 MySQL-embedded-5.6.13-1.el6.x86_64 MySQL-shared-5.6.13-1.el6.x86_64 mha4mysql-node-0.54-1.el5.noarch
A further trap[Read more...]
Using the MySQL 5.6 Performance Schema it is very easy to see what is actually running on your MySQL instance. No more sampling or installing software or worrying about disk I/O performance with techniques like SHOW PROCESSLIST, enabling the general query log or sniffing the TCP/IP stack.
The following SQL is used to give me a quick 60 second view on a running MySQL system of ALL statements executed.
use performance_schema; update setup_consumers set enabled='YES' where name IN ('events_statements_history','events_statements_current','statements_digest'); truncate table events_statements_current; truncate table events_statements_history; truncate table events_statements_summary_by_digest; do sleep(60); select now(),(count_star/(select sum(count_star) FROM events_statements_summary_by_digest) * 100) as pct, count_star, left(digest_text,150) as stmt, digest from[Read more...]
Recently a client asked me how long it would take for an ALTER TABLE to complete. Generally the answer is “it depends”. While this was running on a production system I tried with the Performance Schema in MySQL 5.6 to work out some answer to this question. While I never got to investigate various tests using INPLACE and COPY for comparison, Morgan Tocker made the request for experiences with online ALTER in A closer look at Online DDL in MySQL 5.6. Hopefully somebody with more time can expand on my preliminary observations.
Using Mark Leith’s ps_helper (older version) I monitored the File I/O to see if I could determine when using innodb_file_per_table the percentage of[Read more...]
I was really pleased to see the announcement by Oracle MySQL yum repositories that they have now produced a yum repository from where the MySQL RPMs they provide can be downloaded. This makes keeping up to date much easier. Many companies setup internal yum repositories with the software they need as then updating servers is much easier and can be done with a simple command. For many people at home that means you set this up once and don’t need to check for updates and do manual downloads, but can do a quick yum update xxxx and you get the latest version. Great! This new yum repository only covers RHEL6 did not include RHEL5 which is not yet end of life and still used by me and probably quite a lot of other people. I filed[Read more...]
Working with MySQL 5.6 under CentOS 6.4 I came across the following problem with MySQL reporting it did not shutdown successfully.
$ sudo su - $ service mysql stop Shutting down MySQL................................................................................................................................................................................................................................................................................................ .........................................................................................................................................................................................................................................................................................................[Read more...]
The announcement of the MySQL Yum repositories was a long overdue request on my wish list. While it was possible to find MySQL at http://public-yum.oracle.com/ it was not the GA version MySQL 5.6. (As I write this post, I check and find that indeed it now appears this may be possible http://public-yum.oracle.com/repo/OracleLinux/OL6/MySQL56/).[Read more...]
A trap for those new to the MySQL Performance Schema is the expectation that thread_id in tables such as events_statements_current matches the id you find in the MySQL processlist. This is NOT TRUE.
If we look at the INFORMATION_SCHEMA.PROCESSLIST table we will find information like:
mysql> select id,db,command,state from information_schema.processlist order by id; -----------+--------------------+---------+------------------------------------------------------------------+ | id | db | command | state | +-----------+--------------------+---------+------------------------------------------------------------------+ | 1 | NULL | Connect | Slave has read all relay[Read more...]
Using MySQL 5.6.13 under CentOS 6.4, I had a replication failure on one master/slave topology because the master binary log had the following entry that was intentionally written by the MySQL server.
$ mysqlbinlog --start-position=244670849 mysql-bin.000029 ... # at 244670849 #131028 19:31:38 server id 39 end_log_pos 244670906 CRC32 0xc5d084ec # Incident: LOST_EVENTS RELOAD DATABASE; # Shall generate syntax error # at 244670906 ...
The question is why? I do not know the answer. Searching online indicates this can occur in a MySQL Cluster environment, and can occur around the use of GRANT statements. Neither of these situations are applicable here. This site runs 4 different master/slave topologies all running the same version, and this has been the only occurrence.
The message, if taken literally could indicate that the[Read more...]
I was playing around with MySQL Workbench earlier in the week, and ran across the “clean up SQL” feature, which I thought was neat. Here’s a picture-based demonstration – you can click on the pictures to make them bigger, so they are more readable.
Pretty cool, for just the click of a button!
Last week I was a guest speaker at the second annual MySQL/NoSQL/Cloud Conference held in Buenos Aires, Argentina. Thanks to Santiago Lertora from Binlogic who has taken on the responsibility of organizing a event for the MySQL community in South America.
My presentations slides for my 3 talks.
While the MySQL community and ecosystem has changed[Read more...]
Character sets are like the force: they surround us and penetrate us, binding all our digital world together. A character set is how we convert the 1’s and 0’s that the computer understands into human-readable characters like ABC. In one of the first character sets, ASCII, the number 97 is translated to “a” and 63 is the question mark (?).
“Are there other languages besides English?”
“Don’t think so, Bob.”
The trouble with ASCII is that it was created back in the ‘60s by a bunch of Americans and they were not thinking about French or German, they were thinking about English. Guess what? ASCII works great for American[Read more...]
One day, after taking a job using MySQL, I was writing a query quietly at my desk. It was around quittin’ time, and I was whipping up a new report on the monthly sales figures that the CEO needed for his board meeting in the morning. I, in my ignorance, wrote a nice query with a simple little sum() function. What I did not realize is this caused all my rows to sum up to one row when it really should’ve show several rows. This made my report completely inaccurate. Blindly trusting me as I had blindly trusted MySQL, the CEO presented my report to the board who decided that the company was grossly unprofitable and voted to shut it down. Only when I was waiting in the bread line at my local workhouse did I realize my mistake.[Read more...]
The “Too many connections” problem is a common issue with applications using excessive permissions (and those that grant said global permissions). MySQL will always grant a user with SUPER privileges access to a DB to investigate the problem with a SHOW PROCESSLIST and where you can check the limits. I however found the following.
mysql> show global variables like 'max_connections'; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | max_connections | 2000 | +-----------------+-------+ 1 row in set (0.00 sec) mysql> show global status like 'max%'; +----------------------+-------+ | Variable_name | Value | +----------------------+-------+ | Max_used_connections | 6637 | +----------------------+-------+ 1 row in set (0.00 sec)
How can the max_used_connection exceed max_connections? This is[Read more...]
If you do not have the book yet, you can still do the first week by using the online material from “Browse Contents” on the O’Reilly book page for Learning MySQL. There is homework for week 1, see the master list for all the information.
If you would like to learn MySQL from the ground up, consider joining us. This is for beginners – If you have no[Read more...]
I had to laugh (just a bit) at this on the exhibitor floor at Oracle Open World 2013. There was a large MongoDB presence at the Slot 301. There are a few reasons.
First, the identity crisis remains. There is no MongoDB in the list of exhibitors, it’s 10gen, but where is the 10gen representation in the sign. 99.99% of attendees would not know this.
Second, the first and only slide I saw (as shown below), tries to directly compare implementing a solution to Oracle. The speaker made some comment but I really zoned out quickly. Having worked with MongoDB, even on one of my own projects, contemplated the ROI of being proficient in this for consulting, even discussing at length with the CEO and CTO, and hearing only issues with MongoDB with existing MySQL clients, I have come
I’ve got a new article, which is part of a new three-part series, on moving data between SQL and Hadoop, both the export to Hadoop and importing processed content back into an SQL store.
In this first one, we look at the basic mechanics and considerations before you start the migration of data, such as the data format, content, and export techniques.
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.
If you happen to have some free time tomorrow and are in Madrid please come along to the second Madrid MySQL Users Group.
Details can be found here. The meeting will be in Spanish. I look forward to seeing you.
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:
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.
Measuring how well a MySQL is doing can be tough – most metrics are simply proxies for actual performance. Thankfully, the Percona build of MySQL allows you to see exactly how long your queries are taking. It provides a diagnostic view that shows how long queries take, allowing you to gauge its real-world performance. I’ll get to that in a little bit. Before we do that, I want to talk about some other tools you can use.
You can dump the status of the system by running “show global status;”. This will give you how many connections have aborted, rows written, and[Read more...]