News:

SMF 2.1.4 has been released! Take it for a spin! Read more.

Main Menu

MySQL sleeping processes problem

Started by jan.van.gent, September 16, 2012, 03:55:17 PM

Previous topic - Next topic

jan.van.gent

Hi everyone,

I'm having a problem with my forum on a VPS.
The CPU usage goes up to 100% at the moment (we had an event today and a lot of users are online).
I just called the hosting firm, and there were lot's of sleeping processes.

What should I do?

I'm running 2.0.2 with mods:
1.    SMF 2.0.2 Update    1.0    [ Deïnstalleer ] [ Overzicht bestanden ] [ Verwijderen ]
2.    Tapatalk SMF 2.0 RC4/RC5/Final Plugin    3.0.1    [ Deïnstalleer ] [ Overzicht bestanden ] [ Verwijderen ]
3.    Highslide 4 SMF    0.8.1    [ Deïnstalleer ] [ Overzicht bestanden ] [ Verwijderen ]
4.    SMF Trader System    1.6    [ Deïnstalleer ] [ Overzicht bestanden ] [ Verwijderen ]
5.    Aeva ~ Auto-Embed Video & Audio    7.1    [ Deïnstalleer ] [ Overzicht bestanden ] [ Verwijderen ]
6.    Stop Spammer    2.3.9    [ Deïnstalleer ] [ Overzicht bestanden ] [ Verwijderen ]
7.    Highslide 4 SMF    0.8    [ Overzicht bestanden ] [ Verwijderen ]
8.    httpBL    2.5.1    [ Installeer modificatie ] [ Overzicht bestanden ] [ Verwijderen ]
9.    Event Registration Mod for SMF2    2.19    [ Deïnstalleer ] [ Overzicht bestanden ] [ Verwijderen ]
10.    SimplePortal - Dutch    2.3.3    [ Deïnstalleer ] [ Overzicht bestanden ] [ Verwijderen ]
11.    SimplePortal    2.3.5    [ Deïnstalleer ] [ Overzicht bestanden ] [ Verwijderen ]
12.    SMF Arcade    2.5 RC1.1D    [ Deïnstalleer ] [ Overzicht bestanden ] [ Verwijderen ]
13.    SimpleAds

Best regards

www.bimmerboard.be (BMW forum)

butch2k

are you using persistant connections ?

jan.van.gent


butch2k

in the database options of SMF, there is smthg like "use persistant connections", if checked, uncheck it.

jan.van.gent


jan.van.gent

At the moment the forum is fast again, but there not that many users online as last night. Now only 40 users online, yesterday >150.

jan.van.gent

I uploaded the status.php file:

perating System:    Linux 2.6.32-042stab053.5
Processor:    Intel® Xeon® CPU L5520 @ 2.27GHz (376.838MHz)
Load averages:    1.09, 0.71, 0.72
Current processes:    44 (42 sleeping, 1 running, 1 zombie)
Processes by CPU:    apache2 (14) 51.4%, mysqld (1) 3.8%, (other) (23) 0.0%
Memory usage:    19.356% (405928k / 2097152k)
Swap: 0% (0k / 0k)

Connections per second:    1.9618
Kilobytes received per second:    6.2666
Kilobytes sent per second:    75.6842
Queries per second:    33.2669
Percentage of slow queries:    0
Opened vs. Open tables:
(table_cache)    143.375 (should be <= 80)
Table cache usage:
(table_cache)    1 (should be >= 0.5 and <= 0.9)
Key buffer read hit rate:
(key_buffer_size)    0.0063 (should be <= 0.01)
Key buffer write hit rate:
(key_buffer_size)    0.0709 (should be <= 0.5)
Thread cache hit rate:
(thread_cache_size)    31.9674 (should be >= 30 )
Thread cache usage:
(thread_cache_size)    0.875 (should be >= 0.7 and <= 0.9)
Temporary table disk usage:
(tmp_table_size)    0.036 (should be <= 0.5)
Sort merge pass rate:
(sort_buffer)    0 (should be <= 0.001)
Query cache enabled:
(query_cache_type)    1 (should be >= 1 and <= 1)
Query cache miss rate:
(query_cache_limit)    0.3265 (should be <= 0.5)
Query cache prune rate:
(query_cache_size)    0.0656 (should be <= 0.05)

