Not the answer you need?
Register and ask your own question!

optimize mysql apache for best performance

harivaagharivaag EntrantInactive User Role Beginner
Hi,
I have a dedicated box in which i run the app and the db server with 16GB memory and below server config
HP DL 120 G7, Single Proc Quad Core
- Intel Xeon E1220 (1 CPU x 4 Cores)
- 500 GB x 2 SATA Hard Disks on Raid 1
- 16 GB DDRII ECC Registered RAM

I still am not able to spike up my visitors where as just 6 months back the same server was able to show me 15000+ visitors each day and today i am not able to get more then 5000 visitors...

Mysql CPU load spikes at times to 200% and also the load average goes upto 80 at times

i forgot to mention i have myisam and INNODB tables major myisam

below is my.cnf entries
[mysqld]

local-infile=0
skip-name-resolve
max_connections=400
low_priority_updates=1
myisam-recover=backup,force
thread_concurrency=8
concurrent_insert=2
thread_cache_size=48
max_allowed_packet=8M

innodb_buffer_pool_size=512M
innodb_additional_mem_pool_size=10M
innodb_flush_method=O_DIRECT

symbolic-links=0
socket=/var/lib/mysql/mysql.sock

interactive_timeout = 100
connect_timeout = 60
wait_timeout = 60

table_cache=2048
table_definition_cache =1024
tmp_table_size = 150M
max_heap_table_size = 150M
join_buffer_size=2M
read_buffer_size=128K
sort_buffer_size=2M
table_open_cache=1024
read_rnd_buffer_size=256K
key_buffer =1G
max_allowed_packet=8M
max_connect_errors=10
myisam_sort_buffer_size=512M
query_cache_limit=200M
query_cache_size=1G
query_cache_type=1

slow_query_log=1
slow_query_log_file = mysql-slow.log
long_query_time=10
log-queries-not-using-indexes
open_files_limit=4478
[mysqldump]
quick
max_allowed_packet=16M

[mysql]
no-auto-rehash

[isamchk]
key_buffer=128M
sort_buffer=128M
read_buffer=4M
write_buffer=2M

[myisamchk]
key_buffer_size = 128M
sort_buffer_size = 128M
read_buffer = 4M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

Comments

  • przemekprzemek Percona Support Engineer Percona Staff Role
    query_cache_size=1G - way too big
    innodb_buffer_pool_size=512M - why so small?

    Try to investigate what the MySQL is doing during it's resource utilization is very high - watch processlist, show engine innodb status, etc.
    Which tables are most used ones, MyISAM or InnoDB? Any reason to have MyISAM at all?
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.