Very slow and very bad performance v7

im facing a very slow system and i followed all the steps

there is 9 users and 5 of them is ( pos ) and 4 as backend
im usuing 6 cores vm throw hyperV and 16g ram and i set
innodb-buffer as 9gb
redis-cache 1600mb

and here is my.cnf file

MariaDB database server configuration file.

You can copy this file to one of:

- “/etc/mysql/my.cnf” to set global options,

- “~/.my.cnf” to set user-specific options.

One can use all long options that the program supports.

Run program with --help to get a list of available options and with

–print-defaults to see which it would actually understand and use.

For explanations see

http://dev.mysql.com/doc/mysql/en/server-system-variables.html

This will be passed to all mysql clients

It has been reported that passwords should be enclosed with ticks/quotes

escpecially if they contain “#” chars…

Remember to edit /etc/mysql/debian.cnf when changing the socket location.

[client]
port = 3306
socket = /var/run/mysqld/mysqld.sock

Here is entries for some specific programs

The following values assume you have at least 32M ram

This was formally known as [safe_mysqld]. Both versions are currently parsed.

[mysqld_safe]
socket = /var/run/mysqld/mysqld.sock
nice = 0

[mysqld]

* Basic Settings

user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
lc_messages_dir = /usr/share/mysql
lc_messages = en_US
skip-external-locking

Instead of skip-networking the default is now to listen only on

localhost which is more compatible and is not less secure.

bind-address = 127.0.0.1

* Fine Tuning

max_connections = 100
connect_timeout = 5
wait_timeout = 600
max_allowed_packet = 16M
thread_cache_size = 128
sort_buffer_size = 4M
bulk_insert_buffer_size = 16M
tmp_table_size = 32M
max_heap_table_size = 32M

* MyISAM

This replaces the startup script and checks MyISAM tables if needed

the first time they are touched. On error, make copy and try a repair.

myisam_recover_options = BACKUP
key_buffer_size = 128M
#open-files-limit = 2000
table_open_cache = 400
myisam_sort_buffer_size = 512M
concurrent_insert = 2
read_buffer_size = 2M
read_rnd_buffer_size = 1M

* Query Cache Configuration

Cache only tiny result sets, so we can fit more in the query cache.

query_cache_limit = 128K
query_cache_size = 64M

for more write intensive setups, set to DEMAND or OFF

#query_cache_type = DEMAND

* Logging and Replication

Both location gets rotated by the cronjob.

Be aware that this log type is a performance killer.

As of 5.1 you can enable the log at runtime!

#general_log_file = /var/log/mysql/mysql.log
#general_log = 1

Error logging goes to syslog due to /etc/mysql/conf.d/mysqld_safe_syslog.cnf.

we do want to know about network errors and such

log_warnings = 2

Enable the slow query log to see queries with especially long duration

#slow_query_log[={0|1}]
slow_query_log_file = /var/log/mysql/mariadb-slow.log
long_query_time = 10
#log_slow_rate_limit = 1000
log_slow_verbosity = query_plan

#log-queries-not-using-indexes
#log_slow_admin_statements

The following can be used as easy to replay backup logs or for replication.

note: if you are setting up a replication slave, see README.Debian about

other settings you may need to change.

#server-id = 1
#report_host = master1
#auto_increment_increment = 2
#auto_increment_offset = 1
log_bin = /var/log/mysql/mariadb-bin
log_bin_index = /var/log/mysql/mariadb-bin.index

not fab for performance, but safer

#sync_binlog = 1
expire_logs_days = 10
max_binlog_size = 100M

slaves

#relay_log = /var/log/mysql/relay-bin
#relay_log_index = /var/log/mysql/relay-bin.index
#relay_log_info_file = /var/log/mysql/relay-bin.info
#log_slave_updates
#read_only

If applications support it, this stricter sql_mode prevents some

mistakes like inserting invalid dates etc.

#sql_mode = NO_ENGINE_SUBSTITUTION,TRADITIONAL

* InnoDB

InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.

Read the manual for more InnoDB related options. There are many!

default_storage_engine = InnoDB

you can’t just change log file size, requires special procedure

