my board slows too slow

[opus]

my message board loads slower than the rest of my site and i'm at my wits end trying to figure out what to do. can someone tell me what the best to setup my message board so it loads as fast as possible.

my site it www.trickology.com

[George L]

if you're on a dedicated server please provide the following

1. your server specs, such as mysql and php version
2. if possible how mysql was compiled/installed
3. your top stats
4. your mysql configuration variables located at /etc/my.cnf or c:\my.cnf if on Windows server if you don't have that file you need to log into telnet and as root user type

mysqladmin -u root -p variables

copy and paste output here

5. your mysql extended-status output either still telnet as root user type

mysqladmin -u root -p extended-status

copy and paste output here

or preferred is to installed extended-status output script which is located at http://vbulletin.com/forum/showthread.php?threadid=3477

and post url to that here

6. oh and is your vB the only thing on the server? or other scripts? sites?

7. how many average and max concurrent users on your vB forum ?

if not on dedicated try to get your web host to provide some of the above details and info to help diagnose your problem

[opus]

i'm not on a dedicated server so i just emailed my host to get some of the info you need.

i'll answer the questions i know the answers to right now.

3. as far as top stats do you mean for my whole site? i had 96,000 uniques with 1,300,000 pageviews in January.

6. marticle is running on my and blog are running on my server also. also a little a little script i wrote that organizes my real audio files.

7. i usually have around 30 to 40 users on my message board at one time but the max i've had is 74.

i'll have the answers to the other questions soon.

[opus]

thisis what my webhost told me.

> 1. your server specs, such as mysql and php version
Your webserver runs linux 2.2 kernel, apache 1.3.12 with php 4.0.3p1
and runs from inetd. (we can upgrade that to 1.3.20 and php 4.0.6
if you wish).
mysql server is 3.23.36 and is a separate box (and shared of course).

> 2. if possible how mysql was compiled/installed
Please clarify.

> 3. your top stats
Please explain the need; Output from the "top" command varies constantly
and would be of little use.

> 4. your mysql configuration variables located at /etc/my.cnf or c:\my.cnf if
> on Windows server if you d

Relevent bits:

skip-locking
skip-name-resolve
safe-show-database
set-variable = back_log=100
set-variable = connect_timeout=30
set-variable = key_buffer=16M
set-variable = max_allowed_packet=8M
set-variable = thread_stack=256K
set-variable = max_connections=200
set-variable = max_tmp_tables=128
set-variable = flush_time=0
set-variable = table_cache=128
set-variable = wait_timeout=3600
#log

[opus]

eva is there any other info you need. any advice you could give me would be greatly appreciated.

[George L]

Originally posted by opus
eva is there any other info you need. any advice you could give me would be greatly appreciated.

items 4. and 5. are needed the top stats i only need the memory and cpu usage stats to see if the server is swapping to disk and by how much and the mysql extended-status output to see how well mysql is performing and whether mysql has been fine tuned for shared hosting

[opus]

okay this is the email i got back

It's not swapping at all. The box has 512meg of ram and is a P3-750
and seems to be running 30 - 40 threads consistantly.

I must have missed the request for extended status.
(output from a "show status;" SQL statement).
If your guy can offer any tuning advice we'd appreciate it; we're not
full-time professional DBA's...
/jim


