Advertisement:

Author Topic: Server LOad: Long running mysql queries and PHP script top in list  (Read 7374 times)

Offline desquinn

  • Semi-Newbie
  • *
  • Posts: 33
I received the below text from my hosts. Small excerpts of the log info below and full info in the attached file.

Quote
Hi Des,
This site has had to be suspended due to it causing continuous load spikes in the server due to long running mysql queries. Also your script stays on top in the cpu users list all the time.
---------------
user-name 24250  0.0  0.0  3168 1024 ?        SN   09:33   0:00  |   \_ /bin/bash php-script
user-name 24251  0.1  0.3 31736 15920 ?       SN   09:33   0:00  |       \_ /usr/bin/php-cgi

.......
| 103094 | user_account  | localhost:50487 | domain_name_-_forum | Query   | 343  | Copying to tmp table | SELECT
                        HOUR(FROM_UNIXTIME(posterTime + 0)) AS hour,
                        COUNT(*) AS postCount
                FROM smf_messages
 |
| 103148 | user_account  | localhost:50650 | domain_name_-_forum | Query   | 290  | Locked               | UPDATE smf_messages
                SET ID_MSG_MODIFIED = 476524
                WHERE ID_MSG = 476524                           |
| 103160 | user_account  | localhost:50700 | domain_name_-_forum | Query   | 288  | Locked               | SELECT
                                m.ID_MSG, m.icon, m.subject, m.posterTime, m.posterIP, m.ID_MEMBER, m.modifiedTime, m.mod |
| 103161 | user_account  | localhost:50701 | domain_name_-_forum | Query   | 289  | Locked               | SELECT ID_MSG, ID_MEMBER
                FROM smf_messages
                WHERE ID_TOPIC = 19963
                ORDER BY ID_MSG
                LIMIT 0,  |

......

SMF is 1.1.11 and the mods installed are listed below:

Quote
Mod Name     Version     
1.    vReportBoard Mod    1.03    [ Uninstall ]
2.    SMF 1.0.11 / 1.1.3 Update    1.1    [ Uninstall ]
3.    SMF 1.0.12 / 1.1.4 / 2.0 b1.1 Update    1.0    [ Uninstall ]
4.    Board Notes    1.0.7    [ Uninstall ]
5.    SMF 1.0.13 / 1.1.5 / 2.0 b3.1 Update    1.0    [ Uninstall ]
6.    Modbreaktag    1.0    [ Uninstall ]
7.    SMF 1.0.14 / 1.1.6 Update    1.0    [ Uninstall ]
8.    SMF 1.0.15 / 1.1.7 Update    1.0    [ Uninstall ]
9.    reCAPTCHA for SMF    0.9.5.3    [ Uninstall ]
10.    SMF 1.0.16 / 1.1.8 Update    1.0    [ Uninstall ]
11.    SMF 1.0.17 / 1.1.9 / 2.0 RC1 Update    1.0    [ Uninstall ]
12.    SMF 1.0.18 / 1.1.10 / 2.0 RC1-2 Update    1.1    [ Uninstall ]
13.    SMF 1.0.19 / 1.1.11 Update    1.0    [ Uninstall ]
14.    Stop Spammer    1.0    [ Uninstall ]

Forum stats are below:

Quote
Total Members:     6453
Total Posts:    455827
Total Topics:    27415
Total Categories:    3
Users Online:    1
Most Online:    196 - 02/07/07 20:27
Online Today:    37
Total page views:    10476363

I am looking to remove some of the mods to see if that will help but does anyone have any clues as to why this is happening? Is there anything I can do to limit the database impact or optimise things. Are these queries ok or are they suspect?

Site was suspended but I have it back online albeit with the forum in maintenance mode with only my IP address able to access it. I had a full text search index in place but have now clicked the button to force its use.

Put the auto optimise to require 5 people or less on the board before it runs. Looking for other ideas as I could do with having the charity's forum back online.

