Ad Widget

**arsalan.zahoor** · 12-04-2023, 13:06

MariaDB [(none)]> SELECT TABLE_SCHEMA, TABLE_NAME,(INDEX_LENGTH+DATA_LENGTH)/(1024*1024) AS SIZE_MB, TABLE_ROWS FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA NOT IN ("mysql", "information_schema") ORDER BY SIZE_MB DESC LIMIT 10;
+--------------+--------------+-------------+------------+
| TABLE_SCHEMA | TABLE_NAME | SIZE_MB | TABLE_ROWS |
+--------------+--------------+-------------+------------+
| zabbix | history_uint | 212552.2031 | 2226280917 |
| zabbix | history | 31372.2813 | 379889501 |
| zabbix | history_log | 11833.0469 | 26884077 |
| zabbix | trends_uint | 4288.3125 | 57214829 |
| zabbix | history_str | 1778.0781 | 22601337 |
| zabbix | trends | 1022.2813 | 12898724 |
| zabbix | alerts | 223.1406 | 462513 |
| zabbix | events | 215.0469 | 1042869 |
| zabbix | items | 39.6250 | 69808 |
| zabbix | auditlog | 19.0469 | 120969 |
+--------------+--------------+-------------+------------+

cat /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd

user=mysql
log-bin=/zabbix/log/mysql/bin/mysql-bin
expire_logs_days=7
max_connections = 1500
innodb_buffer_pool_size=12G
innodb_thread_concurrency=8
innodb_log_file_size=256M
innodb_file_per_table
innodb_log_buffer_size=8388608
innodb_io_capacity=500
innodb_flush_method=O_DIRECT
innodb_buffer_pool_instances=4
thread_cache_size=4

[mysqld_safe]
log-error=/zabbix/log/mysql/error/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

#
# include all files from the config directory
#
!includedir /etc/my.cnf.d

**arsalan.zahoor** · 17-04-2023, 13:15

Hi Guys, your support is requested i am new with zabbix and stuck with this.

**tim.mooney** · 17-04-2023, 19:04

From what you've described so far, this isn't a Zabbix problem, it's a mysql/MariaDB problem.

Have you enabled mysql logging? If so, have you looked at mysql's logs? Is there a chance you have some lingering corruption after the crash?

**arsalan.zahoor** · 17-04-2023, 19:37

Hi Tim,

Thank you for the response i was not able to find any thing in the DB logs and I was able to recover zabbix server but mysqld proccess is consuming high CPU. In the mysql process list i can find 29 following queries which are stuck and waiting for something. I have checked the item.id it does not exist in the history table i have doubt on housekeeper. Is there any way i can stop this query and manually drop the data in the history table with the same id.

| 2062773 | zabbix | localhost | zabbix | Query | 9901 | Sorting result | SELECT h.* FROM history h WHERE (h.itemid BETWEEN '27919' AND '27923' OR h.itemid BETWEEN '27968' AN | 0.000 |

Thanks
Arsalan

**tim.mooney** · 18-04-2023, 02:30

Even with nothing logged, this still really sounds like some kind of database corruption.

You should have a command called "mysqlcheck", if it's not installed then there may be a 'mariadb-utils' or 'mariadb-server-utils' or similar package that you need to install.

Read the man page for 'mysqlcheck' and then run it in "extended" check mode against either your 'zabbix' database or all databases. It's basically just doing "CHECK TABLE <tablename here>" in a loop, but it's a convenient way to check *all* your tables.

If any of the tables do not report "OK", then look at the "repair" option to mysqlcheck.

Let us know what the check operation reports.

**arsalan.zahoor** · 18-04-2023, 13:04

I will run mysql check today and will post the output Thanks

Ad Widget

Mysql process consuming high cpu after server crash

Mysql process consuming high cpu after server crash

Comment

Comment

Comment

Comment

Comment

Comment