MySQL configuration adjustment for 1G DB, 8GB Ram

Question

I have a MySQL DB size of 1GB, running on dedicated server with 4C/4T, 8GB of Ram and an SSD drive.

My website is having over 200.000 articles and when using search functions (SELECT queries), it gets slow or unresponsive to the point I need to kill the processes from my WHM panel.

I asked for support today to show me my.conf file and as I can see, It's running on defaults. Here is the data:

max_connections = 150
max_user_connections = 100
query_cache_size = 64M
skip-external-locking
key_buffer_size = 64M
max_allowed_packet=268435456
table_open_cache = 256
sort_buffer_size = 1M
net_buffer_length = 16K
read_buffer_size = 1M
read_rnd_buffer_size = 1M
myisam_sort_buffer_size = 32M
innodb_file_per_table
tmp_table_size = 64M
max_heap_table_size = 64M
thread_cache_size = 8
query_cache_type = on

# This setting allows the use of asynchronous I/O in InnoDB.
# The following files track usage of this resource:
# - /proc/sys/fs/aio-max-nr
# - /proc/sys/fs/aio-nr
# Default limit is 65536, of which a single instance of mysql uses 2661 out   of the box
innodb_use_native_aio = 1
default_storage_engine = MyISAM
open_files_limit=10000

In the given values, I don't see the presence of innodb_buffer_pool_size, and the guy from the support asked would I like to add it to my.conf. I read it's important value, so it probably should be added?

The largest tables in my DB are InnoDB and MyISAM is the default storage engine.

What would be the recommended configuration here and which values would need to be adjusted to gain optimal performance?

Is there anything else needs to be defined in my.cnf like innodb_buffer_pool_size or innodb_log_file_size?

Answer 1

You can't "tune your way out of performance problems", but here are settings to change:

key_buffer_size = 800M
innodb_buffer_pool_size = 1500M

InnoDB is preferred to MyISAM. (If you change, then more changes will be needed.)

What version are you using?

Usually performance depends on indexes and query formulation. Let's see a slow query, together with SHOW CREATE TABLE for its table(s).

0.0002sec sounds like it was found in the Query cache. Run timings thus: SELECT SQL_NO_CACHE ... to avoid unrealistic amounts.

Answer 2

For MYISAM tables:

Key_buffer_size = should be 20% to 30% of Physically RAM memory. Calculate according to your 8 GB RAM.

I would suggest you to change the engine of MYSQL to INNODB

Third one : Your query slow because of lack of indexes or query optimization. USE EXPLAIN plan for query and optimize according to it by adding index to table.