Site loading Slow and Intermittently

[Sarge]

I will try and post mysqlreports probably tomorrow and tunning.sh

Be sure to let MySQL run for at least 48 hours. The more time it's been up, the more accurate the results from mysqlreport and tuning-primer will be.

[slackerpunk]

@sarge here are the results

-- MYSQL PERFORMANCE TUNING PRIMER --
             - By: Matthew Montgomery -

MySQL Version 5.0.22 i686

Uptime = 2 days 9 hrs 33 min 35 sec
Avg. qps = 33
Total Questions = 6877382
Threads Connected = 1

Server has been running for over 48hrs.
It should be safe to follow these recommendations

To find out more information on how each of these
runtime variables effects performance visit:
http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html
Visit http://www.mysql.com/products/enterprise/advisors.html
for info about MySQL's Enterprise Monitoring and Advisory Service

SLOW QUERIES
Current long_query_time = 10 sec.
You have 454 out of 6877394 that take longer than 10 sec. to complete
The slow query log is NOT enabled.
Your long_query_time may be too high, I typically set this under 5 sec.

WORKER THREADS
Current thread_cache_size = 0
Current threads_cached = 0
Current threads_per_sec = 2
Historic threads_per_sec = 1
Threads created per/sec are overrunning threads cached
You should raise thread_cache_size

MAX CONNECTIONS
Current max_connections = 100
Current threads_connected = 2
Historic max_used_connections = 11
The number of used connections is 11% of the configured maximum.
Your max_connections variable seems to be fine.

MEMORY USAGE
Max Memory Ever Allocated : 40 M
Configured Max Per-thread Buffers : 268 M
Configured Max Global Buffers : 10 M
Configured Max Memory Limit : 279 M
Physical Memory : 11.87 G
Max memory limit seem to be within acceptable norms

KEY BUFFER
Current MyISAM index space = 227 M
Current key_buffer_size = 7 M
Key cache miss rate is 1 : 102
Key buffer fill ratio = 99.00 %
You could increase key_buffer_size
It is safe to raise this up to 1/4 of total system memory;
assuming this is a dedicated database server.

QUERY CACHE
Query cache is supported but not enabled
Perhaps you should set the query_cache_size

SORT OPERATIONS
Current sort_buffer_size = 2 M
Current record/read_rnd_buffer_size = 256 K
Sort buffer seems to be fine

JOINS
Current join_buffer_size = 132.00 K
You have had 14615 queries where a join could not use an index properly
You have had 5 joins without keys that check for key usage after each row
You should enable "log-queries-not-using-indexes"
Then look for non indexed joins in the slow query log.
If you are unable to optimize your queries you may want to increase your
join_buffer_size to accommodate larger joins in one pass.

Note! This script will still suggest raising the join_buffer_size when
ANY joins not using indexes are found.

OPEN FILES LIMIT
Current open_files_limit = 1024 files
The open_files_limit should typically be set to at least 2x-3x
that of table_cache if you have heavy MyISAM usage.
Your open_files_limit value seems to be fine

TABLE CACHE
Current table_cache value = 64 tables
You have a total of 384 tables
You have 64 open tables.
Current table_cache hit rate is 0%, while 100% of your table cache is in use
You should probably increase your table_cache

TEMP TABLES
Current max_heap_table_size = 16 M
Current tmp_table_size = 32 M
Of 24226 temp tables, 35% were created on disk
Effective in-memory tmp_table_size is limited to max_heap_table_size.
Perhaps you should increase your tmp_table_size and/or max_heap_table_size
to reduce the number of disk-based temporary tables
Note! BLOB and TEXT columns are not allow in memory tables.
If you are using these columns raising these values might not impact your
ratio of on disk temp tables.

TABLE SCANS
Current read_buffer_size = 128 K
Current table scan ratio = 335 : 1
read_buffer_size seems to be fine

TABLE LOCKING
Current Lock Wait ratio = 1 : 5483
Your table locking seems to be fine

[Sarge]

OK, put these in the [mysqld] section of my.cnf (just before the [mysqld_safe] line):

thread_cache_size = 64
query_cache_type = 1
key_buffer_size = 64M
query_cache_size = 64M

The last two values may need to be higher, but this is a VPS... Restart mysql after saving my.cnf.

[edit]
Added query_cache_type

[slackerpunk]

@Sarge

I think I have those settings well in place but I will check I am at work right now..

@all
anyone know if these specs will run SMF just fine with 75-150 uses at the same time

Server 1 -
- AMD Athlon XP 2000+
- 1024MB DDR Memory (instead of 512MB!)
- 120GB EIDE Hard Drive
- 1000GB Traffic per month
- 100Mbit Connection


Server 2 - DOUBLED RAM!
- Intel Celeron 2.0Ghz
- 1024MB DDR Memory (instead of 512MB!)
- 120GB EIDE Hard Drive
- 1000GB Traffic per month
- 100Mbit Connection

by the way I also have a phpbb forum with around 20 users only and a wordpress site which gets about 50 uniques a day

[Sarge]

Hello, any updates on the situation?

[slackerpunk]

@Sarge

sorry for the long delay been busy the past few months... will try and do this on my off from work

[Sarge]

No problem, we're here if/when you have any questions.

Hopefully everything will turn out alright.

[slackerpunk]

back finally got the time to sit and work out on my issue..

right now my server is running slow seems like apache dies from time to time when I restart httpd the site works fine but apache will die again.

Status PHP
PHPinfo

my host tells me that apache is getting to much hits that is causing apache to slow down and just die for some reason.

a few notes I took this info when my server slowed down a few minutes ago

Code Select Expand

cat /proc/loadavg
0.33 0.41 0.40 1/61 18638


Code Select Expand

netstat -plan|grep :80|awk {'print $5'}|cut -d: -f1|sort|u                                                                                                 niq -c|sort -nk1
      1 0.0.0.0
      1 155.45.81.26
      1 195.229.241.180
      1 202.57.41.4
      1 58.71.64.216
      1 66.249.72.234
      2 122.53.147.135
      2 125.60.241.207
      2 202.175.224.36
      2 66.174.93.47
      2 69.147.111.107
      2 86.96.226.13
      2 86.96.226.14
      3 124.105.13.215
      3 125.60.243.124
      3 213.42.2.22
      4 121.54.32.35
      4 124.106.200.184
      4 125.60.241.250
      4 203.215.116.113
      4 92.236.128.212
      5 122.3.106.93
      5 15.211.153.73
      6 121.1.53.17
      6 121.54.1.12
      6 125.5.36.66
      6 125.60.229.52
      6 203.111.232.106
      6 203.81.166.2
      6 222.127.205.244
      6 69.112.150.197
      7 119.95.117.68
      7 121.97.228.186
      7 122.3.213.47
      7 125.60.241.21
      7 219.90.80.30
      7 222.127.223.71
      8 122.2.80.38
      8 122.53.100.117
      8 124.217.105.170
      8 125.60.241.202
      9 119.94.185.197
      9 122.131.169.109
      9 122.2.114.86
      9 122.53.11.191
      9 125.212.13.225
      9 170.252.160.1
     10 119.94.187.183
     10 121.54.32.50
     10 124.105.161.11
     10 124.217.91.161
     10 58.69.103.163
     10 60.50.20.44
     11 121.96.214.142
     11 121.97.224.21
     11 124.106.138.58
     11 202.8.243.46
     11 203.111.237.49
     11 203.160.170.149
     11 203.87.203.202
     11 58.69.11.11
     11 58.69.37.250
     11 58.69.91.14
     12 203.177.180.234
     12 222.127.189.217
     12 58.69.18.120
     12 71.172.43.70
     15 125.60.243.21
     15 222.127.184.169
     17 119.95.115.73
     23 124.106.178.118
     23 203.87.206.134
     27 218.186.12.8
     31 122.2.219.190
     42 222.127.221.233


Code Select Expand

netstat -apn |grep :80|wc -l
416


Code Select Expand

top - 21:26:24 up 1 day,  9:18,  1 user,  load average: 0.44, 0.48, 0.43
Tasks:  40 total,   1 running,  39 sleeping,   0 stopped,   0 zombie
Cpu(s):  1.9% us,  0.2% sy,  0.0% ni, 96.4% id,  1.5% wa,  0.0% hi,  0.0% si
Mem:  12455808k total, 12009264k used,   446544k free,   211820k buffers
Swap: 12586916k total,      968k used, 12585948k free,  7831540k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
29890 mysql     15   0  210m  60m 4964 S   10  0.5   1:52.00 mysqld
14049 apache    15   0 99.4m  19m  11m S    4  0.2   0:16.45 httpd
14083 apache    15   0 99.3m  21m  13m S    3  0.2   0:14.90 httpd
21900 root      16   0  2100 1024  820 R    1  0.0   0:00.02 top
    1 root      15   0  1960  652  564 S    0  0.0   0:00.19 init
15654 root      16   0  1628  572  484 S    0  0.0   0:00.64 syslogd
19812 root      16   0  6068 1032  668 S    0  0.0   0:00.00 sshd
19825 root      16   0  2600  872  712 S    0  0.0   0:00.03 xinetd
19956 root      21   0  5744  808  552 S    0  0.0   0:00.00 couriertcpd
19958 root      18   0  4588 1004  784 S    0  0.0   0:00.00 courierlogger
19966 root      24   0  5744  808  552 S    0  0.0   0:00.00 couriertcpd
19968 root      18   0  4588 1004  784 S    0  0.0   0:00.00 courierlogger
19974 root      21   0  5744  808  552 S    0  0.0   0:00.00 couriertcpd
19976 root      18   0  4588 1004  784 S    0  0.0   0:00.00 courierlogger
19983 root      22   0  5744  808  552 S    0  0.0   0:00.00 couriertcpd
19985 root      18   0  4588 1004  784 S    0  0.0   0:00.00 courierlogger
20018 qmails    16   0  1608  488  404 S    0  0.0   0:07.10 qmail-send
20026 qmaill    16   0  1560  464  400 S    0  0.0   0:00.01 splogger
20027 root      22   0  1588  352  268 S    0  0.0   0:00.00 qmail-lspawn
20028 qmailr    16   0  1588  384  296 S    0  0.0   0:00.00 qmail-rspawn
20029 qmailq    17   0  1552  344  284 S    0  0.0   0:00.29 qmail-clean
20064 root      16   0 37840 5348 3424 S    0  0.0   0:00.02 httpsd
20068 psaadm    21   0 37840 2532  612 S    0  0.0   0:00.00 httpsd
20107 root      18   0  5356  708  436 S    0  0.0   0:00.00 saslauthd
20108 root      18   0  5356  436  164 S    0  0.0   0:00.00 saslauthd
20127 root      15   0  7288 5368 1652 S    0  0.0   0:00.27 miniserv.pl
22468 root      15   0  3180 1096  576 S    0  0.0   0:00.09 crond
13714 root      16   0 10852 2972 2360 S    0  0.0   0:00.24 sshd
13787 root      15   0  2484 1380 1112 S    0  0.0   0:00.09 bash
14031 root      15   0 98612  12m 7520 S    0  0.1   0:00.27 httpd
14037 apache    16   0  101m  22m  11m S    0  0.2   0:12.56 httpd
14041 apache    17   0 98.4m  19m  11m S    0  0.2   0:09.30 httpd
14049 apache    15   0 99.4m  19m  11m S    0  0.2   0:16.33 httpd
14062 apache    16   0 99.4m  20m  12m S    0  0.2   0:11.75 httpd
14071 apache    16   0 99.7m  21m  12m S    0  0.2   0:18.91 httpd
14072 apache    16   0 99.2m  19m  10m S    0  0.2   0:09.43 httpd
14082 apache    16   0 99.5m  22m  13m S    0  0.2   0:19.75 httpd
29814 apache    16   0 98.7m  19m  11m S    0  0.2   0:06.09 httpd
29853 root      17   0  2340 1116  964 S    0  0.0   0:00.00 mysqld_safe
21900 root      16   0  2100 1024  820 R    0  0.0   0:00.00 top


any ideas I sure need some help right now

[Magic_Mike]

Hey

Mysql:
I noticed that your query_cache_size: is set to 0
that disables the query cache , is that intentional ?

Set this to 10M or whatever size your hoster allows.

I noticed as well that you have set the wait_timeout: to 28800
this is way too much set it to 50

Apache/Php:
Do you have to have so many extension and apache mods installed ?
please remove unwanted php mods and apache modules.
Do you need logfiles ? If not remove them !
they consume a great deal of resources.
(but keep the error logs )


Apache <-> Mysql:
See if you can enable persistent connection for your forum that can
speed up your sites a log.


Regards Mike

[slackerpunk]

Hey

Mysql:
I noticed that your query_cache_size: is set to 0
that disables the query cache , is that intentional ?

Set this to 10M or whatever size your hoster allows.

I noticed as well that you have set the wait_timeout: to 28800
this is way too much set it to 50

Apache/Php:
Do you have to have so many extension and apache mods installed ?
please remove unwanted php mods and apache modules.
Do you need logfiles ? If not remove them !
they consume a great deal of resources.
(but keep the error logs )


Apache <-> Mysql:
See if you can enable persistent connection for your forum that can
speed up your sites a log.


Regards Mike

its not really intentional I have a value for that inside my.cnf but for some reason I don't think its working... anyway to enable that thru the shell?

as for wait_timeout where do exactly I set that? my smf forum has this set to "Seconds before an unused session timeout" 2880

[slackerpunk]

I am getting alot of these errors in httpd error_log

[Mon Jun 02 05:54:27 2008] [error] [client 127.0.0.1] Directory index forbidden by Options directive: /var/www/html/
[Mon Jun 02 05:54:42 2008] [error] [client 127.0.0.1] Directory index forbidden by Options directive: /var/www/html/
[Mon Jun 02 05:55:09 2008] [error] [client 127.0.0.1] Directory index forbidden by Options directive: /var/www/html/
[Mon Jun 02 05:55:13 2008] [error] [client 127.0.0.1] Directory index forbidden by Options directive: /var/www/html/

[Magic_Mike]

This must be inside the [mysqld] section

Code Select Expand


[mysqld]                                                                                                                                                     
.....
wait_timeout=50                                                                                                                                             
query_cache_size = xx M                                                                                                                                       
.....

it would not be recognized otherwise.

this error means that someone tries to access your site tru the ip you are using
apache tells you that in that dire specified in the main configuration file httpd.conf
the default server dir is set to /var/www/html/
That means that someone is scanning your site(s) . But can be harmless as well as the ip is "localhost" = 127.0.0.1 better safe than sorry and point it so somewhere else.
regards

[slackerpunk]

@Magic_Mike

thanks what was the reason why it was not working it was not under mysqld... i've set it up thru the shell directly inside mysql.. found out that the slowness was being caused by TinyPortal 1.0.5 after removing it everything seems to be loading up fast now I will observe again.. thanks