• F
    User Newbie •

    MySql 5.0.22 - Ubuntu 6.06 - CPU al 100%

    Salve a tutti,
    sono nuovo del forum ma ho già potuto sperimentare parecchie conoscenza approfondite da parte di alcuni di voi.

    Vi sottopongo un problemino che mi sta preoccupando non poco.

    Ho un server (2 Xeon 2.3, 4GB RAM) in housing presso un Data Center.
    S.O: Ubuntu 6.06
    MySql 5.0.22
    Apache 2
    PHP 5.1.2

    Si tratta di una classica applicazione transazionale che compie delle query belle "paccute" sul DB.
    Ho notato che ultimamente, non appena la query è una di quelle un po' più pesanti, la CPU mi va al 100% (lo vedo da "top" accedendo ad SSH2).

    Le server variables del DB sono:

    **auto increment increment **1 1 **auto increment offset **1 1 **automatic sp privileges **ON ON **back log **50 50 **basedir **/usr/ /usr/ **binlog cache size **32768 32768 **bulk insert buffer size **8388608 8388608 **character set client **utf8 latin1 **character set connection **utf8 latin1 **character set database **latin1 latin1 **character set filesystem **binary binary **character set results **utf8 latin1 **character set server **latin1 latin1 **character set system **utf8 utf8 **character sets dir **/usr/share/mysql/charsets/ /usr/share/mysql/charsets/ **collation connection **utf8_general_ci latin1_swedish_ci **collation database **latin1_swedish_ci latin1_swedish_ci **collation server **latin1_swedish_ci latin1_swedish_ci **completion type **0 0 **concurrent insert **1 1 **connect timeout **5 5 **datadir **/var/lib/mysql/ /var/lib/mysql/ **date format **%Y-%m-%d %Y-%m-%d **datetime format **%Y-%m-%d %H:%i:%s %Y-%m-%d %H:%i:%s **default week format **0 0 **delay key write **ON ON **delayed insert limit **100 100 **delayed insert timeout **300 300 **delayed queue size **1000 1000 **div precision increment **4 4 **engine condition pushdown **OFF OFF **expire logs days **20 20 **flush **OFF OFF **flush time **0 0 **ft boolean syntax **+ -><()~:""&| + -><()~:""&| **ft max word len **84 84 **ft min word len **4 4 **ft query expansion limit **20 20 **ft stopword file **(built-in) (built-in) **group concat max len **1024 1024 **have archive **YES YES **have bdb **NO NO **have blackhole engine **NO NO **have compress **YES YES **have crypt **YES YES **have csv **YES YES **have example engine **NO NO **have federated engine **YES YES **have geometry **YES YES **have innodb **YES YES **have isam **NO NO **have ndbcluster **DISABLED DISABLED **have openssl **NO NO **have query cache **YES YES **have raid **NO NO **have rtree keys **YES YES **have symlink **YES YES **init connect ****init file ****init slave ****innodb additional mem pool size **1048576 1048576 **innodb autoextend increment **8 8 **innodb buffer pool awe mem mb **0 0 **innodb buffer pool size **8388608 8388608 **innodb checksums **ON ON **innodb commit concurrency **0 0 **innodb concurrency tickets **500 500 **innodb data file path **ibdata1:10M:autoextend ibdata1:10M:autoextend **innodb data home dir ****innodb doublewrite **ON ON **innodb fast shutdown **1 1 **innodb file io threads **4 4 **innodb file per table **OFF OFF **innodb flush log at trx commit **1 1 **innodb flush method ****innodb force recovery **0 0 **innodb lock wait timeout **50 50 **innodb locks unsafe for binlog **OFF OFF **innodb log arch dir ****innodb log archive **OFF OFF **innodb log buffer size **1048576 1048576 **innodb log file size **5242880 5242880 **innodb log files in group **2 2 **innodb log group home dir **./ ./ **innodb max dirty pages pct **90 90 **innodb max purge lag **0 0 **innodb mirrored log groups **1 1 **innodb open files **300 300 **innodb support xa **ON ON **innodb sync spin loops **20 20 **innodb table locks **ON ON **innodb thread concurrency **8 8 **innodb thread sleep delay **10000 10000 **interactive timeout **28800 28800 **join buffer size **131072 131072 **key buffer size **134217728 134217728 **key cache age threshold **300 300 **key cache block size **1024 1024 **key cache division limit **100 100 **language **/usr/share/mysql/english/ /usr/share/mysql/english/ **large files support **ON ON **large page size **0 0 **large pages **OFF OFF **license **GPL GPL **local infile **ON ON **locked in memory **OFF OFF **log **OFF OFF **log bin **ON ON **log bin trust function creators **OFF OFF **log error ****log slave updates **OFF OFF **log slow queries **OFF OFF **log warnings **1 1 **long query time **10 10 **low priority updates **OFF OFF **lower case file system **OFF OFF **lower case table names **0 0 **max allowed packet **16776192 16776192 **max binlog cache size **4294967295 4294967295 **max binlog size **104857600 104857600 **max connect errors **10 10 **max connections **100 100 **max delayed threads **20 20 **max error count **64 64 **max heap table size **99999744 99999744 **max insert delayed threads **20 20 **max join size **18446744073709551615 18446744073709551615 **max length for sort data **1024 1024 **max prepared stmt count **16382 16382 **max relay log size **0 0 **max seeks for key **4294967295 4294967295 **max sort length **1024 1024 **max sp recursion depth **0 0 **max tmp tables **32 32 **max user connections **0 0 **max write lock count **4294967295 4294967295 **multi range count **256 256 **myisam data pointer size **6 6 **myisam max sort file size **2147483647 2147483647 **myisam recover options **OFF OFF **myisam repair threads **1 1 **myisam sort buffer size **8388608 8388608 **myisam stats method **nulls_unequal nulls_unequal **ndb autoincrement prefetch sz **32 32 **ndb force send **ON ON **ndb use exact count **ON ON **ndb use transactions **ON OFF **ndb cache check time **0 0 **net buffer length **16384 16384 **net read timeout **30 30 **net retry count **10 10 **net write timeout **60 60 **new **OFF OFF **old passwords **ON ON **open files limit **1024 1024 **optimizer prune level **1 1 **optimizer search depth **62 62 **pid file **/var/run/mysqld/mysqld.pid /var/run/mysqld/mysqld.pid **prepared stmt count **0 0 **port **3306 3306 **preload buffer size **32768 32768 **protocol version **10 10 **query alloc block size **8192 8192 **query cache limit **1048576 1048576 **query cache min res unit **4096 4096 **query cache size **16777216 16777216 **query cache type **ON ON **query cache wlock invalidate **OFF OFF **query prealloc size **8192 8192 **range alloc block size **2048 2048 **read buffer size **131072 131072 **read only **OFF OFF **read rnd buffer size **262144 262144 **relay log purge **ON ON **relay log space limit **0 0 **rpl recovery rank **0 0 **secure auth **OFF OFF **server id **1 1 **skip external locking **ON ON **skip networking **OFF OFF **skip show database **OFF OFF **slave compressed protocol **OFF OFF **slave load tmpdir **/tmp/ /tmp/ **slave net timeout **3600 3600 **slave skip errors **OFF OFF **slave transaction retries **10 10 **slow launch time **2 2 **socket **/var/run/mysqld/mysqld.sock /var/run/mysqld/mysqld.sock **sort buffer size **2097144 2097144 **sql mode ****sql notes **ON ON **sql warnings **ON ON **storage engine **MyISAM MyISAM **sync binlog **0 0 **sync frm **ON ON **system time zone **CEST CEST **table cache **64 64 **table lock wait timeout **50 50 **table type **MyISAM MyISAM **thread cache size **0 0 **thread stack **131072 131072 **time format **%H:%i:%s %H:%i:%s **time zone **SYSTEM SYSTEM **timed mutexes **OFF OFF **tmp table size **100000000 100000000 **tmpdir **/tmp /tmp **transaction alloc block size **8192 8192 **transaction prealloc size **4096 4096 **tx isolation **REPEATABLE-READ REPEATABLE-READ **updatable views with limit **YES YES **version **5.0.22-Debian_0ubuntu6.06-log 5.0.22-Debian_0ubuntu6.06-log **version comment **Debian Etch distribution Debian Etch distribution **version compile machine **i486 i486 **version compile os **pc-linux-gnu pc-linux-gnu **wait timeout **28800 28800

    Per risolvere il problema di JOIN di tabelle di grosse dimensioni (quasi 3M di record la più grande) che mi portava il DB a generare TMP tables su dusco, ho aumentato (come da manuale MySql) max_heap_size e tmp_table_size a 100M, ma senza (a dire il vero) grossi miglioramenti.
    Ma quello che mi preoccupa di più è proprio la CPU al 100%: mi chiedo cosa succede nel momento in cui vi siano due query concorrenti.....
    Ogni aiuto è altamente apprezzato....

    Grazie!

    Fab

    0
  • G
    User Attivo •

    LOCK TABLES libri WRITE;
    query da eseguire...
    query da eseguire...
    query da eseguire...
    UNLOCK TABLES;

    forse questo può aiutare?

    0
  • F
    User Newbie •

    Ciao, grazie della collaborazione.
    Perchè il fare il lock delle tabelle in scrittura dovrebbe aiutare con il carico CPU?

    Fab

    0
  • G
    User Attivo •

    si aiuta, di fatto è un'operazione consigliata, proprio per query corpose, permette di eseguire tutte in una volta le query.

    0
Effettua l'accesso per rispondere