July 1, 20215 yr 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.
July 1, 20215 yr 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, 20215 yr by ghost82
July 1, 20215 yr Author 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
July 1, 20215 yr 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.
July 1, 20215 yr Author 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
July 2, 20215 yr Author 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?
July 3, 20215 yr 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, 20215 yr by ghost82
July 3, 20215 yr Author Excuse me for asking but I have never checked the ram before. How do I check the amount free.
July 4, 20215 yr Author 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
July 4, 20215 yr 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.
July 4, 20215 yr Author 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
July 4, 20215 yr 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.
July 4, 20215 yr Author 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
July 4, 20215 yr 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
July 4, 20215 yr Author 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
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.