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.
----------------------------------------------------------------------
Need cash? Click to get a payday loan
http://tags.bluebottle.com/fc/CAaCMPJktTdJ7Iu6tODsIDX46jYJlhKA/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
