[aprssig] Re: More on the findU server issue...

[Steve Dimse]

On Sep 25, 2005, at 4:36 PM, <scott at opentrac.org>  
<scott at opentrac.org> wrote:

>> Load balancing works when the problem is the web load, but
>> that is not the case here. The problem is the database, with
>> a constant (dozern of packets a second) write pressure on the
>> database means you need a fast disk array. If you tried to
>> load-balance with findU, you need a server for every 2 or
>> three simultaneous requests if you use hard at the level of
>> the current backup server.
>>
>
> I was assuming that each machine could run its own database.

It does, that's the point. In order to be able to serve even a single  
request, it must write the entire stream to the tables. In primary  
server class hardwar, it can do that and server few dozen  
simultaneous read requests. In the secondary server hardware, it can  
handle only a couple reads along with the write load.

> I'm pretty
> sure I could tune an Oracle database to do pretty well with that  
> load on
> modest hardware (I've had one logging large chunks of the APRS IS  
> stream,
> but never the whole thing), but that doesn't help you much either.   
> I don't
> know enough about MySQL to know how you might improve the write  
> performance.
> For writes alone, raw disk throughput shouldn't be that much - I  
> get about 2
> kB/sec from the IS stream, and even assuming a 100x increase to  
> account for
> db overhead and indexes, that's only 200 kB/sec.  My cheap ATA drives
> benchmark in the tens of megabytes/sec.  Is MySQL really that bad  
> with heavy
> writes?

Depends what you call heavy writes. SQL opinions are a lot like OS  
opinions, as much a religion as fact. When people bash MySQL, it is  
generally not on the subject of performance, but on features like  
transactions. MySQL developed as a fast -and-light sort of SQL, which  
is well suited to a system like findU.

Each packet results in multiple writes...for example, a position  
report writes the raw packet, the position into the 120 day table,  
and into the last position table. If it is an ARISS/PCSat packet,  
that is two more writes into those tables. With a peak of perhaps 50  
packets a second these days on the APRS IS (average would be about  
20) that is a lot of activity, perhaps 150 writes a second, and not  
in a burst, this is a constant load. Each of these writes affects the  
data and the index of each table, and therefore involves at least one  
seek. It is therefore dependent on the seek time of the disk, and  
more imporatntly the latency. The primary server's 15k RPM disks will  
have half the latency of the 7200 RPM drives in the backup.

> Or is it the concurrent read/write load that kills it?  What's the
> cache hit percentage on reads?

With over 2GB of cache on the primary, and 500MB on the secondary, it  
is obviously going to be a lot better on the primary. I do not know  
the specific numbers though. The problem is just general load. For  
example, I do not think the current backup machine could handle just  
the writes if the traffic on the APRS IS doubled.

Steve K4HG