VPS Help Needed - Slow posting but sites fast

Michael

Active member
Hey everyone,

I am having a few issues with my site at the moment, for the past couple of weeks the sites been fast, loading threads is quick, viewing the homepage, posting profile posts, everything seems speedy. The only problem is when you post it will lag and take up to 20 seconds for the post to go through. I have tried tweaking running mysqltuner but theres some things mentioned I am not sure about at the end of it:

Code:
-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.5.28-cll
[!!] Switch to 64-bit OS - MySQL cannot currently use all of your RAM
 
-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 61M (Tables: 84)
[--] Data in InnoDB tables: 171M (Tables: 156)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[--] Data in MEMORY tables: 5M (Tables: 3)
[!!] Total fragmented tables: 41
 
-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned
 
-------- Performance Metrics -------------------------------------------------
[--] Up for: 2d 14h 41m 44s (2M q [10.564 qps], 180K conn, TX: 13B, RX: 674M)
[--] Reads / Writes: 60% / 40%
[--] Total buffers: 672.0M global + 10.4M per thread (50 max threads)
[OK] Maximum possible memory usage: 1.2G (39% of installed RAM)
[OK] Slow queries: 0% (22/2M)
[OK] Highest usage of available connections: 24% (12/50)
[OK] Key buffer size / total MyISAM indexes: 64.0M/56.5M
[OK] Key buffer hit rate: 99.8% (12M cached / 19K reads)
[OK] Query cache efficiency: 41.8% (529K cached / 1M selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (20 temp sorts / 85K sorts)
[!!] Temporary tables created on disk: 38% (9K on disk / 25K total)
[OK] Thread cache hit rate: 99% (12 created / 180K connections)
[OK] Table cache hit rate: 25% (399 open / 1K opened)
[OK] Open file limit used: 25% (256/1K)
[OK] Table locks acquired immediately: 99% (2M immediate / 2M locks)
[OK] InnoDB data size / buffer pool: 171.2M/256.0M
 
-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Temporary table size is already large - reduce result set size
    Reduce your SELECT DISTINCT queries without LIMIT clauses

The part I do not know about is the temporary table size is large already - reduce result set size.

Here is my my.cnf:

Code:
[mysqld]
table_open_cache = 400
query_cache_limit=10M
thread_cache_size=12
read_buffer_size=3M
max_allowed_packet=15M
tmp_table_size=256M
sort_buffer_size=3M
join_buffer_size=4M
key_buffer_size=64M
myisam_sort_buffer_size=32M
query_cache_size=80M
max_heap_table_size=256M
max_connections=50
innodb_buffer_pool_size=256M
innodb_file_per_table=1
default-storage-engine=MyISAM
log-slow-queries

I would be very grateful if someone took at look at this data and helped me sort it out with any changes that are needed.

Thank you :)

Slow log posted below.
 
And this is my slow log:

Code:
# Time: 121228  3:38:34
# User@Host: database_name[database_name] @ localhost []
# Query_time: 16.871512  Lock_time: 0.000041 Rows_sent: 0  Rows_examined: 1
use database_name;
SET timestamp=1356655114;
UPDATE `xf_session` SET `expiry_date` = '1356658697' WHERE (session_id = '9ba8b8e49906f2d0fc1ba6c45b50d123');
# User@Host: database_name[database_name] @ localhost []
# Query_time: 17.244062  Lock_time: 0.000068 Rows_sent: 1  Rows_examined: 1
SET timestamp=1356655114;
SELECT node.*, forum.*
                ,
                permission.cache_value AS node_permission_cache,
                    NULL AS forum_read_date
            FROM xf_forum AS forum
            INNER JOIN xf_node AS node ON (node.node_id = forum.node_id)
         
                LEFT JOIN xf_permission_cache_content AS permission
                    ON (permission.permission_combination_id = 1
                        AND permission.content_type = 'node'
                        AND permission.content_id = forum.node_id)
            WHERE node.node_id = '7';
