MySQL

Percona Live:London, RightScale and Eucalyptus training discounts!

(Tune in tomorrow where I tell you how you can win USD$100, like I did last week!)

Not signed up for Percona Live:London and need some motivation to do so?  How about a £40.00 discount?  That means the Expo Hall is free, regular registration is £260.00 and the tutorials + conference price is £500.00.  Use discount code "PDBUK".

In just 3 weeks, RightScale is having a cloud conference in Santa Clara, CA Nov 8-9.  There is a Zero-to-cloud training on Tuesday, November 8th, and you can get a 25% discount on the training by using code "RSCONF25".

RightScale and Eucalyptus are teaming up to provide a training, too -- on Thursday, November 10th.  If the name Eucalyptus sounds familiar, it is because former MySQL employees Mårten Mickos and Mark Atwood work there.  There is a 25% discount off that training by using code "RSEUCT25".

Important Caveats When Using pt-online-schema-change

 

Using pt-online-schema-change is a great way to change a table without downtime or switching over to a secondary database first.  While using it on production systems, we found some interesting issues to consider.

 

1) There were foreign_keys referencing the table we were altering.  There are two options to handle this.  They are specified with "--update-foreign-keys-method rebuild_constraints|drop_old_table".  One is to rebuild the tables with foreign keys.  This does lock the tables, and will block reads and writes while it is happening.  I chose to use drop_old_table.  This method disables foreign key checks, then drops the original table and renames the new table before reenabling foreign key checks.  Normally, pt-online-schema-change just renames the tables, then drops the old table, so locking is minimal.  We did find that there was locking while dropping the original table and renaming the new table.  This is something to be cautious with, especially with large tables.  In theory, you could run an online change to drop the foreign key constraints on the child tables prior to working on the parent table.

 

2) When testing, this ran in 15 minutes against an unladen database and took over 24 minutes against production.  You could add --sleep n, where n is a number of seconds, if you need it to add less load to production.  

 

3) One limit we also saw is that pt-online-schema-change will only work on tables with at least one unique single column index, such as a single column primary key.  Often partitioned tables don't have a unique single column index.

 

 

Here is some of the output of a sample pt-online-schema-change...

 
 
## Install pt-online-schema-change and pre-requisites...
wget percona.com/get/pt-online-schema-change
chmod 755 pt-online-schema-change
yum install perl-DBI
yum install perl-DBD-MySQL
 