Thanks in advance for looking at this.


Regards
Des Quinn
w:  www.fmauk.org

Offline Ensiferous

  • Full Member
  • ***
  • Posts: 601
  • Gender: Male
    • Nginx, PHP and Site Management
Re: Server LOad: Long running mysql queries and PHP script top in list
« Reply #1 on: February 09, 2010, 12:31:20 PM »
Full text search is not good enough for that size. You will want a large custom index. Other than that you might want to update to SMF 2 soon as that brought significant improvements to queries used.

You can also speed up the site by selectively converting some tables to InnoDB. Besides that, you are really nearing a point where shared hosting might be too small for you.
My Latest Blog Post: Debugging Nginx Errors

Offline _Ziggy_

  • Jr. Member
  • **
  • Posts: 300
    • Bluesforum.com
Re: Server LOad: Long running mysql queries and PHP script top in list
« Reply #2 on: February 09, 2010, 02:47:39 PM »
Besides that, you are really nearing a point where shared hosting might be too small for you.

I had to switch to VPS much earlier.
I think 6500 members and 450.000 is a large forum for shared hosting!
Bluesforum.com   2.0                     Bluesforum.nl   2.0
Rockabilly-forum.com   2.0              Bluesharp.nl   2.0
Bungalowpark-forum.nl   2.0        Eee pad forum   2.01
Cristiano Ronaldo   2.02              Lockout Tagout   2.02


Looking to buy existing forums, send pm.

Offline desquinn

  • Semi-Newbie
  • *
  • Posts: 33
Re: Server LOad: Long running mysql queries and PHP script top in list
« Reply #3 on: February 09, 2010, 03:38:09 PM »
I think we have quite a lot of inactive members and that post count is from August 2006 and I would have loved to Archive things off to another instance or something to keep the live board a little nimbler and smaller in size.

Our average on-line figure is 46 but I must admit there does not seem to be much change in any of our stats from last month or the month before that yet we are having problems now.

Our Innodb setting from status.php says disabled but I think support can enable it.

Offline Something like that

  • SMF Friend
  • SMF Hero
  • *
  • Posts: 2,496
  • Gender: Male
  • ]
Re: Server LOad: Long running mysql queries and PHP script top in list
« Reply #4 on: February 09, 2010, 04:47:51 PM »
It's not just the number of users online, but also the size of the database when it comes to the limit of shared hosting. I would say you're pushing the limit now.

The conversion to InnoDB will not help you much with only 50 users online. It will slow you down if your server is not correctly configured with InnoDB, too (most shared hosts will have a ridiculously small/default innodb_buffer_pool_size of 8M, where 1G is more reasonable on a big shared-hosting box).

A VPS with 512 MB of RAM is your next stop.

In the meantime, make sure you have a custom avatar direct set (see the thread in this forum) and disable the calendar.

Offline desquinn

  • Semi-Newbie
  • *
  • Posts: 33
Re: Server LOad: Long running mysql queries and PHP script top in list
« Reply #5 on: February 09, 2010, 04:59:09 PM »
I have reduced the size of the DB and forum by removing older posts and followed a number of the other suggestions. I will see if that helps the situation and thanks for the help so far.

The site is a charity site and funds are non existent. I am already supporting them as it is but I may have to see about a VPS. I have a windows one for paying clients but not too sure about splitting the linux hosting and the forum over 2 servers  but hey ho ... the fun and games of hosting :)

Offline Something like that

  • SMF Friend
  • SMF Hero
  • *
  • Posts: 2,496
  • Gender: Male
  • ]
Re: Server LOad: Long running mysql queries and PHP script top in list
« Reply #6 on: February 09, 2010, 05:04:50 PM »
If you host your theme content (pictures, icons, etc) on the Windows server, you could reduce the Apache/memory usage somewhat on your current machine. It won't reduce the CPU time much though.