[MDEV-27271] Feature request: Add host machine data to variables and GLOBAL STATUS Created: 2021-12-15  Updated: 2021-12-23

Status: Open
Project: MariaDB Server
Component/s: Variables
Fix Version/s: None

Type: Task Priority: Minor
Reporter: Oliver Jones Assignee: Unassigned
Resolution: Unresolved Votes: 0
Labels: None


 Description   

This is a feature request (Mr Daniel Black suggested filing it with you.)

Please add some system variables (readonly) describing the host machine upon which the MariaDB server runs.

Why? They make server tuning easier and more accessible.

I suggest adding these.

These should be GLOBAL STATUS items – they change.

  • host_loadavg_1 floats showing the familiar linux load averages
  • host_loadavg_5 ...
  • host_loadavg_15 ...

"hostname" is already in a system variable. Some of these could also be system variables rather than global status items.

Also, you probably should add a system variable "hoststatus" (ON/OFF, default ON) to allow server operators to conceal all the above information.

 Comments   
Comment by Sergei Golubchik [ 2021-12-15 ]

No, it should not be in server status variables. There can be new I_S "host status" plugin, or feedback plugin can collect that info (it already does some), but not the server itself.

Comment by Daniel Black [ 2021-12-23 ]

Using the connect storage engine I was able to parse some of the /proc information.

poor broken example of FMT parsing of /proc/self/maps - evidently hex is hard

 
 
 
MariaDB [test]> create or replace table maps ( start BIGINT NOT NULL field_format='%n%llx%n-', end BIGINT NOT NULL field_format='%n%llx%n ', perms char(4) NOT NULL field_format='%n%4s%n ', `offset` BIGINT NOT NULL field_format='%n%llx%n ', dev char(5) NOT NULL field_format='%n%5s%n ', inode BIGINT NOT NULL field_format='%n%d%n', name varchar(200) field_format='%n%s%n') ENGINE=CONNECT table_type=FMT file_name='/proc/self/maps';
 
Query OK, 0 rows affected (0.000 sec)
 
 
 
MariaDB [test]> select * from maps;
 
+---------+---------+-------+---------+-------+---------+------------------------------------------------------------------------------------+
 
| start   | end     | perms | offset  | dev   | inode   | name                                                                               |
 
+---------+---------+-------+---------+-------+---------+------------------------------------------------------------------------------------+
 
|  400000 |       5 | r--p  |       0 | fd:03 | 4993316 |                             /home/dan/repos/build-mariadb-server-10.6/sql/mariadbd |
 
|       5 |       0 | r-xp  |       1 | fd:03 | 4993316 |                             /home/dan/repos/build-mariadb-server-10.6/sql/mariadbd |
 
|       0 | 1646000 | r--p  |       0 | fd:03 | 4993316 |                             /home/dan/repos/build-mariadb-server-10.6/sql/mariadbd |
 
| 1646000 | 1661000 | r--p  | 1245000 | fd:03 | 4993316 |                             /home/dan/repos/build-mariadb-server-10.6/sql/mariadbd |
 
| 1661000 | 1718000 | rw-p  | 1260000 | fd:03 | 4993316 |                             /home/dan/repos/build-mariadb-server-10.6/sql/mariadbd |
 
+---------+---------+-------+---------+-------+---------+------------------------------------------------------------------------------------+

On the simpler formats are: /proc/meminfo /proc/loadavg /proc/cpuinfo (bit harder) /proc/stat

Proc is very linux though there are some BSDs.

Comment by Oliver Jones [ 2021-12-23 ]

Nice. Dan.

Let me give a little more background around my feature request. It's all about helping WordPress (WP) site operators, especially large site operators, get access to actionable information. The WP schema is, to put it mildly, not built for speed. And most WP operators don't know anything about the RDBMS holding their data. So software like ours to help them with their RDBMS (https://wordpress.org/plugins/index-wp-mysql-for-speed/) needs to bridge the knowledge gap at least a little bit, and present actionable information.

Many WP operators respond to site slowdowns by grossly overprovisioning their servers. 96GiB / 64-core server machines aren't uncommon. But they are wasteful.

I'd like to be able to present information like machine provisioning (RAM and core count) , load average (or % cpu utilization) in actionable ways. "Hey, you have a lot of RAM but your innodb buffer size is only 256MiB" is an example. The dream: make large WP sites cheaper to operate and consume less power.

It makes no difference to this application whether the machine info shows up in variables, system status, information_schema.INNODB_METRICS or anyplace else as long as it can be reached by MariaDB / MySQL clients with appropriate privileges.

Generated at Thu Feb 08 09:51:39 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.