time ./pt-online-schema-change h=127.0.0.1,D=my_schema,t=my_table -P3306 --alter "add column signature varbinary(255) NULL DEFAULT NULL; add column signature_version varchar(4) NULL DEFAULT NULL" --child-tables fk_table1,fk_table2 --update-foreign-keys-method drop_old_table -pXXXX --bin-log
# 2011-10-14T03:20:28 ./pt-online-schema-change started
# 2011-10-14T03:20:28 USE `my_schema`
# 2011-10-14T03:20:28 Alter table my_table using temporary table __tmp_my_table
# 2011-10-14T03:20:28 Checking if table my_table can be altered
# 2011-10-14T03:20:28 SHOW TRIGGERS FROM `my_schema` LIKE 'my_table'
# 2011-10-14T03:20:28 Table my_table can be altered
# 2011-10-14T03:20:28 Chunk column id, index PRIMARY
# 2011-10-14T03:20:28 Chunked table my_table into 25685 chunks
# 2011-10-14T03:20:28 User-specified child tables: fk_table1, fk_table2
# 2011-10-14T03:20:28 Starting online schema change
# 2011-10-14T03:20:28 CREATE TABLE `my_schema`.`__tmp_my_table` LIKE `my_schema`.`my_table`
# 2011-10-14T03:20:28 ALTER TABLE `my_schema`.`__tmp_my_table` add column signature varbinary(255) NULL DEFAULT NULL
# 2011-10-14T03:20:29 ALTER TABLE `my_schema`.`__tmp_my_table`  add column signature_version varchar(4) NULL DEFAULT NULL
# 2011-10-14T03:20:30 Shared columns: id, col1, col2, col3, col4, col5
# 2011-10-14T03:20:30 Calling OSCCaptureSync::capture()
# 2011-10-14T03:20:30 CREATE TRIGGER mk_osc_del AFTER DELETE ON `my_schema`.`my_table` FOR EACH ROW DELETE IGNORE FROM `my_schema`.`__tmp_my_table` WHERE `my_schema`.`__tmp_my_table`.id = OLD.id
# 2011-10-14T03:20:30 CREATE TRIGGER mk_osc_upd AFTER UPDATE ON `my_schema`.`my_table` FOR EACH ROW REPLACE INTO `my_schema`.`__tmp_my_table` (id, col1, col2, col3, col4, col5) VALUES (NEW.id, NEW.col1, NEW.col2, NEW.col3, NEW.col4, NEW.col5)
# 2011-10-14T03:20:30 CREATE TRIGGER mk_osc_ins AFTER INSERT ON `my_schema`.`my_table` FOR EACH ROW REPLACE INTO `my_schema`.`__tmp_my_table` (id, col1, col2, col3, col4, col5) VALUES(NEW.id, NEW.col1, NEW.col2, NEW.col3, NEW.col4, NEW.col5)
# 2011-10-14T03:20:30 Calling CopyRowsInsertSelect::copy()
Copying rows:   1% 24:52 remain
Copying rows:   3% 25:01 remain
...
Copying rows:  96% 00:43 remain
Copying rows:  98% 00:15 remain
# 2011-10-14T03:44:14 Calling OSCCaptureSync::sync()
# 2011-10-14T03:44:14 Renaming foreign key constraints in child table
# 2011-10-14T03:44:14 SET foreign_key_checks=0
# 2011-10-14T03:44:14 DROP TABLE `my_schema`.`my_table`
# 2011-10-14T03:44:49 RENAME TABLE `my_schema`.`__tmp_my_table` TO `my_schema`.`my_table`
# 2011-10-14T03:44:49 Calling CopyRowsInsertSelect::cleanup()
# 2011-10-14T03:44:49 Calling OSCCaptureSync::cleanup()
# 2011-10-14T03:44:49 DROP TRIGGER IF EXISTS `my_schema`.`mk_osc_del`
# 2011-10-14T03:44:49 DROP TRIGGER IF EXISTS `my_schema`.`mk_osc_ins`
# 2011-10-14T03:44:49 DROP TRIGGER IF EXISTS `my_schema`.`mk_osc_upd`
# 2011-10-14T03:44:49 ./pt-online-schema-change ended, exit status 0
 
real    24m20.777s
user    0m3.936s
sys     0m1.216s

The Magic of kSar for one-time system graphs

I forget from whom I first learned about kSar, but I am in debt to that person once again.  I first learned about it about a year ago, and it has been extremely useful whenever I am trying to debug system where I do not have access to trending graphs.  kSar is an open source graphing tool for Linux, Mac OS X and Solaris sar output.  Graphs can be generated and exported to many different formats (JPG, PNG, and even a PDF with all the graphs in it).