# Time: 121228  3:40:00
# User@Host: database_name[database_name] @ localhost []
# Query_time: 41.459408  Lock_time: 0.000000 Rows_sent: 0  Rows_examined: 0
SET timestamp=1356655200;
UPDATE `xf_thread_user_post` SET `post_count` = '2' WHERE (thread_id = 25135 AND user_id = 4206);
# User@Host: database_name[database_name] @ localhost []
# Query_time: 41.453627  Lock_time: 0.000000 Rows_sent: 0  Rows_examined: 0
SET timestamp=1356655200;
INSERT INTO `xf_thread_user_post` (`thread_id`, `user_id`, `post_count`) VALUES ('25175', '6371', '1');
# Time: 121229 10:04:06
# User@Host: database_name[database_name] @ localhost []
# Query_time: 10.535764  Lock_time: 0.000044 Rows_sent: 0  Rows_examined: 0
SET timestamp=1356764646;
INSERT INTO `xf_ip` (`user_id`, `content_type`, `content_id`, `action`, `ip`, `log_date`) VALUES ('6429', 'user', '6429', 'register', '2908244133', '1356764619');
# Time: 121229 10:04:10
# User@Host: database_name[database_name] @ localhost []
# Query_time: 13.019035  Lock_time: 0.000164 Rows_sent: 0  Rows_examined: 0
SET timestamp=1356764650;
INSERT INTO `xf_captcha_log` (`hash`, `captcha_type`, `captcha_data`, `captcha_date`) VALUES ('d6a55dad2550ccd984aa61d4242befc9a2876b02', 'Question', '2', '1356764636');
# User@Host: database_name[database_name] @ localhost []
# Query_time: 12.691102  Lock_time: 0.000077 Rows_sent: 0  Rows_examined: 0
SET timestamp=1356764650;
INSERT INTO `xf_search` (`search_results`, `result_count`, `search_type`, `search_query`, `search_constraints`, `search_order`, `search_grouping`, `user_results`, `warnings`, `user_id`, `search_date`, `query_hash`) VALUES ('[[\"thread\",22993],[\"thread\",24983],[\"thread\",25372],[\"thread\",25375],[\"thread\",25109],[\"thread\",25361],[\"thread\",25300],[\"thread\",25134],[\"thread\",25234],[\"thread\",24543],[\"thread\",18231],[\"thread\",25097],[\"thread\",25325],[\"thread\",24192],[\"thread\",24367],[\"thread\",25358],[\"thread\",25359],[\"thread\",25366],[\"thread\",24230],[\"thread\",25360],[\"thread\",25371],[\"thread\",25259],[\"thread\",25255],[\"thread\",24851],[\"thread\",25199],[\"thread\",25270],[\"thread\",1378],[\"thread\",20305],[\"thread\",25209],[\"thread\",25316],[\"thread\",25220],[\"thread\",25276],[\"thread\",25020],[\"thread\",25243],[\"thread\",25363],[\"thread\",25204],[\"thread\",25262],[\"thread\",932],[\"thread\",25342],[\"thread\",25355],[\"thread\",25335],[\"thread\",11759],[\"thread\",25287],[\"thread\",25357],[\"thread\",25275],[\"thread\",16342],[\"thread\",23786],[\"thread\",25225],[\"thread\",25206],[\"thread\",25293],[\"thread\",25340],[\"thread\",25308],[\"thread\",25344],[\"thread\",16335],[\"thread\",1508],[\"thread\",25353],[\"thread\",25350],[\"thread\",25348],[\"thread\",25326],[\"thread\",22294],[\"thread\",23417],[\"thread\",25260],[\"thread\",25343],[\"thread\",25329],[\"thread\",25312],[\"thread\",25112],[\"thread\",25339],[\"thread\",24629],[\"thread\",22544],[\"thread\",23996],[\"thread\",25332],[\"thread\",23702],[\"thread\",24792],[\"thread\",25330],[\"thread\",25324],[\"thread\",19485],[\"thread\",25307],[\"thread\",25277],[\"thread\",23058],[\"thread\",25048],[\"thread\",24728],[\"thread\",25114],[\"thread\",25318],[\"thread\",25315],[\"thread\",25313],[\"thread\",25311],[\"thread\",24492],[\"thread\",25298],[\"thread\",25296],[\"thread\",25278],[\"thread\",24979],[\"thread\",25240],[\"thread\",25229],[\"thread\",23749],[\"thread\",25043],[\"thread\",25283],[\"thread\",25274],[\"thread\",25235]]', '98', 'recent-threads', '', '[]', 'date', '0', '', '[]', '0', '1356764637', 'b956e81b6bb80d5de0e3955b72b3cadf');
# Time: 121230 18:40:32
# User@Host: database_name[database_name] @ localhost []
# Query_time: 10.837035  Lock_time: 0.000038 Rows_sent: 0  Rows_examined: 1
SET timestamp=1356882032;
UPDATE `xf_user` SET `alerts_unread` = '0' WHERE (user_id = 1381);
# Time: 121230 19:33:36
# User@Host: database_name[database_name] @ localhost []
# Query_time: 10.395477  Lock_time: 0.000032 Rows_sent: 0  Rows_examined: 0
SET timestamp=1356885216;
INSERT INTO `xf_login_attempt` (`login`, `ip_address`, `attempt_date`) VALUES ('FlepFeapeli', '532213290', '1356885205');
# Time: 121231  0:30:33
# User@Host: database_name[database_name] @ localhost []
# Query_time: 10.910154  Lock_time: 0.000000 Rows_sent: 0  Rows_examined: 0
SET timestamp=1356903033;
commit;
# Time: 121231 10:06:55
# User@Host: database_name[database_name] @ localhost []
# Query_time: 10.655237  Lock_time: 0.000000 Rows_sent: 0  Rows_examined: 0
SET timestamp=1356937615;
commit;
# Time: 121231 10:06:56
# User@Host: database_name[database_name] @ localhost []
# Query_time: 10.988372  Lock_time: 0.000000 Rows_sent: 0  Rows_examined: 0
SET timestamp=1356937616;
commit;
# Time: 121231 10:26:16
# User@Host: database_name[database_name] @ localhost []
# Query_time: 11.795912  Lock_time: 0.000049 Rows_sent: 0  Rows_examined: 0
SET timestamp=1356938776;
INSERT INTO xf_thread_read
                (user_id, thread_id, thread_read_date)
            VALUES
                ('6400', '25677', '1356935278')
            ON DUPLICATE KEY UPDATE thread_read_date = VALUES(thread_read_date);
