[nycbug-talk] Monitoring programs

[Marc Spitzer]

On 8/21/05, Bob Ippolito <bob at redivi.com> wrote:
> 
> On Aug 20, 2005, at 6:12 PM, Marc Spitzer wrote:
> 
> > On 8/20/05, Bob Ippolito <bob at redivi.com> wrote:
> >
> >>
> >> On Aug 20, 2005, at 5:18 PM, Marc Spitzer wrote:
> >>
> >>
> >>> On 8/20/05, Francisco Reyes <lists at natserv.com> wrote:
> >>>
> >>>
> >>>> On Fri, 19 Aug 2005, Marc Spitzer wrote:
> >>>>
> >>>> Today is "learn mysql" day, but plan tomorrow to take a look at the
> >>>> mentioned packages.. although I am tempted to just capture the
> >>>> output of
> >>>> df, du and "w" for now and dump it into a PostgreSQL database for
> >>>> now..
> >>>>
> >>>>
> >>>
> >>> If you know postgres, why play with mysql?
> >>>
> >>
> >> Uh yeah.  I've had more bad experiences this month with MySQL than
> >> anyone should ever have with a RDBMS.  These things are supposed to
> >> be robust and reliable, MySQL is neither.  Granted, we're doing ~150
> >> queries/sec and have > 4GB data in there, but still.  Stay away if
> >> you can!
> >>
> >
> > what is the select vs insert ratio?  guess is fine, just curious.
> 
> Since the last time we had to restart it (3d 7h):
> 
>  >>> (100.0 * inserts)/all
> 26.201134462676947
>  >>> (100.0 * selects)/all
> 34.751826337623328
>  >>> (100.0 * updates)/all
> 39.047039199699725
> 
> The updates are to facilitate "materialized views" of SUM(*) queries,
> because MySQL really sucks at optimizing that sort of thing, so I had
> to do it myself.  Our batches are very small (we try and keep it as
> near-real-time as we can), so while I could reduce the update count
> using temp tables (or more code out of the database) for some of the
> aggregation, it's not really worth it.
> 
> Regardless of what the usage statistics are, it doesn't mean that:
> - Deadlocking should happen for no good reason -- which ends up
> saturating all of the connections and wedges the daemon if you don't
> catch it in time (we actually had a SELECT query that was locking
> unrelated tables the other day -- even just other SELECTS!)
> - Replication should stop without error in a strictly master/slave
> configuration
> - Tables should have a fixed size and then throw errors until you
> resize the table (IOTW: don't use MyISAM, or set the max row length
> early)
> 
> MyMISTAKE! :)
> 
> -bob
> 
> 


Sticken with postgres, will look at ingres when it gets ported.  

marc