MySQL Forums
Forum List  »  MyISAM

MySQL Performance - multiple concurrent processes
Posted by: Andrew Carlson
Date: April 06, 2009 02:28PM

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.

P.S. - Here is my my.cnf:

[client]
port=5132
socket=/data/mysql-5.1.32/mysql.sock

[mysqld]
port=5132
datadir=/data/mysql-5.1.32
socket=/data/mysql-5.1.32/mysql.sock
log-bin
symbolic-links
expire_logs_days=2
innodb_file_per_table
innodb_buffer_pool_size=1284M
skip-locking
key_buffer=384M
max_allowed_packet=8M
table_cache=768
sort_buffer_size=64M
read_buffer_size=64M
read_rnd_buffer_size=38M
myisam_sort_buffer_size=64M
thread_cache_size=28
query_cache_size=64M
thread_concurrency=48
concurrent_insert=2
delay_key_write=all
delayed_insert_limit=4000
delayed_queue_size=9000

Options: ReplyQuote


Subject
Views
Written By
Posted
MySQL Performance - multiple concurrent processes
4495
April 06, 2009 02:28PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.