SMF Tables causing high CPU Usage

[lonrot]

Hello, I'm running a VPS with virtuozzo.

I have had terrible performance these days. And I haven't found a solution. My site address is www.consolemaniak.com

Check the ticket reply my hosting support has given to me.

Hello,

We have checked your VPS and found that your mysql is taking high CPU resources on the server, please check the attached logs for the same. Please try to tune your mysql configuration and do let us know if you need any assistance on this.
Thank you.

This is the log:

Code Select Expand

| Id  | User                 | Host            | db                     | Command | Time | State | Info             |
+-----+----------------------+-----------------+------------------------+---------+------+-------+------------------+
| 24  | consolem_console     | localhost       | consolem_consolemaniak | Sleep   | 7    |       |                  |
| 43  | consolem_console     | localhost       | consolem_consolemaniak | Sleep   | 13   |       |                  |
| 64  | consolem_console     | localhost       | consolem_consolemaniak | Sleep   | 27   |       |                  |
| 72  | consolem_console     | localhost       | consolem_consolemaniak | Sleep   | 23   |       |                  |
| 75  | consolem_console     | localhost       | consolem_consolemaniak | Sleep   | 7    |       |                  |
| 76  | consolem_console     | localhost       | consolem_consolemaniak | Sleep   | 0    |       |                  |
| 77  | consolem_console     | localhost       | consolem_consolemaniak | Sleep   | 22   |       |                  |
| 78  | consolem_console     | localhost       | consolem_consolemaniak | Sleep   | 2    |       |                  |
| 79  | consolem_console     | localhost       | consolem_consolemaniak | Sleep   | 31   |       |                  |
| 82  | consolem_console     | localhost       | consolem_consolemaniak | Sleep   | 46   |       |                  |
| 83  | consolem_console     | localhost       | consolem_consolemaniak | Sleep   | 34   |       |                  |
| 87  | consolem_console     | localhost       | consolem_consolemaniak | Sleep   | 12   |       |                  |
| 90  | consolem_console     | localhost       | consolem_consolemaniak | Sleep   | 0    |       |                  |
| 91  | consolem_console     | localhost       | consolem_consolemaniak | Sleep   | 42   |       |                  |
| 92  | consolem_console     | localhost       | consolem_consolemaniak | Sleep   | 38   |       |                  |
| 93  | consolem_console     | localhost       | consolem_consolemaniak | Sleep   | 44   |       |                  |
| 94  | consolem_console     | localhost       | consolem_consolemaniak | Sleep   | 0    |       |                  |
| 96  | consolem_console     | localhost       | consolem_consolemaniak | Sleep   | 30   |       |                  |
| 97  | consolem_console     | localhost       | consolem_consolemaniak | Sleep   | 9    |       |                  |
| 98  | consolem_console     | localhost       | consolem_consolemaniak | Sleep   | 1    |       |                  |
| 99  | consolem_console     | localhost       | consolem_consolemaniak | Sleep   | 0    |       |                  |
| 100 | consolem_console     | localhost       | consolem_consolemaniak | Sleep   | 0    |       |                  |
| 101 | consolem_console     | localhost       | consolem_consolemaniak | Sleep   | 1    |       |                  |
| 102 | consolem_console     | localhost       | consolem_consolemaniak | Sleep   | 1    |       |                  |
| 103 | consolem_console     | localhost       | consolem_consolemaniak | Sleep   | 2    |       |                  |
| 107 | consolem_console     | localhost       | consolem_consolemaniak | Sleep   | 10   |       |                  |
| 108 | unauthenticated user | connecting host |                        | Connect |      | login |                  |
| 109 | root                 | localhost       |                        | Query   | 0    |       | show processlist |
+-----+----------------------+-----------------+------------------------+---------+------+-------+------------------+

+-----+----------------------+-----------------+------------------------+---------+------+----------------+---------------------------------------------+
| Id  | User                 | Host            | db                     | Command | Time | State          | Info                                        |
+-----+----------------------+-----------------+------------------------+---------+------+----------------+---------------------------------------------+
| 24  | consolem_console     | localhost       | consolem_consolemaniak | Sleep   | 10   |                |                                             |
| 43  | consolem_console     | localhost       | consolem_consolemaniak | Sleep   | 16   |                |                                             |
| 64  | consolem_console     | localhost       | consolem_consolemaniak | Sleep   | 30   |                |                                             |
| 72  | consolem_console     | localhost       | consolem_consolemaniak | Sleep   | 26   |                |                                             |
| 75  | consolem_console     | localhost       | consolem_consolemaniak | Sleep   | 1    |                |                                             |
| 76  | consolem_console     | localhost       | consolem_consolemaniak | Sleep   | 0    |                |                                             |
| 77  | consolem_console     | localhost       | consolem_consolemaniak | Sleep   | 1    |                |                                             |
| 78  | consolem_console     | localhost       | consolem_consolemaniak | Sleep   | 0    |                |                                             |
| 79  | consolem_console     | localhost       | consolem_consolemaniak | Sleep   | 34   |                |                                             |
| 82  | consolem_console     | localhost       | consolem_consolemaniak | Sleep   | 49   |                |                                             |
| 83  | consolem_console     | localhost       | consolem_consolemaniak | Sleep   | 37   |                |                                             |
| 87  | consolem_console     | localhost       | consolem_consolemaniak | Query   | 0    | Writing to net | SELECT variable, value
                        FROM smf_settings |
