•  

Announcement

Announcement Module
Collapse
No announcement yet.

Innodb, splitting table and index onto multiple spindles, key cache size

Page Title Module
Move Remove Collapse
Conversation Detail Module
Collapse
  • Page of 1
  • Filter
  • Time
  • Show
Clear All
new posts

  • Innodb, splitting table and index onto multiple spindles, key cache size

    #1
    Is it possible to force the index and table to be in separate files, so that the files can be placed on separate disks?

    Also is key cache size important with innodb?

    We have large datasets that grow every second, that we search on multiple keys, and our performance is currently very poor, and we are trying to optimize it at the hardware level first.

    Are there other tuning parameters?
    Thank you.
    Tags: None

  • #2
    No you can't define where you want InnoDB to put index and data.
    One of the main reasons to this is that data and indexes are intermingled with InnoDB tables since it stores the rows as leafs on the primary index.

    If you by key_cache mean the parameter key_buffer_size then no, it is only effective for MyISAM tables.

    For InnoDB tables the most important variable for speed is the:
    innodb_buffer_pool_size
    Which you should set as big as possible to avoid disk activity (but don't set it to big since swapping will slow down the system immensely). Recommended setting is about 80% of available memory on a dedicated server.

    BTW unless you have a pretty large database (about 50GB or more) and you haven't maxed out the RAM on your server you should start by increasing the RAM as much as possible.
    Because it will give you more performance gain than trying to control where data is put on disks.

    Comment

    Working...
    X