Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a big list of domains and URLs that I store in a database, there are around 150M domains and 300M URLs, I'm using InnoDB to store each in the format:

CREATE TABLE IF NOT EXISTS `domains_list` (
  `id` int(10) unsigned NOT NULL DEFAULT '0',
  `domain` varchar(350) DEFAULT NULL
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Which is fine. New records are assigned what is effectively an auto-increment, so there's no fragmentation and inserts are in ascending order.

When new data comes in however (typically between 250K and 2M rows), I use two separate 'hash' tables to see whether the domain or URL already exists in the database. Strictly speaking it's not a 'hash' table, just a bunch of MD5s which I use ensuring values are unique, with the added benefit of the table being fixed length. The table is also partitioned.

CREATE TABLE IF NOT EXISTS `domains_hashes` (
  `id` int(10) unsigned NOT NULL,
  `segment` tinyint(3) unsigned NOT NULL,
  `hash` binary(15) NOT NULL,
  PRIMARY KEY (`segment`,`hash`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE (segment)
(PARTITION p0 VALUES LESS THAN (1) ENGINE = InnoDB,
 PARTITION p1 VALUES LESS THAN (2) ENGINE = InnoDB,
 ...
 PARTITION p254 VALUES LESS THAN (255) ENGINE = InnoDB,
 PARTITION p255 VALUES LESS THAN (256) ENGINE = InnoDB) */;

segment is basically the first character of the hash, which is used for the partition. With the remaining 15 bytes going into hash.

For seeing whether a bunch of domains already exist in the database, this works relatively well, however, the table gets fragmented due to the random nature of insertions.

The hash table is basically only used for insertions and quickly looking up whether a domain exists in the DB or not. During insertions, a script walks from 0-255 and performs the necessary check.

My question is, do you know of a better procedure in order to handle inserts/selects better? I believe when I started out with this database I simply had a key on domains_list.domain, which was slow.

I find that when the partitions are re-organised the lookups are very quick, but after a number of batch insertions, the same lookups slow down somewhat. Server has 32GB of RAM and I use 16GB for the buffer pool, while the table itself takes up 5.4GB on disk.

share|improve this question
    
Is there a requirement to keep this data in InnoDB or could you keep the entire dataset in a memory table? –  Craig Efrein Jul 17 '13 at 8:08
    
I've considered a memory table as it'd be preferable to have the HASH index type that only a MEMORY table would support. It's certainly a possibility, but I'd prefer to avoid having to rebuild the table's hashes in the event of a reboot/crash etc. –  innvo Jul 17 '13 at 8:16
    
The inserts are all random added by users or in batches or both? –  Craig Efrein Jul 17 '13 at 9:04
    
all inserts are made by me. I insert into each partition individually e.g. INSERT INTO domain_hashes PARTITION(p0) ... and the rows are inserted in order –  innvo Jul 17 '13 at 9:10
    
FWIW mentioning the 'all-in-memory' route proved too tempting, so I tested it. Took 15 minutes to put the data from the table into memory... but the lookups are blisteringly fast, in the order of hundreds of thousands a second. I may end up sticking with the memory table but a disk based intermediate solution would be good, particularly for if the table grows to a (too) large proportion of memory. –  innvo Jul 17 '13 at 9:17

1 Answer 1

up vote 1 down vote accepted

Several ideas come to mind:

The hash table could be a memory table, once your inserts are done, you could then copy just the domains that don't already exist into the permanent domain_list table. Having your new data in memory might also make the domain name comparison between the hash table and the domain_list tables go faster.

Another option would be to use the innodb_file_per_table option and keep the database (idb) files on a faster disk.

A third option would be to load your entire dataset into a memory table and then convert that table to InnoDB using the ALTER TABLE table_name ENGINE=InnoDB. This will be much faster than inserting into InnoDB directly. This is possible since you control how and when data is inserted.

share|improve this answer
    
I'd vote you up but I lack the reputation at the moment. Thanks for the considered options. ATM I'm using file per table. –  innvo Jul 17 '13 at 9:23

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.