[nycbug-talk] Monitoring programs

[Bob Ippolito]

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