PlanetMysql.ru – информация о СУБД MySQL

Yesterday we were having a conversation on IRC about the need for more useful information about the internals of the database.

"SHOW STATUS" is just too primitive in its design to provide the sort of detailed information you need to do operations. Yesterday we got a bug request over the number of "open tables" found after a particular query. The user had assumed the number was off, but what they hadn't realized was that the number was accurate (in this particular case, MySQL fudges a number on open tables because it can't handle count its derived tables).

One of the patches coming into the tree right now fully exposes the contents of the table cache and table definition cache to the user. You can see who holds what locks on what tables, and you can see the actual count on table access per table.

drizzle> select * from TABLE_DEFINITION_CACHE;
+-----------------+------------------------+---------+-------------+----------------+
| TABLE_SCHEMA    | TABLE_NAME             | VERSION | TABLE_COUNT | IS_NAME_LOCKED |
+-----------------+------------------------+---------+-------------+----------------+
| data_dictionary | schema_names           |       1 |           1 | FALSE          |
| data_dictionary | table_definition_cache |       1 |           1 | FALSE          |
| data_dictionary | show_tables            |       1 |           1 | FALSE          |
+-----------------+------------------------+---------+-------------+----------------+
3 rows in set (0 sec)

drizzle> select * from TABLE_CACHE;
+------------+-----------------+------------------------+-----------+----------------+---------+----------------+------+----------------+------------+----------------+
| SESSION_ID | TABLE_SCHEMA    | TABLE_NAME             | ARCHETYPE | ENGINE         | VERSION | IS_NAME_LOCKED | ROWS | AVG_ROW_LENGTH | TABLE_SIZE | AUTO_INCREMENT |
+------------+-----------------+------------------------+-----------+----------------+---------+----------------+------+----------------+------------+----------------+
|          0 | data_dictionary | schema_names           | FUNCTION  | FunctionEngine |       1 | FALSE          |  100 |            260 |          0 |              0 |
|          0 | data_dictionary | show_tables            | FUNCTION  | FunctionEngine |       1 | FALSE          |  100 |            260 |          0 |              0 |
|          1 | data_dictionary | table_cache            | FUNCTION  | FunctionEngine |       1 | FALSE          |  100 |           1113 |          0 |              0 |
|          0 | data_dictionary | table_definition_cache | FUNCTION  | FunctionEngine |       1 | FALSE          |  100 |            559 |          0 |              0 |
+------------+-----------------+------------------------+-----------+----------------+---------+----------------+------+----------------+------------+----------------+
4 rows in set (0 sec)

drizzle> select * from TABLE_DEFINITION_CACHE WHERE TABLE_COUNT > 1;
Empty set (0 sec)

drizzle> select * from TABLE_DEFINITION_CACHE WHERE TABLE_COUNT > 0;
+-----------------+------------------------+---------+-------------+----------------+
| TABLE_SCHEMA    | TABLE_NAME             | VERSION | TABLE_COUNT | IS_NAME_LOCKED |
+-----------------+------------------------+---------+-------------+----------------+
| data_dictionary | schema_names           |       1 |           1 | FALSE          |
| data_dictionary | table_cache            |       1 |           1 | FALSE          |
| data_dictionary | table_definition_cache |       1 |           1 | FALSE          |
| data_dictionary | show_tables            |       1 |           1 | FALSE          |
+-----------------+------------------------+---------+-------------+----------------+
4 rows in set (0 sec)

drizzle> select count(*) from TABLE_DEFINITION_CACHE WHERE TABLE_COUNT  > 0;
+----------+
| count(*) |
+----------+
|        4 |
+----------+
1 row in set (0 sec)

The term "ARCHETYPE" is the base primitive about what sort of table was used. It is more detailed then the ANSI "TABLE_TYPE" that exists in I_S. We still have a debate on what exactly this term should mean. One of the things I enjoy about working on Drizzle? I am not stuck in a room full of people who will spend hours on this sort of bike shed decisions.

Version gives you the current definition count for the table. Right now that number is still based on "since opened" but we will soon be storing the metadata for this so you will know how many times in the life of an object it has been changed.

We are still working out the details to SHOW TABLE STATUS. Our SHOW commands are just query rewrites to tables.

Here is a partial example of the new table that is outputted from a SHOW TABLE STATUS:

+---------+--------+-------------------+-----------
| Session | Schema | Name              | Type
+---------+--------+-------------------+-----------
|       0 | Schema | b                 | STANDARD
|       0 | Schema | show_tables       | FUNCTION
|       1 | Schema | show_table_status | FUNCTION
|       0 | Schema | schema_names      | FUNCTION
|       0 | Schema | dfsdf             | STANDARD
|       1 | Schema | b                 | TEMPORARY
|       1 | Schema | a                 | TEMPORARY
+---------+--------+-------------------+-----------+
7 rows in set (0 sec)

Notice Session? Notice that type can be Temporary? With our system you can see the current owner of the open table and we now include whatever temporary tables you have in your own session. We have also included a larger table which you can see just your own temporary tables (and most likely I will soon create a table so that you can see all temporary tables open across all sessions). The current "Type" in SHOW TABLE STATUS is an Archetype so we will be changing that so that terms match up across the database. Consistency in design is an awesome thing :)

There is a lot more to come!

P.S. Just wait until I push the code for tracking locks in Drizzle, I demoed it at SCALE and got a lot of both positive and constructive feedback on it.
PlanetMySQL Voting: Vote UP / Vote DOWN

Giving a talk at the University of Utah on everything from scaling, clustering, mysql, mysql internals, noSQL (Cassandra) to how to manage all this stuff. If you are there at University I'm bringing some Swag!

Also I will upload the slides and put them here.
PlanetMySQL Voting: Vote UP / Vote DOWN

I've been doing Google Summer of Code projects with students since its creation. As far as intern programs go, it has been one of the most successful I have ever worked with.

Last year was particularly awesome in that with Drizzle we were able to have students work on projects that made it back into Drizzle. While I have always seen good work created, it has always been hit or miss on whether the student's work has made it back into the project. Last year though we got more code in then ever before and I believe this year will be the same. We have had students go on to jobs thanks to the work they did on Drizzle.

Interning gives you real experience, and it provides resume material which differentiates students who are going on to work in the software engineering field. Working on open source means that you have real experience on your resume, experience that an employer can see. There are many positions open in the Drizzle/MySQL ecosystem and students who have real world experience should have any easy time finding work with the knowledge you will gain from this program.

For Drizzle we have worked out a partial list for this year:
http://drizzle.org/wiki/Soc

Don't see anything you like? I am happy to add new projects or work with students on libmemcached or Gearman.

Are you interested in working on a different project? Apache, Linux, Postgres? Talk to those projects and ask them to either participate or suggest ideas on projects to them.
PlanetMySQL Voting: Vote UP / Vote DOWN

I want a tool to make some long-running schema changes almost non-blocking. They should block access to a table for no more than a few seconds. I also want to do some of these in place on a master rather than on a slave that has been taken offline. I think this will work for most schema changes. It doesn't have to work for all of them and there are restrictions. This will not work when statements that modify the table for which the schema change is done reference other tables and the other tables are modified during the schema change. If production SQL cannot be changed to meet this restriction, then the schema change can be done on a slave that has been taken offline. Is anyone else interested in such a tool? A hand-waving description of the process is:

1. Create the new table on the master. The new table might use MyISAM without indexes initially to make the insert as fast as possible and reduce the load on InnoDB.
2. Run set sql_log_bin=0 as what follows should not be written to the binlog
3. Run start transaction with consistent innodb snapshot to start an Innodb transaction and get current binlog offset of the master
4. Run insert into new_table select * from original_table on the master. Alas, this will get a transaction duration read lock on every row in original_table unless you use row based replication or hack InnoDB or set innodb_locks_unsafe_for_binlog.
5. Convert new_table to InnoDB and create indexes on it
6. Replay changes from the binlogs after the point in time recorded in step #3. This should extract changes to original_table and replay them against new_table.

PlanetMySQL Voting: Vote UP / Vote DOWN

I want a tool to make some long-running schema changes almost non-blocking. They should block access to a table for no more than a few seconds. I also want to do some of these in place on a master rather than on a slave that has been taken offline. I think this will work for most schema changes. It doesn't have to work for all of them and there are restrictions. This will not work when statements that modify the table for which the schema change is done reference other tables and the other tables are modified during the schema change. If production SQL cannot be changed to meet this restriction, then the schema change can be done on a slave that has been taken offline. Is anyone else interested in such a tool? A hand-waving description of the process is:

1. Create the new table on the master. The new table might use MyISAM without indexes initially to make the insert as fast as possible and reduce the load on InnoDB.
2. Run set sql_log_bin=0 as what follows should not be written to the binlog
3. Run start transaction with consistent innodb snapshot to start an Innodb transaction and get current binlog offset of the master
4. Run insert into new_table select * from original_table on the master. Alas, this will get a transaction duration read lock on every row in original_table unless you use row based replication or hack InnoDB or set innodb_locks_unsafe_for_binlog.
5. Convert new_table to InnoDB and create indexes on it
6. Replay changes from the binlogs after the point in time recorded in step #3. This should extract changes to original_table and replay them against new_table.

