NDO-3

[tbarnett]

Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 236564
Server version: 5.5.68-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> SELECTCONCAT(CEILING(ibps/POWER(1024,1)),'K') IBPS_KB,
-> CONCAT(CEILING(ibps/POWER(1024,2)),'M') IBPS_MB,
-> CONCAT(CEILING(ibps/POWER(1024,3)),'G') IBPS_GB
-> FROM(SELECTSUM(data_length+index_length) ibps
-> FROMinformation_schema.tables WHEREengine='InnoDB') A;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'SELECTCONCAT(CEILING(ibps/POWER(1024,1)),'K') IBPS_KB,
CONCAT(CEILING(ibps/P' at line 1
MariaDB [(none)]>
MariaDB [(none)]> SHOW ENGINE INNODB STATUS\G;
*************************** 1. row ***************************
Type: InnoDB
Name:
Status:
=====================================
210812 12:01:05 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 14 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 162519 1_second, 162516 sleeps, 16133 10_second, 1838 background, 1838 flush
srv_master_thread log flush and writes: 161393
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 1174963, signal count 1386231
Mutex spin waits 4576009, rounds 4170048, OS waits 7626
RW-shared spins 6056866, rounds 102064851, OS waits 1077786
RW-excl spins 241, rounds 186842, OS waits 673
Spin rounds per wait: 0.91 mutex, 16.85 RW-shared, 775.28 RW-excl
--------
FILE I/O
--------
I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
I/O thread 1 state: waiting for completed aio requests (log thread)
I/O thread 2 state: waiting for completed aio requests (read thread)
I/O thread 3 state: waiting for completed aio requests (read thread)
I/O thread 4 state: waiting for completed aio requests (read thread)
I/O thread 5 state: waiting for completed aio requests (read thread)
I/O thread 6 state: waiting for completed aio requests (write thread)
I/O thread 7 state: waiting for completed aio requests (write thread)
I/O thread 8 state: waiting for completed aio requests (write thread)
I/O thread 9 state: waiting for completed aio requests (write thread)
Pending normal aio reads: 0 [0, 0, 0, 0] , aio writes: 0 [0, 0, 0, 0] ,
ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
52426960 OS file reads, 1585953 OS file writes, 328872 OS fsyncs
0.79 reads/s, 16384 avg bytes/read, 6.71 writes/s, 4.29 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 3, seg size 5, 116 merges
merged operations:
insert 0, delete mark 5461, delete 2167
discarded operations:
insert 0, delete mark 0, delete 0
Hash table size 2212673, node heap has 2 buffer(s)
3.29 hash searches/s, 4.29 non-hash searches/s
---
LOG
---
Log sequence number 177898120716
Log flushed up to 177898120716
Last checkpoint at 177898090218
Max checkpoint age 434154333
Checkpoint age target 420587011
Modified age 30498
Checkpoint age 30498
0 pending log writes, 0 pending chkp writes
241127 log i/o's done, 3.93 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 1102053376; in additional pool allocated 0
Total memory allocated by read views 104
Internal hash tables (constant factor + variable factor)
Adaptive hash index 17738368 (17701384 + 36984)
Page hash 1107208 (buffer pool 0 only)
Dictionary cache 4559255 (4427312 + 131943)
File system 90448 (82672 + 7776)
Lock system 2662440 (2657176 + 5264)
Recovery system 0 (0 + 0)
Dictionary memory allocated 131943
Buffer pool size 65535
Buffer pool size, bytes 1073725440
Free buffers 145
Database pages 65388
Old database pages 24117
Modified db pages 62
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 52355235, not young 0
0.86 youngs/s, 0.00 non-youngs/s
Pages read 52426811, created 874515, written 1296988
0.79 reads/s, 0.00 creates/s, 2.71 writes/s
Buffer pool hit rate 979 / 1000, young-making rate 23 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 65388, unzip_LRU len: 0
I/O sum[592]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
1 read views open inside InnoDB
0 transactions active inside InnoDB
0 out of 1000 descriptors used
---OLDEST VIEW---
Normal read view
Read view low limit trx n:o 333992
Read view up limit trx id 333992
Read view low limit trx id 333992
Read view individually stored trx ids:
-----------------
Main thread process no. 32609, id 140506217285376, state: sleeping
Number of rows inserted 20978720, updated 94910, deleted 108188, read 1278194764
3.14 inserts/s, 0.64 updates/s, 0.00 deletes/s, 2.57 reads/s
------------
TRANSACTIONS
------------
Trx id counter 333996
Purge done for trx's n:o < 33398D undo n:o < 0
History list length 1324
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started
MySQL thread id 236564, OS thread handle 0x7fca18272700, query id 266808821 localhost root
SHOW ENGINE INNODB STATUS
---TRANSACTION 0, not started
MySQL thread id 236778, OS thread handle 0x7fca2418c700, query id 266808673 172.16.4.162 nagiosxi
---TRANSACTION 33397C, not started
MySQL thread id 236641, OS thread handle 0x7fca2479e700, query id 266803840 172.16.4.162 nagiosxi
---TRANSACTION 33396B, not started
MySQL thread id 236433, OS thread handle 0x7fca181de700, query id 266800705 172.16.4.162 nagiosxi
---TRANSACTION 333987, not started
MySQL thread id 236294, OS thread handle 0x7fc95b62b700, query id 266806457 172.16.4.162 nagiosxi
---TRANSACTION 333940, not started
MySQL thread id 235908, OS thread handle 0x7fca1814a700, query id 266783382 172.16.4.162 nagiosxi
---TRANSACTION 333957, not started
MySQL thread id 235672, OS thread handle 0x7fc95adc9700, query id 266808576 172.16.4.162 nagiosxi
---TRANSACTION 3338F9, not started
MySQL thread id 235665, OS thread handle 0x7fca2470a700, query id 266808518 172.16.4.162 nagiosxi
---TRANSACTION 33395C, not started
MySQL thread id 235648, OS thread handle 0x7fca28bda700, query id 266798008 172.16.4.162 nagiosxi
---TRANSACTION 333959, not started
MySQL thread id 235461, OS thread handle 0x7fca24676700, query id 266794128 172.16.4.162 nagiosxi
---TRANSACTION 33394C, not started
MySQL thread id 235387, OS thread handle 0x7fc95b54d700, query id 266790120 172.16.4.162 nagiosxi
---TRANSACTION 33398B, not started
MySQL thread id 235019, OS thread handle 0x7fc95b753700, query id 266807146 172.16.4.162 nagiosxi
---TRANSACTION 33395F, not started
MySQL thread id 234683, OS thread handle 0x7fca183e4700, query id 266798427 172.16.4.162 nagiosxi
---TRANSACTION 33396F, not started
MySQL thread id 234238, OS thread handle 0x7fc95b21f700, query id 266801348 172.16.4.162 nagiosxi
----------------------------
END OF INNODB MONITOR OUTPUT
============================

1 row in set (0.01 sec)

ERROR: No query specified

MariaDB [(none)]>

[tbarnett]

I see you have Percona XtraDB installed. Is that db clustering software? Is this db part of a cluster?

I don't know what Percona XtrDB is.

There was some thought of making this DB part of a cluster but it was NOT done.

There is no cluster.

That must have been left over from when it was under consideration.

Tom

[gsmith]

Hey Tom

1. log in to mysql

Code: Select all

  use information_schema;
SELECT CONCAT(CEILING(ibps/POWER(1024,1)),'K') IBPS_KB,
    CONCAT(CEILING(ibps/POWER(1024,2)),'M') IBPS_MB,
    CONCAT(CEILING(ibps/POWER(1024,3)),'G') IBPS_GB
FROM(SELECT SUM(data_length+index_length) ibps
FROM tables WHERE engine='InnoDB') A;

please send me the output of that

2.from command line:

Code: Select all

mysqldump -u root -p nagios  nagios_servicechecks  > /tmp/nagios_servicechecks.sql

log in to mysql:

Code: Select all

use nagios;
check table nagios_servicechecks;
repair table nagios_servicechecks use_frm;

you can repeat the above for any tables giving you trouble

3. in your mariadb.log I see:

Code: Select all

210809 20:16:18 Percona XtraDB (http://www.percona.com) 5.5.61-MariaDB-38.13 started; log sequence number 156019381932

It would be great if you can uninstall it, as it would remove an unknown variable to solving your db issues.

Let me know how things go.

Thanks

[tbarnett]

Here are the results you requested.

I am removing the Percona stuff.

----------------------------------------------


MariaDB [(none)]> use information_schema;
SELECT CONCAT(CEILING(ibps/POWER(1024,1)),'K') IBPS_KB,
CONCAT(CEILING(ibps/POWER(1024,2)),'M') IBPS_MB,
CONCAT(CEILING(ibps/POWER(1024,3)),'G') IBPS_GB
FROM(SELECT SUM(data_length+index_length) ibps
FROM tables WHERE engine='InnoDB') A;Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [information_schema]> SELECT CONCAT(CEILING(ibps/POWER(1024,1)),'K') IBPS_KB,
-> CONCAT(CEILING(ibps/POWER(1024,2)),'M') IBPS_MB,
-> CONCAT(CEILING(ibps/POWER(1024,3)),'G') IBPS_GB
-> FROM(SELECT SUM(data_length+index_length) ibps
-> FROM tables WHERE engine='InnoDB') A;
+---------+---------+---------+
| IBPS_KB | IBPS_MB | IBPS_GB |
+---------+---------+---------+
| 795696K | 778M | 1G |
+---------+---------+---------+
1 row in set (0.01 sec)

----------------------------------------------------------------------------------------------------

[barnt@prime.com@nagiosxidb ~]$ cat /tmp/nagios_servicechecks.sql
-- MySQL dump 10.14 Distrib 5.5.68-MariaDB, for Linux (x86_64)
--
-- Host: localhost Database: nagios
-- ------------------------------------------------------
-- Server version 5.5.68-MariaDB

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `nagios_servicechecks`
--

DROP TABLE IF EXISTS `nagios_servicechecks`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `nagios_servicechecks` (
`servicecheck_id` int(11) NOT NULL AUTO_INCREMENT,
`instance_id` smallint(6) NOT NULL DEFAULT '0',
`service_object_id` int(11) NOT NULL DEFAULT '0',
`check_type` smallint(6) NOT NULL DEFAULT '0',
`current_check_attempt` smallint(6) NOT NULL DEFAULT '0',
`max_check_attempts` smallint(6) NOT NULL DEFAULT '0',
`state` smallint(6) NOT NULL DEFAULT '0',
`state_type` smallint(6) NOT NULL DEFAULT '0',
`start_time` datetime NOT NULL DEFAULT '1970-01-01 00:00:01',
`start_time_usec` int(11) NOT NULL DEFAULT '0',
`end_time` datetime NOT NULL DEFAULT '1970-01-01 00:00:01',
`end_time_usec` int(11) NOT NULL DEFAULT '0',
`command_object_id` int(11) NOT NULL DEFAULT '0',
`command_args` varchar(2048) NOT NULL DEFAULT '',
`command_line` text NOT NULL,
`timeout` smallint(6) NOT NULL DEFAULT '0',
`early_timeout` smallint(6) NOT NULL DEFAULT '0',
`execution_time` double NOT NULL DEFAULT '0',
`latency` double NOT NULL DEFAULT '0',
`return_code` smallint(6) NOT NULL DEFAULT '0',
`output` text NOT NULL,
`long_output` varchar(8192) NOT NULL DEFAULT '',
`perfdata` text NOT NULL,
PRIMARY KEY (`servicecheck_id`),
KEY `instance_id` (`instance_id`),
KEY `service_object_id` (`service_object_id`),
KEY `start_time` (`start_time`)
) ENGINE=MyISAM AUTO_INCREMENT=2147483648 DEFAULT CHARSET=utf8 COMMENT='Historical service checks';
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `nagios_servicechecks`
--

LOCK TABLES `nagios_servicechecks` WRITE;
/*!40000 ALTER TABLE `nagios_servicechecks` DISABLE KEYS */;
/*!40000 ALTER TABLE `nagios_servicechecks` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2021-08-16 14:00:14
[barnt@prime.com@nagiosxidb ~]$

---------------------------------------------------------------------------------------------------------------


MariaDB [(none)]> use nagios;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [nagios]> check table nagios_servicechecks;
+-----------------------------+-------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+-----------------------------+-------+----------+----------+
| nagios.nagios_servicechecks | check | status | OK |
+-----------------------------+-------+----------+----------+
1 row in set (0.04 sec)

MariaDB [nagios]> repair table nagios_servicechecks use_frm;
+-----------------------------+--------+----------+----------------------------------------+
| Table | Op | Msg_type | Msg_text |
+-----------------------------+--------+----------+----------------------------------------+
| nagios.nagios_servicechecks | repair | warning | Number of rows changed from 0 to 37813 |
| nagios.nagios_servicechecks | repair | status | OK |
+-----------------------------+--------+----------+----------------------------------------+
2 rows in set (0.16 sec)

MariaDB [nagios]>

[tbarnett]

Percona is NOT installed.

[barnt@prime.com@nagiosxidb ~]$ sudo yum list installed | grep percona
[barnt@prime.com@nagiosxidb ~]$

[tbarnett]

There are now values in the nagios:servicechecks table.

I think they started populating after I did the table rebuild command.

The have not been any entries in that table until now.

I may have to truncate the nagios.log file to recover some disk space.

Thanks for your help.

Tom

[gsmith]

Hi

Yeah, it went from 0 rows to 37813 rows.

I'm glad things are working for you now.

Take care.