wgalafassijr

Active Member
Jun 23, 2005
25
0
151
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

Quality Assurance Analyst
Staff member
Oct 2, 2010
7,607
42
348
somewhere over the rainbow
cPanel Access Level
Root Administrator
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.