IIssues

MySQL, Percona, MariaDB Error: Out of sort memory, consider increasing server sort buffer size!

  • byJordan
  • May 17, 2022
  • 1 minute read

There is a bug in MySQL 8.0.18, and above https://bugs.mysql.com/bug.php?id=103225 it was patched in 8.0.28 https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-28.html

It looks as though Percona released version 8.0.28, which includes all features and bug fixes in MySQL. However, if you only do security updates, it’s possible you might have an older version of Percona based on your server’s deployment date. You can run MySQL --version via SSH to confirm.

If you’re not on 8.0.28, then you can run apt-get update and then apt-get upgrade. However, this will upgrade all packages on the system. So instead, you might just want to update Percona by running apt-get install --only-upgrade percona-server-common.

If you’re worried about what apt-get upgrade will do, you can run it in safe mode and see what packages will be upgraded and their version; simply type apt-get -V -s upgrade.

As for MariaDB, this was patched in 10.5.7 https://jira.mariadb.org/browse/MDEV-24015 same process applies as above.

TTidbits

MySQL 8 Increasing table_open_cache in Ubuntu 20

  • byJordan
  • May 16, 2021
  • 1 minute read

So if you’ve ever had to increase table_open_cache in Ubuntu 20, you’ll probably find that it doesn’t work the traditional way. Which is mind blowing! But I have a guess as to why. It’s to stop you from blowing up your server from too many open files!

I had updated /etc/mysql/mysql.conf.d/mysqld.cnf with the following

Plain Text

However, MySQL was only showing the value set at 4945

Plain Text

After some Google searching, I found this thread https://askubuntu.com/questions/1241013/cannot-increase-mysql-table-open-cache-in-ubuntu-20-04

Which pointed me to /lib/systemd/system/mysql.service and the line LimitNOFILE

Plain Text

Based on the information in the post, the LimitNOFILE is causing an automatic change to the “table_open_cache” variable. Modifying it to say 20000 will allow you to go with an 8000 table_open_cache. Copy and pasting into WordPress wouldn’t have done this post justice. So I screenshotted it.

After making the changes and restarting MySQL I got table_open_cache set to 8000

UUncategorized

Setting up MySQL Statistics in Netdata

  • byJordan
  • January 6, 2021
  • 1 minute read
MySQL Metrics in Netdata

You might noticed MySQL statistics aren’t being show in Netdata. Here’s what I ran into.

Error: “MySQLdb or PyMySQL module is needed to use mysql.chart.py plugin”

The required python libraries for MySQL are missing, so you simply need to install them. For Ubuntu/Debian the following command should correct it.

Shell

Setting up Netdata MySQL User

You might also see these errors in your log.

Plain Text

The fix is simple, add a user called Netdata with a password. Note, you can setup the Netdata user without a password, but this may expose information to the system that you might not want. Full information is located on Netdata’s website.

https://learn.netdata.cloud/docs/agent/collectors/python.d.plugin/mysql

So let’s get Netdata pulling MySQL data.

Fast, open and potentially insecure no password optiom

This will just work, but leave a user on your MySQL instance without a password.

Plain Text

Password based polling

Let’s run the same command as above but give the user a password.

Plain Text

Now we need to tell Netdata the password.

Plain Text

Instead of editing the defaults, go to the bottom of the config and just create a new section with the following

Plain Text

Now you should see MySQL metrics in Netdata

View of MySQL Metrics in Netdata
DDrafts

MySQL 8 and Persona 8 Tuning for WordPress

  • byJordan
  • May 31, 2019
  • 1 minute read

If you’re running MySQL 8 or Percona 8, then you’ll probably see lots of guides online that reference some out of data configuration that really only deals with functions in MySQL 5 that have been deprecated in MySQL 8 and Percona 8

Query Cache

No longer used, and removed from MySQL 8 and Percona 8, alternative is ProxySQL as per the following Percona article https://www.percona.com/blog/2018/02/07/proxysql-query-cache/

