[ale] Any MySQL Experts around?
hbbs at comcast.net
Sat Feb 14 22:44:46 EST 2004
The inserts themselves don't seem like they'd be that big a deal, at
about 1.2 inserts/second, but some of the other processing you might be
dealing with could hurt.
This might be one of those instances where you set up MySQL and
PostGreSQL (damn, that's a bugger to type) side by side and see how they
do. Also a good place to test out filesystem performance
(ext3/reiserfs/xfs/jfs/fat16 [just kidding])
On Sat, 2004-02-14 at 22:11, Jason Etheridge wrote:
> > I'm thinking of having all my server logs (including NT/2K/2K+3) record
> > into a MySQL db for better reporting. Does anyone know if MySQL can
> > handle 100,000+ inserts per day? Also, if it handling this many
> > inserts,
> > how good is the SELECT performance for the automated reporting? ANy
> > thoughts, pointers?
> Disclaimer: I'm not a real DBA and the tuple calculus I learned in
> school actually tripped me up in the real world :D
> The largest table I work with in mysql has roughly 8 million records
> using full-text indexing. Each row contains a little over 260 bytes of
> data. Every morning I turn indexing off and do a LOAD DATA REPLACE
> touching every record, changing a good bit of them, and adding a
> thousand or so new records. It can take around 3 hours to do that and
> reindex on an old Sun E10K (I forget how many processors; maybe a dozen
> for that domain). Queries are fast after that, but I use READ LOCK's
> and there are no updates during the day. I'm not reporting on live
> If you don't use full-text indexing, and instead use LIKE statements on
> large fields, searching is going to be pretty slow with that many
> records, especially if you can't lock the tables because you're doing
> updates. If you do use full-text indexing, I'd worry about 100,000+
> inserts a day and concurrent searching. Full-text updates work best
> with indexing disabled, and even then, when you go to renable indexing,
> it'll do it via REPAIR BY SORTING, which could take a long time if you
> don't have enough RAM to do it without thrashing the disks.
> What sort of reporting do you want to do and sort of logs are they? If
> you just want to store key statistical information rather than entire
> log entries, that would be better. But for storing actual logs, I'm
> not sure relational databases are the best tool. For log-based
> reporting, I currently use a kludge: the system loads into temporary
> tables specific data parsed from log entries from just the months we're
> interested in for the current running report. This only works well for
> one or two months in our case because of the arbitrary timeout value I
> set. For anything really large, like a year's worth of transactions, I
> just run the logs through a perl script and forget mysql.
> I have another table without full-text indexing that gets searched and
> updated throughout the day, with about a million records. But nothing
> is added except in the mornings, just updated. I'll try to find some
> stats for that if you want.
> Best thing to do is get a test machine and play around with some sample
> loads, and make sure to allocate a lot of filehandles for mysql's
> process space.
> -- Jason
> Ale mailing list
> Ale at ale.org
Jeff Hubbs <hbbs at comcast.net>
More information about the Ale