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 table with ~ 40 mil records, with a unique index on the url collumn, of type varchar(255).

Now the insert speed is about 30/s, is this expected? how could I improve it? I can't use bulk insert (insert delayed or more values at a time).

Specs:

Hardware

  • 16GB RAM
  • intel i7 3.4 Ghz (8core)
  • 2 standard 7200rpm HDD (1x 1TB + 1x 2TB)

Software

  • MySQL 5.5 (innodb_version 1.1.8, protocol_version 10, version 5.5.29-0ubuntu0.12.10.1-log, version_compile_machine x86_64, version_compile_os debian-linux-gnu)
  • Ubuntu 12.10 (debian/linux OS)
  • my.cnf
  • I'm using the c++ conector as client

Query usage:

CREATE TABLE IF NOT EXISTS `url_unique` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `url` varchar(255) NOT NULL,
  `valid` tinyint(4) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY unique_index (`url`)
) ENGINE=MyISAM 
  DEFAULT CHARSET=ascii 
  DEFAULT COLLATE=ascii_bin
  ;


INSERT IGNORE INTO url_unique (url) VALUES ('http://domain.com')

IF(affected_rows>0) {
    id = SELECT LAST_INSERT_ID();
    .... do some ohter insert using id
}else {
    id = SELECT id FROM url_unique WHERE url = 'http://domain.com';
    .... do some ohter insert using id
}

query statistics :

insert ignore into - 0.035s (duplicate value, no insert, affected_rows=0)

update: 0.0328340 (99.588%)
...
Com_insert 1
Handler_write 1
Key_read_requests 34
Key_reads 8
Key_write_requests 2
Key_writes 1
Questions 1
Table_locks_immediate 1

SELECT - 0.002s
(faster because of the insert, some sort of query cache hit probably)

share|improve this question
1  
table_open_cache = 1 seems like a potential performance killer. –  Michael - sqlbot Feb 19 '13 at 22:26
    
oups, I've messed with the .cfg some time before and forgot that there :), should 1000 suffice? –  clickstefan Feb 20 '13 at 6:44
    
now it's at about .015 (~60/s) with nothing else running on the server, is there anything else I could do to speed it up, I see that the selects I did last night still run fast now, even after I've restarted the MySQL server, probably some Linux read cache. How much data can it cache? could I do some warm-up process? –  clickstefan Feb 20 '13 at 6:48
    
Questions: Is the client running on the same machine as the server, or a different machine? Have you benchmarked with multiple concurrent clients? With an empty table with the same structure? Without the unique key? With an InnoDB table? If you slam the server with repeated inserts, what values peak up on the "Cpu(s)" row of top -- %us, %sy, %wa, %hi, %si, or %st? –  Michael - sqlbot Feb 20 '13 at 7:19
    
for these tests I've used a different machine as client, but the main application will be on server. I needed about 10x speed, so I didn't think the networking would matter as most operations are on server. For my data usage I've found that MyISAM is faster, as I don't need high reads as I need inserts. Even with table locks from 500 connections/s it still is faster than InnoDB. Anyway I found that using LOAD INDEX INTO CACHE url_unique did the trick, speed is now at ~ 0.001 (1k/s) from remote. –  clickstefan Feb 20 '13 at 7:49

Your Answer

 
discard

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

Browse other questions tagged or ask your own question.