• P
    User Newbie •

    Configurare mysql, che problema!

    Ciao ragazzi,
    ho un server dedicato su aruba,
    con un Pentium 4 CPU 2.80GHz, con 1 GB di Ram
    e linux distribuzione psa v8.0.1_build80060618.11 os_CentOS 4.2.

    Sul server attualmente c'è solo un forum basato su phpBB che un traffico discreto (circa 5.000 utenti unici e 100.000 page view al giorno).

    Il problema è che ogni giorno nell'orario di punta la ram e lo swap si riempiono al 100% e il server risponde max_user_connetions.
    Presumo che sia un problema di configurazione di mysql, forse c'è qualche impostazione che può essere migliorata?

    Variabile Valore sessione Valore globale
    back log 50 50
    basedir /usr/ /usr/
    bdb cache size 8388600 8388600
    bdb home /var/lib/mysql/ /var/lib/mysql/
    bdb log buffer size 32768 32768
    bdb logdir
    bdb max lock 10000 10000
    bdb shared data OFF OFF
    bdb tmpdir /tmp/ /tmp/
    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 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
    concurrent insert ON ON
    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
    expire logs days 0 0
    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 NO NO
    have bdb YES YES
    have blackhole engine NO NO
    have compress YES YES
    have crypt YES YES
    have csv NO NO
    have example engine NO NO
    have geometry YES YES
    have innodb YES YES
    have isam YES YES
    have ndbcluster NO NO
    have openssl YES YES
    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 data file path ibdata1:10M:autoextend ibdata1:10M:autoextend
    innodb data home dir
    innodb fast shutdown ON ON
    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 table locks ON ON
    innodb thread concurrency 8 8
    interactive timeout 28800 28800
    join buffer size 131072 131072
    key buffer size 8388600 8388600
    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
    license GPL GPL
    local infile ON ON
    locked in memory OFF OFF
    log OFF OFF
    log bin OFF OFF
    log error
    log slave updates OFF OFF
    log slow queries OFF OFF
    log update 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 1048576 1048576
    max binlog cache size 4294967295 4294967295
    max binlog size 1073741824 1073741824
    max connect errors 10 10
    max connections 100 100
    max delayed threads 20 20
    max error count 64 64
    max heap table size 16777216 16777216
    max insert delayed threads 20 20
    max join size 4294967295 4294967295
    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 tmp tables 32 32
    max user connections 0 0
    max write lock count 4294967295 4294967295
    myisam data pointer size 4 4
    myisam max extra sort file size 2147483648 2147483648
    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
    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
    pid file /var/run/mysqld/mysqld.pid /var/run/mysqld/mysqld.pid
    port 3306 3306
    preload buffer size 32768 32768
    prepared stmt count 0 0
    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 0 0
    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 0 0
    skip external locking ON ON
    skip networking OFF OFF
    skip show database OFF OFF
    slave net timeout 3600 3600
    slave transaction retries 0 0
    slow launch time 2 2
    socket /var/lib/mysql/mysql.sock /var/lib/mysql/mysql.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
    sync replication 0 0
    sync replication slave id 0 0
    sync replication timeout 0 0
    system time zone CET CET
    table cache 64 64
    table type MyISAM MyISAM
    thread cache size 0 0
    thread stack 196608 196608
    time format %H:%i:%s %H:%i:%s
    time zone SYSTEM SYSTEM
    tmp table size 33554432 33554432
    tmpdir
    transaction alloc block size 8192 8192
    transaction prealloc size 4096 4096
    tx isolation REPEATABLE-READ REPEATABLE-READ
    version 4.1.20 4.1.20
    version bdb Sleepycat Software: Berkeley DB 4.1.24: (May 24, 2006) Sleepycat Software: Berkeley DB 4.1.24: (May 24, 2006)
    version comment Source distribution Source distribution
    version compile machine i686 i686
    version compile os redhat-linux-gnu redhat-linux-gnu
    wait timeout 28800 28800

    STATISTICHE:

    Traffico del server: Queste tabelle mostrano le statistiche del traffico di retedi questo server MySQL dal momento del suo avvio.

                    Traffico  	 ø all'ora

    Ricevuti 239.767 KB 9.470 KB
    Spediti 1.042 MB 42.161 KB
    Totale 1.277 MB 51.631 KB

                 Connessioni  	 ø all'ora  	 %

    Tentativi falliti 64 2,53 0,04 %
    Fallito 16 0,63 0,01 %
    Totale 150.040 5.925,88 100,00 %

    Query delle Statistiche: Dall'avvio, 1.885.893 queries sono state effettuate sul server.

    Totale ø all'ora ø al minuto ø al secondo
    1.885.893 74.483,98 1.241,40 20,69

    Presumo che il problema sia mysql, visto che il webserver continua
    a funzionare anche se ai limiti del collasso.. mentre mysql si arrende..

    Risolverei il problema aumentando semplicemente la ram?
    Accetto volentieri consigli e critiche costruttive.. 🙂

    Grazie mille,
    Luca Formicola

    0
Effettua l'accesso per rispondere