I currently have on a MySQL 5.6 database using innodb_file_per_table the following individual tablespace file.
The schema is all InnoDB tables, and there ARE NO Full Text Indexes. I cannot comment on if a developer has tried to create one previously.
I am none the wiser in explaining the ongoing use of these files, or if it can be/should be deleted.
On closer inspection there are infact a number of FTS files.
$ ls -al FTS* -rw-r----- 1 mysql mysql 98304 Jan 29 16:21 FTS_00000000000001bb_BEING_DELETED_CACHE.ibd -rw-r----- 1 mysql mysql 98304 Jan 29 16:20 FTS_00000000000001bb_BEING_DELETED.ibd -rw-r----- 1 mysql mysql 98304 Jan 29 16:26 FTS_00000000000001bb_CONFIG.ibd -rw-r----- 1 mysql mysql 98304 Jan 29 16:21 FTS_00000000000001bb_DELETED_CACHE.ibd -rw-r----- 1 mysql mysql 98304 Jan 29[Read more...]
Over the years you collect datasets you have created for various types of testing, seeding databases etc. I have always thought one needs to better manage this for future re-use. Recently I wanted to do some “Big Data” playing and again that question of what datasets can I use let me to review the past collated list at Seeking public data for benchmarks.
The types of things I was wanting to do lead me to realize a lot of content is “public domain” and Project Gutenberg is just one great source of text in multiple languages. This was just one aspect of my wish list but text based data is used from blogs, comments, articles, microblogs etc, and multiple languages was important[Read more...]
It is common practice to grant your application the privileges of “GRANT SELECT, INSERT, UPDATE, DELETE ON yourdb.* TO user@host”.
But what if you want to ensure you cannot DELETE data from just one table?
Ideally I want to be able to “REVOKE DELETE ON yourdb.important_table FROM user@host”. You cannot do currently this with the MySQL privilege system.
If your schema has 100 tables, and you want to remove DELETE from one, you have to define DELETE for the 99 others, and remember that for each new table, you need to remember to also modify user privileges.
Whenever I have a problem query in MySQL, I say to myself, I know, I’ll use Explain Plan. Now I have two problems. I run the explain plan, but then I have to look up all the cryptic terms and read through the documentation to know what in the world is going on. Well, over the weekend, I decided to solve my own problem. I created a simple page that allows you to run an explain plan, and then each of those obscure, abbreviated terms is annotated with a tooltip that explains exactly what’s going on. I’m calling it MySQL Explain Explained.
select gender, count(emp_no) as manager_cnt,[Read more...]
Designing database tables for many customers is a surprisingly foreign concept for some programmers. I’ve been in interviews where a programmer will sketch out a normal object model, and then proceed to suggest that for the database, each customer has their own set of tables, each prefixed with the customer name. What I’d like to do in this post is introduce the concept of multi-tenancy and then show methods you can use to do it right, instead of hacky ways like the one I just mentioned. Multi-tenancy is when several customers (tenants) share the same database and codebase but can only see their own data.
The core method of adding multi-tenancy to your database is adding a column to every table you’d like to segment that indicates the owner of the[Read more...]
I’ve covered some strange default behavior around nulls in MySQL. There’s another nuance to this issue: you can still insert rows without specifying values for not null columns. MySQL will helpfully give you a default value (this is not really helpful – true help would be a quick failure with a descriptive error message). Let’s walk through this example.
mysql> Create Table NullTable (a Int Not Null, b Int Not Null); Query OK, 0 rows affected (0.01 sec)
Here’s a table with two not null columns.
mysql> Insert Into NullTable (a) Values (1); Query OK, 1 row affected, 1 warning (0.01 sec)
We can totally insert, even though we didn’t specify a value for b and b is not[Read more...]
MySQL is the little engine that could. It powers sites like Facebook, YouTube, Twitter, and thousands of blogs, CMSes, and e-commerce sites. Its value to the world and to the development community could be measured in the hundreds of billions, and yet it’s free, and you can use it just by downloading it. Almost every programming language has drivers for it and it can run on so many operating systems and architectures, there’s really no limit on it.
Yet there’s a dark side. MySQL is full of gotchas and bugs, and it lacks features that sometimes call into question its status as a real database. The documentation is often open-ended and confusing, with gaps in key parts. If you want to run it, you have the option of using it on Linux, Mac, Solaris, or Windows and every hosting company or provider like Amazon AWS has[Read more...]
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...]