[aprssig] High speed datbase inserts (CGI)

[Scott Miller]

How about putting the whole thing on a RAM disk?

Scott
N1VG

----- Original Message ----- 
From: "Andrew Rich (VK4TEC)" <vk4tec at tech-software.net>
To: "TAPR APRS Mailing List" <aprssig at lists.tapr.org>
Sent: Tuesday, March 01, 2005 11:24 AM
Subject: RE: [aprssig] High speed datbase inserts (CGI)


> 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
> >
> >
>
>
> _______________________________________________
> aprssig mailing list
> aprssig at lists.tapr.org
> https://lists.tapr.org/cgi-bin/mailman/listinfo/aprssig
>