| 90  | consolem_console     | localhost       | consolem_consolemaniak | Sleep   | 2    |                |                                             |
| 91  | consolem_console     | localhost       | consolem_consolemaniak | Sleep   | 45   |                |                                             |
| 92  | consolem_console     | localhost       | consolem_consolemaniak | Sleep   | 41   |                |                                             |
| 93  | consolem_console     | localhost       | consolem_consolemaniak | Sleep   | 47   |                |                                             |
| 94  | consolem_console     | localhost       | consolem_consolemaniak | Sleep   | 3    |                |                                             |
| 96  | consolem_console     | localhost       | consolem_consolemaniak | Sleep   | 33   |                |                                             |
| 97  | consolem_console     | localhost       | consolem_consolemaniak | Sleep   | 12   |                |                                             |
| 98  | consolem_console     | localhost       | consolem_consolemaniak | Sleep   | 4    |                |                                             |
| 99  | consolem_console     | localhost       | consolem_consolemaniak | Sleep   | 1    |                |                                             |
| 100 | consolem_console     | localhost       | consolem_consolemaniak | Sleep   | 1    |                |                                             |
| 101 | consolem_console     | localhost       | consolem_consolemaniak | Sleep   | 4    |                |                                             |
| 102 | consolem_console     | localhost       | consolem_consolemaniak | Sleep   | 4    |                |                                             |
| 103 | consolem_console     | localhost       | consolem_consolemaniak | Sleep   | 5    |                |                                             |
| 107 | consolem_console     | localhost       | consolem_consolemaniak | Sleep   | 13   |                |                                             |
| 108 | unauthenticated user | connecting host |                        | Connect |      | login          |                                             |
| 109 | root                 | localhost       |                        | Query   | 0    |                | show processlist                            |
| 110 | unauthenticated user | connecting host |                        | Connect |      | login          |                                             |
| 111 | consolem_console     | localhost       | consolem_consolemaniak | Sleep   | 1    |                |                                             |
+-----+----------------------+-----------------+------------------------+---------+------+----------------+---------------------------------------------+

