Re: Total newb at performance tuning mysql
Scott Haneda wrote: Unless I am totally off base here, the mysql docs tell very little in regards to how to performance tune mysql. So far I have found this article to help: http://www.databasejournal.com/features/mysql/article.php/10897_1402311_3 But it still leaves me with a ton of questions. For starters, and don't laugh, I just installed mysql and let it run, started adding sites to it and such. The machine has 1 gig of ram in in, but at times, I could ask it were a little snappier. It is a 1.2Ghz machine. First thing I did last night was to try to get a hnalde on this. From what I can tell, I have no my.cnf file in place, so there must be some default settings that are compiled in. I set up a cron job to run the following: show variables like 'table_cache' show status like 'open%_tables' I have been running this once a minute for a day now, how long should I run it to get an idea of what I need to change to better suit my servers load usage? First entry after a mysql restart: Wed Sep 7 03:18:00 PDT 2005 table_cache 64 Open_tables 64 Opened_tables 1050 --- Last entry as of now: --- Wed Sep 7 16:27:01 PDT 2005 table_cache 64 Open_tables 64 Opened_tables 4407 So it looks like Opened_tables is going to increase forever at the rate I have it, which I am guessing is not a good thing, but not sure what I need to do to fix this. I am really looking for some pretty detailed docs on exactly what I can do in my.cnf to make this work out better. Thanks again, and if there are any questions that would help me get a better answer, please let me know. Try to find a book called High Performance MySQL from Derek J. Balling Jeremy Zawodny (the guy from Yahoo). It's an interesting and useful book which skips all the basic stuff and gets you into performance tuning. ISBN : 0-596-00306-4 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Total newb at performance tuning mysql
One of our engineers first installed MySQL on one of our Sun boxes which was doing nothing more than MySQL... It seems we also put it on the server and turned it on... it behaved very badly. Essentially when we started to investigate MySQL and find out if we could use it we discovered that our Sun box with four processors and 4Gbytes of ram was running MySQL in 64M of memory... it's an easy mistake to make, and the lesson here is that out of the box (as it were) MySQL settings are a little on the low side for performance... but work well for a shared environment where you may have web server, mail server and more all running on the same box. If you want MySQL to sing... you are going to have to do a lot of tuning. On the table_cache issue... We have about 40 tables per database, and some of our servers have 30 databases. Our servers have as many as 500 connections... one server at random which has an uptime of 60 days shows: mysql1 (none): show status like 'open%_tables'; +---+---+ | Variable_name | Value | +---+---+ | Open_tables | 2748 | | Opened_tables | 3288 | +---+---+ 2 rows in set (0.01 sec) mysql1 (none): show variables like 'table_cache'; +---+---+ | Variable_name | Value | +---+---+ | table_cache | 4096 | +---+---+ 1 row in set (0.01 sec) So we have a table cache value, but it's clearly on the high side and could be lower. Yours at 64 is on the low side. While I won't ignore table cache as being important, there are many many performance tuning things that need to get done to have an impact on the server. Firstly (knowing the type of things you are doing) InnoDB will likely be a far better choice for most of your tables than the default database engine, myisam. You need to tune the machine to run InnoDB, and then convert your data to InnoDB. If you are using MySQL 4.1 (which I can't remember) I would advise using innodb_file_per_table. If you are looking at upgrading to 4.1 I'd do that first before switching to innodb_file_per_table... it's a little hard to claim back the shared table space after the fact. If not 4.1 then go with InnoDB and build a big enough shared table space file set to hold all your data with room to spare. We typically build it with 20 2Gbyte files... for 40Gbytes of InnoDB table space. Decide how much memory you have to run MySQL... i the server does only MySQL, this is easy... if it's also a web server running Apache and so on, then you have to decide the mix. Assuming MySQL only give InnoDB 80% of the server's total memory, up to certain limits with 32 bit operating systems and the like... For OS X we found these are pretty much the magic numbers for max values if you have more than 2Gbytes of ram but can't handle 64 bit: innodb_buffer_pool_size=1850M innodb_additional_mem_pool_size=256M innodb_log_files_in_group=2 innodb_log_file_size=250M innodb_log_buffer_size=20M innodb_thread_concurrency=16 innodb_flush_log_at_trx_commit=0 innodb_lock_wait_timeout=30 Once you convert everything to InnoDB the regular MySQL buffers have less importance, but should still have some values. InnoDB or not the query_cache is a good thing, but don't set it too high... We are at 128Mbytes and that's a little higher than we need... it appears we could live in under 64Mbytes, and our query cache handles about 25% of our queries... properly set it's a good thing. So... most likely switching to InnoDB will improve performance... Managing all your memory settings and caches so that the ones that matter have values that will help will make a great deal of difference... of course to do this you'll need to make a my.cnf file and install it where MySQL will look for it. Also important for tuning is watching the slow queries, finding out if there are moe things you can do with indexes, or if there are other ways to optimize the queries. Turn on the slow query log... leave it set to the default 10 seconds... find out what queries are running longer than 10 seconds and figure out how to optimize them... changing indexes, changing the query etc... Once you have worked that out and your slow query log gets few hits, reduce it to 5 seconds and work through those queries... again reduce it further as you work out the performance issues and you'll find that you are streaming along. There's a lot more that can be done with specific memory settings and so on... but I think I've given you a handful of things to get started on, and you can come back for more when you have made some headway on this part. Best Regards, Bruce On Sep 7, 2005, at 4:28 PM, Scott Haneda wrote: Unless I am totally off base here, the mysql docs tell very little in regards to how to performance tune mysql. So far I have found this article to help: http://www.databasejournal.com/features/mysql/article.php/
Re: Total newb at performance tuning mysql
Hello. I can tell, I have no my.cnf file in place, so there must be some default Create your own and the appropriate value for the table_cache. See: http://dev.mysql.com/doc/mysql/en/option-files.html http://dev.mysql.com/doc/mysql/en/program-variables.html Scott Haneda [EMAIL PROTECTED] wrote: Unless I am totally off base here, the mysql docs tell very little in regards to how to performance tune mysql. So far I have found this article to help: http://www.databasejournal.com/features/mysql/article.php/10897_1402311_3 But it still leaves me with a ton of questions. For starters, and don't laugh, I just installed mysql and let it run, started adding sites to it and such. The machine has 1 gig of ram in in, but at times, I could ask it were a little snappier. It is a 1.2Ghz machine. First thing I did last night was to try to get a hnalde on this. From what I can tell, I have no my.cnf file in place, so there must be some default settings that are compiled in. I set up a cron job to run the following: show variables like 'table_cache' show status like 'open%_tables' I have been running this once a minute for a day now, how long should I run it to get an idea of what I need to change to better suit my servers load usage? First entry after a mysql restart: Wed Sep 7 03:18:00 PDT 2005 table_cache 64 Open_tables 64 Opened_tables 1050 --- Last entry as of now: --- Wed Sep 7 16:27:01 PDT 2005 table_cache 64 Open_tables 64 Opened_tables 4407 So it looks like Opened_tables is going to increase forever at the rate I have it, which I am guessing is not a good thing, but not sure what I need to do to fix this. I am really looking for some pretty detailed docs on exactly what I can do in my.cnf to make this work out better. Thanks again, and if there are any questions that would help me get a better answer, please let me know. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Total newb at performance tuning mysql
Instead of your cron job, I suggest http://codenode.com/mysqlreport The mysqlreport documentation (http://codenode.com/mysqlreportdoc) is a pretty baseline introduction to comprehending server performance via a mysqlreport report, and a little bit about what you can do for certain issues. -Daniel Scott Haneda wrote: Unless I am totally off base here, the mysql docs tell very little in regards to how to performance tune mysql. So far I have found this article to help: http://www.databasejournal.com/features/mysql/article.php/10897_1402311_3 But it still leaves me with a ton of questions. For starters, and don't laugh, I just installed mysql and let it run, started adding sites to it and such. The machine has 1 gig of ram in in, but at times, I could ask it were a little snappier. It is a 1.2Ghz machine. First thing I did last night was to try to get a hnalde on this. From what I can tell, I have no my.cnf file in place, so there must be some default settings that are compiled in. I set up a cron job to run the following: show variables like 'table_cache' show status like 'open%_tables' I have been running this once a minute for a day now, how long should I run it to get an idea of what I need to change to better suit my servers load usage? First entry after a mysql restart: Wed Sep 7 03:18:00 PDT 2005 table_cache 64 Open_tables 64 Opened_tables 1050 --- Last entry as of now: --- Wed Sep 7 16:27:01 PDT 2005 table_cache 64 Open_tables 64 Opened_tables 4407 So it looks like Opened_tables is going to increase forever at the rate I have it, which I am guessing is not a good thing, but not sure what I need to do to fix this. I am really looking for some pretty detailed docs on exactly what I can do in my.cnf to make this work out better. Thanks again, and if there are any questions that would help me get a better answer, please let me know. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Total newb at performance tuning mysql
Unless I am totally off base here, the mysql docs tell very little in regards to how to performance tune mysql. So far I have found this article to help: http://www.databasejournal.com/features/mysql/article.php/10897_1402311_3 But it still leaves me with a ton of questions. For starters, and don't laugh, I just installed mysql and let it run, started adding sites to it and such. The machine has 1 gig of ram in in, but at times, I could ask it were a little snappier. It is a 1.2Ghz machine. First thing I did last night was to try to get a hnalde on this. From what I can tell, I have no my.cnf file in place, so there must be some default settings that are compiled in. I set up a cron job to run the following: show variables like 'table_cache' show status like 'open%_tables' I have been running this once a minute for a day now, how long should I run it to get an idea of what I need to change to better suit my servers load usage? First entry after a mysql restart: Wed Sep 7 03:18:00 PDT 2005 table_cache 64 Open_tables 64 Opened_tables 1050 --- Last entry as of now: --- Wed Sep 7 16:27:01 PDT 2005 table_cache 64 Open_tables 64 Opened_tables 4407 So it looks like Opened_tables is going to increase forever at the rate I have it, which I am guessing is not a good thing, but not sure what I need to do to fix this. I am really looking for some pretty detailed docs on exactly what I can do in my.cnf to make this work out better. Thanks again, and if there are any questions that would help me get a better answer, please let me know. -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
