[aprssig] High speed datbase inserts (CGI)

[Andrew Rich (VK4TEC)]

We only want the data to "live" in mysql long enough to do stats on 
it and then chuck it ;-)



On Tue, 2005-03-01 at 12:27 -0500, Steve Dimse wrote:
> On 3/1/05 at 6:26 AM AE5PL Lists <HamLists at ametx.com> sent:
> 
> >Use minimal indexes.  Indexes are more performance problematic than most
> >other factors for databases which are mostly added to instead of
> >searched.
> >
> I agree unused indexes are bad, but it is important to have the indexes you will
> use. As table size goes up, the cost of a query that is not covered by an index
> goes up, it means reading the entire table to answer a query. 
> 
> How bad this affects you in MySQL depends on another character of the table,
> whether there is free space inside the table. If you delete records future
> inserts will reuse that space, but until it is used up, the table cannot do
> simultaneous reads and writes. So if you need to do a full table read, all
> writes are locked out until the read completes, for a gigabyte sized table that
> can be a while.
> 
> With no free space, you can have many reads and one write occuring
> simultaneously in a table, this works because any new data being added is added
> at the end of the table and can be picked up by the reads. If new records can be
> inserted in the middle of the table, then reads are held up while a write is
> executed.
> 
> You can get around this by either optimizing tables when deletes are performed,
> (though be aware UPDATE can also generate free space when the records are not of
> fixed length), or using a separate deleted field to exclude deleted records from
> a query without actually deleting them from a table. If your tables are large,
> OPTIMIZE can be more trouble than it is worth, as it locks out all reads and
> writes. findU's 2004 weather table took 18 hours to optimize, it is 8.6 GB, and
> of course a lot of other things were going on in the machine at the same time.
> 
> So yes, each index adds overhead to every insert operation, but a query not
> covered by an index is also not a good thing, so the indicies should be
> carefully chosen...
> 
> Steve K4HG
> 
> _______________________________________________
> aprssig mailing list
> aprssig at lists.tapr.org
> https://lists.tapr.org/cgi-bin/mailman/listinfo/aprssig
> 
>