Performance tuning suggestion on Nginx, MariaDB, Redis or other variables

Install / Update Deployment
1

Dear Friends,

Just a newbie question. Can anyone please suggest which Nginx, MariaDB, Redis or other services variables that need to be considered for tuning client-side performance?

I noticed slow response while opening Item List, and sometime the screen goes freeze white.

So far, I am thinking these MariaDB variables may need adjustment, or at least on my case:

  • innodb-buffer-pool-size
  • innodb-log-file-size
  • tmp-table-size & max-heap-table-size
  • query_cache_limit & query-cache-size

I run a small server at Google Cloud with only 5-10 concurrent users, around 3,000+ items, 400+ customer & supplier contacts and less than 200 average transactions a day, mostly running CRM, Selling, Buying, Inventory, Support & Maintenance (Accounting function is on other system). The server serves https to clients and running under Ubuntu 16.04.

Thank you in advance for your suggestion.

Lucky

4 Likes
2

Hi, all,

This is what I tune on my.cnf. So far, I am satisfy with the response performance.
I have not change any variable on Nginx or Redis yet.

skip-name-resolve       = 1
#
# * Fine Tuning
#
max_connections         = 200
max_allowed_packet      = 32M
thread_cache_size       = 32
sort_buffer_size        = 2M
bulk_insert_buffer_size = 16M
tmp_table_size          = 128M
max_heap_table_size     = 128M
max_connect_errors      = 1000000
#
# * MyISAM
#
myisam_recover_options  = BACKUP
key_buffer_size         = 8M
open_files_limit        = 32768
table_definition_cache  = 4096
table_open_cache        = 512
myisam_sort_buffer_size = 2M
concurrent_insert       = 2
read_buffer_size        = 512K
read_rnd_buffer_size    = 4M
join_buffer_size        = 2M
#
# * Query Cache Configuration
#
query_cache_limit       = 8M
query_cache_size        = 64M
query_cache_type        = 1
#
# * Logging and Replication
#
slow_query_log                  = 1
long_query_time                 = 5
log-queries-not-using-indexes
sync_binlog                     = 1
binlog_cache_size               = 32K
#
# * InnoDB
#
innodb_log_file_size            = 128M
innodb_buffer_pool_size         = 1G
innodb_log_buffer_size          = 8M
innodb_file_per_table           = 1
innodb_open_files               = 512
innodb_io_capacity              = 512
innodb_flush_method             = O_DIRECT
innodb_log_files_in_group       = 2
innodb_flush_log_at_trx_commit  = 1
innodb_buffer_pool_instances    = 1
innodb_sort_buffer_size         = 4M
1 Like
3

Hello,

I am also facing same issue. Sometime it’s very slow and freeze a screen.

4

@Harsh_Mehta,

For database tuning, I think those parameters on my previous reply above are key to balance performance vs resource. You may adjust those, however pay attention to your workload and system size. I suggest to try this sizing tool by Percona: MySQL Tools and Management Software to Perform System Tasks by Percona.

-Lucky

1 Like
5

Hi,

Have you resolved your problem by this tool.

6

Yes, with this tool and some trial & error.