# Time: 121231 10:26:52
# User@Host: database_name[database_name] @ localhost []
# Query_time: 12.047989  Lock_time: 0.000000 Rows_sent: 0  Rows_examined: 0
SET timestamp=1356938812;
commit;
# Time: 121231 16:40:54
# User@Host: database_name[database_name] @ localhost []
# Query_time: 11.886951  Lock_time: 0.000034 Rows_sent: 0  Rows_examined: 0
SET timestamp=1356961254;
INSERT INTO `xf_captcha_log` (`hash`, `captcha_type`, `captcha_data`, `captcha_date`) VALUES ('c5c28bea6ffb1a8c1d0a8853547d9aae2bc8f0b6', 'Question', '2', '1356961238');
# Time: 121231 16:56:08
# User@Host: database_name[database_name] @ localhost []
# Query_time: 10.666954  Lock_time: 0.000018 Rows_sent: 0  Rows_examined: 1
SET timestamp=1356962168;
DELETE FROM `xf_captcha_log` WHERE (hash = '78c0fc42d6b4dd4d1b9c5db7c70c2084da4d210d');
 
and the rest of it:

Code:
# Time: 121231 18:27:06
 
# User@Host: database_name[database_name] @ localhost []
 
# Query_time: 10.057355  Lock_time: 0.000047 Rows_sent: 0  Rows_examined: 30
 
SET timestamp=1356967626;
 
UPDATE `xf_user_alert` SET `view_date` = '1356967616' WHERE (alerted_user_id = 5989 AND view_date = 0);
 
