OneFreeVoice

October 6, 2008

Information_Schema.Partitions – table_rows

Filed under: MySQL — Tags: , , — Gregory Haase @ 4:00 pm

I’m in the process of converting some very large data tables to partitioned tables. By “In the process” I mean “scripts are running as we speak and I’m monitoring what’s going on.”

When I did this in our test environment (2 or 3 times to be sure), I got familiar with the information_schema.partitions table. There are multiple ways to check if your data is going into your partitions, and going into the correct partition. One easy thing you can do is use “explain partitions” and make up a select query that should be narrowed down to that partition:

explain partitions
select *
  from very_large_table
 where date_string between '20080514' and '20080524'

If set up correctly, you’ll see a result like:

+----+-------------+------------------------------------+------------+-------+---------------+---------+---------+------+---------+-------------+
| id | select_type | table            | partitions | type  | possible_keys | key     | key_len | ref  | rows    | Extra       |
+----+-------------+------------------------------------+------------+-------+---------------+---------+---------+------+---------+-------------+
|  1 | SIMPLE      | very_large_table | p200805    | range | PRIMARY       | PRIMARY | 4       | NULL | 9016317 | Using where |
+----+-------------+------------------------------------+------------+-------+---------------+---------+---------+------+---------+-------------+

You can also take a look at the filesystem real quick to check the volume of the partition files

ls -lh very_large_table*.ibd

-rw-rw---- 1 mysql mysql 1.3G Oct  6 10:40 very_large_table#P#p200804.ibd
-rw-rw---- 1 mysql mysql 1.2G Oct  6 10:45 very_large_table#P#p200805.ibd
-rw-rw---- 1 mysql mysql 2.0G Oct  6 10:51 very_large_table#P#p200806.ibd
-rw-rw---- 1 mysql mysql 2.8G Oct  6 11:02 very_large_table#P#p200807.ibd
-rw-rw---- 1 mysql mysql 2.4G Oct  6 11:12 very_large_table#P#p200808.ibd
-rw-rw---- 1 mysql mysql 3.2G Oct  6 11:32 very_large_table#P#p200809.ibd
-rw-rw---- 1 mysql mysql 628M Oct  6 11:35 very_large_table#P#p200810.ibd

Through the course of my work, I also noticed that there is a table_rows column on the information_schema.partitions table.

select partition_name,
       table_rows
  from information_schema.partitions
 where table_name = 'very_large_table';

+----------------+------------+
| partition_name | table_rows |
+----------------+------------+
| p200804        |   19609997 |
| p200805        |   18041881 |
| p200806        |   31124871 |
| p200807        |   43307939 |
| p200808        |   35578598 |
| p200809        |   48627782 |
| p200810        |    9298862 |
+----------------+------------+

Circle back to todays script. The first thing I do is open a screen session so that if I somehow get disconnected from the server, it’s not going to kill my session. I kick off my script and there are 12 huge tables that are going to get partitioned. I start working on some other things and periodically check the progress using “show processlist”.

After a while I began to get concerned that the largest table had been processing for a very long time. I didn’t want to interrupt anything, and I didn’t want to start running count queries, but I was getting concerned about the progress. So I opened another window and performed the same query:

select partition_name,
       table_rows
  from information_schema.partitions
 where table_name = 'very_large_table';

+----------------+------------+
| partition_name | table_rows |
+----------------+------------+
| p200804        |   19609997 |
| p200805        |   18041881 |
| p200806        |   31124871 |
| p200807        |   21405637 |
| p200808        |   0        |
| p200809        |   0        |
| p200810        |   0        |
+----------------+------------+

Oh good, I can see that at least I’m half way done.

These aren’t exact numbers, but if you just want to get a size estimate of a very large table they are close enough.

There is also table_rows column on information_schema.tables. With the amount of time it takes to count rows on an InnoDB table, if you just need rough numbers on how large your tables are, you can just pull the info from the information_schema.

select count(1)
  from 'very_large_table_two';
+-----------+
| count(1)  |
+-----------+
| 275689898 |
+-----------+
1 row in set (17 min 21.60 sec)

select table_rows
  from information_schema.tables
 where table_name = 'very_large_table_two';
+------------+
| table_rows |
+------------+
|  269991752 |
+------------+
1 row in set (0.49 sec)

The table_rows number is off by more than 2% here, but there are many scenarios when all you really need is a general idea of how big something is. I’ll take even a 5% margin of error in less than a second over waiting 15 minutes for an exact number. Especially when someone is standing over my shoulder asking questions about table sizes.

About these ads

4 Comments »

  1. That is some cool info. I am afraid I am somewhat of an old-timer and don’t think about the information schema as much as I should.

    Comment by Keith Murphy — October 7, 2008 @ 9:45 pm

  2. I like the way you started this out:
    expain partitions
    SELECT *
    FROM very_large_table
    WHERE date_string BETWEEN ‘20080514’ AND ‘20080524’”

    With that much data it sounds like a “painful” process :)
    But for those who aren’t familiar with the function though, it might be painful to make sense of it until they understand it was a typo for “explain”. Nice post btw.

    Comment by JoeD — October 7, 2008 @ 11:10 pm

  3. Typo fixed. Nice catch JoeD.

    Comment by Gregory Haase — October 8, 2008 @ 6:25 am

  4. I found good and simple explanation here.

    Thank you for your notes.

    Comment by Baruch Lvovsky — February 2, 2012 @ 4:32 am


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Silver is the New Black Theme. Create a free website or blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: