If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > MySQL > Large MySQL table/database (50+ gigs)

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old 02-23-04, 19:21
wga22 wga22 is offline
Registered User
 
Join Date: Feb 2004
Posts: 5
Large MySQL table/database (50+ gigs)

What kind of experience have people had with myisam and large tables, on the order of ~50GB(for a single table, other tables are ~2gigs including index)(I am not concerned with OS file size limits.) and billions of rows?

What are the most important settings to tweak in the my.cnf file? I have 2gigs of ram and 4 xenon processors.

I first tried prototyping my database with Innodb, but the transactional nature of it seemed to kill my performance. Am I crazy to not use Innodb? Is it a bad assumption that Innodb is slower? My evaluation is based on the usage of "load data infile" to create the initial values.
Reply With Quote
  #2 (permalink)  
Old 02-24-04, 02:48
ika ika is offline
Registered User
 
Join Date: Oct 2003
Location: Slovakia
Posts: 482
Re: Large MySQL table/database (50+ gigs)

Quote:
Originally posted by wga22
What kind of experience have people had with myisam and large tables, on the order of ~50GB(for a single table, other tables are ~2gigs including index)(I am not concerned with OS file size limits.) and billions of rows?

What are the most important settings to tweak in the my.cnf file? I have 2gigs of ram and 4 xenon processors.

I first tried prototyping my database with Innodb, but the transactional nature of it seemed to kill my performance. Am I crazy to not use Innodb? Is it a bad assumption that Innodb is slower? My evaluation is based on the usage of "load data infile" to create the initial values.
Here you can find something about table limits and OS limits:
http://www.mysql.com/doc/en/Table_size.html

InnoDB is very fast transactional database.If it seems to be slow for you then your problem is probably in your database design.

Performance of data insert could be imporved. Here you can find some optimisation tips for insert (and other) operations:
http://www.mysql.com/doc/en/Insert_speed.html
http://www.mysql.com/doc/en/Tips.html
http://www.mysql.com/doc/en/Query_Speed.html
Reply With Quote
  #3 (permalink)  
Old 02-25-04, 17:54
wga22 wga22 is offline
Registered User
 
Join Date: Feb 2004
Posts: 5
Quote:
InnoDB is very fast transactional database.If it seems to be slow for you then your problem is probably in your database design.
Couldn't it be that a non-transactional database is faster than a transaction based? What would be the reason for keeping myisam around if innodb were better in all ways?
Reply With Quote
  #4 (permalink)  
Old 02-25-04, 22:05
aus aus is offline
Registered User
 
Join Date: Oct 2003
Location: Denver, Colorado
Posts: 137
MySQL AB encourages the use of MyISAM tables for speed when transactions are not necessary (or required). The documentation does say that MyISAM tables can perform poorly when the DB serves mostly heavy updates and complex reads. http://www.mysql.com/doc/en/Design_Limitations.html
What you need to do is to analyze how your database is going to be used and weigh each table type's ability in that regard.
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On