350+ million rows -- is this as fast as it can get? (too slow at the moment!)
My server (Quad Core Dual Xeon 5405, 16 GB RAM, 4x1.5TB SATA HDD in RAID 10) is taking a very, very long time to do various database operations on a massive database. I'm wondering if anyone would have any insight on how we can speed things up.
Background:
A vendor sent us a database with about 365 million rows. All 50 columns in the table are varchar fields, there is no primary key / unique identifier, and there were no indexes on the table they sent us.
To make use of this, we need a table with each row having a unique identifier (auto increment id is fine) with several indexes and with several fields converted from varchar to date fields and with some calculated fields added (i.e. soundex).
To run just one index on the original table took about 6 days to complete! (Queries using that index are fast though.)
Separate from that, I've written a program that reads in rows from the original table and writes them to a new database table while converting dates, adding an auto-increment id, adding soundex columns, etc. Early on this program took about an hour to add a million records, but now it is a slower than that as the new table gets bigger -- about three hours to get a million in. And that's with only 100 million rows in the new table -- barely over a fourth of the way there.
My question to you: Any insight into how we can faster get the database we need with indexes, a primary key, and converted fields?
Below is my my.cnf sans comments.
Thanks much!
Reid
--------------------------
[client]
port = 3306
socket = /var/lib/mysql/mysql.sock
[mysqld]
datadir = /part1/mysql
port = 3306
socket = /var/lib/mysql/mysql.sock
skip-locking
key_buffer_size = 4096M
max_allowed_packet = 32M
table_cache = 1024
sort_buffer_size = 512M
read_buffer_size = 512M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
thread_cache_size = 16
query_cache_size = 512M
thread_concurrency = 8
log-bin=mysql-bin
server-id = 1
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
[isamchk]
key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M
[myisamchk]
key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout
--------------------------