Previous Thread
Next Thread
Print Thread
Rating: 5
#253430 07/11/2003 4:05 PM
Joined: Feb 2002
Posts: 1,759
Addict
Addict
Offline
Joined: Feb 2002
Posts: 1,759
Granted, if you are on a shared hosting solution, you won't have much control over these changes, but most hosts will tune their MySQL as much as possible.

But if you are hosting your forum on a dedicated server and have full control over your configuration, this may help. A lot of times, MySQL is installed without any detailed configuration file. While this works, it may not be optimal for your use.

So, we'll start there. The config file for MySQL is called "my.cnf". On linux servers, it is typically located in the /etc directory. If you are unsure of where yours is located, at a command prompt type "locate my.cnf". If you are on windows, you can edit the file named my.ini in the C:\WINNT or C:\Windows directory.

If you open that file and see it is almost empty, then you have a lot of different options. Typically, the MySQL installation directory will have some example my.cnf files for you to use.

my-small.cnf � For systems with less than 64MB of RAM, where MySQL is used occasionally.

my-medium.cnf � For systems with less than 64MB of RAM, where MySQL is the primary activity on the system, or for systems with up to 128MB of RAM, where MySQL shares the box with other processes. This is the most common configuration, where MySQL is installed on the same box as a Web server and receives a moderate amount of traffic.

my-large.cnf � For a system with 128MB to 512MB of RAM, where MySQL is the primary activity.

my-huge.cnf � For a system with 1GB to 2GB of RAM, where MySQL is the primary activity

These are excellent places to start. You can copy the contents of the file you choose to use into your existing my.cnf file, or just move and rename the appropriate file.

A word of warning, if your server hosts other websites, and is running a lot of httpd processes, you will probably not want to run the huge.cnf file, as almost all of your resources will be used by MySQL, possibly not leaving enough for HTTP.

So, you have filled your my.cnf file with goodies.. its time to put it into action. You will need to restart MySQL for these changes to take effect. That was the easy part, you may or may not notice much performance difference yet. So, to dig a little deeper, we need to analyze what mysql is doing, and how well it is performing.

For you command line junkies, you can find out some information by typing this command: mysqladmin extended-status That will display a lot of important information.

But, to make it easier on you I have attached a script that you can run on your server that will provide current server loads, current running processes, netstat information, the extended-status information from mysql, and some important calculations regarding mysql queries and cache.

So, when you run the script, what do you see? A lot of information that probably gives you a headache That's ok, we don't need to understand ALL of it to get the most out of MySQL.

There are two primary start-up parameters that will affect your system the most: key_buffer_size and table_cache. If you get only two server parameters correctly tuned, make sure they're these two!

The value of key_buffer_size is the size of the buffer used with indexes. The larger the buffer, the faster the SQL command will finish and a result will be returned. Try to find the fine line between finely tuned and over-optimized; you may have a key_buffer_size of 256MB on a system with 512MB of RAM, but any more than 256MB could cause degraded server performance.

A simple way to check the actual performance of the buffer is to examine four additional variables: key_read_requests, key_reads, key_write_requests, and key_writes.

Find the rows that look something like this (your values will differ):

Code
| Key_read_requests    | 602843 |
| Key_reads | 151 |
| Key_write_requests | 1773 |
| Key_writes | 805 |

If you divide the value of key_read by the value of key_reads_requests, the result should be less than 0.01. Also, if you divide the value of key_write by the value of key_writes_requests, the result should be less than 1. Using the values above, we have results of 0.000250479809834401 and 0.454032712915962 respectively, well within the acceptable parameters. To try to get these numbers even smaller, more tuning could occur by increasing the value of key_buffer_size, but these numbers would be fine to leave as they are.

The other important server parameter is table_cache, which is the number of open tables for all threads. The default is 64, but you may need to adjust this number. Look for a variable called open_tables in the output. If this number is large, the value of table_cache should be increased.

Each time MySQL accesses a table, it places it in the cache. If your system accesses many tables, it is faster to have these in the cache. A good way to see whether your system needs to increase this is to examine the value of open_tables at peak times (one of the extended status values, above). If you find it stays at the same value as your table_cache value, and then the number of opened_tables starts increasing, you should increase the table_cache if you have enough memory.

