List:General Discussion« Previous MessageNext Message »
From:George Law Date:January 10 2006 6:13pm
Subject:mysql 5 - disk bound ?
View as plain text  
Hi All,

I have a question for you all.

Working with 2 innodb tables.  One is a table of cdrs (call detail
records) with 33 columns and 7 million + rows.  Second table is a clone
of the first, meant as a "work" table.

From the mysql command line client, I gave the query :
insert into table2 select * from table1 where time_epoch between xx and
yy; (I did not do a BEGIN/COMMIT)

Time_epoch is a numeric field which is indexed.

This took 13+ minutes for 1,130,000 records.

It seems to me that 13 minutes is a little high.  

During this time, a "load data infile" command into table1 (using
begin/commit) was stalled out waiting for the the "insert into
table2...."

The "load data infile" command - 1110 
This is a HP 1U server, dual P4 3 Gig, 4 GB of ram, optimized for disk
IO. 
Hyperthreading is enabled. This is running suse 9.3, 2.6.11.4-20a-smp
kernel.


Raw .idb files : 
11G     comp_cdr.ibd 		(table1 above)
1.1G    temp_comp_cdr.ibd  	(table2 above)

Mysql is running with almost 2 GB ram, no swapping seems to be going
on...

Tasks:  65 total,   1 running,  64 sleeping,   0 stopped,   0 zombie
Cpu(s):  8.7% us,  4.6% sy,  3.1% ni, 75.7% id,  7.9% wa,  0.0% hi,
0.0% si
Mem:   3960896k total,  3845864k used,   115032k free,    11260k buffers
Swap:  4200956k total,     2764k used,  4198192k free,  1830060k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND

 4515 mysql     16   0 2258m 1.9g 4296 S 99.9 49.1   1174:55 mysqld   


my.cnf:

[mysqld]
local-infile=1
bulk_insert_buffer_size = 512M
big-tables
port            = 3306
socket          = /tmp/mysql.sock
max_connections = 200
skip-locking
key_buffer = 512M
max_allowed_packet = 16M
table_cache = 2048
sort_buffer_size = 8M
join_buffer_size = 8M
read_buffer_size = 2M
myisam_sort_buffer_size = 128M
thread_cache = 32
query_cache_size = 96M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 8  # 2 cpu x 2 (hyperthreading) x 2


tmp_table_size = 256M

# Replication Master Server (default)
# binary logging is required for replication
#log-bin

# required unique id between 1 and 2^32 - 1
# defaults to 1 if master-host is not set
# but will not function as a master if omitted
server-id       = 1
# Uncomment the following if you are using InnoDB tables
innodb_data_home_dir = /usr/local/mysql/data
innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend
innodb_log_group_home_dir = /usr/local/mysql/data
innodb_log_arch_dir = /usr/local/mysql/data
## You can set .._buffer_pool_size up to 50 - 80 %
## of RAM but beware of setting memory usage too high
innodb_buffer_pool_size = 1024M
innodb_additional_mem_pool_size = 256M
## Set .._log_file_size to 25 % of buffer pool size
innodb_log_file_size = 256M
innodb_log_buffer_size = 8M
#innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 150
#skip-innodb
innodb_thread_concurrency = 8
innodb_file_per_table


any help is appreciated :)

TIA

George

Thread
mysql 5 - disk bound ?George Law10 Jan
RE: mysql 5 - disk bound ?George Law11 Jan