Previous Thread
Next Thread
Print Thread
Rating: 5
Page 1 of 2 1 2
#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
palmen #253431 07/11/2003 4:08 PM
Joined: Feb 2002
Posts: 1,759
Addict
Addict
Offline
Joined: Feb 2002
Posts: 1,759
Notice, you'll have to edit your login information in the attached script for it to work

palmen #253432 07/11/2003 5:41 PM
Joined: Aug 2002
Posts: 1,191
Kahuna
Kahuna
Joined: Aug 2002
Posts: 1,191
Jer you are a superstar!! Thanks a bunch!


Nikos
palmen #253433 07/11/2003 5:49 PM
Joined: Apr 2002
Posts: 1,768
Addict
Addict
Offline
Joined: Apr 2002
Posts: 1,768

Joined: Oct 2000
Posts: 2,223
Veteran
Veteran
Offline
Joined: Oct 2000
Posts: 2,223
I just made this post sticky so people can book mark it.

Good job, Jeremy.


Picture perfect penmanship here.
Sponsored Links
palmen #253435 07/11/2003 8:29 PM
Joined: Jun 2001
Posts: 3,273
That 70's Guy
That 70's Guy
Offline
Joined: Jun 2001
Posts: 3,273
Very nice job!

Joined: Feb 2002
Posts: 1,759
Addict
Addict
Offline
Joined: Feb 2002
Posts: 1,759
I can't take all the credit, I pieced together a lot of information I've been saving from across the internet

palmen #253437 07/11/2003 9:51 PM
Joined: Mar 2003
Posts: 159
Member
Member
Joined: Mar 2003
Posts: 159
I just ran the script and to be honest I have no idea what I am doing, but I am getting a bit concerned with the speed of the forum and wanted to see if this shows anything that would point the finger to the hosting company.

Here is the info I got:

Fri Jul 11 19:46:19 PDT 2003 7:46pm up 11 days, 4:10, 1 user, load average: 1.83, 3.65, 6.84 319 processes: 303 sleeping, 11 running, 5 zombie, 0 stopped Mem: 2582300K av, 2563864K used, 18436K free, 0K shrd, 185480K buff Swap: 4194192K av, 77868K used, 4116324K free 1885028K cached PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME COMMAND 14707 nobody 16 0 1220 1220 828 R 14.9 0.0 0:00 top Http processes currently running = 95 Mysql processes currently running = 5 Netstat information summary 1 CLOSING 1 LAST_ACK 2 FIN_WAIT2 6 SYN_RECV 15 FIN_WAIT1 22 LISTEN 183 ESTABLISHED 1078 TIME_WAIT


Can someone decifer the above info to me?

P.S.- Jeremy, I have been playing on your site and man is it fast compared to mine. The truck club is growning a lot faster than expected, so it looks like I may have to get away from the $9.95 / month hosting and get you to help me out in transfering it

vajraman #253438 07/11/2003 10:00 PM
Joined: Feb 2002
Posts: 1,759
Addict
Addict
Offline
Joined: Feb 2002
Posts: 1,759
Well, the first part of your results, and somewhat important is the load average. 1.83 is the 1 minute average, 3.65 is the 5 min, and 6.84 is a 15 minute average. This is showing average to slightly high loads (hard to really say for sure without knowing server hardware)

So.. the first clue is that the server may be slightly overworked. The second clue, is you see 300+ processes running. This is typical for a busy server.. not terribly alarming.

Next on the list is memory. as you can see, only 18436K is free, This isn't a lot. When you run low on memory, things need to be stored on the hard drive... which is the swap. Reading and writing data to a disk is much slower than to ram.

So, that is the jist of what that part you posted is. Again, nothing terribly alarming or really sticks out, it just seems to be a kinda busy server, which may be part of your slowdown. The one load average of almost 7 is kinda high, so if the server frequently spikes like that, that may be part of the problem. Or, it could be just the host not having MySQL optimized as well as they should, or a number of things

palmen #253439 07/11/2003 10:47 PM
Joined: Nov 2001
Posts: 10,369
I type Like navaho
I type Like navaho
Joined: Nov 2001
Posts: 10,369
Jeremy -
This is awesome!

Great work! Thanks for posting it.

Sponsored Links
palmen #253440 07/12/2003 8:54 AM
Joined: Apr 2001
Posts: 3,266
Member
Member
Offline
Joined: Apr 2001
Posts: 3,266
well I edited and entered MYSQL user and password and this is all I get when I run the script.

Sat Jul 12 09:53:01 EDT 2003 Http processes currently running = 5 Mysql processes currently running = 6 Netstat information summary

