Sudhir, please remove the || from your name in your email: "|| Sudhir
We have about 1 BILLION rows of data here and we do a few tricks which may
or may not be of interest to you.
For starters, setup replication and write to a master and have at least 2
slaves. This makes backups MUCH easier as aside from having redundancy
across 3 servers, you can actually take a slave down, backup, then restore
it as part of the cluster again with no down time for the whole system/web.
Another trick we do is this: Since we have hundreds of thousands of 'events'
coming in per offload, we store the start and end ID of the events for more
detailed reports etc. but pre-process into aggregate tables the data we need
(including tallys etc). This requires some table locking so the inserts are
guaranteed sequential, but it saves us a stupid join for thousands of rows
we really don't need. Plus it also gives us a nice PK integer range to use
when we do need it.
If you don't REALLY need foreign keys and transactions and all that other
bloat, then use MYISAM tables. They're much faster and they also have the
nice benefit of "SELECT COUNT(*)" being accurate. INNODB is NOT accurate and
requires a table scan to determine the true number. Lame I know.