The sample configuration files use various combinations of key_buffer_size and table_cache, which you can use as a baseline for any modifications you need to make. Whenever you modify your configuration, you'll be restarting your server in order for changes to take effect, sometimes with no knowledge of the consequences of your changes. In this case, be sure to try your modifications in a development environment before rolling the changes into production.

Let's use some scenarios to further illustrate:

Scenario 1
table_cache - 512
open_tables - 98
opened_tables - 1513
uptime - 3046085

Here it looks like the table cache has been set too high. The server has been up for ages, the number of opened_tables is relatively low, and the open_tables (remember we're checking at a peak time) is nowhere near what it could be.

Scenario 2
table_cache - 64
open_tables - 64
opened_tables - 517
uptime - 1662790 (measure in seconds)

Here, although the open_tables is maxed out, the number of open_tables is very low even though the server has been up for ages. There is probably not much benefit in upping the table_cache (this example comes from a development server).


Scenario 3
table_cache - 64
open_tables - 64
opened_tables - 13918
uptime - 33754
This table_cache is set too low. The open_tables is running at maximum, and the number of open_tables is high, even though the uptime is less than a day. If you have the memory, up the table_cache. One thing to note is that even if you only have 64 tables in your database, you may still have more open tables. MySQL, being multi-threaded, may be running many queries on the table at one time, and each of these will open a table.

Scenario 4
key_buffer_size - 402649088 (384M)
Key_read_requests - 609601541
Key_reads - 67299


Scenario 5
key_buffer_size - 16777216 (16M)
Key_read_requests - 609601541
Key_reads - 46729832
The values in scenario 1 are looking healthy. The ratio of key_reads to key_read_requests should be as low as possible, no more than 1:100. In scenario 1 it is close to 1:10000. In scenario 2, it is shocking, about 1:15, and the key_buffer size should be increased to as much as the memory allows (you can see that RAM is the primary hardware upgrade you can do to improve your system).

Lastly, if you are using MySQL 4.x, a few new variables have been introduced that will help your performance tremendously:

query_cache_limit = 1M
query_cache_size = 32M
query_cache_type = 1

You may want to drop query_cache_size to 16M if you have only 1GB of ram, and maybe even lower if you have less ram.

Again, these are just guidelines and rules of thumb to help you better get the most performance. Each machine is slightly different, and different values will apply. So you may need to really sit down and experiment to find the fine line between optimized and overkill.
Attachments
88576-stats.txt (0 Bytes, 932 downloads)

Sponsored Links
Entire Thread
Subject Posted By Posted
Let's tune MySQL! msula 07/11/2003 11:05 PM
Re: Let's tune MySQL! cdavis 09/25/2007 8:35 PM
Re: Let's tune MySQL! Gizmo 09/26/2007 1:20 AM
Re: Let's tune MySQL! msula 07/11/2003 11:08 PM
Re: Let's tune MySQL! dimopoulos 07/12/2003 12:41 AM
Re: Let's tune MySQL! Dave_L_dup1 07/12/2003 12:49 AM
Re: Let's tune MySQL! navaho 07/12/2003 2:19 AM
Re: Let's tune MySQL! JustDave 07/12/2003 3:29 AM
Re: Let's tune MySQL! msula 07/12/2003 4:38 AM
Re: Let's tune MySQL! BWilliams_dup1 07/12/2003 4:51 AM
Re: Let's tune MySQL! msula 07/12/2003 5:00 AM
Re: Let's tune MySQL! JoshPet 07/12/2003 5:47 AM
Re: Let's tune MySQL! omegatron 07/12/2003 3:54 PM
Re: Let's tune MySQL! msula 07/12/2003 4:24 PM
Re: Let's tune MySQL! omegatron 07/12/2003 4:28 PM
Re: Let's tune MySQL! msula 07/12/2003 4:47 PM
Re: Let's tune MySQL! omegatron 07/12/2003 5:00 PM
Re: Let's tune MySQL! AllenAyres 07/13/2003 9:35 PM
Re: Let's tune MySQL! dimopoulos 07/14/2003 1:34 AM
Re: Let's tune MySQL! AllenAyres 07/14/2003 1:40 AM
Re: Let's tune MySQL! msula 07/14/2003 1:47 AM
Re: Let's tune MySQL! dimopoulos 07/14/2003 1:48 AM
Re: Let's tune MySQL! msula 07/14/2003 1:56 AM
Re: Let's tune MySQL! AllenAyres 07/14/2003 4:40 AM
Re: Let's tune MySQL! msula 07/14/2003 4:49 AM
Re: Let's tune MySQL! AllenAyres 07/14/2003 6:05 AM
Re: Let's tune MySQL! Ian_W 07/14/2003 9:20 AM
Re: Let's tune MySQL! msula 07/14/2003 2:53 PM
Re: Let's tune MySQL! ScottCargill 07/15/2003 2:30 AM
Re: Let's tune MySQL! navaho 07/15/2003 8:06 PM
Re: Let's tune MySQL! Ian_W 08/13/2003 12:48 AM
Re: Let's tune MySQL! navaho 08/13/2003 3:02 AM
Re: Let's tune MySQL! Ian_W 08/13/2003 10:23 AM
Re: Let's tune MySQL! omegatron 08/13/2003 12:06 PM
Re: Let's tune MySQL! Astaran 08/13/2003 12:15 PM
Re: Let's tune MySQL! Ian_W 08/13/2003 6:50 PM
Re: Let's tune MySQL! navaho 08/13/2003 9:13 PM
Re: Let's tune MySQL! omegatron 08/13/2003 9:44 PM
Re: Let's tune MySQL! navaho 08/13/2003 11:37 PM
Re: Let's tune MySQL! santana 01/29/2004 8:23 AM
Re: Let's tune MySQL! Astaran 01/29/2004 11:24 AM
Re: What Need to be Tuned? Tony 02/20/2004 1:57 AM
Re: What Need to be Tuned? Tony 02/20/2004 1:59 AM
Re: What Need to be Tuned? Tony 02/20/2004 12:43 PM
Re: What Need to be Tuned? slayer60 02/20/2004 7:34 PM
Re: What Need to be Tuned? msula 02/21/2004 3:00 AM
Re: What Need to be Tuned? msula 02/21/2004 3:02 AM
Re: What Need to be Tuned? Tony 02/21/2004 10:22 AM
Re: What Need to be Tuned? msula 02/22/2004 9:56 PM
Managing MySQL AllenAyres 10/21/2004 6:19 AM
Re: Let's tune MySQL! Conrad 12/23/2004 3:41 PM
Re: Let's tune MySQL! Conrad 01/11/2005 5:17 PM
Re: Let's tune MySQL! Astaran 01/11/2005 6:15 PM
Re: Let's tune MySQL! Conrad 01/11/2005 6:50 PM
Re: Let's tune MySQL! little_joe 01/12/2005 12:57 AM
Re: Let's tune MySQL! Conrad 01/12/2005 1:30 AM
Re: Let's tune MySQL! little_joe 01/12/2005 4:49 AM
Re: Let's tune MySQL! SKB 05/15/2005 2:16 AM
Re: Let's tune MySQL! SKB 05/15/2005 10:47 PM

Link Copied to Clipboard
Donate Today!
Donate via PayPal

Donate to UBBDev today to help aid in Operational, Server and Script Maintenance, and Development costs.

Please also see our parent organization VNC Web Services if you're in the need of a new UBB.threads Install or Upgrade, Site/Server Migrations, or Security and Coding Services.
Recommended Hosts
We have personally worked with and recommend the following Web Hosts:
Stable Host
bluehost
InterServer
Visit us on Facebook
Member Spotlight
isaac
isaac
California
Posts: 1,157
Joined: July 2001
Forum Statistics
Forums63
Topics37,573
Posts293,925
Members13,849
Most Online5,166
Sep 15th, 2019
Today's Statistics
Currently Online
Topics Created
Posts Made
Users Online
Birthdays
Top Posters
AllenAyres 21,079
JoshPet 10,369
LK 7,394
Lord Dexter 6,708
Gizmo 5,833
Greg Hard 4,625
Top Posters(30 Days)
Top Likes Received
isaac 82
Gizmo 20
Brett 7
WebGuy 2
Top Likes Received (30 Days)
None yet
The UBB.Developers Network (UBB.Dev/Threads.Dev) is ©2000-2024 VNC Web Services

 
Powered by UBB.threads™ PHP Forum Software 8.0.0
(Preview build 20221218)