ajax 定时请求mysql_php – MySQL查询在AJAX请求中花费大量时间

问题

包含任何数据库查询的每个AJAX请求都需要比正常时间更多的时间.

我一个星期以来没有更新代码库,但是突然之间,所有在AJAX请求中完成的所有数据库查询都花了很多时间.这里要注意的一点是,如果查询是在一个页面中写的,然后该页面是正常加载的,就像你访问：www.example.com/mypage.php,

mypage.php：

$query = $db_handler->prepare(

"SELECT * FROM table_x LIMIT 5"

);

$query->execute();

$fetch = $query->fetchAll(PDO::FETCH_ASSOC);

?>

所有的结果页面加载非常快.

但是如果它在AJAX的响应文件中完成,则需要花费大量时间(比如15秒)才能加载

客户端的AJAX代码：

$.ajax

({

url: 'server_files/ajaxtest.php',

type: 'POST',

dataType: 'JSON',

data:

{

data: 'some data'

},

success: function(data)

{

if( data.success === true )

{

}

else if( data.success === false )

{

}

},

error: function(e)

{

alert('Error');

}

});

ajax_response.php：

header('Content-Type: application/json');

if( isset($_POST['data']) )

{

$query = $db_handler->prepare(

"SELECT * FROM table_x LIMIT 5"

);

$query->execute();

$fetch = $query->fetchAll(PDO::FETCH_ASSOC);

echo json_encode([

'success' => true,

'response' => $fetch

]);

}

?>

^需要15秒加载(具有5个行集(LIMIT 5)的查询与具有10个行集(LIMIT 10)的查询相同.)

如果同一个文件只包含这个

header('Content-Type: application/json');

if( isset($_POST['data']) )

{

echo json_encode([

'success' => true

]);

}

?>

^需要300-400ms加载

显然,一个查询会增加响应时间(1-3秒),但是15秒太多了.

我做了什么

1)我已经联系我的托管服务提供商,但没有多少帮助.

2)我也安装了mysqltuner,它显示：

-------- General Statistics --------------------------------------------------

[--] Skipped version check for MySQLTuner script

[OK] Currently running supported MySQL version 5.5.49-0ubuntu0.14.04.1-log

[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------

[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster

[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)

[--] Data in MyISAM tables: 27K (Tables: 13)

[--] Data in InnoDB tables: 6M (Tables: 21)

[!!] Total fragmented tables: 21

-------- Security Recommendations -------------------------------------------

[!!] User 'rootAlbert@127.0.0.1' has no password set.

[!!] User 'rootAlbert@::1' has no password set.

[!!] User 'rootAlbert@lamp' has no password set.

-------- Performance Metrics -------------------------------------------------

[--] Up for: 11h 23m 42s (21K q [0.533 qps], 11K conn, TX: 6M, RX: 2M)

[--] Reads / Writes: 92% / 8%

[--] Total buffers: 432.0M global + 2.7M per thread (151 max threads)

[OK] Maximum possible memory usage: 837.8M (84% of installed RAM)

[OK] Slow queries: 2% (488/21K)

[OK] Highest usage of available connections: 3% (6/151)

[OK] Key buffer size / total MyISAM indexes: 16.0M/156.0K

[OK] Key buffer hit rate: 99.2% (133 cached / 1 reads)

[OK] Query cache efficiency: 61.9% (6K cached / 10K selects)

[OK] Query cache prunes per day: 0

[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 113 sorts)

[!!] Temporary tables created on disk: 50% (421 on disk / 842 total)

[OK] Thread cache hit rate: 99% (6 created / 11K connections)

[OK] Table cache hit rate: 33% (75 open / 223 opened)

[OK] Open file limit used: 1% (76/6K)

[OK] Table locks acquired immediately: 100% (4K immediate / 4K locks)

[OK] InnoDB data size / buffer pool: 6.5M/128.0M

-------- Recommendations -----------------------------------------------------

General recommendations:

Run OPTIMIZE TABLE to defragment tables for better performance

MySQL started within last 24 hours - recommendations may be inaccurate

When making adjustments, make tmp_table_size/max_heap_table_size equal

Reduce your SELECT DISTINCT queries without LIMIT clauses

Variables to adjust:

tmp_table_size (> 16M)

max_heap_table_size (> 16M)

3)搜索很多,并更新my.cnf文件.

这是我的my.cnf文件(这个文件在发生问题的时候看起来有点不同)

[client]

port = 3306

socket = /var/run/mysqld/mysqld.sock

[mysqld_safe]

socket = /var/run/mysqld/mysqld.sock

nice = 0

[mysqld]

local-infile=0

log=/var/log/mysql-logfile

skip_name_resolve

user = mysql

pid-file = /var/run/mysqld/mysqld.pid

socket = /var/run/mysqld/mysqld.sock

port = 3306

basedir = /usr

datadir = /var/lib/mysql

tmpdir = /tmp

lc-messages-dir = /usr/share/mysql

skip-external-locking

slow-query-log = 1

slow-query-log-file = /var/log/mysql-slow.log

long_query_time = 2

log-queries-not-using-indexes

key_buffer = 16M

max_allowed_packet = 32M

thread_stack = 192K

thread_cache_size = 8

myisam-recover = BACKUP

query_cache_type=1

query_cache_limit=2M

query_cache_size=256M

tmp_table_size=16M

max_heap_table_size=16M

table_cache=3084

log_error = /var/log/mysql/error.log

expire_logs_days = 10

max_binlog_size = 100M

big-tables

[mysqldump]

quick

quote-names

max_allowed_packet = 16M

[mysql]

[isamchk]

key_buffer = 16M

!includedir /etc/mysql/conf.d/

4)优化DB中的所有表

5)我也升级了我的服务器从1GB内存和1CPU,2TB传输到2GB内存和2CPUS,3TB传输

我还没有得到为什么会发生,如何解决这个问题.