At 03:23 AM 4/2/2007, you wrote:
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.
Only if the table is empty when you first start the Load Data command. If
you have data in the table then the index is maintained as the rows are
loaded (much slower). If this is the case then you may want to disable the
indexes prior to Load Data and then re-enable the indexes later will
rebuild them.
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?
For MyISAM tables, you need to increase the Key_Buffer_Size to up to 30% of
available memory. This is where the keys are built. Increasing this value
(get more RAM if you need to), will dramatically speed up the performance
of building the index, as much as 100x faster. So if you don't have enough
RAM, beg borrow or steal some for a few days and bump up Key_Buffer_Size
and you should notice a big difference.
i am using the default storage engine.
I assume then it is MyISAM. You can of course override it and make InnoDb
the default in which case the Key_Buffer_Size won't have any effect.
Mike
> 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]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