PlanetMySQL Voting: Vote UP / Vote DOWN

Imagine you tried to use the slow query log to debug a performance problem. Does the current format have enough details?

# Time: 100309 18:48:23
# User@Host: root[root] @ localhost []
# Query_time: 0  Lock_time: 0  Rows_sent: 1  Rows_examined: 1

I have added Thread_id, Errno, Start and End. Thread_id can be used to find similar data from SHOW PROCESSLIST and the binlog. Errno is useful in many cases. Start and End are there for convenience. Can you suggest anything else that would be easy to add? Note that Rows_sent and Rows_examined are always zero for insert, update and delete statements. Feature request 49756 is open to change that. Maybe that is easy to fix.

# Query_time: 0  Lock_time: 0  Rows_sent: 1  Rows_examined: 1\
           Thread_id: 3 Errno: 0 Start: 18:48:23 End: 18:48:23

PlanetMySQL Voting: Vote UP / Vote DOWN

I'll be presenting "The Thinking Person's Guide to Data Warehouse Design" at the upcoming MySQL User conference. While a lot of people think that bad SQL code is the #1 wrecking ball of data warehouses and marts, the fact is that poor database design is the first cause of both downtime and bad performance. In my presentation, I'll do my best to show how up-front worRead More...
PlanetMySQL Voting: Vote UP / Vote DOWN

 

Few weeks ago I was at FOSDEM.  It was really amazing experience. I meet many interesting people, learned quite some thing and I returned full of enthusiasm. Open Source events are really great.

But all the fun wasn't over even after the FOSDEM. I spent few more days in Bruxelles attending MySQL packagers meeting organized by SUN/Oracle. We spent quite some time talking to each other. We learned what MySQL people are doing and how. And they learned how do we deal with MySQL and what is troubling us. And many good things will come from this.

First but certainly not last of them is about to appear now. One very interesting thing we learned at meeting was MySQL release policy. What openSUSE and Ubuntu and maybe some others are doing is that after release date there is generaly no version updates allowed. We are only fixing serious bugs and security related issues. It takes quite some work. What we learned is that new releases in stable branch of MySQL are in fact maintanance updates. If you update from 5.1.43 to 5.1.44 you wouldn't get any new features. All you will get are bugfixes. And only bugfixes of serious or security related issues. Does it sound familiar? Yes it is the same thing we are doing! So I discussed it with our maintanance team. And we came to the conclusion that we want to give our users all serious fixes. Not only these few selected. And the best way to do it is to use maintanance updates provided by MySQL people themself. I'm not saying that I don't have enough confidence to play with MySQL sources, but I think that MySQL people can do it better

Yes, you are guessing right. What I'm trying to say is that we are going to update MySQL to the latest available version. This means 5.1.44 for openSUSE 11.2 and 5.0.90 for older openSUSE. We will start with 11.2 as version gap is smaller there and if everything will proceed smoothly, we will continue with 11.1 and 11.0. For 11.2 you can help by testing update. Currently 5.1.44 update is prepared for 11.2 in server: database: STABLE and I'm running some final tests. If you want, you can try it too (not recomended on production servers yet) and if you'll find any problems, please report them before it will hit official updates.

Remember, this is just the beginning. I've got some bigger plans regarding MySQL in 11.3

PlanetMySQL Voting: Vote UP / Vote DOWN

Well, more defections from Oracle, it's clear where the wind is blowing. It's as if all the cool and interesting stuff is quickly shedding itself from Oracle.
PlanetMySQL Voting: Vote UP / Vote DOWN

I'll be presenting two talks this year:

Faster Than Alter - Less Downtime

"This will be a informative talk about real world problem solving and the powerful yet sometimes overlooked LOAD DATA INFILE command. This talk is for MySQL DBAs who want to expand their
knowledge, improve performance and decrease customer facing downtime"

Get Your Replication On: Advanced Techniques, Tips and Tricks

Co-speaking with Sarah Sproehnle for this one! We have lots of interesting uses for replication and some best practices up our
sleeves. Warning: we won't be covering how to set up basic replication
- that's a prerequisite for this talk!

Hope to see you there!

PlanetMySQL Voting: Vote UP / Vote DOWN