Re: mysqlstat (WAS Re: Optimizing Ext3 for MySQL)
Jeremy Zawodny wrote: Now, I just have to remember to push out mytop 1.3 so that folks can shake out any new bugs I've introduced... Just make it one binary that recognizes its argv[0] calling (like gzip) and also supports command-line options. The two data collection methods are the same ... so you might as well have essentially `alias mysqlstat $x='mytop --vmstat=$x'` (yes, I know that's not valid bash symantics). -- Michael T. Babcock C.T.O., FibreSpeed Ltd. http://www.fibrespeed.net/~mbabcock - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Optimizing Ext3 for MySQL
Steven Roussey wrote: Might also look at: vmstat 1 `vmstat 1` is my favorite instant-info server debugging tool. I wouldn't mind the same program for MySQL (where's that mytop author anyhow? j/k) Blocked processes (second column) is a very useful piece of info too. -- Michael T. Babcock C.T.O., FibreSpeed Ltd. http://www.fibrespeed.net/~mbabcock - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
mysqlstat (WAS Re: Optimizing Ext3 for MySQL)
On Wed, Jan 22, 2003 at 02:21:34PM -0500, Michael T. Babcock wrote: Steven Roussey wrote: Might also look at: vmstat 1 `vmstat 1` is my favorite instant-info server debugging tool. I wouldn't mind the same program for MySQL (where's that mytop author anyhow? j/k) Hmm. mytop 1.4 will have a feature that may help you. It'll sorta like a vmstat the watches the output of SHOW STATUS, mostly the Com_* counters. You'll be able to get the data refreshed at whatever rate you like, just like vmstat. Initially, I was going to do it as a full-screen sort of display, but now that you mention it I think a vmstat-style let-it-scroll-by version might be helpful. Maybe I could rig it up so that if you call it as mysqlstat (via a symlink) it'd automatically start in that mode. Hmm. Thoughts? Now, I just have to remember to push out mytop 1.3 so that folks can shake out any new bugs I've introduced... Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.8: up 4 days, processed 118,983,374 queries (342/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: mysqlstat (WAS Re: Optimizing Ext3 for MySQL)
Hmm. mytop 1.4 will have a feature that may help you 1.4? I'm still on 1.0. Guess I'm behind the curve. Jeremy, can you add something to protect against binary data coming across in a query and messing up the terminal window? Leave it running a while and all of a sudden it is a big mess. Yikes! It'll sorta like a vmstat the watches the output of SHOW STATUS, mostly the Com_* counters Poor man's version: watch mysqladmin extended-status Sincerely, Steven Roussey http://Network54.com/ query,sql,stuff,cool - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: mysqlstat (WAS Re: Optimizing Ext3 for MySQL)
On Wed, Jan 22, 2003 at 06:06:51PM -0800, Steven Roussey wrote: Hmm. mytop 1.4 will have a feature that may help you 1.4? I'm still on 1.0. Guess I'm behind the curve. Well, 1.3 is sitting in my CVS tree. I just need to update the docs and changelog. I already have some patches pending for 1.4... Heh. Jeremy, can you add something to protect against binary data coming across in a query and messing up the terminal window? Leave it running a while and all of a sudden it is a big mess. Yikes! Oh, good idea. I'd never run into that, but I can see how that'd be a prolbem. I'll make sure there's a binary filtering option added. It'll sorta like a vmstat the watches the output of SHOW STATUS, mostly the Com_* counters Poor man's version: watch mysqladmin extended-status One of my favorite Borg quotes is: Crude but effective. :-) Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.8: up 4 days, processed 123,404,209 queries (335/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: mysqlstat (WAS Re: Optimizing Ext3 for MySQL)
One of my favorite Borg quotes is: Crude but effective. :-) I like that. ;) This got me thinking again about a feature I'd like to see in mysqld. I'd like to add something like SQL_STATISTICS to SELECT/UPDATE/INSERT statements. The idea would be that if this keyword was used, then mysqld would calculate additional statistics for that query (which you could get later with SHOW SQL_STATISTICS or something). The statistics would include actual numbers from the processing of the query: # of index records read # of data section records read # bytes requested from file system (read write) [this being the most important] # CPU time # Disk read, write, and wait times etc... Such information would be extremely valuable. For example, it wasn't until I put fulltext on separate server that I would discover what a disk read hog it was: Main server: 3000 q/s Disk read: 540 KB/sLoad: 1 FTS server: 2 q/s Disk read: 7600 KB/sLoad: 12 (taken from http://marc.theaimsgroup.com/?l=mysqlm=104042853614294w=2) Sadly, I will have to write my own FTS system soon. I'll be using mysql to do it, so hopefully Serg will be able to port whatever ideas I settle on back inside the mysqld engine (assuming they worth porting!!) I'll start by moving the ideas of the cosine vector search from C to SQL. Then I can try a lot of different things without constant re-compiling. I'll also get the advantage of having it work with several languages on a record basis, rather than a table basis... Now if only I had a paying job, I could focus on it and get it done quicker... Sincerely, Steven Roussey http://Network54.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Optimizing Ext3 for MySQL
On Tue, Jan 14, 2003 at 11:33:54PM -0800, Jeff Kilbride wrote: Are there any general guidelines for optimizing ext3 for MySQL? I have a perl script that runs 200K + updates into my database once a day and I see pretty wildly fluctuating query/sec numbers using Jeremy Z's mytop program. I've seen in excess of 2000 qps and then seen that number drop to 40 qps. The average seems to be about 200 qps, which seems kinda slow given my hardware: Dual P3 1.3MHz 1GB RAM Dual SCSI drives (160 MB/s) RedHat 7.3 MySQL 3.23.53a w/MyISAM tables This particular table being updated has about 5 million rows. The fields being updated are not indexed. By updated you mean inserts? Or are you doing a table scan for each update (since they're not indexed)? Also, the qps numbers seem to slow down every 5 seconds or so, which I think matches the default write timing for the ext3 journal. Can anybody share their experience with optimizing ext3 -- i.e. which mode is best (writeback, ordered, journal) and any parameters that can be tuned? Any insight is appreciated. Yeah, 5 seconds is the ext3 default. You can tune it. I recently saw someone suggest this: # set disk flush to 30,000 clicks or 5 minutes echo 30 64 64 256 3 3000 60 0 0 /proc/sys/vm/bdflush But have not tried it myself. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 3.23.51: up 31 days, processed 1,040,147,810 queries (381/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Optimizing Ext3 for MySQL
- Original Message - From: Jeremy Zawodny [EMAIL PROTECTED] To: Jeff Kilbride [EMAIL PROTECTED] Cc: MySQL [EMAIL PROTECTED] Sent: Wednesday, January 15, 2003 10:41 AM Subject: Re: Optimizing Ext3 for MySQL On Tue, Jan 14, 2003 at 11:33:54PM -0800, Jeff Kilbride wrote: Are there any general guidelines for optimizing ext3 for MySQL? I have a perl script that runs 200K + updates into my database once a day and I see pretty wildly fluctuating query/sec numbers using Jeremy Z's mytop program. I've seen in excess of 2000 qps and then seen that number drop to 40 qps. The average seems to be about 200 qps, which seems kinda slow given my hardware: Dual P3 1.3MHz 1GB RAM Dual SCSI drives (160 MB/s) RedHat 7.3 MySQL 3.23.53a w/MyISAM tables This particular table being updated has about 5 million rows. The fields being updated are not indexed. By updated you mean inserts? Or are you doing a table scan for each update (since they're not indexed)? No, they're actual updates (UPDATE table SET field=value WHERE primary_key=value2). The field being updated is not an index, so I'm assuming the index files aren't changing -- which, in general, should be faster than a case where an index is changed. (right?) However, I'm using the primary key in the WHERE clause to find the correct record to update -- so it's not a table scan for each record. Also, the qps numbers seem to slow down every 5 seconds or so, which I think matches the default write timing for the ext3 journal. Can anybody share their experience with optimizing ext3 -- i.e. which mode is best (writeback, ordered, journal) and any parameters that can be tuned? Any insight is appreciated. Yeah, 5 seconds is the ext3 default. You can tune it. I recently saw someone suggest this: # set disk flush to 30,000 clicks or 5 minutes echo 30 64 64 256 3 3000 60 0 0 /proc/sys/vm/bdflush But have not tried it myself. What's a click? I've seen some other suggestions for bdflush, also -- but I've seen other articles that say the defaults are pretty good and playing with these numbers could cause more harm than good... I haven't come across anything definitive that deals with tuning ext3. I'm tempted to try mounting the DB drive as ext2, to see what difference it makes. Apache/PHP is also running on this box and accessing the database, but the load is 0.00 until I run the update script -- then the load jumps to anywhere between 2 and 5. If I switch modes with mytop, I see something like this: 233 245 218 158 2 120 250 235 195 4 etc... Which makes me think the slowdown has something to do with the journal writes. I've seen numbers as high as 2000 in mytop consistently over 3 or 4 seconds, and more than once while the script runs, but I don't know why I'm getting these huge bursts of speed intermittently. This isn't a huge problem, I'm just puzzled that I can get such high numbers when my average seems 10 or 20 times less. Is this normal? Is there any way to optimize large numbers of UPDATES with MySQL, like you can INSERTS? Thanks, --jeff Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 3.23.51: up 31 days, processed 1,040,147,810 queries (381/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Optimizing Ext3 for MySQL
On Wed, Jan 15, 2003 at 11:24:05AM -0800, Jeff Kilbride wrote: By updated you mean inserts? Or are you doing a table scan for each update (since they're not indexed)? No, they're actual updates (UPDATE table SET field=value WHERE primary_key=value2). The field being updated is not an index, so I'm assuming the index files aren't changing -- which, in general, should be faster than a case where an index is changed. (right?) However, I'm using the primary key in the WHERE clause to find the correct record to update -- so it's not a table scan for each record. Ah, good. I mis-understood what you were saying about indexes. Cool. Yeah, 5 seconds is the ext3 default. You can tune it. I recently saw someone suggest this: # set disk flush to 30,000 clicks or 5 minutes echo 30 64 64 256 3 3000 60 0 0 /proc/sys/vm/bdflush But have not tried it myself. What's a click? I've seen some other suggestions for bdflush, also -- but I've seen other articles that say the defaults are pretty good and playing with these numbers could cause more harm than good... I haven't come across anything definitive that deals with tuning ext3. I'm tempted to try mounting the DB drive as ext2, to see what difference it makes. Yeah, I'd suggest diabling the journal and see what happens. I belive you can use tunefs to do that. I'm a ReiserFS person myself, so this is mostly second-hand info. That's why I don't know what clicks are in that comment either. Apache/PHP is also running on this box and accessing the database, but the load is 0.00 until I run the update script -- then the load jumps to anywhere between 2 and 5. If I switch modes with mytop, I see something like this: 233 245 218 158 2 120 250 235 195 4 etc... Which makes me think the slowdown has something to do with the journal writes. I've seen numbers as high as 2000 in mytop consistently over 3 or 4 seconds, and more than once while the script runs, but I don't know why I'm getting these huge bursts of speed intermittently. This isn't a huge problem, I'm just puzzled that I can get such high numbers when my average seems 10 or 20 times less. Is this normal? Hmm. Yeah I'd expect to see something a bit more even than that. Is there any way to optimize large numbers of UPDATES with MySQL, like you can INSERTS? I don't think so. A bulk-update syntax would be interesting... Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 3.23.51: up 31 days, processed 1,043,772,320 queries (381/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Optimizing Ext3 for MySQL
I use ext3 and have a qps of anywhere from 2800-8000 and use the defaults with no problems. Have you tried: iostat -k 1 to look at your disk access? What kind of disks are they anyhow? IDE or SCSI? RAIDed? In what fashion? Lastly, you said that this is a script that is running, right? The table that gets updated, is it fixed or dynamic? Using blobs? Might also look at: vmstat 1 and look at CPU usage... sql,query,queries -steve- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Optimizing Ext3 for MySQL
Hi Steven, Shame on me, but I have no idea what the output of iostat tells me -- though I know I should. Can you point me to a good reference to flesh out the man page? I have 2 18GB Seagate drives -- from dmesg: Vendor: SEAGATE Model: ST318406LC (scsi0:A:0): 160.000MB/s transfers (80.000MHz DT, offset 63, 16bit) (scsi0:A:1): 160.000MB/s transfers (80.000MHz DT, offset 63, 16bit) No raid. I have the system on disk1 and MySQL data directory on disk2. I'm not running the update or binary logs. The table is fixed with 12 fields -- 2 chars, 8 int/smallint/tinyint, 1 date, 1 timestamp. Here's the info from show table status: | list | MyISAM | Fixed | 3786043 |137 | 518687891 |588410519551 |161528832 | 0 |3787110 | 2002-12-20 13:32:41 | 2003-01-15 19:57:16 | 2003-01-14 13:39:39 | | | Thanks, --jeff - Original Message - From: Steven Roussey [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: 'Jeff Kilbride' [EMAIL PROTECTED] Sent: Wednesday, January 15, 2003 4:04 PM Subject: Re: Optimizing Ext3 for MySQL I use ext3 and have a qps of anywhere from 2800-8000 and use the defaults with no problems. Have you tried: iostat -k 1 to look at your disk access? What kind of disks are they anyhow? IDE or SCSI? RAIDed? In what fashion? Lastly, you said that this is a script that is running, right? The table that gets updated, is it fixed or dynamic? Using blobs? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Optimizing Ext3 for MySQL
Are there any general guidelines for optimizing ext3 for MySQL? I have a perl script that runs 200K + updates into my database once a day and I see pretty wildly fluctuating query/sec numbers using Jeremy Z's mytop program. I've seen in excess of 2000 qps and then seen that number drop to 40 qps. The average seems to be about 200 qps, which seems kinda slow given my hardware: Dual P3 1.3MHz 1GB RAM Dual SCSI drives (160 MB/s) RedHat 7.3 MySQL 3.23.53a w/MyISAM tables This particular table being updated has about 5 million rows. The fields being updated are not indexed. Also, the qps numbers seem to slow down every 5 seconds or so, which I think matches the default write timing for the ext3 journal. Can anybody share their experience with optimizing ext3 -- i.e. which mode is best (writeback, ordered, journal) and any parameters that can be tuned? Any insight is appreciated. Thanks, --jeff - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