# Time: 130101  1:04:10
 
# User@Host: root[root] @ localhost []
 
# Query_time: 12.294535  Lock_time: 0.000000 Rows_sent: 169549  Rows_examined: 169549
 
use database_name;
 
SET timestamp=1356991450;
 
SELECT /*!40001 SQL_NO_CACHE */ * FROM `xf_post`;
 
# Time: 130101  4:14:07
 
# User@Host: database_name[database_name] @ localhost []
 
# Query_time: 21.998828  Lock_time: 0.000035 Rows_sent: 0  Rows_examined: 1
 
SET timestamp=1357002847;
 
UPDATE `xf_session` SET `expiry_date` = '1357006424' WHERE (session_id = 'b850385ddb18bf462c78017e6471941e');
 
# User@Host: database_name[database_name] @ localhost []
 
# Query_time: 23.084731  Lock_time: 0.000053 Rows_sent: 1  Rows_examined: 1
 
SET timestamp=1357002847;
 
SELECT thread.*
 
                ,
 
                    user.avatar_date, user.gravatar,
 
                    NULL AS thread_read_date,
 
                    0 AS thread_is_watched, schedule.up_enable AS up_enable,
 
            schedule.up_value AS up_value,
 
            schedule.up_value_type AS up_value_type
 
            FROM xf_thread AS thread
 
       
 
                                LEFT JOIN xf_sonnb_up_thread_schedule AS schedule ON
 
                                        (schedule.thread_id = thread.thread_id)
 
                    LEFT JOIN xf_user AS user ON
 
                        (user.user_id = thread.user_id)
 
            WHERE thread.thread_id = '14710';
 
# User@Host: database_name[database_name] @ localhost []
 
# Query_time: 11.357805  Lock_time: 0.000033 Rows_sent: 0  Rows_examined: 0
 
SET timestamp=1357002847;
 
INSERT INTO `xf_ip` (`user_id`, `content_type`, `content_id`, `action`, `ip`, `log_date`) VALUES ('3327', 'user', '3327', 'login', '1587847090', '1357002835');
 
# User@Host: database_name[database_name] @ localhost []
 
# Query_time: 23.624249  Lock_time: 0.029378 Rows_sent: 0  Rows_examined: 0
 
SET timestamp=1357002847;
 
TRUNCATE TABLE xf_error_log;
 
# Time: 130101 10:01:24
 
# User@Host: database_name[database_name] @ localhost []
 
# Query_time: 13.784765  Lock_time: 0.000030 Rows_sent: 0  Rows_examined: 1
 
SET timestamp=1357023684;
 
UPDATE `xf_user` SET `alerts_unread` = '0' WHERE (user_id = 6477);
 
# Time: 130101 10:02:34
 
# User@Host: database_name[database_name] @ localhost []
 
# Query_time: 24.407177  Lock_time: 0.000043 Rows_sent: 0  Rows_examined: 86
 
SET timestamp=1357023754;
 
UPDATE `xf_user_alert` SET `view_date` = '1357023729' WHERE (alerted_user_id = 6400 AND view_date = 0);
 
# Time: 130101 10:02:37
 
# User@Host: database_name[database_name] @ localhost []
 
# Query_time: 24.010187  Lock_time: 0.000052 Rows_sent: 0  Rows_examined: 0
 
SET timestamp=1357023757;
 
INSERT IGNORE INTO xf_flood_check
 
                (user_id, flood_action, flood_time)
 
            VALUES
 
                ('6371', 'post', '1357023733');
 
# User@Host: database_name[database_name] @ localhost []
 
# Query_time: 25.420814  Lock_time: 0.000035 Rows_sent: 0  Rows_examined: 0
 
SET timestamp=1357023757;
 
INSERT INTO `xf_ip` (`user_id`, `content_type`, `content_id`, `action`, `ip`, `log_date`) VALUES ('6371', 'post', '170655', 'insert', '404811273', '1357023723');
 
# Time: 130101 10:06:43
 
# User@Host: database_name[database_name] @ localhost []
 
