Jump to content
Search In
  • More options...
Find results that contain...
Find results in...

Linux - Slow Mysql Database

Go to solution Solved by 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

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

Link to comment
Share on other sites

Link to post
Share on other sites

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/

 

 

Link to comment
Share on other sites

Link to post
Share on other sites

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

Link to comment
Share on other sites

Link to post
Share on other sites

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)

 

 

Link to comment
Share on other sites

Link to post
Share on other sites

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

Link to comment
Share on other sites

Link to post
Share on other sites

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

 

 

 

Link to comment
Share on other sites

Link to post
Share on other sites

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

Link to comment
Share on other sites

Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share


×