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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