I do not see where to set a path maybe mysql is dieing from improper path. I know the login is correct as I can telnet in no problem and run mysqladmin extended-status however I have to set the path first


Joined: Feb 2002
Posts: 1,759
Addict
Addict
Offline
Joined: Feb 2002
Posts: 1,759
Code
 <br />/*** <br />  Configure statistic gathering programs; specify path if necessary <br />***/ <br /> <br />$netstat = 'netstat -a -n|grep -E "^(tcp)"| cut -c 68-|sort|uniq -c|sort -n'; <br />$http = "ps -auxww | grep -c http"; <br />$mysql = "ps -auxww | grep -c mysql"; <br />$top = "top"; <br />$uptime = "uptime"; <br />$free = "free"; <br />$mysqladmin = "/set/path/here/mysqladmin"; <br />


I think this would do what you need

palmen #253442 07/12/2003 9:28 AM
Joined: Apr 2001
Posts: 3,266
Member
Member
Offline
Joined: Apr 2001
Posts: 3,266
Yeah I did that and the results are the same. If we look at my output where is it dieing at TOP??

Joined: Feb 2002
Posts: 1,759
Addict
Addict
Offline
Joined: Feb 2002
Posts: 1,759
You said all you get is this:

[]Sat Jul 12 09:53:01 EDT 2003 Http processes currently running = 5 Mysql processes currently running = 6 Netstat information summary [/]

So, like you mentioned, it looks like it is running netstat, http, and mysql fine.. but nothing after Top.

Can you just type top at the command line without a path?

EDIT: Actually, netstat isn't running either, since that would be listing some information as well. So it looks like just the ps -auxww commands are running, you may need paths for everything.

Last edited by Jeremy; 07/12/2003 9:49 AM.
palmen #253444 07/12/2003 10:00 AM
Joined: Apr 2001
Posts: 3,266
Member
Member
Offline
Joined: Apr 2001
Posts: 3,266
top will not run at the path

so what are the normal paths or folders?

palmen #253445 07/13/2003 2:35 PM
Joined: Mar 2000
Posts: 21,079
Likes: 3
I type Like navaho
I type Like navaho
Joined: Mar 2000
Posts: 21,079
Likes: 3
for windows servers, isn't the file called my.ini?


- Allen wavey
- What Drives You?
Joined: Aug 2002
Posts: 1,191
Kahuna
Kahuna
Joined: Aug 2002
Posts: 1,191
Yes and it usually gets created by the WinMySQLAdmin under your windows folder C:\WINNT or C:\WINDOWS


Nikos
Joined: Mar 2000
Posts: 21,079
Likes: 3
I type Like navaho
I type Like navaho
Joined: Mar 2000
Posts: 21,079
Likes: 3
danke I thought so, but spent a while searching for my.cnf anyways


- Allen wavey
- What Drives You?
Joined: Feb 2002
Posts: 1,759
Addict
Addict
Offline
Joined: Feb 2002
Posts: 1,759
oops, sorry about that mixup. I get so confused in the windows world

palmen #253449 07/13/2003 6:48 PM
Joined: Aug 2002
Posts: 1,191
Kahuna
Kahuna
Joined: Aug 2002
Posts: 1,191
LOL I had the same problem searching for the cnf files


Nikos
Joined: Feb 2002
Posts: 1,759
Addict
Addict
Offline
Joined: Feb 2002
Posts: 1,759
max edit time is up on my original post, so if someone could edit it so it specifies my.ini for windows, that would be good

palmen #253451 07/13/2003 9:40 PM
Joined: Mar 2000
Posts: 21,079
Likes: 3
I type Like navaho
I type Like navaho
Joined: Mar 2000
Posts: 21,079
Likes: 3
got it for you As moderator for this forum, you should be able to indefinitely edit your posts... let's see if we can fix it


- Allen wavey
- What Drives You?
Joined: Feb 2002
Posts: 1,759
Addict
Addict
Offline
Joined: Feb 2002
Posts: 1,759
But I'm not a moderator of the MySQL forum

palmen #253453 07/13/2003 11:05 PM
Joined: Mar 2000
Posts: 21,079
Likes: 3
I type Like navaho
I type Like navaho
Joined: Mar 2000
Posts: 21,079
Likes: 3
? really? hmmm... gotta fix that


- Allen wavey
- What Drives You?
palmen #253454 07/14/2003 2:20 AM
Joined: Feb 2002
Posts: 2,286
Veteran
Veteran
Joined: Feb 2002
Posts: 2,286
[]Jeremy said:
But I'm not a moderator of the MySQL forum [/]

You are now and thanks for the post.


Fans Focus - Focusing on Fans of Sport