# Query_time: 13.561923  Lock_time: 0.000043 Rows_sent: 0  Rows_examined: 1
 
SET timestamp=1357024003;
 
UPDATE xf_flood_check
 
            SET flood_time = '1357023990'
 
            WHERE user_id = '6371'
 
                AND flood_action = 'post'
 
                AND flood_time < '1357023975';
 
# Time: 130101 10:07:09
 
# User@Host: database_name[database_name] @ localhost []
 
# Query_time: 13.460666  Lock_time: 0.000000 Rows_sent: 0  Rows_examined: 0
 
SET timestamp=1357024029;
 
commit;
 
# Time: 130101 10:07:14
 
# User@Host: database_name[database_name] @ localhost []
 
# Query_time: 16.480662  Lock_time: 0.000028 Rows_sent: 0  Rows_examined: 1
 
SET timestamp=1357024034;
 
DELETE FROM `xf_captcha_log` WHERE (hash = 'eeb2658c50b98c228d61834f7ad6751127b2e2d6');
 
# User@Host: database_name[database_name] @ localhost []
 
# Query_time: 14.122928  Lock_time: 0.000040 Rows_sent: 0  Rows_examined: 12
 
SET timestamp=1357024034;
 
UPDATE `xf_user_alert` SET `view_date` = '1357024020' WHERE (alerted_user_id = 5370 AND view_date = 0);
 
# Time: 130101 10:12:11
 
# User@Host: database_name[database_name] @ localhost []
 
# Query_time: 10.344068  Lock_time: 0.000041 Rows_sent: 0  Rows_examined: 0
 
SET timestamp=1357024331;
 
INSERT INTO xf_thread_read
 
                (user_id, thread_id, thread_read_date)
 
            VALUES
 
                ('6565', '25702', '1356959182')
 
            ON DUPLICATE KEY UPDATE thread_read_date = VALUES(thread_read_date);
 
# Time: 130101 10:14:33
 
# User@Host: database_name[database_name] @ localhost []
 
# Query_time: 10.317348  Lock_time: 0.000040 Rows_sent: 0  Rows_examined: 0
 
SET timestamp=1357024473;
 
INSERT INTO xf_thread_read
 
                (user_id, thread_id, thread_read_date)
 
            VALUES
 
                ('6569', '22993', '1355248965')
 
            ON DUPLICATE KEY UPDATE thread_read_date = VALUES(thread_read_date);
 
# Time: 130102 15:11:11
 
# User@Host: database_name[database_name] @ localhost []
 
# Query_time: 10.263687  Lock_time: 0.000024 Rows_sent: 0  Rows_examined: 1
 
SET timestamp=1357128671;
 
UPDATE xf_cron_entry
 
            SET next_run = '1357132201'
 
            WHERE entry_id = 'cleanUpHourly'
 
                AND next_run = '1357128655';
 
# Time: 130102 15:11:15
 
# User@Host: database_name[database_name] @ localhost []
 
# Query_time: 13.142971  Lock_time: 0.000033 Rows_sent: 0  Rows_examined: 0
 
SET timestamp=1357128675;
 
INSERT INTO xf_thread_read
 
                (user_id, thread_id, thread_read_date)
 
            VALUES
 
                ('5989', '25767', '1357128263')
 
            ON DUPLICATE KEY UPDATE thread_read_date = VALUES(thread_read_date);
 
# User@Host: database_name[database_name] @ localhost []
 
# Query_time: 12.182255  Lock_time: 0.000027 Rows_sent: 0  Rows_examined: 0
 
SET timestamp=1357128675;
 
INSERT INTO xf_data_registry
 
                (data_key, data_value)
 
            VALUES
 
                ('cron', 'i:1357128748;')
 
            ON DUPLICATE KEY UPDATE
 
                data_value = VALUES(data_value);
 
# Time: 130103  1:04:34
 
# User@Host: root[root] @ localhost []
 
# Query_time: 13.721442  Lock_time: 0.000000 Rows_sent: 201928  Rows_examined: 201928
 
