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

[Andrew Rich]

steve, do you use insert delayed ?

to give priority to select statements ?

On Monday 26 September 2005 07:21, Steve Dimse wrote:
> 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
> 
> _______________________________________________
> aprssig mailing list
> aprssig at lists.tapr.org
> https://lists.tapr.org/cgi-bin/mailman/listinfo/aprssig
> 
> 
> 

-- 
------------------------------------
Andrew Rich VK4TEC
Satellite Ground Station for PCSAT2
www.tech-software.net
vk4tec at tech-software.net