Large database problem

[azhamid]

hi
i just installed virtualmin and imported my woocommerce site , i have 10k products and my database size is about 180mb , my vps ram is 1gb and swap size is 270mb .
my problem is whenever i try edit products my ram get full and mysql stop working and get error : “unable to connect to database” , after i restart mysql i can reach my website again .
the result of “free -m” is :
total used free shared buff/cache available
Mem: 985 474 218 199 292 195
Swap: 270 244 26

is it possible to do anything or i should upgrade my vps to more ram ?
its only website in this vps

[calport]

Hello @azhamid and welcome to the community.

180 MB db size should not be a problem. You could try, in Webmin -> Servers -> MySQL Database Server -> MySQL Server Configuration:
Maximum packet size: 512 MB

If this does not solve your problem, feel free to undo the change.

When installing Virtualmin, do you remember what memory size you defined for MySQL in the Install Wizard? Options were small to large. If MySQL process is being terminated on your VPS due to lack of memory, you could try increasing RAM. Also see https://www.virtualmin.com/documentation/id%2Cvirtualmin_on_low_memory_systems

[azhamid]

when i click on MySQL Server Configuration i get :

Could not find [mysqld] section in my.cnf

[azhamid]

i picked default 1GB

[calport]

@azhamid

Could not find [mysqld] section in my.cnf

Hmm. Could you share details about OS and version of Webmin / Virtualmin that you have installed.

[azhamid]

os : ubuntu server 18
webmin/virtualmin i just installed last version today

[calport]

@azhamid

os : ubuntu server 18

Great, that’s one of the versions that Virtualmin supports fully. Could you post the contents of your my.cnf file?

[azhamid]

in etc/mysql/my.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/

in etc/mysql/mysql.conf.d/mysqld.cnf :

[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
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

key_buffer_size = 16M
max_allowed_packet = 16M
thread_stack = 192K
thread_cache_size = 8

This replaces the startup script and checks MyISAM tables if needed

the first time they are touched

myisam-recover-options = BACKUP
#max_connections = 100
#table_open_cache = 64
#thread_concurrency = 10

* Query Cache Configuration

query_cache_limit = 1M
query_cache_size = 16M

* 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 log - should be very few entries.

log_error = /var/log/mysql/error.log

Here you can see queries with especially long duration

#slow_query_log = 1
#slow_query_log_file = /var/log/mysql/mysql-slow.log
#long_query_time = 2
#log-queries-not-using-indexes

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
#log_bin = /var/log/mysql/mysql-bin.log
expire_logs_days = 10
max_binlog_size = 100M
#binlog_do_db = include_database_name
#binlog_ignore_db = include_database_name

* 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!

* 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

[azhamid]

all informations are in
etc/mysql/mysql.conf.d/mysqld.cnf

[azhamid]

in my.cnf is only :
!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/

[calport]

@azhamid

etc/mysql/mysql.conf.d/mysqld.cnf

Well spotted, @azhamid!

Now if you could apply this to the box next to the label captioned ‘MySQL configuration file’ in the Module Configuration of MySQL Database Server, we could all rejoice. How to get to the Module Configuration screen, you ask? Webmin → Servers → MySQL Database Server and then click the cog icon on the top left of the screen.

Once you have done the deed:
Webmin → Servers → MySQL Database Server → MySQL Server Configuration:
Maximum packet size: 512 MB

[azhamid]

@calport

Once you have done the deed:
Webmin → Servers → MySQL Database Server → MySQL Server Configuration:
Maximum packet size: 512 MB

do i need change " Query cache size in bytes " too ?

[calport]

Not necessarily. Could you go with just the one change to ‘Maximum packet size’ and see if it solves your problem?

[azhamid]

yes i think problem solved , really thank you

[calport]

You are most welcome @azhamid