DDrafts

Creating Users and Databases in MySQL 8 and Percona 8

  • byJordan
  • May 30, 2019
  • 1 minute read

If you’ve started using MySQL 8, you’ll notice your usually commands for creating a user no longer function. That’s because it’s changed in MySQL 8, you need to now create a user, then grant privileges.

Let’s create a user

CREATE USER ‘newuser’@'localhost' IDENTIFIED WITH mysql_native_password BY ‘user_password';

The critical part here is the “mysql_native_password” if you don’t have this portion, you’ll get errors like the following.

PHP Warning: mysqli_connect(): The server requested authentication method unknown to the client [caching_sha2_password] in mysql_test.php on line 8
PHP Warning: mysqli_connect(): (HY000/2054): The server requested authentication method unknown to the client in mysql_test.php on line 8

This is due to the default authentication plugin being set to sha2, you can change it back to native by adding the following into your my.cnf

default-authentication-plugin=mysql_native_password

Now let’s grant the user privileges.

GRANT ALL ON database_name.* TO ‘newuser’@'localhost';

Now confirm the privileges.

SHOW GRANTS FOR 'newuser’@'localhost';

Maybe you want to Update a Password

ALTER USER ‘root'@'localhost' IDENTIFIED BY 'MyNewPass';
DDrafts

Searching WordPress MySQL Dump Files

  • byJordan
  • May 30, 2019
  • 1 minute read

From time to time, you’ll need to look at data within MySQL dump files, specifically I do it mostly with WordPress. There isn’t an easy way to search the dump unless you use grep. Here are some useful grep commands.

Pull out a table.

[enlighter linenumbers=”true”] grep ‘INSERT INTO `wp_bspr_users`’ dump.sql > /tmp/users.sql [/enlighter]

Format a table in a readable format.

[enlighter linenumbers=”true”]cat dump.sql | grep ‘INSERT INTO `wp_users`’ | sed ‘s/),/’$’n/g’ [/enlighter]

Now if you run a popular shell like csh, you can add the following function.

[enlighter linenumbers=”true”]msd () { grep “INSERT INTO \`$2\`” $1 | sed “s/),/\’$’\n/g” }[/enlighter]

[enlighter linenumbers=”true”]Then run the following[/enlighter]

msd dump.sql wp_users

This might work, don’t know!

UUncategorized

Configuring MySQL for Low Memory VPS

  • byJordan
  • September 8, 2011
  • 1 minute read

I’ve found the following configuration helps when you’re trying to squeeze out as much memory from a VPS as possible.

 


[mysqld]
port = 3306
socket = /var/lib/mysql/mysql.sock
skip-locking
key_buffer = 16K
max_allowed_packet = 1M
table_cache = 4
sort_buffer_size = 64K
read_buffer_size = 256K
read_rnd_buffer_size = 256K
net_buffer_length = 2K
thread_stack = 64K

# For low memory, Berkeley DB should not be used so keep skip-bdb uncommented unless required
skip-bdb

# For low memory, InnoDB should not be used so keep skip-innodb uncommented unless required
skip-innodb

# Uncomment the following if you are using InnoDB tables
#innodb_data_home_dir = /var/lib/mysql/
#innodb_data_file_path = ibdata1:10M:autoextend
#innodb_log_group_home_dir = /var/lib/mysql/
#innodb_log_arch_dir = /var/lib/mysql/
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
#innodb_buffer_pool_size = 16M
#innodb_additional_mem_pool_size = 2M
# Set .._log_file_size to 25 % of buffer pool size
#innodb_log_file_size = 5M
#innodb_log_buffer_size = 8M
#innodb_flush_log_at_trx_commit = 1
#innodb_lock_wait_timeout = 50

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates

[isamchk]
key_buffer = 8M
sort_buffer_size = 8M

[myisamchk]
key_buffer = 8M
sort_buffer_size = 8M

[mysqlhotcopy]
interactive-timeout