By simply copying a few files from /var/log/sa (at least that's the default on Linux, and of course it does depend on sar being installed and running, but 95% of the time it is), running kSar and choosing the data file, you get beautiful graphs.  Try it right now, it will take you less than 10 minutes. (Note, I have a Mac, and I know the client graphical program works on Linux/Unix/Solaris, but I have no clue if it works on Windows -- there is a run.sh script, but it just calls a jar file, so I am pretty sure it will work on Windows.)

I copy the /var/log/sa/sar* files to my laptop, then cat them together (usually cd /path/to/sa, cat sar* > alldays), then run kSar and click "Data -> Load from text file".  It is just that simple.

Call for Papers for Collaborate ends tomorrow!

The call for papers for Collaborate ends tomorrow, Friday October 14th.  Collaborate is Sunday, April 22nd through Thursday April 26th at the Mandalay Bay Convention Center in Las Vegas.  This year the folks scheduling the MySQL Conference and Expo have smartly chosen not to conflict, and in fact they are 2 weeks apart.

Collaborate is the largest independent (not put on by Oracle) conference for users of Oracle software.  This year we are having a one-day MySQL track at Collaborate, so that speakers and attendees can focus and optimize their time spent at the conference.

This is a great chance to give a talk to operations folks about running MySQL.  The audience is mixed, with some beginners and some intermediate folks.  We expect over 100 attendees to attend the MySQL sessions, as have done in the past 2 years.  

 

The track will have 1 or 2 MySQL talks per session time, so that the attendance is bigger and there is less that each attendee can miss -- last year the feedback from attendees was "I want to clone myself!" and the feedback fom speakers was "I wish there were more people in the room".  This year we are rectifying that.

 

So, don't forget to submit your paper today!

Why are your indexes larger than your actual data?

I sometimes encounter this phenomenon when a database has tables in which the indexes are larger—sometimes much larger—than the actual data stored in the table. Often, the table becomes unwieldy, which generates a number of questions: Should I partition? Should I shard? Should I use a different database entirely?

 

The problem is especially common with InnoDB tables because of:

* Clustered indexes - in Innodb, the primary key is included in every secondary key. If the primary key is large, it could have a detrimental effect on the entire table.

* Slight index overhead - "each index record contains a five-byte header that may be preceded by a variable-length header. The header is used to link together consecutive records, and also in row-level locking." 

* The indexes are fragmented (here’s what you can do about this problem).

When comparing to MyISAM, which compresses its string indexes, InnoDB looks quite big.

 

Here is an actual example:

 mysql> SELECT engine, count(*) tables, concat(round(sum(table_rows)/1000000,2),'M') rows, concat(round(sum(data_length)/(1024*1024*1024),2),'G') data, concat(round(sum(index_length)/(1024*1024*1024),2),'G') idx, concat(round(sum(data_length+index_length)/(1024*1024*1024),2),'G') total_size, round(sum(index_length)/sum(data_length),2) idxfrac

FROM information_schema.TABLES GROUP BY engine

ORDER BY sum(data_length+index_length) DESC LIMIT 10;

+------------+--------+---------+--------+---------+------------+---------+

| engine     | tables | rows    | data   | idx     | total_size | idxfrac |

+------------+--------+---------+--------+---------+------------+---------+

| InnoDB     |    960 | 278.31M | 75.76G | 119.27G | 195.03G    |    1.57 |

| MyISAM     |   1054 | 140.75M | 16.43G | 8.21G   | 24.64G     |    0.50 |

| MRG_MYISAM |     18 | 4.46M   | 0.48G  | 0.00G   | 0.48G      |    0.00 |

| MEMORY     |     44 | NULL    | 0.00G  | 0.00G   | 0.00G      |    NULL |

| NULL       |      7 | NULL    | NULL   | NULL    | NULL       |    NULL |

+------------+--------+---------+--------+---------+------------+---------+

 

mysql> SELECT CONCAT(table_schema, '.', table_name),  CONCAT(ROUND(table_rows / 1000000, 2), 'M')  rows,  CONCAT(ROUND(data_length / ( 1024 * 1024 * 1024 ), 2), 'G')                    DATA, CONCAT(ROUND(index_length / ( 1024 * 1024 * 1024 ), 2), 'G')  idx, CONCAT(ROUND(( data_length + index_length ) / ( 1024 * 1024 * 1024 ), 2), 'G') total_size, ROUND(index_length / data_length, 2) idxfrac FROM  information_schema.TABLES ORDER  BY data_length + index_length DESC LIMIT  3;

+---------------------------------------+--------+-------+--------+------------+---------+

| CONCAT(table_schema, '.', table_name) | rows   | DATA  | idx    | total_size | idxfrac |

+---------------------------------------+--------+-------+--------+------------+---------+

| db1.big_old_table              | 48.48M | 8.38G | 27.25G | 35.63G     |    3.25 |

| db1.big_new_table            | 38.63M | 6.32G | 18.13G | 24.44G     |    2.87 |

| db1.another_big_table       | 17.83M | 2.14G | 9.92G  | 12.06G     |    4.64 |

+---------------------------------------+--------+-------+--------+------------+---------+

 

Warning: be careful when you run the above queries. Try to run them on an available and non-production server whenever possible as they may seriously slow down your server.

 

As you can see, the differences between the index sizes vary between 3-4.5 times the size of the data.

 

My experience suggests the root causes of why the indexes are bigger than the data, could be any of the following:

1) Too many indexes

2) Indexes on big columns - like varchar(255)

3) Redundant or duplicate indexes

4) Combination of all of the above

 