+-----+----------------------+-----------------+------------------------+---------+------+----------------+------------------------------------------------------------------------------------------------------+
| Id  | User                 | Host            | db                     | Command | Time | State          | Info                                                                                                 |
+-----+----------------------+-----------------+------------------------+---------+------+----------------+------------------------------------------------------------------------------------------------------+
| 24  | consolem_console     | localhost       | consolem_consolemaniak | Sleep   | 12   |                |                                                                                                      |
| 43  | consolem_console     | localhost       | consolem_consolemaniak | Sleep   | 18   |                |                                                                                                      |
| 64  | consolem_console     | localhost       | consolem_consolemaniak | Sleep   | 32   |                |                                                                                                      |
| 72  | consolem_console     | localhost       | consolem_consolemaniak | Sleep   | 28   |                |                                                                                                      |
| 75  | consolem_console     | localhost       | consolem_consolemaniak | Sleep   | 3    |                |                                                                                                      |
| 76  | consolem_console     | localhost       | consolem_consolemaniak | Sleep   | 2    |                |                                                                                                      |
| 77  | consolem_console     | localhost       | consolem_consolemaniak | Sleep   | 3    |                |                                                                                                      |
| 78  | consolem_console     | localhost       | consolem_consolemaniak | Sleep   | 0    |                |                                                                                                      |
| 82  | consolem_console     | localhost       | consolem_consolemaniak | Sleep   | 51   |                |                                                                                                      |
| 83  | consolem_console     | localhost       | consolem_consolemaniak | Sleep   | 39   |                |                                                                                                      |
| 87  | consolem_console     | localhost       | consolem_consolemaniak | Query   | 2    | Sorting result | SELECT t.ID_FIRST_MSG
                FROM (smf_topics as t, smf_boards as b)
                WHERE t.ID_BOARD IN (56,19,26,44,2 |
| 90  | consolem_console     | localhost       | consolem_consolemaniak | Sleep   | 4    |                |                                                                                                      |
| 91  | consolem_console     | localhost       | consolem_consolemaniak | Sleep   | 47   |                |                                                                                                      |
| 92  | consolem_console     | localhost       | consolem_consolemaniak | Sleep   | 43   |                |                                                                                                      |
| 93  | consolem_console     | localhost       | consolem_consolemaniak | Sleep   | 49   |                |                                                                                                      |
| 94  | consolem_console     | localhost       | consolem_consolemaniak | Sleep   | 5    |                |                                                                                                      |
| 96  | consolem_console     | localhost       | consolem_consolemaniak | Sleep   | 35   |                |                                                                                                      |
| 97  | consolem_console     | localhost       | consolem_consolemaniak | Sleep   | 0    |                |                                                                                                      |
| 98  | consolem_console     | localhost       | consolem_consolemaniak | Sleep   | 6    |                |                                                                                                      |
| 99  | consolem_console     | localhost       | consolem_consolemaniak | Sleep   | 1    |                |                                                                                                      |
| 100 | consolem_console     | localhost       | consolem_consolemaniak | Sleep   | 1    |                |                                                                                                      |
| 101 | consolem_console     | localhost       | consolem_consolemaniak | Sleep   | 6    |                |                                                                                                      |
| 102 | consolem_console     | localhost       | consolem_consolemaniak | Sleep   | 6    |                |                                                                                                      |
| 103 | consolem_console     | localhost       | consolem_consolemaniak | Sleep   | 7    |                |                                                                                                      |
| 107 | consolem_console     | localhost       | consolem_consolemaniak | Sleep   | 15   |                |                                                                                                      |
| 108 | consolem_console     | localhost       | consolem_consolemaniak | Query   | 2    | Sending data   | SELECT variable, value, ID_MEMBER, ID_THEME
                        FROM smf_themes
                        WHERE ID_MEMBER IN (-1, 0, -1)
                         |
| 109 | root                 | localhost       |                        | Query   | 0    |                | show processlist                                                                                     |
| 110 | unauthenticated user | connecting host |                        | Connect |      | login          |                                                                                                      |
| 111 | consolem_console     | localhost       | consolem_consolemaniak | Sleep   | 3    |                |                                                                                                      |
+-----+----------------------+-----------------+------------------------+---------+------+----------------+------------------------------------------------------------------------------------------------------+

+-----+----------------------+-----------------+------------------------+---------+------+--------------+------------------------------------------------------------------------------------------------------+
| Id  | User                 | Host            | db                     | Command | Time | State        | Info                                                                                                 |
+-----+----------------------+-----------------+------------------------+---------+------+--------------+------------------------------------------------------------------------------------------------------+
| 24  | consolem_console     | localhost       | consolem_consolemaniak | Sleep   | 14   |              |                                                                                                      |
| 43  | consolem_console     | localhost       | consolem_consolemaniak | Sleep   | 20   |              |                                                                                                      |
| 64  | consolem_console     | localhost       | consolem_consolemaniak | Sleep   | 34   |              |                                                                                                      |
| 72  | consolem_console     | localhost       | consolem_consolemaniak | Sleep   | 30   |              |                                                                                                      |
| 75  | consolem_console     | localhost       | consolem_consolemaniak | Sleep   | 5    |              |                                                                                                      |
| 76  | consolem_console     | localhost       | consolem_consolemaniak | Sleep   | 0    |              |                                                                                                      |
| 77  | consolem_console     | localhost       | consolem_consolemaniak | Sleep   | 5    |              |                                                                                                      |
| 78  | consolem_console     | localhost       | consolem_consolemaniak | Sleep   | 0    |              |                                                                                                      |
| 82  | consolem_console     | localhost       | consolem_consolemaniak | Sleep   | 53   |              |                                                                                                      |
| 87  | consolem_console     | localhost       | consolem_consolemaniak | Sleep   | 0    |              |                                                                                                      |
| 90  | consolem_console     | localhost       | consolem_consolemaniak | Sleep   | 6    |              |                                                                                                      |
| 92  | consolem_console     | localhost       | consolem_consolemaniak | Sleep   | 45   |              |                                                                                                      |
| 93  | consolem_console     | localhost       | consolem_consolemaniak | Sleep   | 51   |              |                                                                                                      |
| 94  | consolem_console     | localhost       | consolem_consolemaniak | Sleep   | 7    |              |                                                                                                      |
| 96  | consolem_console     | localhost       | consolem_consolemaniak | Sleep   | 37   |              |                                                                                                      |
| 97  | consolem_console     | localhost       | consolem_consolemaniak | Sleep   | 2    |              |                                                                                                      |
| 98  | consolem_console     | localhost       | consolem_consolemaniak | Sleep   | 8    |              |                                                                                                      |
| 99  | consolem_console     | localhost       | consolem_consolemaniak | Sleep   | 3    |              |                                                                                                      |
| 100 | consolem_console     | localhost       | consolem_consolemaniak | Sleep   | 3    |              |                                                                                                      |
| 101 | consolem_console     | localhost       | consolem_consolemaniak | Sleep   | 8    |              |                                                                                                      |
| 102 | consolem_console     | localhost       | consolem_consolemaniak | Sleep   | 8    |              |                                                                                                      |
| 103 | consolem_console     | localhost       | consolem_consolemaniak | Sleep   | 9    |              |                                                                                                      |
| 107 | consolem_console     | localhost       | consolem_consolemaniak | Sleep   | 17   |              |                                                                                                      |
| 108 | consolem_console     | localhost       | consolem_consolemaniak | Query   | 4    | Sending data | SELECT variable, value, ID_MEMBER, ID_THEME
                        FROM smf_themes
                        WHERE ID_MEMBER IN (-1, 0, -1)
                         |
| 109 | root                 | localhost       |                        | Query   | 0    |              | show processlist                                                                                     |
| 110 | unauthenticated user | connecting host |                        | Connect |      | login        |                                                                                                      |
| 111 | consolem_console     | localhost       | consolem_consolemaniak | Sleep   | 5    |              |                                                                                                      |
+-----+----------------------+-----------------+------------------------+---------+------+--------------+------------------------------------------------------------------------------------------------------+

[karlbenson]

Well to me the log is useless. other than showing the length of time.
Its not actually showing most of the queries which were being ran. (If it were we could say which ones were causing it)
The queries that are shown are minor ones. (that should be very very very quick).

Try the performance topics at the top of the forum.
What mods do you have installed. Things that constantly update like Chatrooms/Shoutboxes use a ton of queries.

[metallica48423]

one of the stickies in this board has a file called status.php attached to it -- could you download it and upload it to your forum, and link us to it?  Thanks

[lonrot]

http://consolemaniak.com/status.php

The current SMF directory is a clean one (no mods) since using the original will only end up as a server crash...

[Aleksi "Lex" Kilpinen]

Just a few things I've noticed can help a lot:

Threads_cached: 0
Threads_connected: 1
Threads_created: 12944

You could benefit from a thread cache, even a small one, so that everytime MySQL needs a new thread, it won't have to create one, but just reuse a cached thread.

Also, you have Query cache enabled, but no memory for it.  query_cache_size: 0
So assigning some memory for query cache, could lighten the load, as the database server wouldn't actually need to run a query for every little thing, but could pull some info from memory.

As for the rest of the settings, it seems you've quite recently restarted the server?
(Uptime: 31303) so the values shown will change a lot over time, and it's hard to say much right now...

[lonrot]

Sorry guys but I'll have to ask where should I change all these parameters, have looked at the Sticky topics but can't find it.

[H]

You need to edit the thread and query settings which are probably in /etc/my.cnf.

[lonrot]

/etc is an empty folder :S

[H]

That is very strange.

What is the output of the following command at the console?

Code Select Expand

updatedb && locate my.cnf

[lonrot]

[metallica48423]

it is updatedb, not update

[lonrot]

Code Select Expand

login as: root
[email protected]'s password:
Last login: Tue May 13 03:32:42 2008 from 201.195.185.38
[root@vps1184 ~]# updatedb && locate my.cnf
^[
updatedb && locate my.cnf
/root/.my.cnf
/usr/local/Zend/etc/my.cnf.org
/usr/local/cpanel/whostmgr/my.cnf
/etc/my.cnf
/etc/my.cnf.tunned
/backup/cpbackup/weekly/files/_root_.my.cnf.gz
/backup/cpbackup/weekly/files/_etc_my.cnf.gz
/backup/cpbackup/monthly/files/_root_.my.cnf.gz
/backup/cpbackup/monthly/files/_etc_my.cnf.gz
[root@vps1184 ~]# updatedb && locate my.cnf
/root/.my.cnf
/usr/local/Zend/etc/my.cnf.org
/usr/local/cpanel/whostmgr/my.cnf
/etc/my.cnf
/etc/my.cnf.tunned
/backup/cpbackup/weekly/files/_root_.my.cnf.gz
/backup/cpbackup/weekly/files/_etc_my.cnf.gz
/backup/cpbackup/monthly/files/_root_.my.cnf.gz
/backup/cpbackup/monthly/files/_etc_my.cnf.gz
[root@vps1184 ~]# updatedb && locate my.cnf
/root/.my.cnf
/usr/local/Zend/etc/my.cnf.org
/usr/local/cpanel/whostmgr/my.cnf
/etc/my.cnf
/etc/my.cnf.tunned
/backup/cpbackup/weekly/files/_root_.my.cnf.gz
/backup/cpbackup/weekly/files/_etc_my.cnf.gz
/backup/cpbackup/monthly/files/_root_.my.cnf.gz
/backup/cpbackup/monthly/files/_etc_my.cnf.gz
[root@vps1184 ~]# updatedb && locate my.cnf
/root/.my.cnf
/usr/local/Zend/etc/my.cnf.org
/usr/local/cpanel/whostmgr/my.cnf
/etc/my.cnf
/etc/my.cnf.tunned
/backup/cpbackup/weekly/files/_root_.my.cnf.gz
/backup/cpbackup/weekly/files/_etc_my.cnf.gz
/backup/cpbackup/monthly/files/_root_.my.cnf.gz
/backup/cpbackup/monthly/files/_etc_my.cnf.gz
[root@vps1184 ~]#
/root/.my.cnf
/usr/local/Zend/etc/my.cnf.org
/usr/local/cpanel/whostmgr/my.cnf
/etc/my.cnf
/etc/my.cnf.tunned
/backup/cpbackup/weekly/files/_root_.my.cnf.gz
/backup/cpbackup/weekly/files/_etc_my.cnf.gz
/backup/cpbackup/monthly/files/_root_.my.cnf.gz
/backup/cpbackup/monthly/files/_etc_my.cnf.gz
[root@vps1184 ~]# /root/.my.cnf
-bash: /root/.my.cnf: Permission denied
[root@vps1184 ~]# /usr/local/Zend/etc/my.cnf.org
-bash: /usr/local/Zend/etc/my.cnf.org: Permission denied
[root@vps1184 ~]# /usr/local/cpanel/whostmgr/my.cnf
-bash: /usr/local/cpanel/whostmgr/my.cnf: Permission denied
[root@vps1184 ~]# /etc/my.cnf
-bash: /etc/my.cnf: Permission denied
[root@vps1184 ~]# /etc/my.cnf.tunned
-bash: /etc/my.cnf.tunned: Permission denied
[root@vps1184 ~]# /backup/cpbackup/weekly/files/_root_.my.cnf.gz
-bash: /backup/cpbackup/weekly/files/_root_.my.cnf.gz: Permission denied
[root@vps1184 ~]# /backup/cpbackup/weekly/files/_etc_my.cnf.gz
-bash: /backup/cpbackup/weekly/files/_etc_my.cnf.gz: Permission denied
[root@vps1184 ~]# /backup/cpbackup/monthly/files/_root_.my.cnf.gz
-bash: /backup/cpbackup/monthly/files/_root_.my.cnf.gz: Permission denied
[root@vps1184 ~]# /backup/cpbackup/monthly/files/_etc_my.cnf.gz
-bash: /backup/cpbackup/monthly/files/_etc_my.cnf.gz: Permission denied
[root@vps1184 ~]# [root@vps1184 ~]# updatedb && locate my.cnf
-bash: [root@vps1184: command not found
[root@vps1184 ~]# /root/.my.cnf
-bash: /root/.my.cnf: Permission denied
[root@vps1184 ~]# /usr/local/Zend/etc/my.cnf.org
-bash: /usr/local/Zend/etc/my.cnf.org: Permission denied
[root@vps1184 ~]# /usr/local/cpanel/whostmgr/my.cnf
-bash: /usr/local/cpanel/whostmgr/my.cnf: Permission denied
[root@vps1184 ~]# /etc/my.cnf
-bash: /etc/my.cnf: Permission denied
[root@vps1184 ~]# /etc/my.cnf.tunned
-bash: /etc/my.cnf.tunned: Permission denied
[root@vps1184 ~]# /backup/cpbackup/weekly/files/_root_.my.cnf.gz
-bash: /backup/cpbackup/weekly/files/_root_.my.cnf.gz: Permission denied
[root@vps1184 ~]# /backup/cpbackup/weekly/files/_etc_my.cnf.gz
-bash: /backup/cpbackup/weekly/files/_etc_my.cnf.gz: Permission denied
[root@vps1184 ~]# /backup/cpbackup/monthly/files/_root_.my.cnf.gz
-bash: /backup/cpbackup/monthly/files/_root_.my.cnf.gz: Permission denied
[root@vps1184 ~]# /backup/cpbackup/monthly/files/_etc_my.cnf.gz
-bash: /backup/cpbackup/monthly/files/_etc_my.cnf.gz: Permission denied
[root@vps1184 ~]# [root@vps1184 ~]#
-bash: [root@vps1184: command not found
[root@vps1184 ~]#


[Ben_S]

That shows it's in /etc, which you previously said was emoty.

[metallica48423]

you can't just enter the file path and file name to open a file, they aren't executables.

you need to use a text viewer such as nano or vi to view the contents of the file.

[lonrot]

This is what I have managed to do:

Code Select Expand

login as: root
[email protected]'s password:
Last login: Tue May 13 14:16:35 2008 from 60.54.14.50
[root@vps1184 ~]# cd /etc/
[root@vps1184 etc]# ls
DIR_COLORS                       named.conf.fixrndc
DIR_COLORS.xterm                 named.conf.prefixrndc
X11                              nameserverips
adjtime                          netplug
aliases                          netplug.d
alternatives                     nscd.conf
antirelayddisable                nsswitch.conf
antivirus.exim                   odbc.ini
apt                              odbcinst.ini
audit.rules                      openldap
auditd.conf                      opt
authlib                          pam.d
backupmxhosts                    passwd
bashrc                           passwd,v
buildapache                      passwd-
chkserv.d                        passwd.OLD
cpanel_exim_system_filter        portsentry
cpanelsync.exclude               ppp
cpbackup.conf                    printcap
cpbackup.public.conf             profile
cpupdate.conf                    profile.d
cron.d                           proftpd
cron.daily                       proftpd.conf
cron.deny                        proftpd.conf,v
cron.hourly                      proftpd.conf.bytes
cron.monthly                     proftpd.conf.tmpeditlib
cron.weekly                      protocols
crontab                          pure-ftpd
csh.cshrc                        pure-ftpd.conf
csh.login                        pure-ftpd.pem
default                          pwdb.conf
demodomains                      quota.conf
demouids                         quota.conf,v
demousers                        quotagrpadmins
dev.d                            quotatab
domain_keys_active               rc
domainips                        rc.d
domainusers                      rc.local
entropychatdisable               rc.sysinit
environment                      rc0.d
exim                             rc1.d
exim.aclspam                     rc2.d
exim.conf                        rc3.d
exim.conf.buildtest              rc4.d
exim.conf.dist                   rc5.d
exim.conf.mailman2.dist          rc6.d
exim.conf.mailman2.exiscan.dist  redhat-release
exim.crt                         relayhosts
exim.key                         relayhostsusers
exim.pl                          remotedomains
exim.pl.local                    remotedomains,v
eximrejects                      resolv.conf
eximrejects.rpmorig              rndc.conf
eximstatsdisable                 rndc.key
exports                          rpc
fdprm                            rpm
filesystems                      samba
fonts                            screenrc
fstab                            scsi_id.config
ftpd-rsa-key.pem                 secondarymx
ftpd-rsa.pem                     secondarymx,v
gpm-root.conf                    secondarymx.rpmnew
group                            securetty
group-                           security
host.conf                        senderverifybypasshosts
hosts                            sensors.conf
hosts.allow                      services
hosts.deny                       setuptool.d
hotplug.d                        sgml
httpd                            shadow
httpd.old                        shadow,v
httpddisable.wascreatedbyea3     shadow-
init.d                           shells
initlog.conf                     skel
inittab                          skipsmtpcheckhosts
inputrc                          snmp
interchangedisable               spamddisable
ipaddrpool                       spamdisable
iproute2                         spammeripblocks
ips                              spammers
ips.dnsmaster                    ssh
issue                            ssl
issue.net                        stunnel
jwhois.conf                      sudoers
krb5.conf                        sysconfig
ld.so.cache                      sysctl.conf
ld.so.conf                       sysctl.conf-pre-kernel
ld.so.conf.d                     syslog.conf
libuser.conf                     termcap
localaliases                     trueuserdomains
localdomains                     trueuserowners
localdomains,v                   trustedmailhosts
localdomains.rpmnew              udev
localtime                        updatedb.conf
login.defs                       userbwlimits
logrotate.conf                   userdomains
logrotate.d                      userplans
lynx-site.cfg                    valiases
lynx.cfg                         vdomainaliases
lynx.cfg.cs                      vfilters
lynx.cfg.ja                      vftp
lynx.cfg.sk                      vimrc
mail                             vmail
mail.rc                          vz
mailcap                          warnquota.conf
makedev.d                        wgetrc
man.config                       wwwacct.conf
melangedisable                   wwwacct.conf.shadow
mime.types                       xinetd.conf
motd                             xinetd.d
mtab                             xml
my.cnf                           yum
my.cnf.tunned                    yum.conf
named.conf                       yum.conf.rpmnew
named.conf,v                     yum.repos.d
[root@vps1184 etc]# my.cnf
-bash: my.cnf: command not found
[root@vps1184 etc]# ./my.cnf
./my.cnf: line 1: [mysqld]: command not found
./my.cnf: line 2: set-variable: command not found
./my.cnf: line 3: safe-show-database: command not found
[root@vps1184 etc]#


[Ben_S]

You need to edit the file with a text editor such as pico/nano or vi etc. You won't stand a chance with vi so try nano /etc/my.cnf or pico /etc/my.cnf

However you really should buy a book and learn how to use your os or pay someone to manage it for you.

[SinnerSaint]

It seems that my problem is somewhat similar to lonrot. I am having site slowdowns, mostly during 1am to 2am (GMT +8) and MySQL/CPU usage hits 100%. Although sometimes the slowdown happens in a different time. My host suggested that the problem might have been caused by too many sleeping MySQL processes. Here is their reply:

Please check the mysql processlist and checking the mysql usage we could find that the database allpi3_allpi3 has been sleeping  many times when we check the server on load. This could be due to an un-optimized usage.

-bash-3.00# mysqladmin processlist
+-------+---------------+-----------+-----------+---------+------+-------+------------------+
| Id    | User          | Host      | db        | Command | Time | State | Info             |
+-------+---------------+-----------+-----------+---------+------+-------+------------------+
| 4352  | eximstats     | localhost | eximstats | Sleep   | 359  |       |                  |
| 25585 | allpi3_allpi3 | localhost |           | Sleep   | 19   |       |                  |
| 25588 | allpi3_allpi3 | localhost |           | Sleep   | 18   |       |                  |
| 25590 | allpi3_allpi3 | localhost |           | Sleep   | 18   |       |                  |
| 25597 | allpi3_allpi3 | localhost |           | Sleep   | 9    |       |                  |
| 25598 | allpi3_allpi3 | localhost |           | Sleep   | 9    |       |                  |
| 25599 | allpi3_allpi3 | localhost |           | Sleep   | 8    |       |                  |
| 25600 | allpi3_allpi3 | localhost |           | Sleep   | 8    |       |                  |
| 25603 | allpi3_allpi3 | localhost |           | Sleep   | 4    |       |                  |
| 25604 | allpi3_allpi3 | localhost |           | Sleep   | 4    |       |                  |
| 25606 | root          | localhost |           | Query   | 0    |       | show processlist |
+-------+---------------+-----------+-----------+---------+------+-------+------------------+

Since I am not yet familiar with MySQL processes, I asked them what "sleeping" means and here is their reply:

Sleeping is a state of the process, simply saying it means that the process has been called upon to be used, but is not used and hence it consumes the resources as if it would be using them if correctly worked. Since the process is not active, it is a loss of resources, as the resource used by it is not freed for other process, hope this is clear. Optimization is the technique if making the processes consume as much less resource as possible and yet work efficiently also.

Is there a way to optimize my databases or other configurations of my VPS? My forum has the following details:

• Link to my forum: http://www.allpinoystuff.net/forum/index.php
• status.php: http://www.allpinoystuff.net/status.php
• phpinfo.php: http://www.allpinoystuff.net/phpinfo.php
• This is a VPS server running on CENTOS Enterprise 4.6 i686 on virtuozzo - WHM X v3.1.0 and with guaranteed RAM of 384MB
• Number of posts: 90,435
• Average users online: 28 users over 15 minutes
• Mods installed: Users Online Today Mod 1.4.0, nneonneo's AJAX ShoutBox 1.14b, VisualWarning 1.32, Custom Profile Field Mod 3.16, Ad Managment 2.3, Global Headers Footers 1.3, SMF Sitemap 1.2.1, Karma Description Mod 2.3
• The performance issue is still the same even with the default theme
• I already have eAccelerator 0.9.5.2 installed
• I haven't converted my tables to InnoDB, although some of them might be InnoDB already
• I don't know how to tweak MySQL's settings to optimize memory usage and general efficiency. This seems to be the problem according to my host.
• I am sure that other users in the same node of my VPS are not eating up resources because we are guaranteed with specific amounts of RAM
• The last things I have done before these issues surfaced were updating nneonneo's Shoutbox to 1.14b and installing Karma Description Mod but I doubt they are the cause of this because there are no reports of similar problems in the support thread of the above mentioned Mods

[metallica48423]

well... your open vs. open number will definitely cause bottlenecks

Most likely though, the cause of your slowdown is eximstats running updating the stats, judging by the first line in your paste.

do you have access to edit my.cnf and restart MySQL?

Sleep, i believe, can also denote the state of waiting for a query to process and return its result.  eximstats runs on a daily basis depending on its setup, usually at night, and is VERY heavy, in terms of resource usage. 

[Ben_S]

Sleeping process take up bugger all resources, if you want to get rid of them disable persistant connections, but really, the resources they rake up are nothing.

Any host that thinks it's worth showing a customer sleeping processes should be avoided.

[lonrot]

It's kind of sad, but the only solution I found to fix this was moving to Invision Power Board, the site is much stable now. And the speed, well is incredible fast. :S

[SinnerSaint]

well... your open vs. open number will definitely cause bottlenecks

Most likely though, the cause of your slowdown is eximstats running updating the stats, judging by the first line in your paste.

do you have access to edit my.cnf and restart MySQL?

Sleep, i believe, can also denote the state of waiting for a query to process and return its result.  eximstats runs on a daily basis depending on its setup, usually at night, and is VERY heavy, in terms of resource usage. 

Yes I probably have access to edit my.cnf because I can access my VPS through SSH, WHM, and VZPP. I just need guidance in editing it in case it needs to be edited. I believe eximstats might be the cause because these slowdowns usually happen between 1:00 to 2:00am. What does eximstats do by the way?

Sleeping process take up bugger all resources, if you want to get rid of them disable persistant connections, but really, the resources they rake up are nothing.

Any host that thinks it's worth showing a customer sleeping processes should be avoided.

How do I disable persistent connection? In fairness to my host (SLHost.com), they have been very  cooperative regarding this issue and this has happened only after almost 1 year of hosting with them. By the way, this slowdown only happens now after midnight everyday. 2 weeks ago, it happens almost all of the time and my host managed to fix it somehow by recompiling Apache and tweaking MySQL. Now they cannot see any possible cause of the slowdown except for the sleeping processes and opened vs. open tables. Is there a way to resolve this issue?

Here is their latest email to me regarding last night's slowdown:

Your server was having a bit load and it was seen as caused by Mysql and httpd services. We have optimized the settings of these services to the maximum . Please try to optimize the database setting as we have mentioned in the previous tickets. Nothing else we can see here as a cause for this.
============================================================================
-bash-3.00# w
02:20:40 up 5 days,  6:31,  0 users,  load average: 0.09, 0.10, 0.19
USER     TTY      FROM              LOGIN@   IDLE   JCPU   PCPU WHAT

============================================================================


--------------

[metallica48423]

you need to raise the size of the table_cache, that should help with open vs opened.

As i already said.  likely this is a recurring process that runs every night, like eximstats does.  exim's log rotation is a VERY VERY heavy server intense process.  this wouldn't be SMF's doing, but rather eximstats using up resources around then.

[SinnerSaint]

What would you recommend to be the optimal table_cache value for my site? How can I change it's value? Can I change it using WHM or do I need to use SSH?

Regarding eximstats, can I disable this service or is it an essential Linux service? If it is safe to remove or disable this service, how can I do it? I'm sorry for asking too many questions, I'm not yet familiar with Linux and MySQL commands.

[metallica48423]

you have to locate the my.cnf file which is the mysql configuration file.

as for the amount, it depends on your server's memory.  Im not experienced heavily in mysql optimization though, so lets stick with the fact that it needs to be more, probably a good bit more, than what it is now.

You may be able to change it in WHM, i do not know. 

eximstats is a service which handles stats/logs for exim, i believe, which is a mailer daemon

I'm not certain if its an essential service or not.  I'll leave it to someone who knowsm ore about it.

[SinnerSaint]

Thanks metallica for the help.

Currently, if I'm correct, the table_cache value of my server is 1. Can I safely increase this to the number of tables of my SMF Database or to a much higher value? My server's memory is 384MB.

[metallica48423]

can you verify what it says in my.cnf?  If so, that is extremely low?  why would it be giving so little memory.  Try 32MB or 64MB first.

[vbgamer45]

table_cache bump that up to at least 256 for your fourm. That is how many tables mysql can have in memory at a time without having to open them from the disk. After you make a change in my.cnf you need to restart mysql.

[SinnerSaint]

Here is my current my.cnf file:

[mysqld]
port            = 3306
socket          = /var/lib/mysql/mysql.sock
skip-locking
key_buffer = 12M
sort_buffer_size = 12M
max_allowed_packet = 1M
table_cache = 16
net_buffer_length = 1M
thread_stack = 512K
max_user_connections=150
max_connections=800
server-id       = 1
old-passwords = 1
query-cache-type = 1
query-cache-size = 20M

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash
"/etc/my.cnf" 34L, 549C                                       1,1           Top

Can you please help me optimize it by highlighting which settings should I change? By the way, I also don't know how to save the file in VI once I'm done editing it. I'm using Putty 0.58, do I have to use another SSH client?

[vbgamer45]

Are you on a shared hosting? Or do you have your own dedicated server. If it is a dedicated server what kind of hardware and stats.

[metallica48423]

table_cache = 16

change to

table_cache = 256MB

also i suggest using nano if it is available rather than vi to edit.  vi is a very advanced, and thus difficult to use, editor.

[vbgamer45]

Table cache is actually not a size in MB but is a whole number which is the amount of tables that mysql can keep in memory

[metallica48423]

ah, perhaps i was thinking about something else then?

[vbgamer45]

key_buffer is way too small. Bump that up to 100mb

[Ben_S]

It's kind of sad, but the only solution I found to fix this was moving to Invision Power Board, the site is much stable now. And the speed, well is incredible fast. :S

Well, sleeping processes aren't a problem, any host who suggests they are shouldn't be in the business of hosting, but regardless, if you wanted rid of sleeping processes, all you needed to do was disable persistent connections. .

[slackerpunk]

same question what is eximstats? I also notice that my server slows down before or after 12 midnight

[SinnerSaint]

@vbgamer45 here is the specs of my server and site details:

It seems that my problem is somewhat similar to lonrot. I am having site slowdowns, mostly during 1am to 2am (GMT +8) and MySQL/CPU usage hits 100%. Although sometimes the slowdown happens in a different time. My host suggested that the problem might have been caused by too many sleeping MySQL processes. Here is their reply:

Please check the mysql processlist and checking the mysql usage we could find that the database allpi3_allpi3 has been sleeping  many times when we check the server on load. This could be due to an un-optimized usage.

-bash-3.00# mysqladmin processlist
+-------+---------------+-----------+-----------+---------+------+-------+------------------+
| Id    | User          | Host      | db        | Command | Time | State | Info             |
+-------+---------------+-----------+-----------+---------+------+-------+------------------+
| 4352  | eximstats     | localhost | eximstats | Sleep   | 359  |       |                  |
| 25585 | allpi3_allpi3 | localhost |           | Sleep   | 19   |       |                  |
| 25588 | allpi3_allpi3 | localhost |           | Sleep   | 18   |       |                  |
| 25590 | allpi3_allpi3 | localhost |           | Sleep   | 18   |       |                  |
| 25597 | allpi3_allpi3 | localhost |           | Sleep   | 9    |       |                  |
| 25598 | allpi3_allpi3 | localhost |           | Sleep   | 9    |       |                  |
| 25599 | allpi3_allpi3 | localhost |           | Sleep   | 8    |       |                  |
| 25600 | allpi3_allpi3 | localhost |           | Sleep   | 8    |       |                  |
| 25603 | allpi3_allpi3 | localhost |           | Sleep   | 4    |       |                  |
| 25604 | allpi3_allpi3 | localhost |           | Sleep   | 4    |       |                  |
| 25606 | root          | localhost |           | Query   | 0    |       | show processlist |
+-------+---------------+-----------+-----------+---------+------+-------+------------------+

Since I am not yet familiar with MySQL processes, I asked them what "sleeping" means and here is their reply:

Sleeping is a state of the process, simply saying it means that the process has been called upon to be used, but is not used and hence it consumes the resources as if it would be using them if correctly worked. Since the process is not active, it is a loss of resources, as the resource used by it is not freed for other process, hope this is clear. Optimization is the technique if making the processes consume as much less resource as possible and yet work efficiently also.

Is there a way to optimize my databases or other configurations of my VPS? My forum has the following details:

• Link to my forum: http://www.allpinoystuff.net/forum/index.php
• status.php: http://www.allpinoystuff.net/status.php
• phpinfo.php: http://www.allpinoystuff.net/phpinfo.php
• This is a VPS server running on CENTOS Enterprise 4.6 i686 on virtuozzo - WHM X v3.1.0 and with guaranteed RAM of 384MB
• Number of posts: 90,435
• Average users online: 28 users over 15 minutes
• Mods installed: Users Online Today Mod 1.4.0, nneonneo's AJAX ShoutBox 1.14b, VisualWarning 1.32, Custom Profile Field Mod 3.16, Ad Managment 2.3, Global Headers Footers 1.3, SMF Sitemap 1.2.1, Karma Description Mod 2.3
• The performance issue is still the same even with the default theme
• I already have eAccelerator 0.9.5.2 installed
• I haven't converted my tables to InnoDB, although some of them might be InnoDB already
• I don't know how to tweak MySQL's settings to optimize memory usage and general efficiency. This seems to be the problem according to my host.
• I am sure that other users in the same node of my VPS are not eating up resources because we are guaranteed with specific amounts of RAM
• The last things I have done before these issues surfaced were updating nneonneo's Shoutbox to 1.14b and installing Karma Description Mod but I doubt they are the cause of this because there are no reports of similar problems in the support thread of the above mentioned Mods

[青山 素子]

same question what is eximstats? I also notice that my server slows down before or after 12 midnight

eximstats(8) manpage

It parses mail log files to create statistics on the mail server usage. Since we're talking about heavy text parsing and calculations, it will use a lot of resources. It should probably be launched with a high positive nice value so it doesn't affect normal server use (it'll run slower as the tradeoff).

More than likely, it is causing some heavy loads and is the root cause of the problems.

[SinnerSaint]

I just wanna summarize all your suggestions and ask your advices one more time if I'm good to go:

table_cache - bump that up to at least 256
key_buffer - bump that up to 100mb

Is this all I need to edit now? How about the other values in my.cnf, do I just retain them or increase also proportionally to the new values of table_cache and key_buffer? Here is my current my.cnf:

[mysqld]
port            = 3306
socket          = /var/lib/mysql/mysql.sock
skip-locking
key_buffer = 12M
sort_buffer_size = 12M
max_allowed_packet = 1M
table_cache = 16
net_buffer_length = 1M
thread_stack = 512K
max_user_connections=150
max_connections=800
server-id       = 1
old-passwords = 1
query-cache-type = 1
query-cache-size = 20M

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash
"/etc/my.cnf" 34L, 549C                                       1,1           Top

By the way, I already disabled eximstats and it seems that my forum is working fine now, except that I was not able to monitor its speed last time because I was asleep.

[Ben_S]

I'd start over completely, unless you have 30GB ram, that config is way way way wrong. The best thing to do is start reading up on MySQL optimization, your sort buffer is a per connection setting, 30 connections and your looking using upto 360MB of ram there alone.

Have a look at my the sample files that come with MySQL (my-small, my-large etc) and based off of them.

[slackerpunk]

I'd start over completely, unless you have 30GB ram, that config is way way way wrong. The best thing to do is start reading up on MySQL optimization, your sort buffer is a per connection setting, 30 connections and your looking using upto 360MB of ram there alone.

Have a look at my the sample files that come with MySQL (my-small, my-large etc) and based off of them.

where can I find your sample files

[SinnerSaint]

Thanks Ben. I will try to compare my settings to the default values provided by MySQL and from there perhaps I can customize it according to my requirements. I'll just post my proposed settings here before editing my.cnf just to check if they are correct.

[Ben_S]

Sorry, meant the sample files that come with MySQL.

Usually they will be on your server, try the following command

locate my-