Performance Problems

I am having MySQL performance problems with my current schema. Here is a rundown of what the inserter does:

- get's the filename, filesize and hash for a file
- checks the objects database to see if that combination already exists
- if it exists, skip to next step
- if it does not exisit, insert it
- insert the information in the file table

Here is the create table definition for the tables:

CREATE TABLE `objects` (
`objectid` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`filename` varchar(256) COLLATE latin1_bin DEFAULT NULL,
`filesize` bigint(20) unsigned DEFAULT NULL,
`hash` varchar(32) COLLATE latin1_bin DEFAULT NULL,
PRIMARY KEY (`objectid`),
UNIQUE KEY `nsh` (`filename`,`filesize`,`hash`)
) ENGINE=MyISAM AUTO_INCREMENT=7037849 DEFAULT CHARSET=latin1 COLLATE=latin1_bin

CREATE TABLE `files` (
`path` varchar(4096) COLLATE latin1_bin DEFAULT NULL,
`filename` varchar(256) COLLATE latin1_bin DEFAULT NULL,
`filesize` bigint(20) unsigned DEFAULT NULL,
`hash` varchar(32) COLLATE latin1_bin DEFAULT NULL,
`backuptime` datetime DEFAULT NULL,
`status` enum('Active','Inactive','Deleted') COLLATE latin1_bin DEFAULT NULL,
`objectid` bigint(20) unsigned DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin

I currently have a files table per backup client node. On my test system, I can run the inserts sequentially for 15 nodes (37 or so processes for multiple filesystem clients), and it runs in about 50 minutes. When i run them at the same time, it runs many times longer. It looks like it is the objects table, since I tried "insert delayed" and that cut the time back down to around the sequential time. The downside to the delayed, is that a crash can lose data.

Does anyone have any ideas as to what I am doing wrong? If it matters, I am running a dual core AMD 5800+, 4GB memory, and a 4 disk raid-0 SSD Array.

Thanks for any input anyone may have.

Initial Stats from 15 Servers

I picked 15 servers at my workplace to get some initial data on. I tried to pick different servers, not multiple copies of the same servers, like for high availability, etc. The data I gathered gave a fairly good poor man's data-dedup savings.

My "poor man's dedup" consists of saving the backup files based on filename/filesze/md5 hash of the file contents. This provided savings, even on single servers, as there are some number of the same named files on the same system, with different paths.

Here are my numbers from the 15 servers:

Total Files: 13753410
Total Objects: 7037848
File Savings: 6715562
Percentage Savings: 48.8283414804

Total File Size: 1747437157724
Total Object Size: 1134826850745
Size Savings: 612610306979
Percentage Savings: 35.0576445208

At 35% space savings seems pretty incredible to me, for as little I had to put into making it happen. Some of the more aggressive data dedup alogoritms search files for long common strings, which certainly might improve the amount of savings, but at the cost of more CPU and IO processing.

Recursing into subdirectories

One of the things that needs to be done when backing up files is to determine what files exist on the server. I had written code to recurse into subdirectories, but it was very slow. It also basically only gave the filename, which necessitated a call to stat to get the important information. Then I discovered a system call ftw and nftw. These calls do the work of recursing into the subdirectories, and also provides the stat structure for each file. It is also very much faster than my original code. Here is the code I wrote to do that:

#define _XOPEN_SOURCE 500
#include 
#include 
#include 
#include 
#include 
#include 
#include 
#include 

static int
display_info(const char *fpath, const struct stat *sb,
int tflag, struct FTW *ftwbuf)
{
printf("p:%s ",fpath);
printf("tf:%d ",tflag);
printf("dev:%lu ",sb->st_dev);
printf("ino:%lu ",sb->st_ino);
printf("m:%o ",sb->st_mode);
printf("nl:%lu ",sb->st_nlink);
printf("u:%d ",sb->st_uid);
printf("g:%d ",sb->st_gid);
printf("rd:%lx ",sb->st_rdev);
printf("s:%lu ",sb->st_size);
printf("bs:%lu ",sb->st_blksize);
printf("b:%lu ",sb->st_blocks);
printf("ta:%lu ",sb->st_atime);
printf("tm:%lu ",sb->st_mtime);
printf("tc:%lu ",sb->st_ctime);
printf("\n");
return 0; /* To tell nftw() to continue */
}

int
main(int argc, char *argv[])
{
int flags = FTW_MOUNT;

if (argc > 2 && strchr(argv[2], 'd') != NULL)
flags = FTW_DEPTH;
if (argc > 2 && strchr(argv[2], 'p') != NULL)
flags = FTW_PHYS;

if (nftw64((argc < 2) ? "." : argv[1], display_info, 80, flags) == -1) {
perror("nftw");
exit(EXIT_FAILURE);
}
exit(EXIT_SUCCESS);
}

Books that I Use

I thought it might be interesting to list the books I use as reference while programming for this project. In no particular order:

SQL in a Nutshell - Kevin E. Kline - This book was not what I thought it was, but it is a good reference for SQL and distinguishes between various flavors: DB2, MySQL, Oracle, and SQL Server mostly.

High Performance MySQL - Jeremy D. Zawodny & Derek J. Balling - Good book on getting performance out of MySQL

Advanced Programming in the Unix Environment - W. Richard Stevens

Unix Network Programming - W. Richard Stevens

The C Programming Language - Kernighan and Richie

Programming with Posix Threads - David R. Butenhof

I'm sure there's more, but that's all I can think of right now.

My Thoughts on Creating a Backup Storage System

I have worked with backups and network backup systems for about 13 years now. I decided to investigate what it would take to write such a system, using MySQL for the backend database.

The first thing I discovered is that traversing the directories is a slow process, though many times faster than actually backing up the files, but still slow. I discovered a nifty subroutine called nftw that recurses into directories and gives a stat structure and other information for each file found. Much, much faster, and you get the stat structure to boot.

The other thing I have been investigating is a poor man's data de-duplication. I've run some experiments on servers here at work, creating a hash of each file. After looking at millions of files, I settled on MD5 hash, with no duplicate hashes found so far.

I will post later about the code to recurse into directories, and the hash stuff and some timings and why I chose MD5.