And what are the root causes of these problems? Here are some potential answers, garnered, once again, from personal experience:

1) A happy trigger on the phpmyadmin "add index" button

2) When the table was small, some queries were slow and indexes were added indiscriminately until these queries were fast (which explains the previous point)

3) Someone tried numerous different combinations of multi-column indexes to speed up different queries using the same table(s)

4) Additional indexes could have been used to solve bad (after-the-fact) schema design issues

 

Please note that there could be more causes.  This is only a small sample of possibilities.

 

How to solve the problem?

This is something I am still learning, but so far I have discovered that:

1) You need to find out which indexes are not being used any more. To do that, you can use pt-index-usage. What you would need is the general log (or your slow log at long_query_time=0) and you would need to run it through pt-index-usage. This will generate a list of ALTER TABLE statements. This tool can also generate the findings or the decisions for the results into database of your choice (meaning you need to give it a database server if you want that extra data).

This report logs the indexes that were used by the queries in the general/slow log and suggests that you DROP the ones that are not. What's important to remember is that queries which did not run during the time you took your log - such as weekly or monthly reports - will not show up in the results.

I find this tool useful, because I can use it with the same log I used for pt-digest-query. However, I do not blindly run all the drop table statements, I just use them to help me understand the indexes better.

I sometimes use pt-duplicate-key-checker, but usually, pt-index-usage finds all or almost all of what pt-duplicate-key-checker finds.

 

2) I pick a table from the top table query I ran and investigate the indexes on it. I look for duplicate indexes or an index with similar columns (such as index1(a,b), index2(a), index3(b,a,c)) and try to determine why they are there.

Next, I try to go over the queries that access the table from the general/slow log and see how they use the indexes. There are no real short cuts to this process. Usually, I focus on low hanging fruit and I am very careful not to negatively impact other queries that I may not know about.

What I try to do is gather the uniqueness/cardinality of the indexes and determine how they help queries.

 

Here is an example to explain better:

show create table users(

user_id INT(4) UNSIGNED AUTOINC,

fullname VARCHAR(255) DEFAULT '',

active ENUM('Y','N') DEFAULT 'Y',

PRIMARY KEY(id),

KEY index1(fullname)

);

 

Let’s pretend that the users table has 100k rows.  Your query searches on the `fullname` column, which also has an index. 

In the case of most queries, the names may be unique enough to give back a few rows. But names like "John Smith" may give back an unwieldy number of results. If you are only interested in active users, then you can make a change like this:

 

ALTER TABLE users DROP INDEX index1, ADD INDEX index1(fullname,active);

 

By doing so, you increase the cardinality of the index because the additional column `active` may reduce the number of results yielded. In the case of the example, there may be over 100 John Smiths, but only 30 that are active.

 

3) Try to reduce the size of the queries. In the case of the earlier example, `fullname` is a varchar(255). If we use UTF8 for the table, then the index for it will use 767byes. We must determine what the fewest amount of characters that we can get away with is. This could be difficult if we don’t have any data to use for comparison.

If I were running this query, I would use the following process to  determine what that number might be:

 

mysql> set @unique_total=0;

mysql> select count(distinct fullname) into @unique_total from users;

0 row in set (6 min 28.29 sec)

 

mysql> select @unique_total;

+---------------+

| @unique_total |

+---------------+

|        100448 |

+---------------+

1 row in set (0.00 sec)

 

mysql> select round(count(distinct left(fullname,200))/@unique_total*100,2) as c200, round(count(distinct left(fullname,100))/@unique_total*100,2) as c100,

    -> round(count(distinct left(fullname,75))/@unique_total*100,2) as c75, round(count(distinct left(fullname,50))/@unique_total*100,2) as c50,

    -> round(count(distinct left(fullname,30))/@unique_total*100,2) as c30, round(count(distinct left(fullname,20))/@unique_total*100,2) as c20 from users;

+--------+--------+--------+-------+-------+-------+

| c200   | c100   | c75    | c50   | c30   | c20   |

+--------+--------+--------+-------+-------+-------+

| 100.00 | 100.00 | 100.00 | 99.97 | 99.81 | 92.86 |

+--------+--------+--------+-------+-------+-------+

1 row in set (4 min 13.49 sec)

 

