MySQL performance issues?

[Mouth]

Hi,

Trial monitoring service gives the following feedback ...

Error : Heavy join rate value 43.9675727229 % should be less than 20.0 %
Warning : Heavy join rate value 43.9675727229 % should be less than 10.0 %
Error : On-disk temporary table rate value 53.9682539683 % should be less than 20.0 %
Warning : On-disk temporary table rate value 53.9682539683 % should be less than 10.0 %
Error : Query cache miss rate value 24.8371431955 % should be less than 20.0 %
Warning : Query cache miss rate value 24.8371431955 % should be less than 10.0 %
Error : Innodb buffer pool usage value 96.8673467052 % should be less than 90.0 %
Warning : Innodb buffer pool usage value 96.8673467052 % should be less than 80.0 %

Feedback/thoughts and, if necessary, advice on how and where to further identify and resolve these issues. Normally I'd ask someone to do it, but I'd like to start and learn a little more (consider me between entry level and competent) about the configuring and tweaking/tuning of MySQL

Server: i3 2100 dedicated with 4Gb RAM and single SATA disk
OS: Debian. Average load during heavy/peak usage is 0.8, with io-wait at 15%
MySQL: Percona 5.5 (config changes/additions below)
http: Nginx, with PHP5-FPM
Xf: 22,000 users, average 250 online and peaking at 550. 2.5Gb DB with 1.93 million posts.

# As per https://tools.percona.com/ with add-ons from me in # JASON # section

[mysqld]
# GENERAL #
bind-address            = 127.0.0.1
default-storage-engine  = InnoDB

# myISAM #
key_buffer              = 32M
myisam_recover          = FORCE,BACKUP

# SAFETY #
max_allowed_packet      = 16M
max_connect_errors      = 1000000
sql_mode                = STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,NO_EN
GINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ONLY_FULL_GROUP_BY
sysdate_is_now          = 1
innodb                  = FORCE
innodb_strict_mode      = 1

# BINARY LOGGING #
log_bin                = /var/lib/mysql/mysql-bin
expire_logs_days        = 14
sync_binlog            = 1

# CACHES AND LIMITS #
tmp_table_size          = 64M
max_heap_table_size    = 64M
query_cache_type        = 1
query_cache_size        = 64M
query_cache_limit      = 40M
max_connections        = 150
thread_cache_size      = 50
open_files_limit        = 65535
table_definition_cache  = 2048
table_open_cache        = 2048  # synonyms with table_cache

# INNODB #
innodb_flush_method            = O_DIRECT
innodb_log_files_in_group      = 2
innodb_log_file_size            = 128M
innodb_flush_log_at_trx_commit  = 2
innodb_file_per_table          = 1
innodb_buffer_pool_size        = 2G

# LOGGING #
log_error                      = /var/log/mysql/mysql-error.log
slow_query_log                  = 1
slow_query_log_file            = /var/log/mysql/mysql-slow.log
long_query_time                = 2

# JASON #
tmpdir                  = /var/tmp
low_priority_updates    = 1
concurrent_insert      = ALWAYS
back_log                = 2048
innodb_log_buffer_size  = 8M


[mysqld_safe]
open_files_limit        = 8192

[mysqldump]
quick
max_allowed_packet = 32M

[mysqlhotcopy]
interactive-timeout

Thanks.

 

[MattW]

Are you on a 64bit version of Debian?

Error : Innodb buffer pool usage value 96.8673467052 % should be less than 90.0 %
Warning : Innodb buffer pool usage value 96.8673467052 % should be less than 80.0 %

So your current buffer pool is at 2GB, and you are at 96% of that. Server has 4GB RAM, so I'd look at getting another 4GB of RAM in the box and upping your buffer pool first.

 

[Mouth]

Are you on a 64bit version of Debian?

Yes

So your current buffer pool is at 2GB, and you are at 96% of that. Server has 4GB RAM, so I'd look at getting another 4GB of RAM in the box and upping your buffer pool first.

Yes, but unfortunately not an option at the current time.

 

[MattW]

You'll not be able to get around that warning then with the current amount of RAM available and the size of your DB.

Do you actually notice any performance issues, or is it just the warnings you are getting from that monitoring service?

 

[Mouth]

Do you actually notice any performance issues, or is it just the warnings you are getting from that monitoring service.

No performance issues, and nothing of any significance within slow_queries logging, or queries_without_indexes logging. It was more about trying to learn what these warnings meant, and why.