MySQL Forums :: Performance :: 350+ million rows -- is this as fast as it can get? (too slow at the moment!)


Advanced Search

350+ million rows -- is this as fast as it can get? (too slow at the moment!)
Posted by: Reid Wilson ()
Date: February 09, 2009 04:12PM

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

--------------------------

Options: ReplyQuote


Subject Views Written By Posted
350+ million rows -- is this as fast as it can get? (too slow at the moment!) 10051 Reid Wilson 02/09/2009 04:12PM
Re: 350+ million rows -- is this as fast as it can get? (too slow at the moment!) 4023 Rick James 02/10/2009 12:28AM
Re: 350+ million rows -- is this as fast as it can get? (too slow at the moment!) 3317 lau ch 02/10/2009 02:13AM
Re: 350+ million rows -- is this as fast as it can get? (too slow at the moment!) 3006 Rick James 02/11/2009 01:46AM
Re: 350+ million rows -- is this as fast as it can get? (too slow at the moment!) 2685 Reid Wilson 02/11/2009 12:19PM
Re: 350+ million rows -- is this as fast as it can get? (too slow at the moment!) 2733 Rick James 02/11/2009 10:21PM
Re: 350+ million rows -- is this as fast as it can get? (too slow at the moment!) 2754 aftab khan 02/21/2009 11:59AM
Re: 350+ million rows -- is this as fast as it can get? (too slow at the moment!) 2441 Osman SARI 02/23/2009 01:18AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.