You can see that if I limit the index to 30 characters, it will be very precise. If I try a lower number, it will become less and less precise. Technically, I would aim for an index that’s limited to over 95% of the existing data.

 

If you set the number for the index’s characters too low, this may have a negative effect.

To explain what I mean by that, let’s take the full name of the actor Daniel Michael Blake Day-Lewis, and let’s say that our index is limited to 20 characters.  If we search “Daniel Michael Blake Day-Lewis,” we may get back results from the database for:

Daniel Michael Blake Day-Lewis

Daniel Michael Blake

Daniel Michael Blake Smith

Daniel Michael Blake Willis

 

The database will have to further search these rows after it has been brought up from the storage engine. So using a limited index in this case will require the database to do additional work.

 

4) If at all possible, try to change a VARCHAR column/index that has a defined number of rows to an ENUM (1 or 2 bytes) or normalize it to another table and keep the id (usually an INT) in the original table. Doing so can save a ton of space (remember the VARCHAR and UTF8 comment I made above?). Check the cardinality of your VARCHAR column using SHOW INDEX and see if that column’s cardinality is between 0-255 or 0-65535 to determine if it’s a good candidate for this conversion.

 

There are many more techniques for and things you should know about going over indexes. I hope that this post will encourage you to learn more of them. If you are interested in doing so, I will be speaking at Percona Live London, were I will cover this subject (and others) in more depth. Hope to see you there!

PalominoDB at PerconaLive

PalominoDB is very excited about our participation in the upcoming PerconaLive conference in London.  We'll have two of our European staff presenting.  On Monday, Jonathan Levin will be doing a tutorial on Advanced MySQL Scaling Strategies for Developers, and on Tuesday Rene Cannao will be presenting on MySQL Backup and Recovery Tools and Techniques.  Rene and Jonathan are two of our newer team members, and represent an exciting growth in staff outside of the US; Jonathan being in the UK and Rene being in Malta.  I know I'm thrilled to get out to London to meet a lot of new folks in our community.

We did get a chance to present at PerconaLive in NYC this year as well, which was quite a lot of fun, and the positive reception Sheeri's session got was gratifying.  Percona has become a huge part of our community and has provided great value in their information share, tools, services and now conferences - including the MySQL Conference & Expo 2012.  Having been involved in professional MySQL consulting and remote support for four years has been quite the adventure, and the fact that so many companies find space to create, share and prosper only shows the viability of open source software and the communities behind it.  

I know we at PalominoDB are quite proud to share space with companies such as Pythian, Blue Gecko, SkySQL and, of course, Percona. We are proud to support open source solutions across the board, and are even more excited to have grown to a place where we have the resources to contribute back to them, to non-profits and to the growth of our clients and every person on our team.  Here's to an exciting and brilliant future, and a great conference!

TechRepublic Article: "Are tech certs valuable or not?"

I normally do not repost articles and make teeny tiny blog posts (I have noticed a few bloggers who do that, and it bothers me), but I found a short article on TechRepublic called Are tech certs valuable or not? to be quite interesting.

The author did an informal poll of a few CBS interactive higher-ups, and it was interesting to note that in addition to the expected "it depends" and "experience is the most important thing" and "beware the person with many certs and no experience" answers, the MySQL certification was specifically noted as being good:

Doug Lane, Director of Site Reliability and Performance Engineering, says, “The MySQL DBA certifications are valuable to me, since we support so many instances of that. Frankly, the Java and MS certs became less valuable due to the number of those who were just paper certified but not actually practitioners.”

Interestingly, he says the reason the MySQL certifications are valuable is because CBS interactive supports so many instances of MySQL, but he implies that folks who have MySQL DBA certifications also have experience (as opposed to Java/MS certs).

Change Views DEFINER without ALTER VIEW : how to fix thousands of views!

Recently I faced an interesting challenge: modify over 26k views on a single mysqld instance.

Altering the view one by one was far from an easy task, so a I had to look for an efficient way to do it. Read to find out more.

 

Preface:

Views have security context and their own access control.

When a view is created, a DEFINER is assigned to it (by default, the user that creates the view), and a SQL SECURITY that specifies the security context (definer by default).

Assume you use 'user123'@'192.168.1.%' , and you issue the follows (a very simple view):