(Okay - mainly football (the British variety wink at the moment - but expanding all the time....)
Gorlum #253455 07/14/2003 7:53 AM
Joined: Feb 2002
Posts: 1,759
Addict
Addict
Offline
Joined: Feb 2002
Posts: 1,759
Thanks I can't hack threads code for crap, but at least I can be helpful in other areas

palmen #253456 07/14/2003 7:30 PM
Joined: Feb 2001
Posts: 104
Journeyman
Journeyman
Offline
Joined: Feb 2001
Posts: 104
Dunno what else you're good for, but this thread was VERY helpful to me...

Thanks!

[]https://www.ubbdev.com/threads/php/uploads/panels/6747-13980.png[/]

Joined: Oct 2000
Posts: 2,223
Veteran
Veteran
Offline
Joined: Oct 2000
Posts: 2,223
Chcuk, try /bin/top /bin/ps

or if they don't work try

bash2.04$ whereis top
bash2.04$ whereis ps
bash2.04$ whereis netstat

It looks to me as though your environment isn't setup well, no paths. We should probably make another thread soemwhere on customizing your environment.


Picture perfect penmanship here.
zerind #253458 08/12/2003 5:48 PM
Joined: Feb 2002
Posts: 2,286
Veteran
Veteran
Joined: Feb 2002
Posts: 2,286
I notice from the large version it says in mine...

# Try number of CPU's*2 for thread_concurrency
set-variable = thread_concurrency=8

What would be the additional setting to benefit from dual CPU's?



Fans Focus - Focusing on Fans of Sport

(Okay - mainly football (the British variety wink at the moment - but expanding all the time....)
Gorlum #253459 08/12/2003 8:02 PM
Joined: Oct 2000
Posts: 2,223
Veteran
Veteran
Offline
Joined: Oct 2000
Posts: 2,223
set-variable = threads_concurrency=4

dual CPU = 2 CPU, times 2 = 4.



Picture perfect penmanship here.
zerind #253460 08/13/2003 3:23 AM
Joined: Feb 2002
Posts: 2,286
Veteran
Veteran
Joined: Feb 2002
Posts: 2,286
Thanks.

Going off to add

set-variable = threads_concurrency=4
dual CPU = 2 CPU, times 2 = 4.

to my cnf fle


Fans Focus - Focusing on Fans of Sport

(Okay - mainly football (the British variety wink at the moment - but expanding all the time....)
Gorlum #253461 08/13/2003 5:06 AM
Joined: Apr 2001
Posts: 3,266
Member
Member
Offline
Joined: Apr 2001
Posts: 3,266
Actually Dave ps is the only one in the /bin directory the other two top and netstat are nowhere to be found. The command whereis is not found LOL

Gorlum #253462 08/13/2003 5:15 AM
Joined: Dec 2000
Posts: 1,471
Addict
Addict
Offline
Joined: Dec 2000
Posts: 1,471
[]Ian_W said:
Thanks.

Going off to add

set-variable = threads_concurrency=4
dual CPU = 2 CPU, times 2 = 4.

to my cnf fle
[/]

I think you'll have more luck if you only add
Code
set-variable = threads_concurrency=4

-Fusion- #253463 08/13/2003 11:50 AM
Joined: Feb 2002
Posts: 2,286
Veteran
Veteran
Joined: Feb 2002
Posts: 2,286
Doh!! Just re-read what he put LOL


Fans Focus - Focusing on Fans of Sport

(Okay - mainly football (the British variety wink at the moment - but expanding all the time....)
Gorlum #253464 08/13/2003 2:13 PM
Joined: Oct 2000
Posts: 2,223
Veteran
Veteran
Offline
Joined: Oct 2000
Posts: 2,223
chcuk when I run whereis whereis on one of the machines I am handy to I get

[root@machine:/root]# whereis whereis
whereis: /usr/bin/whereis /usr/share/man/man1/whereis.1.gz

perhaps /usr/bin is not in your path?

Look at your .bash_profile if you're running bash.


PATH=$PATH:$HOME/bin:/usr/local/bin

Add more to it as you feel necessary, colon delimited. log out, log in. magic. Now it will start finding things for you.


Picture perfect penmanship here.
zerind #253465 08/13/2003 2:44 PM
Joined: Apr 2001
Posts: 3,266
Member
Member
Offline
Joined: Apr 2001
Posts: 3,266
How do you find your bash profile?

Joined: Oct 2000
Posts: 2,223
Veteran
Veteran
Offline
Joined: Oct 2000
Posts: 2,223
When you are ssh'd in just type more .bash_profile to read it, to edit it use pico or vi.

the file will be in your home directory.


Picture perfect penmanship here.
palmen #253467 01/29/2004 1:23 AM
Joined: Sep 2000
Posts: 129
Member
Member
Offline
Joined: Sep 2000
Posts: 129
Jeremy

I think my server needs your trick.

Here is my result:
Code
<br />Thu Jan 29 13:16:11 2004 13:16:18 up 1:33, 2 users, load average: 26.99, 27.69, 31.70 184 processes: 183 sleeping, 1 running, 0 zombie, 0 stopped CPU0 states: 89.2% user 10.0% system 0.0% nice 0.0% iowait 0.0% idle CPU1 states: 84.1% user 7.0% system 0.0% nice 0.0% iowait 8.1% idle Mem: 1030284k av, 540420k used, 489864k free, 0k shrd, 36708k buff 478752k actv, 2664k in_d, 3780k in_c Swap: 1020116k av, 6532k used, 1013584k free 225288k cached Http processes currently running = 122 Mysql processes currently running = 3 Netstat information summary 2 SYN_RECV 3 CLOSING 5 LAST_ACK 10 FIN_WAIT1 10 LISTEN 15 CLOSE_WAIT 81 ESTABLISHED 392 TIME_WAIT <br />


I am using Xeon HT 2.4Ghz on Intel Server board with 1GB ECC RAM and SCSI.

On average there are 100 users login at the same time. Does persistant connection help?

often #253468 01/29/2004 4:24 AM
Joined: Dec 2000
Posts: 1,471
Addict
Addict
Offline
Joined: Dec 2000
Posts: 1,471
No, in most cases persistant connections are a bad idea.

Joined: Feb 2004
Posts: 4
Lurker
Lurker
Offline
Joined: Feb 2004
Posts: 4
I have a P4 2.4 with 1GB RAM dedicated server 100Mbps net connection

It runs WHM 8.8.0 cPanel 8.8.0-R73 RedHat 9 - WHM X v2.1.2 apache (1.3.29 (Unix)) mysql (4.0.15-standard)

This machine is entirely use for just 1 domain with a vBulletin board. The site has about somewhere from 200+ to 800+ users online concurrent depends on time of the day.

When the number of users about 300- (less than 300) the server seem to react normally (Not Slow)

However, when the number of users increase at night (7mp - 5am PST) the server react extreamly slow (I mean really really slow) and it sometimes refuse to dislay page and not to load images as well.

1. Is this because of MySQL not tuned or Apache not Turned? How do I go about finding what need to fix?

2. I have include the 2 results (when site not busy & busy) of the script Germy provide, with the numbers shown, How I optimize MySQL to be the most efficient for my site?

3. With the hardware I have how many users I could have on the site just to browse the vBulletin board?

Thank you in advance for helping.

Result when site not busy:

Thu Feb 19 15:57:00 PST 2004

15:57:01 up 8:37, 1 user, load average: 0.53, 0.68, 0.72
99 processes: 89 sleeping, 1 running, 9 zombie, 0 stopped
Mem: 1032320k av, 994240k used, 38080k free, 0k shrd, 16036k buff
151248k active, 805548k inactive
Swap: 2104504k av, 936k used, 2103568k free 862944k cached

PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME CPU COMMAND
7416 nobody 19 0 1096 1096 752 R 1.9 0.1 0:00 0 top


Http processes currently running = 24
Mysql processes currently running = 3

Joined: Feb 2004
Posts: 4
Lurker
Lurker
Offline
Joined: Feb 2004
Posts: 4
I have a P4 2.4 with 1GB RAM dedicated server 100Mbps net connection

It runs WHM 8.8.0 cPanel 8.8.0-R73 RedHat 9 - WHM X v2.1.2 apache (1.3.29 (Unix)) mysql (4.0.15-standard)

This machine is entirely use for just 1 domain with a vBulletin board. The site has about somewhere from 200+ to 800+ users online concurrent depends on time of the day.

When the number of users about 300- (less than 300) the server seem to react normally (Not Slow)

However, when the number of users increase at night (7mp - 5am PST) the server react extreamly slow (I mean really really slow) and it sometimes refuse to dislay page and not to load images as well.

1. Is this because of MySQL not tuned or Apache not Turned? How do I go about finding what need to fix?

2. I have include the 2 results (when site not busy & busy) of the script Jeremy provide, with the numbers shown, How I optimize MySQL to be the most efficient for my site?

3. With the hardware I have how many users I could have on the site just to browse the vBulletin board?

Thank you in advance for helping.

==========================
Result when site not busy:

Thu Feb 19 16:55:03 PST 2004

16:55:04 up 9:35, 0 users, load average: 1.20, 1.42, 1.24
113 processes: 108 sleeping, 1 running, 4 zombie, 0 stopped
Mem: 1032320k av, 1003632k used, 28688k free, 0k shrd, 37436k buff
154672k active, 810984k inactive
Swap: 2104504k av, 936k used, 2103568k free 823300k cached

PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME CPU COMMAND
17311 nobody 13 0 1088 1088 748 R 1.9 0.1 0:00 0 top

Http processes currently running = 36
Mysql processes currently running = 3

Key Reads/Key Read Requests = 0.000096 (Cache hit = 99.999904%)
Key Writes/Key Write Requests = 0.960247
Connections/second = 2.483 (/hour = 8939.866)
KB received/second = 8.783 (/hour = 31619.251)
KB sent/second = 7.370 (/hour = 26531.916)
Temporary Tables Created/second = 0.417 (/hour = 1502.356)
Opened Tables/second = 0.003 (/hour = 10.014)
Slow Queries/second = 0.000 (/hour = 0.104)
% of slow queries = 0.000%
Queries/second = 59.012 (/hour = 212441.723)
MySQL Query Cache hits = 0/1685434(0%)

=========================
Result when site busy (I'll update tonight when the site get busy)

Last edited by Tony; 02/19/2004 7:57 PM.
Joined: Feb 2004
Posts: 4
Lurker
Lurker
Offline
Joined: Feb 2004
Posts: 4
Continue for above, here is the result when site farely busy (450 users online)

Fri Feb 20 02:40:07 PST 2004

02:40:08 up 19:21, 1 user, load average: 4.69, 4.60, 3.96
165 processes: 130 sleeping, 9 running, 26 zombie, 0 stopped
Mem: 1032320k av, 1014388k used, 17932k free, 0k shrd, 126924k buff
326156k active, 645548k inactive
Swap: 2104504k av, 436k used, 2104068k free 659844k cached

PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME CPU COMMAND
21166 nobody 13 0 1124 1124 848 R 2.9 0.1 0:00 0 top

Http processes currently running = 80
Mysql processes currently running = 3

Netstat information summary
2 CLOSING
3 FIN_WAIT2
6 FIN_WAIT1
19 CLOSE_WAIT
32 LISTEN
71 SYN_RECV
150 ESTABLISHED
407 TIME_WAIT

+--------------------------+-----------+
| Variable_name | Value |
+--------------------------+-----------+
| Aborted_clients | 152 |
| Aborted_connects | 23 |
| Bytes_received | 23027568 |
| Bytes_sent | 326226515 |
| Com_admin_commands | 0 |
| Com_alter_table | 0 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 0 |
| Com_change_db | 5882 |
| Com_change_master | 0 |
| Com_check | 0 |
| Com_commit | 0 |
| Com_create_db | 0 |
| Com_create_function | 0 |
| Com_create_index | 0 |
| Com_create_table | 0 |
| Com_delete | 885 |
| Com_delete_multi | 0 |
| Com_drop_db | 0 |
| Com_drop_function | 0 |
| Com_drop_index | 0 |
| Com_drop_table | 0 |
| Com_flush | 0 |
| Com_grant | 0 |
| Com_ha_close | 0 |
| Com_ha_open | 0 |
| Com_ha_read | 0 |
| Com_insert | 1187 |
| Com_insert_select | 0 |
| Com_kill | 0 |
| Com_load | 0 |
| Com_load_master_data | 0 |
| Com_load_master_table | 0 |
| Com_lock_tables | 159 |
| Com_optimize | 0 |
| Com_purge | 0 |
| Com_rename_table | 0 |
| Com_repair | 0 |
| Com_replace | 1 |
| Com_replace_select | 0 |
| Com_reset | 0 |
| Com_restore_table | 0 |
| Com_revoke | 0 |
| Com_rollback | 0 |
| Com_savepoint | 0 |
| Com_select | 127412 |
| Com_set_option | 0 |
| Com_show_binlog_events | 0 |
| Com_show_binlogs | 0 |
| Com_show_create | 0 |
| Com_show_databases | 0 |
| Com_show_fields | 0 |
| Com_show_grants | 0 |
| Com_show_keys | 0 |
| Com_show_logs | 0 |
| Com_show_master_status | 0 |
| Com_show_new_master | 0 |
| Com_show_open_tables | 0 |
| Com_show_processlist | 4 |
| Com_show_slave_hosts | 0 |
| Com_show_slave_status | 0 |
| Com_show_status | 1 |
| Com_show_innodb_status | 0 |
| Com_show_tables | 0 |
| Com_show_variables | 1 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_truncate | 0 |
| Com_unlock_tables | 159 |
| Com_update | 9539 |
| Connections | 5943 |
| Created_tmp_disk_tables | 662 |
| Created_tmp_tables | 1544 |
| Created_tmp_files | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Delayed_errors | 0 |
| Flush_commands | 1 |
| Handler_commit | 0 |
| Handler_delete | 52 |
| Handler_read_first | 6073 |
| Handler_read_key | 772807 |
| Handler_read_next | 1301166 |
| Handler_read_prev | 3106 |
| Handler_read_rnd | 1333630 |
| Handler_read_rnd_next | 91569972 |
| Handler_rollback | 0 |
| Handler_update | 8601 |
| Handler_write | 370103 |
| Key_blocks_used | 1465 |
| Key_read_requests | 4431052 |
| Key_reads | 1446 |
| Key_write_requests | 756 |
| Key_writes | 755 |
| Max_used_connections | 25 |
| Not_flushed_key_blocks | 0 |
| Not_flushed_delayed_rows | 0 |
| Open_tables | 67 | 7% of table_cache in use
| Open_files | 99 |
| Open_streams | 0 |
| Opened_tables | 73 |
| Questions | 151063 |
| Qcache_queries_in_cache | 0 |
| Qcache_inserts | 0 |
| Qcache_hits | 0 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 0 |
| Qcache_free_memory | 0 |
| Qcache_free_blocks | 0 |
| Qcache_total_blocks | 0 |
| Rpl_status | NULL |
| Select_full_join | 2 |
| Select_full_range_join | 2 |
| Select_range | 67929 |
| Select_range_check | 0 |
| Select_scan | 23125 |
| Slave_open_temp_tables | 0 |
| Slave_running | OFF |
| Slow_launch_threads | 0 |
| Slow_queries | 1 | (execution time > 10 secs)
| Sort_merge_passes | 0 |
| Sort_range | 8539 |
| Sort_rows | 1364197 |
| Sort_scan | 15137 |
| Table_locks_immediate | 144766 |
| Table_locks_waited | 217 |
| Threads_cached | 16 |
| Threads_created | 26 |
| Threads_connected | 10 |
| Threads_running | 1 |
| Uptime | 1435 | 23 mins 55 secs
+--------------------------+-----------+


Key Reads/Key Read Requests = 0.000326 (Cache hit = 99.999674%)
Key Writes/Key Write Requests = 0.998677
Connections/second = 4.141 (/hour = 14909.268)
KB received/second = 15.670 (/hour = 56413.380)
KB sent/second = 222.007 (/hour = 799225.087)
Temporary Tables Created/second = 1.076 (/hour = 3873.449)
Opened Tables/second = 0.051 (/hour = 183.136)
Slow Queries/second = 0.001 (/hour = 2.509)
% of slow queries = 0.001%
Queries/second = 105.270 (/hour = 378973.380)
<br />
<b>Warning</b>: Division by zero in <b>/home/.../public_html/showvars.php</b> on line <b>259</b><br />
MySQL Query Cache hits = 0/0(0%)

====
Please help me anything seem odd in the result. My site is really really slow now.

Joined: Jun 2003
Posts: 1,025
Junior Member
Junior Member
Offline
Joined: Jun 2003
Posts: 1,025
As this is a UBB Threads site, the tuning recommendations made here are with UBB Threads in mind. You may find information more usefull to your needs on a vBulliten sight, as they would be tuning thier systems to run best for vBulliten.

That being said, and a disclaimer that all in all, I know jack about MySQL:
This looks like a problem to me and is where I would start: 26 zombie processes. That number seems way too high to me.

Joined: Feb 2002
Posts: 1,759
Addict
Addict
Offline
Joined: Feb 2002
Posts: 1,759
I haven't had time to read a lot of of that, but I caught the zombie processes bit, and I agree, that is a problem. On one of my dedicated threads servers, I can have about 300 processes, server loads under 1, and 0 zombie processes.

So IMO, you have way too many zombie processes for one. I don't know if that is ultimately your problem, but a properly setup server and/or software should not lead to that many zombie (defunct) processes.

I'll try to take a look at all the numbers and stats you posted earlier.. but in the meantime, can you say what OS you are using, kernel version, etc?

Joined: Feb 2002
Posts: 1,759
Addict
Addict
Offline
Joined: Feb 2002
Posts: 1,759
Nevermind, I see you are running Redhat 9.

FIrst of all, redhat 9 sucks. I've had NOTHING but troubles with RH9 machines, and IMO I'd only run RH7.3 or Fedora. But that's just me

Joined: Feb 2004
Posts: 4
Lurker
Lurker
Offline
Joined: Feb 2004
Posts: 4
Now that the machine is locked up after runing for about 2 hours; then I need to reboot then it would run for the next 2 hours

Joined: Feb 2002
Posts: 1,759
Addict
Addict
Offline
Joined: Feb 2002
Posts: 1,759
Seems like a certain script or something is eating up all of your resources, and most likely it takes about 2 hours for this to happen. Probably whatever is causing all of the zombie processes.

Since you are using cPanel/WHM, login to WHM and check this:

On the left menu, under the "Server Status" section, click on CPU/Memory/MySQL Usage

Take a look and see what user is using the most resources (whether it be mysql itself or your one vbulletin account) and it should tell you the scripts or commands that are using the most resources.

Otherwise from your info, nothing is terribly out of whack, other than what looks to be a script bombing out, creating a zombie, and this happening over and over again until the system comes to a grinding halt.

palmen #253477 10/20/2004 11:19 PM
Joined: Mar 2000
Posts: 21,079
Likes: 3
I type Like navaho
I type Like navaho
Joined: Mar 2000
Posts: 21,079
Likes: 3
Seems there's a pretty good book out on managing/ tuning/ and writing high performance mysql:

http://books.slashdot.org/books/04/10/13/2016211.shtml?tid=198&tid=221&tid=172&tid=6


- Allen wavey
- What Drives You?
palmen #253478 12/23/2004 8:41 AM
Joined: Mar 2002
Posts: 193
Member
Member
Offline
Joined: Mar 2002
Posts: 193
Well, this is my first attempt at tuning MySQL so i thought I'd take the smart approach of asking a few questions before attempting to modify anything on my server...

1. My key 'reads : read requests' & 'write : write requests' ratios are fine at 0.000697 and 0.613655 respectively, so that's taken care of I guess.

2. In the next bit I'm not sure what my table_cache is, but judging by the open_tables stat as well as an almost blank "my.cnf" file I assume that it's 64.

open_tables - 64
opened_tables - 4454
uptime - 1504472

Using Jeremy's script I also see info like "100% of table_cache in use" and "(Cache hit = 99.999303%)".

2. How can up up my cache to 128, and how will this affect my system memory (I've got 1GB RAM)? What do I need to add to the "my.cnf" file, which is very bare at the moment (just 3 lines!):

[mysqld]
set-variable = max_connections=500
safe-show-database

3. Also, is there a procedure to follow when restarting MySQL? Or do I just switch the forums off and restart MySQL in WHM?

4. Jeremy also mentions "key_buffer_size". Where can I find out what it's set to? If the "my.cnf" file is just the three lines listed above, then does that mean that I have no key_buffer_size?

5. Last but not least, how do I protect the script from getting into the wrong hands (after all, I don't want to make my db login+password public...)? Placing it above the web root probably makes no sense, so am I doing the right thing by creating a new folder for this file and then setting a password on the folder via CPanel? Is that secure enough?

CHOELIN #253479 01/11/2005 10:17 AM
Joined: Mar 2002
Posts: 193
Member
Member
Offline
Joined: Mar 2002
Posts: 193
Anyone?

CHOELIN #253480 01/11/2005 11:15 AM
Joined: Dec 2000
Posts: 1,471
Addict
Addict
Offline
Joined: Dec 2000
Posts: 1,471
[]Conrad said:


2. How can up up my cache to 128, and how will this affect my system memory (I've got 1GB RAM)? What do I need to add to the "my.cnf" file, which is very bare at the moment (just 3 lines!):

[mysqld]
set-variable = max_connections=500
safe-show-database
[/]
Just add the variables that aren't defined yet to the configuration file.
So inserting:
table_cache = 128

will set your table cache to 128.
Same thing for key_buffer_size and all other thigs mentioned in Jeremy's text. If a variable isn't set in the config file a default value is used.

[]
3. Also, is there a procedure to follow when restarting MySQL? Or do I just switch the forums off and restart MySQL in WHM?
[/]
Yes, close the board and restart mysql.

[]
5. Last but not least, how do I protect the script from getting into the wrong hands (after all, I don't want to make my db login+password public...)? Placing it above the web root probably makes no sense, so am I doing the right thing by creating a new folder for this file and then setting a password on the folder via CPanel? Is that secure enough? [/]
Yes, that's fine.

-Fusion- #253481 01/11/2005 11:50 AM
Joined: Mar 2002
Posts: 193
Member
Member
Offline
Joined: Mar 2002
Posts: 193
Many thanks for the kind help, Astaran!

I take it that I can insert "table_cache = 128" right below "safe-show-database
", or is there a certain order to these settings in my.cnf?

How will this affect my mem use, btw? Are we talking megabytes here? If so then 64MB extra for the cache shouldn't be a problem on a 1GB system...

Joined: Oct 2001
Posts: 213
Member
Member
Offline
Joined: Oct 2001
Posts: 213
Conrad, with 1GB RAM you can start table_cache at 512, and yes just add it on the next line in my.cnf: set-variable = table_cache=512. Then on the line after that: set-variable = key_buffer=256M.

Close forums, start and stop MySQL, see what happens. You may need to tweak them up or down depending on the load on your server.

Joined: Mar 2002
Posts: 193
Member
Member
Offline
Joined: Mar 2002
Posts: 193
Hi Joe!

Which is the correct line to insert:

table_cache = 512 or set-variable = table_cache=512 ?

Also, although I know that the default table_cache is 64, what is the default key_buffer size ?

Joined: Oct 2001
Posts: 213
Member
Member
Offline
Joined: Oct 2001
Posts: 213
set-variable = table_cache=512

I *believe* the default key_buffer is 16M.

Get these 2 settings in your my.cnf and let's see what happens. I also wrote you a lengthy PM on Infopop's forums regarding this.

palmen #253485 05/14/2005 7:16 PM
Joined: Feb 2002
Posts: 8
SKB Offline
Lurker
Lurker
Joined: Feb 2002
Posts: 8
Hi everyone,

I've been tweaking my my.cnf file, first attempt. Judging from details gathered, most indicators are fine.

However, now my site is busy, there are two indicators that aren't so good:

Open_tables | 512 | 100% of table_cache in use

I can just up that figure, I guess.

But this one:
Key_read_requests | 92766222 | | Key_reads | 40777

That's 4.39. WAY over the recommendation here.

Which area of the cnf file should I target to bring this in control? Scenario 4 in the intial post seems to similar to this, but there's no elaboration on what it means.

Thanks!

Squib #253486 05/15/2005 3:47 PM
Joined: Feb 2002
Posts: 8
SKB Offline
Lurker
Lurker
Joined: Feb 2002
Posts: 8
Actually, I've tweaked (all these stats are very informative) and those numbers are healthy again.

But should I be concerned by this number of delayed locks?

Table_locks_immediate | 2684226
Table_locks_waited | 44895 |

msula #315832 09/25/2007 1:35 PM
Joined: Jun 2004
Posts: 6
Lurker
Lurker
Offline
Joined: Jun 2004
Posts: 6
Yikes! Anyone care to help figure this out? What's up with my LAST_ACK connections?

Web Server: Xserve G5/Dual 2.0Ghz/4GB RAM/3x80GB RAID 5/10.4.10
DB Server: Dell PowerEdge 2850/Dual 3.0Ghz/4GB RAM/3x74 RAID 5/CentOS 4.5

This is from the web server:

Processes: 205 total, 5 running, 200 sleeping... 377 threads 13:34:25
Load Avg: 3.45, 3.67, 3.27 CPU usage: 44.2% user, 48.4% sys, 7.4% idle
SharedLibs: num = 164, resident = 33.1M code, 4.05M data, 19.3M LinkEdit
MemRegions: num = 43255, resident = 497M + 21.6M private, 90.9M shared
PhysMem: 309M wired, 514M active, 3.02G inactive, 3.83G used, 174M free
VM: 14.8G + 106M 631810(0) pageins, 1276(0) pageouts

Http processes currently running = 102
Mysql processes currently running = 3

Netstat information summary
1 CLOSING
5 SYN_RCVD
7 CLOSED
15 FIN_WAIT_1
26 LISTEN
207 ESTABLISHED
253 CLOSE_WAIT
458 LAST_ACK

cdavis #315841 09/25/2007 6:20 PM
Joined: Jan 2000
Posts: 5,833
Likes: 20
UBBDev / UBBWiki Owner
Time Lord
UBBDev / UBBWiki Owner
Time Lord
Joined: Jan 2000
Posts: 5,833
Likes: 20
Sounds like you're using persistant connections (as you have a ton of "sleeping" threads). I'd recommend that you set all of your MySQL scripts to NOT use persistant connections...

Perisstant connections sound cool in theory, that you can have a thread open and ready for users, HOWEVER, these threads STAY open, even after the session is termed... So available resources are out the window.


UBB.Dev - Putting Dev into UBB.threads
Company: VNC Web Services - UBB.threads Scripts and Scripting, Install and Upgrade Services, Site and Server Maintenance.
Forums: A Gardeners Forum, Scouters World, and UGN Security
UBB.Threads: My UBB Themes, My UBB Scripts
Page 1 of 2 1 2

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
Bill B
Bill B
Issaquah, WA
Posts: 87
Joined: December 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
Morgan 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)