The preset gallery is the window where you pick tiles NeoSQL has prepared for watching a database and add them to a dashboard. Metrics such as sessions and locks, slow queries, storage and uptime come with SQL written for each DBMS. A preset you pick is copied into your own tile, so afterwards you can change it freely in Tile settings.
What's on the screen
| No. | Name | Description |
|---|---|---|
| 1 | Connection field | The connection the presets query. If the dashboard already uses a connection, that one is selected first. Only presets for this connection's DBMS are shown. |
| 2 | Schema field | The database and schema the presets query. The tiles you add keep this target. |
| 3 | n of m ready label | How many of the shown presets can run on this connection now. |
| 4 | Re-check availability button | Checks availability again. Click it after granting a permission or installing an extension in the database. |
| 5 | Categories | Cards are grouped in the order of the Sessions, Performance, Storage, Health and Schema quality categories. Categories with no preset for this DBMS are not shown. |
| 6 | Set card | A card with the set label. Several tiles share the result of one query ('Sets' below). |
| 7 | Preset card | Name, type, default size, default interval and description. An interval shown as manual means it does not run automatically. Heavy queries carry a heavy label. |
| 8 | Add n selected button | Adds the selected cards as tiles. Next to it is the Selected presets are copied into your own tiles — app updates will not overwrite your edits. note. |
- When the connection's DBMS has no presets at all, the No presets for … yet. notice appears instead of cards.
Adding tiles from presets
- Click the Presets button on the dashboard toolbar. On an empty dashboard you can also click the Start from presets button in the center.
- Choose the Connection field and the schema field. The availability check runs for that target.
- Click the cards to add. Selected cards get a check mark; click again to deselect. Unavailable cards cannot be clicked.
- Click the Add n selected button. The tiles take free slots on the dashboard and run once.
- Preset tiles come with automatic runs on, at the interval the preset sets. Only presets whose interval is manual come with it off. Heavy presets never come with an interval shorter than 10 minutes, and default to 6 hours.
- Changing the connection or schema clears every selected card.
Availability check
Session lists and performance statistics can be read only with the right database permissions, extensions and server settings. Metadata does not reveal these, so when the gallery opens and when you change the connection or schema, it actually sends a light one-row query for each preset.
- The checks are sent one at a time, so opening the gallery does not shake the connection pool.
- A preset confirmed to work is not asked again while the app is running. Ones that failed are asked again the next time the gallery opens. The Re-check availability button asks them all again.
Typical reasons shown on unavailable cards:
| DBMS | What is needed |
|---|---|
| PostgreSQL | The pg_stat_statements extension for slow and most-called queries, the pg_monitor role for replication lag, the CREATE privilege or the pg_read_all_stats role for tablespace size |
| MySQL, MariaDB | performance_schema for slow and most-called queries and blocked sessions (MariaDB has it off by default), the PROCESS privilege for long-running transactions, read access to mysql.user for password expiry (MySQL), and the MySQL 8+ performance_schema replication tables for replication lag |
| Oracle | Read access to dynamic performance views such as v$session, v$sql and v$sysstat; DBA views (SELECT_CATALOG_ROLE) for storage |
| SQL Server | VIEW SERVER STATE for sessions and performance, VIEW DATABASE STATE for storage |
| SQLite | SQLite 3.16 or later with pragma functions for database size |
Presets for each DBMS
| Category | What it watches | DBMS |
|---|---|---|
| Sessions | Active sessions, Live sessions | MariaDB, MySQL, PostgreSQL, Oracle, SQL Server, H2 |
| Sessions | Blocked sessions | MariaDB, MySQL, PostgreSQL, Oracle, SQL Server |
| Sessions | Connection usage | MariaDB, MySQL, PostgreSQL, Oracle |
| Performance | Slowest queries (top 5), Most called queries (top 5), Long-running transactions | MariaDB, MySQL, PostgreSQL, Oracle, SQL Server |
| Performance | Buffer / cache hit ratio | MariaDB, MySQL, PostgreSQL, Oracle |
| Storage | Database size | MariaDB, MySQL, PostgreSQL, Oracle, SQL Server, SQLite |
| Storage | Largest tables (top 10) | MariaDB, MySQL, PostgreSQL, Oracle, SQL Server |
| Storage | Index / data ratio | MariaDB, MySQL, PostgreSQL, Oracle |
| Storage | Tablespace usage | PostgreSQL, Oracle, SQL Server |
| Health | Database uptime | MariaDB, MySQL, PostgreSQL, Oracle, SQL Server |
| Health | Password expiry | MySQL, PostgreSQL, Oracle, SQL Server |
| Health | Replication lag | MySQL, PostgreSQL |
| Health | Dead tuple ratio | PostgreSQL |
| Schema quality | Tables without a primary key (manual) | MariaDB, MySQL, PostgreSQL, Oracle, SQL Server, H2 |
| Schema quality | Unused indexes | MySQL, PostgreSQL, SQL Server |
- The two sets (Session set and Storage set) exist for MariaDB, MySQL, PostgreSQL, Oracle and SQL Server.
- The same DBMS may use different SQL depending on its version. When NeoSQL does not know the connection's version, it uses SQL that works on the oldest version.
- DBMSs not in the table have no presets. Make your own tiles.
Sets
A set is a group of tiles that share the result of one query. Added separately, the same query goes out once per tile on every cycle; added as a set, it goes out once. The n queries as separate tiles → 1 as a set label on the card shows the difference.
| Set | Tiles it adds |
|---|---|
| Session set | Active sessions, Blocked sessions, Live sessions. Every 30 seconds |
| Storage set | Largest tables (Top 10), Data / index split (stacked), Size share, Index size vs data size, Total schema size, Tables, Index / data ratio. Every 6 hours |
- Tiles that cannot be made on this DBMS are shown on the card with the not on this DBMS label and are left out. For example, the Session set card on MariaDB and MySQL has no Blocked sessions tile.
- The position and size of set tiles can be changed tile by tile in edit mode. What they share is the query and the interval.
- To open the settings window of a set tile, you detach it from the set first. See Tile settings.
The dashboard offered when you create a connection
When you save a new connection, the Create a dashboard for this connection window appears. Clicking the Create button creates and opens a dashboard with every preset for that DBMS, named after the connection with a status suffix.
- This dashboard skips the availability check, so a just-created connection does not send a burst of queries to a production database. Tiles without permission show an error when opened; delete them.
- Presets are added individually, not as sets.
- For a connection whose DBMS has no presets, you are not asked.
Things to keep in mind
- On PostgreSQL, the slow queries and most-called queries cards are checked by actually reading the
pg_stat_statementsview. If the extension is missing or not loaded throughshared_preload_libraries, the cards show as blocked. Set up the extension and run the availability check again. - Presets are copied in. Updating the app does not change the SQL of tiles you have already added. To use new preset SQL, delete the tile and add it again.
- Just opening the gallery sends check queries to that connection. Keep this in mind for production databases.
- Performance and storage presets read system views. Rather than granting broad privileges, set up a separate read-only monitoring account (Safeguards for production databases).
- Heavy presets (the heavy label) can take a long time on a large schema. Do not shorten their interval.
Related screens
- Dashboards: the screen where added tiles are placed, and automatic runs.
- Tile settings: change added tiles and detach them from a set.
- Create and edit a connection: create connections and choose the database account.
- Safeguards for production databases: why a separate monitoring account helps.