Inserting in Real Time... a beginner DB guy

[ottdeveloper]

I need to insert data into a table in real time. The table is simple... int, char, char, double, int.

First I assume that insertion will be much faster if the table is not indexed although indexing would be nice.

Each day the table is cleared out but towards the end of the day the table could have 10-15 Million rows in it.

Given a pretty good piece of hardware what can I expect on insertion of rows per second.

I'm running tests but I would like any tips on this.

Would it be worth it to break up the table into 25 smaller tables?

 

[ingresman]

Before you go fr 25 tables (why 25 ) make sure that it is worth it. It does soubd like a lot of rows but non the less MYSQL is very quick.
If you do split the table try to put them on seperate spindles (can this be done in mysql ?) and if possible on seperate controlers (or a few any how).
You might have to go done the old raid5 route which can be good, can be bad.

Show your bechmarks and an idea of the hardware your on, i'd be interested in how you get on.

 

[vanekl]

If you're using a disk drive with 8 millisecond access time
and do an implicit/explicit commit after each insert (which is
the MySQL default operation), there isn't enough time in the
day to enter 15 million records.
.008 * 15,000,000 / 60 / 60 = 33.3 hours
So plan on using transactions and only commit every few
thousand inserts, and that should speed things up.

If you want to avoid hard drives completely then you can use
heap tables. They are very fast because they are stored
completely in RAM. They are volatile, though, and you will lose
all the data in case of a power failure. Since you are clearing
the tables out every day that doesn't seem to be too much of an
issue.
An index on the primary key is automagically created by MySQL.
Yes, they slow down inserts but if you use only one
index and other processes use it to do SELECTS then it's
probably worth it to keep one index, especially if you only
do a commit every few thousand inserts.

Get enough RAM to hold the entire table in memory. That means
you will need 400MB for the (HEAP) table, and 115 MB for the index, and
190 MB+/- for the OS, so you should probably plan on 1 GB RAM
(minimum).

MySQL cannot distribute one table across more than 1 spindle,
unless you are using RAID, so there is an advantage to splitting
the table into several smaller tables assuming:
1. each of the tables gets its own spindle
2. the data is evenly distributed across the spindles so that
all INSERTS/SELECTS just don't hit one drive and create a hot spot.

The primary factors that will determine insertion speed will be:
- whether you decide to go with HEAP tables or disk-based tables
- how often you do commits
- whether you have enough RAM to keep the table and index in memory
The secondary factors are:
- use of indexes
- number of spindles used

 

[hos2]

I wonder who is giving a beginner (as you call yourself) a job to make an application for a 15 million record insert on daily basis routine ??