[no subject]

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

- Jeff

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 
> data.
> 
> 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
&gt; <a  rel="nofollow" href="http://www.ale.org/mailman/listinfo/ale";>http://www.ale.org/mailman/listinfo/ale</a>
-- 
Jeff Hubbs &lt;hbbs at comcast.net&gt;


</pre>
<!--X-Body-of-Message-End-->
<!--X-MsgBody-End-->
<!--X-Follow-Ups-->
<hr>
<!--X-Follow-Ups-End-->
<!--X-References-->
<ul><li><strong>References</strong>:
<ul>
<li><strong><a name="00500" href="msg00500.html">[ale] Any MySQL Experts around?</a></strong>
<ul><li><em>From:</em> jonathan.glass at ibb.gatech.edu (Jonathan Glass)</li></ul></li>
<li><strong><a name="00503" href="msg00503.html">[ale] Any MySQL Experts around?</a></strong>
<ul><li><em>From:</em> phasefx at magusaptus.com (Jason Etheridge)</li></ul></li>
</ul></li></ul>
<!--X-References-End-->
<!--X-BotPNI-->
<ul>
<li>Prev by Date:
<strong><a href="msg00504.html">[ale] Any MySQL Experts around?</a></strong>
</li>
<li>Next by Date:
<strong><a href="msg00505.html">[ale] OT: Cities/Counties/Zips by State?</a></strong>
</li>
<li>Previous by thread:
<strong><a href="msg00504.html">[ale] Any MySQL Experts around?</a></strong>
</li>
<li>Next by thread:
<strong><a href="msg00517.html">[ale] Any MySQL Experts around?</a></strong>
</li>
<li>Index(es):
<ul>
<li><a href="maillist.html#00509"><strong>Date</strong></a></li>
<li><a href="threads.html#00509"><strong>Thread</strong></a></li>
</ul>
</li>
</ul>

<!--X-BotPNI-End-->
<!--X-User-Footer-->
<!--X-User-Footer-End-->
</body>
</html>