Re: Fw: Tuning MySQL for Large Database
matt ryan wrote: >There is a perl script that comes with MySQL called mysqldumpslow. You can just run it on your slow log and it will output summary statistics about the slow log. I saw that in the docs, but I definitly dont want to install perl on a production server, I never looked to see if I could do it offline, I only have ms boxes, no linux here, none of our techs know linux well enough to move to it. Matt copy the slow log from the windows box to a linux box and run the script there. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fw: Tuning MySQL for Large Database
>There is a perl script that comes with MySQL called mysqldumpslow. You can just run it on your slow log and it will output summary statistics about the slow log. I saw that in the docs, but I definitly dont want to install perl on a production server, I never looked to see if I could do it offline, I only have ms boxes, no linux here, none of our techs know linux well enough to move to it. Matt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fw: Tuning MySQL for Large Database
Hi, On Thursday, July 22, 2004, at 01:42 PM, matt ryan wrote: I've found the slow query log is useless to me, it's 50 meg right now. Is there a tool that will identify common querys? I could probably come up with some sql's if I load it into a table, but it would take quite a while to sort out. There is a perl script that comes with MySQL called mysqldumpslow. You can just run it on your slow log and it will output summary statistics about the slow log. Another possible solution, is to delete the old one and just use it for a few hours to see what is going on right now. Regards, Harrison -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fw: Tuning MySQL for Large Database
I've found the slow query log is useless to me, it's 50 meg right now. Is there a tool that will identify common querys? I could probably come up with some sql's if I load it into a table, but it would take quite a while to sort out. I posted a request on the mysql bugtraq to move it to a table instead of that raw file, but they closed the request, guess they didnt like that idea -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fw: Tuning MySQL for Large Database
From: http://dev.mysql.com/doc/mysql/en/Server_status_variables.html Key_reads The number of physical reads of a key block from disk. If Key_reads is big, then your key_buffer_size value is probably too small. The cache miss rate can be calculated as Key_reads/Key_read_requests. Select_full_join The number of joins that do not use indexes. If this value is not 0, you should carefully check the indexes of your tables. This variable was added in MySQL 3.23.25. Select_full_range_join The number of joins that used a range search on a reference table. This variable was added in MySQL 3.23.25. Select_range The number of joins that used ranges on the first table. (It's normally not critical even if this is big.) This variable was added in MySQL 3.23.25. Select_range_check The number of joins without keys that check for key usage after each row. (If this is not 0, you should carefully check the indexes of your tables.) This variable was added in MySQL 3.23.25. Select_scan The number of joins that did a full scan of the first table. This variable was added in MySQL 3.23.25. Sort_scan The number of sorts that were done by scanning the table. This variable was added in MySQL 3.23.25. So... it's not as bad as it could be ;-) I would still check the slow query log to see if there are any common queries that could use an index or two. I would also look at changing some simple indexes into compound indexes (indices?) as another means of speeding things up. Yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine matt ryan <[EMAIL PROTECTED]> wrote on 07/22/2004 01:17:12 PM: > Hmm > > I'm guessing my stats arent too good, lots of full table scans, but this > is to be expected, my users can query any table by any column, and I > cant index all column combinations > > Variable_name > Value > > Select_full_join > 0 > > Select_full_range_join > 24 > > Select_range > 145321 > > Select_range_check > 0 > > Select_scan29402 > Sort_Scan 15360 > Key_reads 37811885 > > > and on the other big db.. > > Variable_name > Value > > Select_full_join > 535 > > Select_full_range_join > 0 > > Select_range > 1098 > > Select_range_check > 0 > > Select_scan10443 > Sort_Scan2464 > Key_reads 20282002 > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] >
Re: Fw: Tuning MySQL for Large Database
Resend, firefox did not send the way it looked when I typed it! I'm guessing my stats arent too good, lots of full table scans, but this is to be expected, my users can query any table by any column, and I cant index all column combinations Variable_name Value Select_full_join 0 Select_full_range_join 24 Select_range 145321 Select_range_check 0 Select_scan29402 Sort_Scan 15360 Key_reads 37811885 and on the other big db.. Variable_name Value Select_full_join 535 Select_full_range_join 0 Select_range 1098 Select_range_check 0 Select_scan10443 Sort_Scan 2464 Key_reads 20282002 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fw: Tuning MySQL for Large Database
Hmm I'm guessing my stats arent too good, lots of full table scans, but this is to be expected, my users can query any table by any column, and I cant index all column combinations Variable_name Value Select_full_join 0 Select_full_range_join 24 Select_range 145321 Select_range_check 0 Select_scan29402 Sort_Scan 15360 Key_reads 37811885 and on the other big db.. Variable_name Value Select_full_join 535 Select_full_range_join 0 Select_range 1098 Select_range_check 0 Select_scan10443 Sort_Scan2464 Key_reads 20282002 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fw: Tuning MySQL for Large Database
This is part of the results of a SHOW STATUS command. See one of the earlier posts for the full list of his settings. Read These For More: http://dev.mysql.com/doc/mysql/en/SHOW.html http://dev.mysql.com/doc/mysql/en/SHOW_STATUS.html http://dev.mysql.com/doc/mysql/en/Server_status_variables.html Yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine matt ryan <[EMAIL PROTECTED]> wrote on 07/22/2004 12:43:55 PM: > > >I went over your data. This is what I noticed first: > >| Select_full_join | 0| > >| Select_full_range_join | 0| > >| Select_range | 1| > >| Select_range_check | 0| > >| Select_scan | 301 | > > > > > > What command will provide this data? > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] >
Re: Fw: Tuning MySQL for Large Database
I went over your data. This is what I noticed first: | Select_full_join | 0| | Select_full_range_join | 0| | Select_range | 1| | Select_range_check | 0| | Select_scan | 301 | What command will provide this data? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Fw: Tuning MySQL for Large Database
I agree with Matthew. A database is a lot like a car. You can have a well-built, high-powered vehicle that could do 200mph while still carrying 15 people (good hardware + good tuning) but if you drive it off-road, alone, and in second gear (bad table structure, poor index coverage, poor SQL statement optimization, and poor application concurrency) all of that capacity and performance do you no good. I went over your data. This is what I noticed first: | Select_full_join | 0| | Select_full_range_join | 0| | Select_range | 1| | Select_range_check | 0| | Select_scan | 301 | Which implies me that when you needed to combine data between two tables (JOINs), you could not use an index 301 of 302 times. I might be reading it wrong so I hope the list will correct me if I am mistaken.. I also that you are at the point you need to read your slow query log and do a lot of EXPLAIN SELECTs to improve your index coverage. long_query_time = 5 | Slow_queries | 626 | | Com_select | 23934| I read this as: 626 or 23934 queries took more than 5 seconds to complete. At the bare minimum those queries consumed 626x5=3130 query-seconds ( or just over 52 query-minutes) to execute. That may be a big part of your bottleneck. Also, one more thing to worry about in a high-traffic situation. Connection bloat: | Max_used_connections | 78 | Make sure your code opens its connections AS LATE AS POSSIBLE and closes them AS EARLY AS POSSIBLE. Sometimes connections can be pooled, it depends on your library and/or the available developer's skills. Many designers when building a web-based application (PERL, PHP, ASP, etc) will forget to close the connection before the end of the script *on each page*. Sure, the database library may eventually close it for you (as your variables go out of scope and get garbage collected) but it is MUCH more efficient to ALWAYS close it yourself (or manually return it to the pool). In some scripting languages, the garbage collection utilities will go on "indefinite hold" during high-traffic conditions. This is generally not good for database performance as it means you will have orphaned objects (possibly still open database connections and result datasets) hanging around on the heap waiting to be recovered. Implementations vary but you can usually help your garbage collectors by manually de-referencing all object variables before you exit the script. (I know you have probably already read this already but...) Here is the page in the docs describing all of the system variables: http://dev.mysql.com/doc/mysql/en/Server_system_variables.html You may want to re-examine all of your "buffer" settings to make sure you are getting the most out of your available memory. Yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine Matthew McNicol <[EMAIL PROTECTED]> wrote on 07/22/2004 03:25:29 AM: > > My 5 cents... tuning the MySQL database config file is half a solution. > In my experience optimizing the application code and table/index > structure delivers performance results. > > > > Michael Sleman wrote: > > > Hello, > > > > We're running 1 web server (apache 2 & php) / 1 dedicated DB server > > (MySQL 4.0.20-standard) and are experiencing serious performance issues > > on the DB during some load testing. > > > > Hardware on both > > Dual Xeon 2.8GHz, 2GB RAM > > > > The database size is a little under 1 GB. > > > > Naturally, we started taking a look at MySQL config and have gotten some > > significant speed improvements but the app is really slow and queries > > take 10secs on average to execute. From my past experiences with MySQL, > > I still think the DB can do much better. > > > > Any recommendations for additional configuration changes? > > > > The stats during a 15 minute load test was up the roof at: > > Load Average: 12 avg, 26 peak, > > MySQL processes: 20 avg, 80 peak > > Memory usage was 280MB used, 1.4GB cached, 122MB buffers, 216MB FREE. > > > > I'm attaching my.cnf, show status, show variables from a 15 minute load > > test. > > > > Thanks, > > -Michael > > --8<--- clipped for space 8<---
