[aprssig] High speed datbase inserts (CGI)

[Gerry Creager N5JXS]

We have snagged data and done bulk inserts, and we've inserted data as 
it's decoded directly into the database.  We use PostGIS instead of 
MySQL, but that's essentially a nit.

You don't mention the volume of data you'll be trying to manage.  We 
work with real-time weather data, and downloads from a variety of 
sources.  Our stream is a lot bigger than the APRS-IS.

Based on our experience, a well-designed dabase with decent keys, and a 
well-tuned insert strategy allows you to insert data essentially in 
real-time rather than in a batch mode, from files.  If you're going to 
batch the inserts, consider dropping the indices, copying the data into 
the database, then reindexing.

gerry

Andrew Rich wrote:
> Howdy,
> 
> I have a situation at my work that is very close to APRS.
> 
> I was wondering if someone could pass on some words of wisdom.
> 
> I have a stream from a data source not unlike and APRS server stream.
> 
> I would like this to be split up and end up in a MySQL database to be
> searched on.
> 
> I have been experimenting with writting to files every 10 minutes and then
> doing inserts into mysql
> from these files. With the idea of giving the database time to breath for
> searches.
> 
> I have some questions regarding perl. In the duration I am closing off one
> file and opening up another,
> would the telnet data be "pooled" by Net/Telnet.pm.
> 
> Would I be better off inserting straight into the database ? and not worry
> about files ?
> 
> How does findu.com do it ?
> 
> I think I see two ways of doing this
> 
> 1) Write data from the telnet stream into files each minute, then start a
> new file and process the old files
> 2) Place data straight into the mysql database
> 
> My only concern is bottle necks.
> 
> Comments ?
> 
> Andy VK4TEC
> 
> 
> 
> _______________________________________________
> aprssig mailing list
> aprssig at lists.tapr.org
> https://lists.tapr.org/cgi-bin/mailman/listinfo/aprssig

-- 
Gerry Creager -- gerry.creager at tamu.edu
Texas Mesonet -- AATLT, Texas A&M University	
Cell: 979.229.5301 Office: 979.458.4020 FAX: 979.847.8578
Page: 979.228.0173
Office: 903A Eller Bldg, TAMU, College Station, TX 77843