Previous Thread
Next Thread
Print Thread
Rating: 5
Page 2 of 2 1 2
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.
Sponsored Links
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

Sponsored Links
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?

Sponsored Links
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 2 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,574
Posts293,926
Members13,850
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 20240506)