new server... slow mysql

[wgalafassijr]

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

 

[cPanelTristan]

Hello,

First of all, remove things that aren't needed in the /etc/my.cnf or don't actually exist on Linux. Reduce those using too much memory. Here's some suggestions:

[client]
#port = 3306 (already the default, no need for it)
#socket = /var/lib/mysql/mysql.sock (already the default, no need for it)

[mysqld]
#port = 3306 (already the default, no need for it)
#socket = /var/lib/mysql/mysql.sock (already the default, no need for it)
#skip-locking (already the default, no need for it)
key_buffer_size = 384M (this is far too high, reduce it to 80-120M to start)
max_allowed_packet = 1M
table_cache = 5096
#sort_buffer_size = 2M (commenting it out to take it out of memory consumption)
#read_buffer_size = 2M (commenting it out to take it out of memory consumption)
#read_rnd_buffer_size = 8M (commenting out to take it out of memory consumption)
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size = 128M (possibly too high)
#thread_concurrency = 8 (doesn't exist on Linux, only on Solaris)
max_connections = 400
local-infile=0
skip-federated
server-id = 1
innodb_buffer_pool_size = 384M (this is too high, remove or reduce it)
join_buffer_size = 64M
#key_buffer_size = 900M (you already have key_buffer_size previously, key_buffer doesn't exist and it's called key_buffer_size, 900M is ridiculously high)
log-slow-queries=/var/lib/mysql/slow.log
log-queries-not-using-indexes

[mysqldump]
#quick (already the default for mysqldump)
max_allowed_packet = 16M

[mysql]
no-auto-rehash

[isamchk]
key_buffer_size = 256M
sort_buffer_size = 256M
read_buffer_size = 2M
write_buffer_size = 2M

[myisamchk]
key_buffer_size = 256M
sort_buffer_size = 256M
read_buffer_size = 2M
write_buffer_size = 2M

[mysqlhotcopy]
interactive-timeout

Thanks.