Professional Web Applications Themes

Split large tables - MySQL

I have a heavily used message system on my site (mysql 4 & php4). The site is running on a dedicated server with two dualcore opterons and 4GB RAM and raid. At the moment I have one table for sent items and one inbox table (storage time 45 days/deletion of older messages and table optimize every night). At the moment both tables getting near 1 Gigabyte. For selects I only use the ID or the owners name. On both fields are indexes. (sorting is only possible by id, newest <-> oldest). With over 600 concurrent users on the site the ...

  1. #1

    Default Split large tables

    I have a heavily used message system on my site (mysql 4 & php4). The
    site is running on a dedicated server with two dualcore opterons and
    4GB RAM and raid.

    At the moment I have one table for sent items and one inbox table
    (storage time 45 days/deletion of older messages and table optimize
    every night). At the moment both tables getting near 1 Gigabyte. For
    selects I only use the ID or the owners name. On both fields are
    indexes. (sorting is only possible by id, newest <-> oldest). With over
    600 concurrent users on the site the performance is well at this point.
    But the mysqld prozess sometimes uses a lot/most of the cpu-%. Slow
    query log is empty.

    Now I would like to add more functions to the message area (like more
    sort options, order by threads, flags, folders). That will make the
    select statements more complex and I'm thinking of possibilities to
    optimize the database layout. Also the database is growing and in some
    month the table will be 2 GB in size.

    The easiest way in my opinion is just to split the tables. Maybe for
    every usernames first character (A-Z and 0-9). Then I would have 36
    tables for inbox and 36 for sent items (maybe I will put inbox and
    outbox in one table). But in this szenario I will have a lot more open
    tables but fewer locks. Another advantage could be that if at this
    point the large table fails it affects all users. Also a repair lasts
    longer. I also read something about InnoDB but i don't know that much.

    I appreciate any ideas. Thanks in advance

    Daniel Onnen Guest

  2. #2

    Default Re: Split large tables

    "Daniel Onnen" <itroxxgmx.de> wrote in message
    news:1141806653.641277.312190j52g2000cwj.googlegr oups.com...
    > Now I would like to add more functions to the message area (like more
    > sort options, order by threads, flags, folders). That will make the
    > select statements more complex and I'm thinking of possibilities to
    > optimize the database layout. Also the database is growing and in some
    > month the table will be 2 GB in size.
    >
    > The easiest way in my opinion is just to split the tables.
    I wouldn't do that as an initial step to optimize. It will make your
    application code more complex because you'll have to query a different table
    based on the username.

    I'd recommend indexing the other fields you may need to search/sort on, and
    then increase your key buffer (since you have 4GB of RAM, go ahead and use
    some of it for caching indexes).

    Note that MySQL has a limitation that it can use only one index per table
    per query. Your application would benefit greatly from the use of multiple
    indexes (e.g. restrict rows based on userid, then sort on flags field).
    Have you considered other RDBMS brands, like PostgreSQL or Firebird or
    Oracle?

    Regards,
    Bill K.


    Bill Karwin Guest

Similar Threads

  1. split large array into chunks
    By Jenda Krynicky in forum PERL Beginners
    Replies: 1
    Last Post: September 5th, 04:28 PM
  2. split large amount of data for loading
    By Toralf Kirsten in forum IBM DB2
    Replies: 3
    Last Post: August 23rd, 04:26 PM
  3. [PHP] HOW DO U SPLIT UP LARGE STRINGS?
    By Jabber@Raditha.Com in forum PHP Development
    Replies: 0
    Last Post: August 16th, 02:22 AM
  4. HOW DO U SPLIT UP LARGE STRINGS?
    By John Ryan in forum PHP Development
    Replies: 1
    Last Post: August 16th, 02:16 AM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139