Protip: When bean-counting a 100 million+ row table,

Protip:  When bean-counting a 100 million+ row table,

It is easier to make a summary table then sort its results than have MySQL do this:

select item, count(*) as num_deletions from DeletedCrap group by item order by num_deletitions


Do this instead:

create table report
(
def bigint not null default 0,
deletions bigint not null default 0,
primary key (def_id)
) engine = innodb;

insert into report (select item,1 from db.DeletedCrap) on duplicate key update deletions=deletions+1;

Select * from report order by deletions desc limit 1000;

(I've found that one finishes... the other doesn't)

P.S.

The other fastest way is to use something like PHP where array keys can be dynamically created, and increment dynamic array bean counters as the data streams in from the DB.  The above way while faster still incurs transaction overhead the PHP way does not.

Why don't you use more tools for MySQL/MariaDB?

This is a question I get asked a lot.

Rather, I also get heckled for it.  Even by people I hire.

For me, it kinda goes back to using a calculator in math class.

A lot of DBAs, developers rely on tools to get the job done, and that is fine. 

But what if you were in an environment where those tools were not allowed?  What if you were in an environment that was foreign to you, and you needed to help fix a problem straight away?

What if there was no google? Oh noes.

Tools are great, they make our lives easier.  But don't you want to know how MySQL is doing something, or why?

Friday was a perfect example.  One of our internal clients has circular replication setup between two machines.  One of the machines ran out of disks pace (Bad Shinken for not finding that in time.)  The VIP was setup to be a smart active-active VIP so after the fact we had one machine in the middle of some updates, and we had another machine that crashed, got fixed, started taking updates again, crashed, and had to be fixed.

You may be able to find a tool on the web that claims I can fix your data in 10 seconds or less.  Unless you have read it's source code, and it is extremely important data, do you want to trust that tool to it?

What if you knew how to use mysqlbinlog yourself export out the updates from both machines as best you can, give it a proper known start and stop point on each machine, a known state that you trust because you know what you are doing, apply the updates through the mysql cli, reset replication to the last exported and execution log position, and voila.

How do you figure out the start position for the mysqlbinlog export? By looking at the last executed log position from mysql's show slave status of course. :-)

The tool may have worked, but you took it on faith.  If you know how to do it with the tools MySQL gives you natively, there is nothing to take on faith.  You see it work right then.  More importantly doing it this way you can see when something fails or goes sideways during the repair (if it does) and you can react better to it, because you see the failure.  You don't have to troubleshoot where the failure occurred.  You see it happen, you know exactly what line.

For me, bottom line, it is great to use tools.  It is also great to know how to do it with what MySQL or Linux gives you out of the box.  

Auto Increment and InnoDB

Auto Increment is one of the neat pieces of functionality that MySQL has.  It is not implemented the way other database platforms do it.  Typically, a platform like T-SQL (Postgres, Microsoft) will create an internal UDF/SP that does work behind the scenes in a sequence table.

When MySQL initially created Auto Increment, they did so for MyISAM.  When it came time to implemented in a transactional storage engine, they changed the game a little.  It is both good and bad.

To get performance MySQL stores the Auto-Inc current value for a table in memory.  It doesn't actually flush the current value of the counter until some event forces MySQL to do so (a server shutdown/restart or flush perhaps.)  I have seen MySQL crash in the middle of operation and reset the counter back to the last flush and in weird circumstances start handing out the same IDs generating duplicate keys.

Further, because InnoDB is transactional, and the counter is cached in memory until a flush, this is one of the few times where when generating a new sequence will cause InnoDB to do a full table lock for just a split microsecond.

Microsecond you say? That's hardly a horrible amount of time to wait, and to a human that is true. To a computer thousands of updates per second, each microsecond can feel like a life time.  We don't want our databases feeling old ahead of their time.

I present to you...

The counter UDF/SP.

DELIMITER ;;
DROP FUNCTION IF EXISTS calc_id;;
CREATE FUNCTION calc_id(_n BIGINT) RETURNS bigint(20)
    DETERMINISTIC
BEGIN

declare n BIGINT;
declare x BIGINT;

set x = (floor(_n / @@auto_increment_increment)) * @@auto_increment_increment;

set x = x + IF( (@@auto_increment_offset = @@auto_increment_increment),0,@@auto_increment_offset);

if x<=_n then
  set x=x+@@auto_increment_increment;

end if ;
return x;
END
;;
DELIMITER ;


DELIMITER ;;
DROP FUNCTION IF EXISTS get_id;;
CREATE FUNCTION get_id(_seq_idx char(32)) RETURNS bigint(20)
    DETERMINISTIC
BEGIN
  UPDATE sequences SET seq_id= @_seq_id:= calc_id(seq_id) where seq_idx = _seq_idx;
  return @_seq_id;
END ;;
DELIMITER ;

DROP TABLE IF EXISTS sequences;
CREATE TABLE sequences (
  seq_idx char(32) NOT NULL,
  seq_id bigint(20) NOT NULL DEFAULT 0,
  PRIMARY KEY (seq_idx)
) ENGINE=InnoDB;


This little snippet of code has been tested for several years in multiple large scale environments.  I am sure there are ways to improve some of the internal logic, but when it can handle six digits of updates per second on a MySQL (ndb) cluster or five digits of updates per second on a single InnoDB master who cares?

Insert into sequences VALUES('users_pk_idx',0);
Select get_id('users_pk_idx');


This code carries some nifty side effects that eventually developers smile about.

• To make sure it is happy in any replication environment, I always advise the developers to call the get_id() from code.  Then s/he will explicitly insert the PK value into the query.
• This means the developer actually knows sequence value ahead of time.  They can plan other code and strategies around knowing the ID before it is inserted.
• You no longer have to call mysql_insert_id() to get the last insert ID, because you already know what it is.
• There is no more global locking of the table for microseconds to milliseconds (depending on how many tps you are having on the server)
• It also follows the protocols for auto-increment ID skipping, so if you are in multi-master environments you can keep that.  
• The auto-increment number becomes atomic and is as good and durable as to how you setup InnoDB to be. :-)

It is a little more work on the front end, but once you get used to it, you start to like the flexibility of it.  You can even maintain counters for fields that don't need to be PKs, or even UNIQUES explicitly.

An amusing introduction...

Well hello there.

Commonly, I like to tell people I play a DBA at work.  Apparently, I've done it so well I have everyone fooled into believing I am a good one. :-)

I've decided to start sharing the amusing, quirky, out-of-the-box thoughts I have on databases, architecture, technology.

Since you are still here, you will occasionally think I am nuts, or psycho in what I write.

Don't worry.  Anything I put here will have been tested and used in large scale production environments.

See you back here, soon...

Cheers,

DrSQL