I will try to check this when the problem occurs again.

jan.van.gent

This is from a moment of weak performance/sluggish loading of site:

Operating System:    Linux 2.6.32-042stab053.5
Processor:    Intel® Xeon® CPU L5520 @ 2.27GHz (376.838MHz)
Load averages:    4.76, 6.15, 7.14
Current processes:    87 (67 sleeping, 20 running, 0 zombie)
Processes by CPU:    apache2 (57) 85.3%, mysqld (1) 4.8%, (other) (23) 0.0%
Memory usage:    73.715% (1545920k / 2097152k)
Swap: 0% (0k / 0k)
MySQL processes
Total processes:    23 (19 sleeping, 2 running, 2 locked)

Running processes
State    Time    Query
Sending data    1s    

SELECT
   a.id_attach, a.id_folder, a.id_msg, a.filename, a.file_hash, IFNULL(a.size, 0) AS filesize, a.downloads, a.approved,
   a.width, a.height,
   IFNULL(thumb.id_attach, 0) AS id_thumb, thumb.width AS thumb_width, thumb.height AS thumb_height
FROM smf_attachments AS a
   LEFT JOIN smf_attachments AS thumb ON (thumb.id_attach = a.id_thumb)
WHERE a.id_msg IN (955671, 955668, 955662, 955649, 955631, 955613, 955608, 955605, 955602, 955595, 955590, 955576, 955570, 955558, 955549, 955544, 955540, 955539, 955532, 955514, 955507, 955502, 955501, 955499, 955493, 955470, 955467, 955460, 955457, 955453)
   AND a.attachment_type = 0

MySQL Statistics
MySQL 5.0.x
Connections per second:    2.7549
Kilobytes received per second:    8.268
Kilobytes sent per second:    103.8621
Queries per second:    44.1575
Percentage of slow queries:    0
Opened vs. Open tables:
(table_cache)    254.3594 (should be <= 80)
Table cache usage:
(table_cache)    1 (should be >= 0.5 and <= 0.9)
Key buffer read hit rate:
(key_buffer_size)    0.0052 (should be <= 0.01)
Key buffer write hit rate:
(key_buffer_size)    0.0609 (should be <= 0.5)
Thread cache hit rate:
(thread_cache_size)    34.9453 (should be >= 30 )
Thread cache usage:
(thread_cache_size)    1 (should be >= 0.7 and <= 0.9)
Temporary table disk usage:
(tmp_table_size)    0.0189 (should be <= 0.5)
Sort merge pass rate:
(sort_buffer)    0 (should be <= 0.001)
Query cache enabled:
(query_cache_type)    1 (should be >= 1 and <= 1)
Query cache miss rate:
(query_cache_limit)    0.335 (should be <= 0.5)
Query cache prune rate:
(query_cache_size)    0.0496 (should be <= 0.05)

jan.van.gent

perating System:    Linux 2.6.32-042stab053.5
Processor:    Intel® Xeon® CPU L5520 @ 2.27GHz (2261.033MHz)
Load averages:    3.13, 7.24, 7.03
Current processes:    136 (135 sleeping, 1 running, 0 zombie)
Processes by CPU:    apache2 (98) 157.9%, mysqld (1) 5.0%, (other) (23) 0.0%
Memory usage:    93.308% (1956804k / 2097152k)
Swap: 0% (0k / 0k)
MySQL processes
Total processes:    62 (60 sleeping, 2 running, 0 locked)

Running processes
State    Time    Query
Sending data    0s    

SELECT COUNT(*)
FROM smf_topics_posted_in AS pi
   LEFT JOIN smf_log_topics_posted_in AS lt ON (lt.id_topic = pi.id_topic)
