Slow Response Times

[carltonwb]

I am running an Ubuntu Server VM that is being used for a website test development platform.

I am using Apache, MariaDB, PHP and such. It has it's own IP and is using a br0 network bridge.

I have devoted 4096 mb of ram and 4 cores to running it.

It is not the only VM that is STARTED.

 

My problem is that when I click on most internal links it takes over a minute to load the information from the database.

Comparing this to my same data on a hosted live site which loads in a few seconds.

 

What can I check to see what is causing the problem.

Thanks.

 

[ghost82]

1 hour ago, carltonwb said:

What can I check to see what is causing the problem

How big is the database?

Can you share the .cnf config file (mariadb)?

Edited July 1, 2021 by ghost82

[carltonwb]

Thanks for helping.

The database size is 36.0041 mb

The mysql.cnf file is.

  GNU nano 4.8                                                                                                                   mysql.cnf                                                                                                                             
#
# The MySQL database server configuration file.
#
# You can copy this 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

#
# * 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/
!includedir /etc/mysql/mysql.conf.d/

The debian.cnf mysql file is.

# Automatically generated for Debian scripts. DO NOT TOUCH!
[client]
host     = localhost
user     = debian-sys-maint
password = FR2x6lu7OlCzDvxw
socket   = /var/run/mysqld/mysqld.sock
[mysql_upgrade]
host     = localhost
user     = debian-sys-maint
password = FR2x6lu7OlCzDvxw
socket   = /var/run/mysqld/mysqld.sock

 

[ghost82]

42 minutes ago, carltonwb said:

Thanks for helping.

The database size is 36.0041 mb

The mysql.cnf file is.

mm no, don't you have a my.cnf?

It includes innodb settings, sorry if I wasn't clear enough.

[carltonwb]

The name of the file you are asking about is called my.cnf.

This is the file /etc/mysql/my.cnf

 

It seems that both files are identical.

#
# The MySQL database server configuration file.
#
# You can copy this 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

#
# * 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/
!includedir /etc/mysql/mysql.conf.d/

This is my /etc/mysql directory structure

drwxr-xr-x   4 root root 4096 Jul  1 19:01 .
drwxr-xr-x 102 root root 4096 Jul  1 06:31 ..
drwxr-xr-x   2 root root 4096 Jul  1 17:15 conf.d
-rw-------   1 root root  317 Jun 28 17:19 debian.cnf
-rwxr-xr-x   1 root root  120 May 11 12:49 debian-start
lrwxrwxrwx   1 root root   24 Jun 28 17:19 my.cnf -> /etc/alternatives/my.cnf
-rw-r--r--   1 root root  839 Aug  3  2016 my.cnf.fallback
-rw-r--r--   1 root root  682 Nov  6  2019 mysql.cnf
drwxr-xr-x   2 root root 4096 Jul  1 17:16 mysql.conf.d

 

[ghost82]

Are there any cnf files in 

 /etc/mysql/conf.d/ 

or in

/etc/mysql/mysql.conf.d/

?

[carltonwb]

Thanks for sticking with me.

This is what is in the /etc/mysql/conf.d/mysql.cnf

[mysql]

This is from the /etc/mysql/mysql.conf.d/mysql.cnf

# The MySQL database client configuration file
#
# Ref to https://dev.mysql.com/doc/refman/en/mysql-command-options.html

[mysql]

Should there be more in these files?

[ghost82]

Well, yes, I was expecting a file with some more entries for the config

I think that the slowness happens because the system "swaps" (memory), and I know that this can be very detrimental for database queries.

 

Apart this some people in the internet suggest to modify some variables in the config file, that's why I asked for it, especially the innodb-buffer-pool-size.

 

Look at here for example:

https://www.bloglogistics.com/mariadb-mysql-config-inndb-2-cpu-4-gb-ram/

 

There's a config for a 2 cpu+4GB ram system

 

Or here:

https://dba.stackexchange.com/questions/27328/how-large-should-be-mysql-innodb-buffer-pool-size

 

which explains how to calculate the value of innodb-buffer-pool-size.

 

Maybe it's an issue with the config, maybe the vm system is too poor to run that queries in that database.

How much free ram do you have when the os is started?

Edited July 3, 2021 by ghost82

[carltonwb]

Excuse me for asking but I have never checked the ram before. How do I check the amount free.

[ghost82]

I think you can use:
 

free -m

in terminal

[carltonwb]

The free memory is (thanks I am learning a lot)

root@Micah:~# free -m
              total        used        free      shared  buff/cache   available
Mem:          64252       13157       17220        1343       33874       49082
Swap:             0           0           0

 

[ghost82]

41 minutes ago, carltonwb said:

The free memory is (thanks I am learning a lot)

root@Micah:~# free -m
              total        used        free      shared  buff/cache   available
Mem:          64252       13157       17220        1343       33874       49082
Swap:             0           0           0

 

I think this is the ram of the unraid box, isn't it?

Run the command inside the terminal of your linux vm.

[carltonwb]

Here is the one from inside the VM

              total        used        free      shared  buff/cache   available
Mem:          3931         520         2944        3       467        3171
Swap:             0           0           0

 

[ghost82]

ok, now run everything you need to run the query (apache, sql, etc) and when the query is running run again the free -m command from inside the vm: "hopefully" we will see the ram getting busy.

[carltonwb]

I ran a comparison of the two ways. One with all processes stopped Apache/MySql etc, and one with everything running.

These are the results.

All processes stopped
              total        used        free      shared  buff/cache   available
Mem:           3931         205        2378           1        1347        3469
Swap:             0           0           0

All processes started
              total        used        free      shared  buff/cache   available
Mem:           3931         518        2992           3         421        3175
Swap:             0           0           0

 

[ghost82]

mmm, here there's nothing wrong: are you running the query when you say "everything running" or everything is started but idle?

If it is, monitor the ram when the query run.

If it's not the ram I don't know what's happening there...sorry

[carltonwb]

I will continue to monitor it and I greatly thank you for sticking with me and providing some guidance.

 

I will have to see what else I can tweak to get better response times.

Carlton