[nycbug-talk] Monitoring programs

Bob Ippolito bob
Sun Aug 21 00:49:26 EDT 2005

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
 >>> (100.0 * selects)/all
 >>> (100.0 * updates)/all

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  
- 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  



More information about the talk mailing list