sure, i am inserting the data with: LOAD DATA LOCAL INFILE 'lightning-data.txt' INTO TABLE strikes;
does that clear things up? i assume that the data is first inserted into the table and then the index is created afterwards. following the creation of the database files i see that first one is filled up, which then remains static while the other one (the index, i assume) is populated. it is the second step that takes such a long time (although the cpu is doing very little and ram is barely occupied, it spends all its time swapping). so, my question really is how to tell the server to use ram instead of swap? i am using the default storage engine. > Could you post the actual code you are using for the INSERT? Also, what > storage engine are you using? > > Jay > > andrew collier wrote: > >hello, > > > >i am having some trouble getting mysql to perform decently on my machine. > >it is a 2 GHz dual core AMD 64 machine (although i am presently running a > >32 bit linux system) with 1 Gb RAM and 1 Gb swap partition. the data i am > >using is loaded into a table described by: > > > >CREATE TABLE IF NOT EXISTS strikes ( > > id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, > > epoch DATETIME, > > usec MEDIUMINT UNSIGNED, > > fdate DOUBLE, > > lat FLOAT(6,4), > > lon FLOAT(7,4), > > error TINYINT UNSIGNED, > > nstat TINYINT UNSIGNED > >); > > > >the data itself is pretty big: 70082053 records. > > > >during the loading process mysqlq pretty much hogs the CPU but uses only > >around 5% of the RAM. it takes 13m50s to load the data. there is a lot of > >disk activity, but this is just reading the data and there is virtually no > >swap space in use. > > > >adding the following index: > > > > INDEX coords (lat,lon) > > > >takes a really long time. once again the hard disk is working hard, but > >there is no swapping, so obviously this is just due to database reads. CPU > >usage is about the same. in the end after 60 hours i gave up: had to > >reboot to windows to do some other stuff. but it was just taking > >unreasonably long anyway. > > > >i am pretty sure that a lot more of this processing could be done without > >that much disk activity and i am guessing that is what is slowing the > >whole process down. the configuration i have in my.cnf is: > > > >[client] > >port = 3306 > >socket = /var/run/mysql/mysql.sock > > > >[mysqld] > >port = 3306 > >socket = /var/run/mysql/mysql.sock > >skip-locking > >key_buffer = 32M > >max_allowed_packet = 1M > >table_cache = 4 > >sort_buffer_size = 128K > >read_buffer_size = 256K > >read_rnd_buffer_size = 256K > >net_buffer_length = 2K > >thread_stack = 64K > > > >server-id = 1 > > > >[mysqldump] > >quick > >max_allowed_packet = 16M > > > >[mysql] > >no-auto-rehash > > > >[isamchk] > >key_buffer = 8M > >sort_buffer_size = 8M > > > >[myisamchk] > >key_buffer = 8M > >sort_buffer_size = 8M > > > >[mysqlhotcopy] > >interactive-timeout > > > >please could someone give me an idea of how i might go about making this > >whole thing a little more efficient? thanks! > > > >best regards, > >andrew collier. ---------------------------------------------------------------------- Click to consolidate debt and lower month expenses http://tags.bluebottle.com/fc/CAaCMPJklgxQ4NVfi4KJjZZEBd8Cw1Pv/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