+--------------------------+------------+
| Variable_name | Value |
+--------------------------+------------+
| Aborted_clients | 23418 |
| Aborted_connects | 30 |
| Bytes_received | 180944455 |
| Bytes_sent | 3723987550 |
| Connections | 46033 |
| Created_tmp_disk_tables | 1508 |
| Created_tmp_tables | 257085 |
| Created_tmp_files | 10 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Delayed_errors | 0 |
| Flush_commands | 1 |
| Handler_delete | 4712 |
| Handler_read_first | 67546 |
| Handler_read_key | 9141454 |
| Handler_read_next | 15865798 |
| Handler_read_prev | 102264 |
| Handler_read_rnd | 4184298 |
| Handler_read_rnd_next | 294474596 |
| Handler_update | 6254330 |
| Handler_write | 4110097 |
| Key_blocks_used | 15641 |
| Key_read_requests | 11854496 |
| Key_reads | 221059 |
| Key_write_requests | 25912 |
| Key_writes | 16771 |
| Max_used_connections | 38 |
| Not_flushed_key_blocks | 0 |
| Not_flushed_delayed_rows | 0 |
| Open_tables | 128 |
| Open_files | 247 |
| Open_streams | 0 |
| Opened_tables | 101865 |
| Questions | 1454884 |
| Select_full_join | 2648 |
| Select_full_range_join | 14 |
| Select_range | 31283 |
| Select_range_check | 0 |
| Select_scan | 537682 |
| Slave_running | OFF |
| Slave_open_temp_tables | 0 |
| Slow_launch_threads | 1 |
| Slow_queries | 2 |
| Sort_merge_passes | 5 |
| Sort_range | 151899 |
| Sort_rows | 8317773 |
| Sort_scan | 314103 |
| Table_locks_immediate | 1367383 |
| Table_locks_waited | 346 |
| Threads_cached | 0 |
| Threads_created | 46032 |
| Threads_connected | 18 |
| Threads_running | 1 |
| Uptime | 24852 |
+--------------------------+------------+

[opus]

help me, somebody!

[opus]

107 views and not one suggestion?

[George L]

could you post a more up to date extended-status output as well before i make any recommendations...

[opus]

that's not up to date? i'm posting what the tech support guys at my webhost are telling me.

[George L]

Originally posted by opus
that's not up to date? i'm posting what the tech support guys at my webhost are telling me.

extended-status output is best viewed real time since it changes by the second and resets whenever mysql server is restarted or when tables are flushed in mysql

[opus]

is it possible that i can access that info without asking my web host?

[George L]

generally you can't access that info if you are virtually hosted (shared hosting) unless your web host uses cpanel control panel you can access that info with your mysql username and password via SSH shell access

just ask your web host to give you another snap shot of extended-status output

[opus]

+--------------------------+-----------+
| Variable_name | Value |
+--------------------------+-----------+
| Aborted_clients | 32869 |
| Aborted_connects | 34 |
| Bytes_received | 319499432 |
| Bytes_sent | 127751234 |
| Connections | 63901 |
| Created_tmp_disk_tables | 1975 |
| Created_tmp_tables | 366219 |
| Created_tmp_files | 6 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Delayed_errors | 0 |
| Flush_commands | 1 |
| Handler_delete | 7820 |
| Handler_read_first | 81675 |
| Handler_read_key | 11945335 |
| Handler_read_next | 38715352 |
| Handler_read_prev | 86459 |
| Handler_read_rnd | 7425274 |
| Handler_read_rnd_next | 360105603 |
| Handler_update | 8331678 |
| Handler_write | 6012029 |
| Key_blocks_used | 15641 |
| Key_read_requests | 21738236 |
| Key_reads | 340539 |
| Key_write_requests | 41316 |
| Key_writes | 25490 |
| Max_used_connections | 49 |
| Not_flushed_key_blocks | 0 |
| Not_flushed_delayed_rows | 0 |
| Open_tables | 128 |
| Open_files | 244 |
| Open_streams | 0 |
| Opened_tables | 144310 |
| Questions | 2343440 |
| Select_full_join | 3138 |
| Select_full_range_join | 5 |
| Select_range | 40328 |
| Select_range_check | 0 |
| Select_scan | 693264 |
| Slave_running | OFF |
| Slave_open_temp_tables | 0 |
| Slow_launch_threads | 3 |
| Slow_queries | 2 |
| Sort_merge_passes | 3 |
| Sort_range | 543312 |
| Sort_rows | 11805270 |
| Sort_scan | 439342 |
| Table_locks_immediate | 2212779 |
| Table_locks_waited | 667 |
| Threads_cached | 0 |
| Threads_created | 63900 |
| Threads_connected | 29 |
| Threads_running | 1 |
| Uptime | 25006 |
+--------------------------+-----------+