Hello,
Recently i've migrated some of my customers to a new server:
2x Intel Xeon 5520 2.26
12GB RAM
300GB 15K RPM HDD
I have some problems when i run a php script that import 2 files:
1. 700kb xml file
2. 8mb xml file
The files is to sync product to a customer online store.
When some of this 2 scripts the mysq process uses 100% or more cpu and the load increase to 3 or more and the server goes very low.
my /etc/my.cnf
[client]
port = 3306
socket = /var/lib/mysql/mysql.sock
[mysqld]
port = 3306
socket = /var/lib/mysql/mysql.sock
skip-locking
key_buffer = 384M
max_allowed_packet = 1M
table_cache = 5096
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size = 128M
thread_concurrency = 8
max_connections = 400
local-infile=0
skip-federated
server-id = 1
innodb_buffer_pool_size = 384M
join_buffer_size = 64M
key_buffer_size = 900M
log-slow-queries=/var/lib/mysql/slow.log
log-queries-not-using-indexes
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
[isamchk]
key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M
[myisamchk]
key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout
After running mysqltuner.pl i have:
>> MySQLTuner 1.0.1 - Major Hayden <[email protected]>
>> Bug reports, feature requests, and downloads at MySQLTuner
>> Run with '--help' for additional options and output filtering
-------- General Statistics --------------------------------------------------
[^[[0;34m--^[[0m] Skipped version check for MySQLTuner script
[^[[0;32mOK^[[0m] Currently running supported MySQL version 5.0.91-community-log
[^[[0;32mOK^[[0m] Operating on 64-bit architecture
-------- Storage Engine Statistics -------------------------------------------
[^[[0;34m--^[[0m] Status: ^[[0;32m+Archive ^[[0m^[[0;31m-BDB ^[[0m^[[0;31m-Federated ^[[0m^[[0;32m+InnoDB ^[[0m^[[0;31m-ISAM ^[[0m^[[0;31m-NDBCluster ^[[0m
[^[[0;34m--^[[0m] Data in MyISAM tables: 1G (Tables: 2544)
[^[[0;34m--^[[0m] Data in InnoDB tables: 203M (Tables: 849)
[^[[0;34m--^[[0m] Data in MEMORY tables: 0B (Tables: 17)
[^[[0;31m!!^[[0m] Total fragmented tables: 41
-------- Performance Metrics -------------------------------------------------
[^[[0;34m--^[[0m] Up for: 20m 8s (383K q [317.483 qps], 3K conn, TX: 2B, RX: 38M)
[^[[0;34m--^[[0m] Reads / Writes: 73% / 27%
[^[[0;34m--^[[0m] Total buffers: 1.4G global + 76.2M per thread (400 max threads)
[^[[0;31m!!^[[0m] Maximum possible memory usage: 31.2G (265% of installed RAM)
[^[[0;31m!!^[[0m] Slow queries: 7% (28K/383K)
[^[[0;32mOK^[[0m] Highest usage of available connections: 2% (11/400)
[^[[0;32mOK^[[0m] Key buffer size / total MyISAM indexes: 900.0M/619.1M
[^[[0;32mOK^[[0m] Key buffer hit rate: 99.3% (2M cached / 18K reads)
[^[[0;32mOK^[[0m] Query cache efficiency: 69.9% (191K cached / 273K selects)
[^[[0;32mOK^[[0m] Query cache prunes per day: 0
[^[[0;32mOK^[[0m] Sorts requiring temporary tables: 0% (21 temp sorts / 16K sorts)
[^[[0;31m!!^[[0m] Joins performed without indexes: 1536
[^[[0;32mOK^[[0m] Temporary tables created on disk: 9% (147 on disk / 1K total)
[^[[0;32mOK^[[0m] Thread cache hit rate: 99% (12 created / 3K connections)
[^[[0;32mOK^[[0m] Table cache hit rate: 99% (3K open / 3K opened)
[^[[0;32mOK^[[0m] Open file limit used: 48% (5K/10K)
[^[[0;32mOK^[[0m] Table locks acquired immediately: 99% (127K immediate / 128K locks)
[^[[0;32mOK^[[0m] InnoDB data size / buffer pool: 203.3M/384.0M
-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
MySQL started within last 24 hours - recommendations may be inaccurate
Reduce your overall MySQL memory footprint for system stability
Adjust your join queries to always utilize indexes
Variables to adjust:
*** MySQL's maximum memory usage is dangerously high ***
*** Add RAM before increasing MySQL buffer variables ***
join_buffer_size (> 64.0M, or always use indexes with joins)
How to optimize my mysql installation? Some idea?
Thanks,
Wilson
Recently i've migrated some of my customers to a new server:
2x Intel Xeon 5520 2.26
12GB RAM
300GB 15K RPM HDD
I have some problems when i run a php script that import 2 files:
1. 700kb xml file
2. 8mb xml file
The files is to sync product to a customer online store.
When some of this 2 scripts the mysq process uses 100% or more cpu and the load increase to 3 or more and the server goes very low.
my /etc/my.cnf
[client]
port = 3306
socket = /var/lib/mysql/mysql.sock
[mysqld]
port = 3306
socket = /var/lib/mysql/mysql.sock
skip-locking
key_buffer = 384M
max_allowed_packet = 1M
table_cache = 5096
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size = 128M
thread_concurrency = 8
max_connections = 400
local-infile=0
skip-federated
server-id = 1
innodb_buffer_pool_size = 384M
join_buffer_size = 64M
key_buffer_size = 900M
log-slow-queries=/var/lib/mysql/slow.log
log-queries-not-using-indexes
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
[isamchk]
key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M
[myisamchk]
key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout
After running mysqltuner.pl i have:
>> MySQLTuner 1.0.1 - Major Hayden <[email protected]>
>> Bug reports, feature requests, and downloads at MySQLTuner
>> Run with '--help' for additional options and output filtering
-------- General Statistics --------------------------------------------------
[^[[0;34m--^[[0m] Skipped version check for MySQLTuner script
[^[[0;32mOK^[[0m] Currently running supported MySQL version 5.0.91-community-log
[^[[0;32mOK^[[0m] Operating on 64-bit architecture
-------- Storage Engine Statistics -------------------------------------------
[^[[0;34m--^[[0m] Status: ^[[0;32m+Archive ^[[0m^[[0;31m-BDB ^[[0m^[[0;31m-Federated ^[[0m^[[0;32m+InnoDB ^[[0m^[[0;31m-ISAM ^[[0m^[[0;31m-NDBCluster ^[[0m
[^[[0;34m--^[[0m] Data in MyISAM tables: 1G (Tables: 2544)
[^[[0;34m--^[[0m] Data in InnoDB tables: 203M (Tables: 849)
[^[[0;34m--^[[0m] Data in MEMORY tables: 0B (Tables: 17)
[^[[0;31m!!^[[0m] Total fragmented tables: 41
-------- Performance Metrics -------------------------------------------------
[^[[0;34m--^[[0m] Up for: 20m 8s (383K q [317.483 qps], 3K conn, TX: 2B, RX: 38M)
[^[[0;34m--^[[0m] Reads / Writes: 73% / 27%
[^[[0;34m--^[[0m] Total buffers: 1.4G global + 76.2M per thread (400 max threads)
[^[[0;31m!!^[[0m] Maximum possible memory usage: 31.2G (265% of installed RAM)
[^[[0;31m!!^[[0m] Slow queries: 7% (28K/383K)
[^[[0;32mOK^[[0m] Highest usage of available connections: 2% (11/400)
[^[[0;32mOK^[[0m] Key buffer size / total MyISAM indexes: 900.0M/619.1M
[^[[0;32mOK^[[0m] Key buffer hit rate: 99.3% (2M cached / 18K reads)
[^[[0;32mOK^[[0m] Query cache efficiency: 69.9% (191K cached / 273K selects)
[^[[0;32mOK^[[0m] Query cache prunes per day: 0
[^[[0;32mOK^[[0m] Sorts requiring temporary tables: 0% (21 temp sorts / 16K sorts)
[^[[0;31m!!^[[0m] Joins performed without indexes: 1536
[^[[0;32mOK^[[0m] Temporary tables created on disk: 9% (147 on disk / 1K total)
[^[[0;32mOK^[[0m] Thread cache hit rate: 99% (12 created / 3K connections)
[^[[0;32mOK^[[0m] Table cache hit rate: 99% (3K open / 3K opened)
[^[[0;32mOK^[[0m] Open file limit used: 48% (5K/10K)
[^[[0;32mOK^[[0m] Table locks acquired immediately: 99% (127K immediate / 128K locks)
[^[[0;32mOK^[[0m] InnoDB data size / buffer pool: 203.3M/384.0M
-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
MySQL started within last 24 hours - recommendations may be inaccurate
Reduce your overall MySQL memory footprint for system stability
Adjust your join queries to always utilize indexes
Variables to adjust:
*** MySQL's maximum memory usage is dangerously high ***
*** Add RAM before increasing MySQL buffer variables ***
join_buffer_size (> 64.0M, or always use indexes with joins)
How to optimize my mysql installation? Some idea?
Thanks,
Wilson