#innodb_log_file_size = 50M
innodb_buffer_pool_size = 256M
innodb_log_buffer_size = 8M
innodb_file_per_table = 1
innodb_open_files = 400
innodb_io_capacity = 400
innodb_flush_method = O_DIRECT

* Security Features

Read the manual, too, if you want chroot!

chroot = /var/lib/mysql/

For generating SSL certificates I recommend the OpenSSL GUI “tinyca”.

ssl-ca=/etc/mysql/cacert.pem

ssl-cert=/etc/mysql/server-cert.pem

ssl-key=/etc/mysql/server-key.pem

* Galera-related settings

[galera]

Mandatory settings

#wsrep_on=ON
#wsrep_provider=
#wsrep_cluster_address=
#binlog_format=row
#default_storage_engine=InnoDB
#innodb_autoinc_lock_mode=2

Allow server to accept connections on all interfaces.

#bind-address=0.0.0.0

Optional setting

#wsrep_slave_threads=1
#innodb_flush_log_at_trx_commit=0

[mysqldump]
quick
quote-names
max_allowed_packet = 16M

[mysql]
#no-auto-rehash # faster start of mysql but no tab completion

[isamchk]
key_buffer = 16M

* IMPORTANT: Additional settings that can override those from this file!

The files must end with ‘.cnf’, otherwise they’ll be ignored.

!includedir /etc/mysql/conf.d/

******* here is my frappe.cnf file **********

[mysqld]
innodb_buffer_pool_size=3928M
innodb-file-format=barracuda
innodb-file-per-table=1
innodb-large-prefix=1
character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
max_allowed_packet=64M

[mysql]
default-character-set = utf8mb4

and my slow-query log is empty

******* my nginx.conf ********

For more information on configuration, see:

* Official English Documentation: http://nginx.org/en/docs/

* Official Russian Documentation: http://nginx.org/ru/docs/

user www-data;
worker_processes auto;
error_log /var/log/nginx/error.log;
pid /run/nginx.pid;

events {
worker_connections 65536;
use epoll;
multi_accept on;
}

http {
log_format main '$remote_addr - $remote_user [$time_local] “$request” ’
'$status $body_bytes_sent “$http_referer” ’
‘"$http_user_agent" “$http_x_forwarded_for”’;

access_log  /var/log/nginx/access.log  main;




sendfile            on;
tcp_nopush          on;
tcp_nodelay         on;
keepalive_timeout   65;
keepalive_requests 100000;
types_hash_max_size 2048;

client_body_buffer_size    128k;
client_max_body_size       10m;
client_header_buffer_size    1k;
large_client_header_buffers  4 4k;
output_buffers   1 32k;

postpone_output 1460;

client_header_timeout  3m;
client_body_timeout    3m;
send_timeout           3m;

open_file_cache max=1000 inactive=20s;
open_file_cache_valid 30s;
open_file_cache_min_uses 5;
open_file_cache_errors off;

include             /etc/nginx/mime.types;
default_type        application/octet-stream;

# Load modular configuration files from the /etc/nginx/conf.d directory.
# See http://nginx.org/en/docs/ngx_core_module.html#include
# for more information.
include /etc/nginx/conf.d/*.conf;

}

so can i find some help to optimize this server

Someone (@noetico) was recently experiencing such slowness. Performance improved significantly after upgrading to v9. If you will go that path, please don’t forget to back up your database

Hello Sir; as @tundebabzy pointed, please upgrade, backup your vm image I also suggest, this way you have a quick image to restore if your update fails or breaks. Export an appliance. or make a copy of the raw vmdk file on the same server so its just minutes to restore if your update fails. Here’s a link for updates:

SYSTEM:
Also, for your server, I found from experience that it’s not about the number of cores but the processor speed, the core i7 is the best, if you have the money get the turbo boost, Quad, maybe used from ebay, around $400 to $600. We also ran an 8 core Dell r610, 2.5GHz (8 cores given to vm) and a HP elitebook, core i7, 2.9GHz (only 2 cores given to vm) was way faster. Also throw in an SSD hard drive; that’s a MAJOR boost…

Overall the upgrade gave a boost to POS and their are fine features too. Go for it, when you do, stick to the online POS and follow the forums to see if we make headway with any patches.

Good luck!