Order Tray | Contact Us | Home | SIG Lists

[aprssig] High speed datbase inserts (CGI)

Andrew Rich (VK4TEC) vk4tec at tech-software.net
Tue Mar 1 20:08:13 UTC 2005


How you do that in linux ?

On Tue, 2005-03-01 at 11:37 -0800, Scott Miller wrote:
> 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
> >
> 
> 
> _______________________________________________
> aprssig mailing list
> aprssig at lists.tapr.org
> https://lists.tapr.org/cgi-bin/mailman/listinfo/aprssig
> 
> 





More information about the aprssig mailing list