SET timestamp=1357164274;
 
SELECT /*!40001 SQL_NO_CACHE */ * FROM `xf_search_index`;
 
# Time: 130103 11:24:33
 
# User@Host: database_name[database_name] @ localhost []
 
# Query_time: 11.977704  Lock_time: 0.000022 Rows_sent: 0  Rows_examined: 1
 
SET timestamp=1357201473;
 
DELETE FROM `xf_captcha_log` WHERE (hash = '38d80f1b51a2ad39b9fc5c353ba364beb1fadf4d');
 
What is the spec of your VPS?

Things to add for sure,

innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT
 
Thank you I will add that right away.

Intel(R) Xeon(R) CPU E5520 @ 2.27GHz, 2GB RAM up to 3 Burstable but I believe it cant go up to 3gb with it not being 64bit CentOS. I have Litespeed webserver, MySQL 5.5.28 APC installed and caching enabled in my config with this:

Code:
$config['cache']['enabled'] = true;
$config['cache']['frontend'] = 'Core';
$config['cache']['frontendOptions']['cache_id_prefix'] = 'xf_';
$config['cache']['backend'] = 'Apc';

PHP memory limit set to 64M, max_execution_time set to 30, max input time set to 60 in the php config. Let me know if you need any more info :)

I just added those two you mentioned and restarted mysql and it seems to have picked up a bit but there isnt as many members online as their usually is, I will see how it goes.

EDIT: After some posting around it seems to be a little faster but still pretty slow in comparison to here.
 
I am willing to try, do you know the best way to go about doing it and is it fairly easy to install/configure?
 
Yeah thats correct :)

Obviously make full backups of your databases before starting.

Code:
rpm -Uhv http://www.percona.com/downloads/percona-release/percona-release-0.0-1.i386.rpm
yum install Percona-SQL-shared-compat.i386
yum remove mysql-server-*your version* mysql-*your versuon*
yum install Percona-XtraDB-server Percona-XtraDB-client
 
Will it start itself etc once I have ran that? If not do you know the ssh command for it and will my.cnf still work with it? Will I need to import databases, users etc too and will phpmyadmin work still?

Sorry for all the questions but I dont want to run it and end up with needing more steps to take that I just dont know of :D

Thanks a lot guys :)

Edit: Also when running those, do I need the asterisk before and after the MySQL version? :)
 
I will get onto it right away then and let you guys know how it goes, I will be backing up which shouldnt take long at all :) Thanks a lot!
 
Will it start itself etc once I have ran that? If not do you know the ssh command for it and will my.cnf still work with it? Will I need to import databases, users etc too and will phpmyadmin work still?

Sorry for all the questions but I dont want to run it and end up with needing more steps to take that I just dont know of :D

Thanks a lot guys :)

Edit: Also when running those, do I need the asterisk before and after the MySQL version? :)

It should start automatically, if not your standard service start mysqld should do it.

The databases should be all there as before.

Phpmyadmin should work.

Nope, dont use the asterisks, I just added those to make sure you spotted it.

When done, you may just want to run a table repair on the tables just to ensure everything is up-to-date with any changes percona may want to make.
 
Excellent, this sounds too easy, I am waiting for the catch lol I will let you guys know how it goes :)
 
I'd also recommend stopping MySQL before issuing the uninstall command. It's probably OK without, but I'd be happier knowing the databases were not being used when yum starts to uninstall MySQL.
 
I'd also recommend stopping MySQL before issuing the uninstall command. It's probably OK without, but I'd be happier knowing the databases were not being used when yum starts to uninstall MySQL.

Thanks Matt I will run that first in SSH.
 
Hey guys, I just finished running the commands but it doesnt appear to be working, when I run service start mysqld I get unrecognized service.
I see this towards the end of the install log:

file /usr/share/man/man1/mysqlimport.1.gz from install of Percona-Server-clie nt-51-5.1.66-rel14.1.496.rhel6.i686 conflicts with file from package MySQL-clie nt-5.5.28-2.cp.1132.i386
 
Top Bottom