WHERE pi.id_board IN (1, 56, 120, 6, 48, 75, 76, 79, 77, 80, 78, 8, 10, 84, 2, 111, 14, 9, 58, 130, 97, 90, 133, 134, 91, 100, 101, 106, 102, 132, 92, 103, 104, 105, 110, 93, 96, 94, 95, 107, 98, 99, 127, 138, 55, 26, 27, 43, 31, 109, 112, 131, 87, 50, 89, 114, 115, 119, 124, 125, 136, 137, 52, 135, 11, 45, 46, 72, 83, 24, 40, 29, 30, 126, 25, 122, 33, 121, 21, 5, 3, 37, 74, 12, 88, 44, 35)
   AND IFNULL(lt.id_msg, pi.id_msg) < pi.id_last_msg

MySQL Statistics
MySQL 5.0.x
Connections per second:    2.9012
Kilobytes received per second:    8.6631
Kilobytes sent per second:    109.4831
Queries per second:    46.3359
Percentage of slow queries:    0
Opened vs. Open tables:
(table_cache)    285.9375 (should be <= 80)
Table cache usage:
(table_cache)    1 (should be >= 0.5 and <= 0.9)
Key buffer read hit rate:
(key_buffer_size)    0.0046 (should be <= 0.01)
Key buffer write hit rate:
(key_buffer_size)    0.0599 (should be <= 0.5)
Thread cache hit rate:
(thread_cache_size)    31.902 (should be >= 30 )
Thread cache usage:
(thread_cache_size)    0.625 (should be >= 0.7 and <= 0.9)
Temporary table disk usage:
(tmp_table_size)    0.0173 (should be <= 0.5)
Sort merge pass rate:
(sort_buffer)    0 (should be <= 0.001)
Query cache enabled:
(query_cache_type)    1 (should be >= 1 and <= 1)
Query cache miss rate:
(query_cache_limit)    0.3362 (should be <= 0.5)
Query cache prune rate:
(query_cache_size)    0.0484 (should be <= 0.05)

butch2k

Apache load seems high, do you have APC enabled ?
What's the status of SMF cache ?

jan.van.gent

Quote from: butch2k on September 17, 2012, 04:52:17 PM
Apache load seems high, do you have APC enabled ?
What's the status of SMF cache ?
How to check?

butch2k

In the admin section, server parameters, cache configuration, what does it say ? (Green or Red line)

jan.van.gent

Quote from: butch2k on September 17, 2012, 05:49:55 PM
In the admin section, server parameters, cache configuration, what does it say ? (Green or Red line)
caching is off.
Should I put it on?

No danger for the board when putting it on?

butch2k

Yes you should put it on, Level 1 is fine for most usage.
What does it says exactly ? is APC detected somehow ? If not you should activate it (or XCache), they are PHP opcode cache as well as being data caches, you should see a real boost in performance with APC in use.

When APC is not installed PHP must compile PHP files everytime, which is very ressource intensive, with APC the files are compiled once, placed in a cache and used from there whenever they are accessed.

jan.van.gent

OK I've put it on Level 1.

How can I see if APC is detected?
I just switched the dropdown box from "no caching" to "level 1 caching".
But there are nog green bars or whatever.

butch2k

Scroll down the text area above the cache level selection. There should either a green line if a software cache is detected or a red line if none is detected.

jan.van.gent

OK found it, thanks!

It's red  :(
SMF heeft geen compatibele accelerator op jouw server kunnen vinden.
or translated:
SMF did not found any compatibel accelerator on your server.

What to do?  ;D

butch2k

You have ssh access to the server ?
if so: sudo yum install php-pecl-apc

if not ask for the package to be installed and enabled on your vps.

jan.van.gent

Quote from: butch2k on September 18, 2012, 05:36:27 AM
You have ssh access to the server ?
if so: sudo yum install php-pecl-apc

if not ask for the package to be installed and enabled on your vps.
That command is unknown:

vps: sudo yum install php-pecl-apc
sudo: yum: command not found

butch2k

What distro is it ? I thought it was a RedHat one thanks to the kernel name but it looks like i was wrong or you are not authorized to install new packages  :(

Advertisement: