UBB.Developers Forums Community Development Programming MySQL and Server Configuration Let's tune MySQL!

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)

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.

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.

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?

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

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

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.

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

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?

Anyone?

[]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.

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...

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.

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 ?

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.

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!

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 |

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

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.