Linux - Slow Mysql Database

[Depstros]

So few months back i installed Mysql With phpmyadmin (MariaDB) and now its super slow im not friend of mysql but i have to use it. The DB size is something around 1gb on ubuntu 20.04 i did not change any seettings or something like that. The database has slow load time and cant take many writes at one moment

[mariushm]

Make sure the tables have proper primary keys and indexes.

If you use InnoDB make sure there's big enough buffers.  Double check mysql config file for how much memory mysql is allowed to use, how big the transaction log for InnoDB is and other things... 

Wherever possible, one should use InnoDB tables, MyISAM in general should only be used for tables that are mostly read only.

 

Use utf8mb4 if you store unicode characters... 

 

Pick some of the queries that take a long time, and use tools to analyze the query , or use the EXPLAIN command in mysql  : https://dev.mysql.com/blog-archive/mysql-explain-analyze/

 

 

[Depstros]

27 minutes ago, mariushm said:

Make sure the tables have proper primary keys and indexes.

If you use InnoDB make sure there's big enough buffers.  Double check mysql config file for how much memory mysql is allowed to use, how big the transaction log for InnoDB is and other things... 

Wherever possible, one should use InnoDB tables, MyISAM in general should only be used for tables that are mostly read only.

 

Use utf8mb4 if you store unicode characters... 

 

Pick some of the queries that take a long time, and use tools to analyze the query , or use the EXPLAIN command in mysql  : https://dev.mysql.com/blog-archive/mysql-explain-analyze/

 

 

i dont see ny my.conf anything about ram or something

[Depstros]

Databases uses utf8mb4_general_cli

[mariushm]

It's called  my.cnf

 

This page has a list of commands you may have in my.cnf :  https://getmoven.com/index.php/knowledgebase/18/Sample-my.cnf---MySQL-Configuration-File.html

 

Note that the values may not be correct for you, so don't just copy paste without understanding what they do.

 

Pay attention to stuff that defines number of connections, number of tables opened in memory, amount of megabytes used for temporary tables, sorting etc, set the maximum ram usage to something reasonable (don't limit your sql server to 128-500 MB if your server has 8-16 GB which sit mostly unused)

 

 

[Depstros]

5 minutes ago, mariushm said:

It's called  my.cnf

 

This page has a list of commands you may have in my.cnf :  https://getmoven.com/index.php/knowledgebase/18/Sample-my.cnf---MySQL-Configuration-File.html

 

Note that the values may not be correct for you, so don't just copy paste without understanding what they do.

 

Pay attention to stuff that defines number of connections, number of tables opened in memory, amount of megabytes used for temporary tables, sorting etc, set the maximum ram usage to something reasonable (don't limit your sql server to 128-500 MB if your server has 8-16 GB which sit mostly unused)

 

 

my server has almost 1GB

[mariushm]

12 minutes ago, Depstros said:

Databases uses utf8mb4_general_cli

That's collation.  Character set is utf8mb4 and one of the collations you could use (and most often used is utf8mb4_general_ci ) but it doesn't have to be and one should often check if new versions introduce new collations or corrected collations (especially when a new unicode version is released)

 

See Supported Character Sets and Collations - MariaDB Knowledge Base

MariaDB also has collations with nopad, which account for trailing spaces, something that can be useful in some scenarios.

 

As for what does utf8mb4 means here's an explanation :

UTF8MB4 | Die wunderbare Welt von Isotopp   (it's in English, the blog title is German)

In MySQL, never use “utf8”. Use “utf8mb4”. | by Adam Hooper | Medium

 

 

 

[AkIonSight]

Try Increasing working memory, idk about MySQL, but postgres has it aggressively low at about only 8mb

 

This single-handedly should increase perfs by a considerable margin