[nycbug-talk] MySQL

[Bob Ippolito]

On Aug 21, 2005, at 8:00 AM, Charles Sprickman wrote:

> On Sun, 21 Aug 2005, Francisco Reyes wrote:
>
>
>> On Sat, 20 Aug 2005, Bob Ippolito wrote:
>>
>>
>>> 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!
>>>
>>
>> I have 20 to 60 queries per second and MySQL is using up 50% to  
>> 70% of CPU. What optimizations have you done that you could  
>> recommend?
>>
>
> I enjoy it when mysql does this while handling a whopping 20-40  
> selects/second:
>
> PID USERNAME  PRI NICE  SIZE    RES STATE  C   TIME   WCPU   CPU  
> COMMAND
> 60151 mysql   64   0  86832K 47436K CPU1   0  35.6H 96.73% 96.73%  
> mysqld
>
> It's bogus of course, the thing is just spinning on something and  
> once it's stopped/restarted it uses almost no CPU.  Annoying as  
> hell though. At least these days it can be gracefully shutdown when  
> hung like that.  It used to require a -9 to stop it.
>
> I need to check out where Postgres is at with replication, and how  
> well vpopmail is supporting Postgres...

Slony-I is probably what you want for replication.

For backup purposes, you can look into the PITR (point-in-time  
recovery) features in 8.0.  Basically, the WAL (write-ahead log) can  
be shipped off to another machine when they're rolled over, and using  
these you can reconstruct the database on another machine if anything  
goes wrong.  However, you can't actually run the database until  
something goes wrong, because you can't add PITR logs to a running  
database..

You can also do something similar with Slony-I log shipping, except  
Slony-I logs can be imported into a live database.  The (only)  
reasons to consider PITR are that it's already there, and that it's  
simple to configure.

-bob