CREATE VIEW view1 SELECT * FROM tbl1;

 

Behind the scene, this becomes:

CREATE ALGORITHM=UNDEFINED DEFINER=`user123`@`192.168.1.%` SQL SECURITY DEFINER VIEW `view1` AS select <omitted> from `tbl1` ;

 

 

Problem:

 

So far, all good.

What if your application change user to `user456`@`192.168.1.%` ?

The result can be very disappointing (even if expected):

mysql> SELECT * FROM view1;

ERROR 1045 (28000): Access denied for user 'user456'@'192.168.1.%' (using password: YES)

 

What if you use a highly privileged user like root?

mysql> SELECT * FROM view1;

ERROR 1449 (HY000): The user specified as a definer ('user123'@'192.168.1.%') does not exist

Not much more luck, we are sort of stuck until we change permission.

 

ALTER VIEW has the same syntax of CREATE VIEW , ( just 's/CREATE/VIEW/' ) , therefore there is no way to change only the definer: all the statement that define the view need to be re-issued.

 

Even if recreating the view is an easy task, isn't that easy if you thousands and thousands of views to fix.

 

Solution:

There is a dirty way to do this, but it does its job!

Each view is a .frm file in the database directory.

Changing the definer is easy as editing the file changing definer_user and/or definer_host .

 

This is the procedure to update all the views, no matter their number (26k views updates in only few minutes):

shell> cd /var/lib/mysql

shell> for i in `find . -name "*frm"` ; do if [ `cat $i | egrep '^definer_user=user123$' | wc -l` -ne 0 ]; then echo $i ; fi ; done > /tmp/list_views.txt

# don't forget to make a backup of your views!

shell> tar cf /tmp/views_backup.tar /tmp/list_views.txt

shell> for i in `cat /tmp/list_views.txt` ; do tar rf /tmp/views_backup.tar $i ; done

shell> for i in `cat /tmp/list_views.txt` ; do cp -a $i $i.old && sed -e 's/^definer_user=user123$/definer_user=user456/' $i.old > $i && rm $i.old ; done

mysql> FLUSH TABLES;

 

Describing the above procedure:

- /tmp/list_views.txt lists all the views that need to be modified ;

- /tmp/views_backup.tar is a backup with all the frm listed in above list ;

- for each view (file) : copy the .frm file as .frm.old , applies the change using sed , remove the .frm.old file

- close all opened tables (included view) so the changes take effects.

 

References:

http://dev.mysql.com/doc/refman/5.5/en/create-view.html

http://dev.mysql.com/doc/refman/5.5/en/stored-programs-security.html

http://dev.mysql.com/doc/refman/5.5/en/alter-view.html

http://forge.mysql.com/wiki/MySQL_Internals_File_Formats

How Oracle Has Done Nothing to Change MySQL

Last night at the Oracle OpenWorld MySQL Community Reception, there were lots of old and new friends milling about.  It occurred to me that there is one very important thing Oracle has NOT changed about the MySQL world - the rock stars and higher-ups are still readily accessible.

One of the things I love about being in the open source community is that you can have an in-depth conversation with someone, and only later on find out that this person is famous.  For the most part, rock stars and important people are readily accessible.  They stay in the same hotels that attendees do, they take the same elevators, they are not whisked away by bodyguards, and they do not play the "don't you know who I am?" card.

Now, it's not surprising that the community rock stars like Mark Callaghan, Baron Schwartz, Giuseppe Maxia and Sarah Novotny are still as accessible as ever.  However, Ed Screven and Thomas Ulin were also around for the party, and I can confirm that Thomas was one of the last dozen or so to leave (Ronald Bradford and I closed out the party and were the last to leave).

So, kudos to Oracle for not keeping your VIPs locked up in a bunker.  I am very glad to see this aspect of open source culture still thriving.

Securing MySQL and How to Be a Rock Star DBA Presentation Slides

For those who missed my presentations at Oracle OpenWorld (or if you caught it but want the slides) - PDF slide decks are online:

 

Securing MySQL (IOUG)  - http://technocation.org/files/doc/MySQLSecurity2011_09.pdf

How to Be a Rockstar MySQL DBA - http://technocation.org/files/doc/2011_09_RockStar.pdf

Syndicate content
Website by Digital Loom