Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2004
    Posts
    5

    Unanswered: 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.

  2. #2
    Join Date
    Oct 2003
    Location
    Slovakia
    Posts
    482

    Re: Large MySQL table/database (50+ gigs)

    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

  3. #3
    Join Date
    Feb 2004
    Posts
    5
    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?

  4. #4
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •