modals - datatables php jquery ajax bootstrap mysql

Consulta MySQL que toma mucho tiempo en una solicitud AJAX

Question

El problema

Cada solicitud de AJAX que contenga cualquier consulta DB toma mucho más tiempo de lo normal.

No he actualizado la base de código desde hace una semana, pero de repente todas las consultas DB hechas en una solicitud AJAX están tomando mucho tiempo. Algo que hay que notar aquí es que si la consulta está escrita en una página y luego la página se carga normalmente como si fuera a visitar: www.example.com/mypage.php ,

mypage.php:

<?php

   $query = $db_handler->prepare(
      "SELECT * FROM table_x LIMIT 5"
   );
   $query->execute();
   $fetch = $query->fetchAll(PDO::FETCH_ASSOC);

?>

La página se carga muy rápido con todo el resultado.

Pero si se hace en un archivo de respuestas de AJAX, lleva mucho tiempo (digamos 15 segundos) cargar

Código AJAX en el lado del cliente:

$.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:

<?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
       ]);
    }
?>

^ tarda 15 segundos en cargarse ( una consulta con 5 conjuntos de filas ( LIMIT 5 ) está tomando el mismo tiempo que una consulta con 10 conjuntos de filas ( LIMIT 10 ). )

si el mismo archivo contiene solo esto

<?php

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

   if( isset($_POST[''data'']) )
   {    
       echo json_encode([
           ''success''  => true
       ]);
   }
?>

^ toma 300-400ms para cargar

Obviamente, una consulta aumentará el tiempo de respuesta un poco (1-3secs) pero 15secs es demasiado.

Qué he hecho

1) Me he puesto en contacto con mi proveedor de hosting, pero eso no ayudó mucho.

2) También instalé mysqltuner y muestra esto:

-------- 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 ''[email protected]'' 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 mucho y actualicé mi archivo my.cnf . Este es mi archivo my.cnf (este archivo se veía un poco diferente en el momento en que ocurrió el problema)

[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) Optimicé todas las tablas en el DB

5) También actualicé mi servidor de 1GB memory and 1CPU, 2TB Transfer a 2GB memory and 2CPUS, 3TB Transfer

Todavía no entiendo por qué está sucediendo y cómo resolver esto.

Answer 1

Creo que primero debe averiguar si su consulta está demorando o no. - Pon la misma consulta en phpmyadmin y ejecuta la consulta y verifica la hora. Si la consulta lleva tiempo, intente eliminar el límite y ejecutar o usar la indexación para él. - Si la consulta no lleva tiempo, verifique si hay algún error o problema en la red mientras obtiene los datos. Elimine la consulta del ajax por el momento y envíe datos estáticos. Compruebe a qué hora lleva tiempo.

Espero que esto ayude.

Answer 2

El problema estaba en la cadena de conexión. Estaba usando mi nombre de dominio ( example.com ) para conectarme a la base de datos. Así que lo cambié a mi dirección IP y resolvió el problema.

Gracias a todos por su ayuda.