Previous Thread
Next Thread
Print Thread
Rating: 5
Page 2 of 2 1 2
Re: What Need to be Tuned? [Re: -Fusion-] #253470
02/19/2004 5:59 PM
02/19/2004 5:59 PM
Joined: Feb 2004
Posts: 4
T
Tony Offline
Lurker
Tony  Offline
Lurker
T

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 6:57 PM.
Sponsored Links
Re: What Need to be Tuned? [Re: BlindFreddy] #253471
02/20/2004 4:43 AM
02/20/2004 4:43 AM
Joined: Feb 2004
Posts: 4
T
Tony Offline
Lurker
Tony  Offline
Lurker
T

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.

Re: What Need to be Tuned? [Re: BlindFreddy] #253472
02/20/2004 11:34 AM
02/20/2004 11:34 AM
Joined: Jun 2003
Posts: 1,185
New Boston, NH
S
slayer60 Offline
Junior Member
slayer60  Offline
Junior Member
S

Joined: Jun 2003
Posts: 1,185
New Boston, NH
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.

Re: What Need to be Tuned? [Re: BrokenToy] #253473
02/20/2004 7:00 PM
02/20/2004 7:00 PM
Joined: Feb 2002
Posts: 1,969
Lansing, Michigan
M
msula Offline OP
Addict
msula  Offline OP
Addict
M

Joined: Feb 2002
Posts: 1,969
Lansing, Michigan
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?

Re: What Need to be Tuned? [Re: palmen] #253474
02/20/2004 7:02 PM
02/20/2004 7:02 PM
Joined: Feb 2002
Posts: 1,969
Lansing, Michigan
M
msula Offline OP
Addict
msula  Offline OP
Addict
M

Joined: Feb 2002
Posts: 1,969
Lansing, Michigan
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

Sponsored Links
Re: What Need to be Tuned? [Re: palmen] #253475
02/21/2004 2:22 AM
02/21/2004 2:22 AM
Joined: Feb 2004
Posts: 4
T
Tony Offline
Lurker
Tony  Offline
Lurker
T

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

Re: What Need to be Tuned? [Re: BlindFreddy] #253476
02/22/2004 1:56 PM
02/22/2004 1:56 PM
Joined: Feb 2002
Posts: 1,969
Lansing, Michigan
M
msula Offline OP
Addict
msula  Offline OP
Addict
M

Joined: Feb 2002
Posts: 1,969
Lansing, Michigan
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.

Managing MySQL [Re: palmen] #253477
10/20/2004 10:19 PM
10/20/2004 10:19 PM
Joined: Mar 2000
Posts: 25,587
Texas
AllenAyres Offline
I type Like navaho
AllenAyres  Offline
I type Like navaho

Joined: Mar 2000
Posts: 25,587
Texas
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?
Re: Let's tune MySQL! [Re: palmen] #253478
12/23/2004 7:41 AM
12/23/2004 7:41 AM
Joined: Mar 2002
Posts: 207
C
Conrad Offline
Member
Conrad  Offline
Member
C

Joined: Mar 2002
Posts: 207
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?

Re: Let's tune MySQL! [Re: CHOELIN] #253479
01/11/2005 9:17 AM
01/11/2005 9:17 AM
Joined: Mar 2002
Posts: 207
C
Conrad Offline
Member
Conrad  Offline
Member
C

Joined: Mar 2002
Posts: 207
Anyone?

Sponsored Links
Re: Let's tune MySQL! [Re: CHOELIN] #253480
01/11/2005 10:15 AM
01/11/2005 10:15 AM
Joined: Dec 2000
Posts: 1,545
Germany
A
Astaran Offline
Addict
Astaran  Offline
Addict
A

Joined: Dec 2000
Posts: 1,545
Germany
[]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.

Re: Let's tune MySQL! [Re: -Fusion-] #253481
01/11/2005 10:50 AM
01/11/2005 10:50 AM
Joined: Mar 2002
Posts: 207
C
Conrad Offline
Member
Conrad  Offline
Member
C

Joined: Mar 2002
Posts: 207
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...

Re: Let's tune MySQL! [Re: CHOELIN] #253482
01/11/2005 4:57 PM
01/11/2005 4:57 PM
Joined: Oct 2001
Posts: 209
Cleveland, Ohio
L
little_joe Offline
Member
little_joe  Offline
Member
L

Joined: Oct 2001
Posts: 209
Cleveland, Ohio
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.

Re: Let's tune MySQL! [Re: JoshPet] #253483
01/11/2005 5:30 PM
01/11/2005 5:30 PM
Joined: Mar 2002
Posts: 207
C
Conrad Offline
Member
Conrad  Offline
Member
C

Joined: Mar 2002
Posts: 207
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 ?

Re: Let's tune MySQL! [Re: CHOELIN] #253484
01/11/2005 8:49 PM
01/11/2005 8:49 PM
Joined: Oct 2001
Posts: 209
Cleveland, Ohio
L
little_joe Offline
Member
little_joe  Offline
Member
L

Joined: Oct 2001
Posts: 209
Cleveland, Ohio
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.

Re: Let's tune MySQL! [Re: palmen] #253485
05/14/2005 6:16 PM
05/14/2005 6:16 PM
Joined: Feb 2002
Posts: 8
SKB Offline
Lurker
SKB  Offline
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!

Re: Let's tune MySQL! [Re: Squib] #253486
05/15/2005 2:47 PM
05/15/2005 2:47 PM
Joined: Feb 2002
Posts: 8
SKB Offline
Lurker
SKB  Offline
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 |

Re: Let's tune MySQL! [Re: msula] #315832
09/25/2007 12:35 PM
09/25/2007 12:35 PM
Joined: Jun 2004
Posts: 6
C
cdavis Offline
Lurker
cdavis  Offline
Lurker
C

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

Re: Let's tune MySQL! [Re: cdavis] #315841
09/25/2007 5:20 PM
09/25/2007 5:20 PM
Joined: Jan 2000
Posts: 5,947
Portland, OR, USA
Gizmo Offline

UBB.Dev / UBB.Wiki Owner
Time Lord
Gizmo  Offline

UBB.Dev / UBB.Wiki Owner
Time Lord

Joined: Jan 2000
Posts: 5,947
Portland, OR, USA
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, UBB.SitemapsJames Corthell
Page 2 of 2 1 2

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
· Blue Host
· Interserver.net
Visit us on Facebook
Member Spotlight
isaac
isaac
California
Posts: 1,208
Joined: July 2001
Show All Member Profiles 
Forum Statistics
Forums64
Topics37,467
Posts293,523
Members13,802
Most Online1,498
Mar 17th, 2017
Top Posters(All Time)
AllenAyres 25,587
JoshPet 11,330
Rick 8,373
LK 7,396
Lord Dexter 6,503
Gizmo 5,947
Greg Hard 5,533
Top Posters(30 Days)
isaac 1
Today's Statistics
Currently Online 870
Topics Created 2
Posts Made 2
Users Online 2
Birthdays 12
The UBB.Developers Network (UBB.Dev/Threads.Dev) is ©2000-2018 VNC Web Services

 
Powered by UBB.threads™ PHP Forum Software 7.7.0
(Snapshot build 20181115.dev)
PHP: 5.4.45 Page Time: 0.057s Queries: 16 (0.015s) Memory: 3.3580 MB (Peak: 3.6379 MB) Data Comp: Zlib Server Time: 2018-12-13 10:32:55 UTC