/ Check-in [954ef61f]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Add the sqlite_memstat extension - an eponymous virtual table that shows memory usages statistics for SQLite.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: 954ef61f6a02532e72c815208ec839310f1e00d69e0003cc6f5426f0559229d1
User & Date: drh 2018-09-27 17:03:03
Context
2018-09-27
17:15
Modify the memstat virtual table so that it works with SQLite version 3.9.2. check-in: 7171d8ae user: drh tags: trunk
17:03
Add the sqlite_memstat extension - an eponymous virtual table that shows memory usages statistics for SQLite. check-in: 954ef61f user: drh tags: trunk
16:57
Enhancements to sqlite_memstat: (1) Add an extra "schema" column to show the schema name for ZIPVFS stats. (2) Only show ZIPVFS stats to schema that use ZIPVFS (3) Put a NULL in unused columns of the output. Closed-Leaf check-in: 9351135b user: drh tags: memstat-vtab
12:14
Disallow the use of window functions in the recursive part of a recursive CTE. Fix for ticket [e8275b415a2f03bee]. check-in: 7fc29944 user: dan tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Added ext/misc/memstat.c.

            1  +/*
            2  +** 2018-09-27
            3  +**
            4  +** The author disclaims copyright to this source code.  In place of
            5  +** a legal notice, here is a blessing:
            6  +**
            7  +**    May you do good and not evil.
            8  +**    May you find forgiveness for yourself and forgive others.
            9  +**    May you share freely, never taking more than you give.
           10  +**
           11  +*************************************************************************
           12  +**
           13  +** This file demonstrates an eponymous virtual table that returns information
           14  +** from sqlite3_status64() and sqlite3_db_status().
           15  +**
           16  +** Usage example:
           17  +**
           18  +**     .load ./memstat
           19  +**     .mode quote
           20  +**     .header on
           21  +**     SELECT * FROM memstat;
           22  +*/
           23  +#if !defined(SQLITE_CORE) || defined(SQLITE_ENABLE_MEMSTATVTAB)
           24  +#if !defined(SQLITEINT_H)
           25  +#include "sqlite3ext.h"
           26  +#endif
           27  +SQLITE_EXTENSION_INIT1
           28  +#include <assert.h>
           29  +#include <string.h>
           30  +
           31  +#ifndef SQLITE_OMIT_VIRTUALTABLE
           32  +
           33  +/* memstat_vtab is a subclass of sqlite3_vtab which will
           34  +** serve as the underlying representation of a memstat virtual table
           35  +*/
           36  +typedef struct memstat_vtab memstat_vtab;
           37  +struct memstat_vtab {
           38  +  sqlite3_vtab base;  /* Base class - must be first */
           39  +  sqlite3 *db;        /* Database connection for this memstat vtab */
           40  +};
           41  +
           42  +/* memstat_cursor is a subclass of sqlite3_vtab_cursor which will
           43  +** serve as the underlying representation of a cursor that scans
           44  +** over rows of the result
           45  +*/
           46  +typedef struct memstat_cursor memstat_cursor;
           47  +struct memstat_cursor {
           48  +  sqlite3_vtab_cursor base;  /* Base class - must be first */
           49  +  sqlite3 *db;               /* Database connection for this cursor */
           50  +  int iRowid;                /* Current row in aMemstatColumn[] */
           51  +  int iDb;                   /* Which schema we are looking at */
           52  +  int nDb;                   /* Number of schemas */
           53  +  char **azDb;               /* Names of all schemas */
           54  +  sqlite3_int64 aVal[2];     /* Result values */
           55  +};
           56  +
           57  +/*
           58  +** The memstatConnect() method is invoked to create a new
           59  +** memstat_vtab that describes the memstat virtual table.
           60  +**
           61  +** Think of this routine as the constructor for memstat_vtab objects.
           62  +**
           63  +** All this routine needs to do is:
           64  +**
           65  +**    (1) Allocate the memstat_vtab object and initialize all fields.
           66  +**
           67  +**    (2) Tell SQLite (via the sqlite3_declare_vtab() interface) what the
           68  +**        result set of queries against memstat will look like.
           69  +*/
           70  +static int memstatConnect(
           71  +  sqlite3 *db,
           72  +  void *pAux,
           73  +  int argc, const char *const*argv,
           74  +  sqlite3_vtab **ppVtab,
           75  +  char **pzErr
           76  +){
           77  +  memstat_vtab *pNew;
           78  +  int rc;
           79  +
           80  +/* Column numbers */
           81  +#define MSV_COLUMN_NAME    0   /* Name of quantity being measured */
           82  +#define MSV_COLUMN_SCHEMA  1   /* schema name */
           83  +#define MSV_COLUMN_VALUE   2   /* Current value */
           84  +#define MSV_COLUMN_HIWTR   3   /* Highwater mark */
           85  +
           86  +  rc = sqlite3_declare_vtab(db,"CREATE TABLE x(name,schema,value,hiwtr)");
           87  +  if( rc==SQLITE_OK ){
           88  +    pNew = sqlite3_malloc( sizeof(*pNew) );
           89  +    *ppVtab = (sqlite3_vtab*)pNew;
           90  +    if( pNew==0 ) return SQLITE_NOMEM;
           91  +    memset(pNew, 0, sizeof(*pNew));
           92  +    pNew->db = db;
           93  +  }
           94  +  return rc;
           95  +}
           96  +
           97  +/*
           98  +** This method is the destructor for memstat_cursor objects.
           99  +*/
          100  +static int memstatDisconnect(sqlite3_vtab *pVtab){
          101  +  sqlite3_free(pVtab);
          102  +  return SQLITE_OK;
          103  +}
          104  +
          105  +/*
          106  +** Constructor for a new memstat_cursor object.
          107  +*/
          108  +static int memstatOpen(sqlite3_vtab *p, sqlite3_vtab_cursor **ppCursor){
          109  +  memstat_cursor *pCur;
          110  +  pCur = sqlite3_malloc( sizeof(*pCur) );
          111  +  if( pCur==0 ) return SQLITE_NOMEM;
          112  +  memset(pCur, 0, sizeof(*pCur));
          113  +  pCur->db = ((memstat_vtab*)p)->db;
          114  +  *ppCursor = &pCur->base;
          115  +  return SQLITE_OK;
          116  +}
          117  +
          118  +/*
          119  +** Clear all the schema names from a cursor
          120  +*/
          121  +static void memstatClearSchema(memstat_cursor *pCur){
          122  +  int i;
          123  +  if( pCur->azDb==0 ) return;
          124  +  for(i=0; i<pCur->nDb; i++){
          125  +    sqlite3_free(pCur->azDb[i]);
          126  +  }
          127  +  sqlite3_free(pCur->azDb);
          128  +  pCur->azDb = 0;
          129  +  pCur->nDb = 0;
          130  +}
          131  +
          132  +/*
          133  +** Fill in the azDb[] array for the cursor.
          134  +*/
          135  +static int memstatFindSchemas(memstat_cursor *pCur){
          136  +  sqlite3_stmt *pStmt = 0;
          137  +  int rc;
          138  +  if( pCur->nDb ) return SQLITE_OK;
          139  +  rc = sqlite3_prepare_v2(pCur->db, "PRAGMA database_list", -1, &pStmt, 0);
          140  +  if( rc ){
          141  +    sqlite3_finalize(pStmt);
          142  +    return rc;
          143  +  }
          144  +  while( sqlite3_step(pStmt)==SQLITE_ROW ){
          145  +    char **az, *z;
          146  +    az = sqlite3_realloc(pCur->azDb, sizeof(char*)*(pCur->nDb+1));
          147  +    if( az==0 ){
          148  +      memstatClearSchema(pCur);
          149  +      return SQLITE_NOMEM;
          150  +    }
          151  +    pCur->azDb = az;
          152  +    z = sqlite3_mprintf("%s", sqlite3_column_text(pStmt, 1));
          153  +    if( z==0 ){
          154  +      memstatClearSchema(pCur);
          155  +      return SQLITE_NOMEM;
          156  +    }
          157  +    pCur->azDb[pCur->nDb] = z;
          158  +    pCur->nDb++;
          159  +  }
          160  +  sqlite3_finalize(pStmt);
          161  +  return SQLITE_OK;
          162  +}
          163  +
          164  +
          165  +/*
          166  +** Destructor for a memstat_cursor.
          167  +*/
          168  +static int memstatClose(sqlite3_vtab_cursor *cur){
          169  +  memstat_cursor *pCur = (memstat_cursor*)cur;
          170  +  memstatClearSchema(pCur);
          171  +  sqlite3_free(cur);
          172  +  return SQLITE_OK;
          173  +}
          174  +
          175  +
          176  +/*
          177  +** Allowed values for aMemstatColumn[].eType
          178  +*/
          179  +#define MSV_GSTAT   0          /* sqlite3_status64() information */
          180  +#define MSV_DB      1          /* sqlite3_db_status() information */
          181  +#define MSV_ZIPVFS  2          /* ZIPVFS file-control with 64-bit return */
          182  +
          183  +/*
          184  +** An array of quantities that can be measured and reported by
          185  +** this virtual table
          186  +*/
          187  +static const struct MemstatColumns {
          188  +  const char *zName;    /* Symbolic name */
          189  +  unsigned char eType;  /* Type of interface */
          190  +  unsigned char mNull;  /* Bitmask of which columns are NULL */
          191  +                        /* 2: dbname,  4: current,  8: hiwtr */
          192  +  int eOp;              /* Opcode */
          193  +} aMemstatColumn[] = {
          194  + {"MEMORY_USED",            MSV_GSTAT,  2, SQLITE_STATUS_MEMORY_USED          },
          195  + {"MALLOC_SIZE",            MSV_GSTAT,  6, SQLITE_STATUS_MALLOC_SIZE          },
          196  + {"MALLOC_COUNT",           MSV_GSTAT,  2, SQLITE_STATUS_MALLOC_COUNT         },
          197  + {"PAGECACHE_USED",         MSV_GSTAT,  2, SQLITE_STATUS_PAGECACHE_USED       },
          198  + {"PAGECACHE_OVERFLOW",     MSV_GSTAT,  2, SQLITE_STATUS_PAGECACHE_OVERFLOW   },
          199  + {"PAGECACHE_SIZE",         MSV_GSTAT,  6, SQLITE_STATUS_PAGECACHE_SIZE       },
          200  + {"PARSER_STACK",           MSV_GSTAT,  6, SQLITE_STATUS_PARSER_STACK         },
          201  + {"DB_LOOKASIDE_USED",      MSV_DB,     2, SQLITE_DBSTATUS_LOOKASIDE_USED     },
          202  + {"DB_LOOKASIDE_HIT",       MSV_DB,     6, SQLITE_DBSTATUS_LOOKASIDE_HIT      },
          203  + {"DB_LOOKASIDE_MISS_SIZE", MSV_DB,     6, SQLITE_DBSTATUS_LOOKASIDE_MISS_SIZE},
          204  + {"DB_LOOKASIDE_MISS_FULL", MSV_DB,     6, SQLITE_DBSTATUS_LOOKASIDE_MISS_FULL},
          205  + {"DB_CACHE_USED",          MSV_DB,    10, SQLITE_DBSTATUS_CACHE_USED         },
          206  + {"DB_CACHE_USED_SHARED",   MSV_DB,    10, SQLITE_DBSTATUS_CACHE_USED_SHARED  },
          207  + {"DB_SCHEMA_USED",         MSV_DB,    10, SQLITE_DBSTATUS_SCHEMA_USED        },
          208  + {"DB_STMT_USED",           MSV_DB,    10, SQLITE_DBSTATUS_STMT_USED          },
          209  + {"DB_CACHE_HIT",           MSV_DB,    10, SQLITE_DBSTATUS_CACHE_HIT          },
          210  + {"DB_CACHE_MISS",          MSV_DB,    10, SQLITE_DBSTATUS_CACHE_MISS         },
          211  + {"DB_CACHE_WRITE",         MSV_DB,    10, SQLITE_DBSTATUS_CACHE_WRITE        },
          212  + {"DB_CACHE_SPILL",         MSV_DB,    10, SQLITE_DBSTATUS_CACHE_SPILL        },
          213  + {"DB_DEFERRED_FKS",        MSV_DB,    10, SQLITE_DBSTATUS_DEFERRED_FKS       },
          214  +#ifdef SQLITE_ENABLE_ZIPVFS
          215  + {"ZIPVFS_CACHE_USED",      MSV_ZIPVFS, 8, 231454 },
          216  + {"ZIPVFS_CACHE_HIT",       MSV_ZIPVFS, 8, 231455 },
          217  + {"ZIPVFS_CACHE_MISS",      MSV_ZIPVFS, 8, 231456 },
          218  + {"ZIPVFS_CACHE_WRITE",     MSV_ZIPVFS, 8, 231457 },
          219  + {"ZIPVFS_DIRECT_READ",     MSV_ZIPVFS, 8, 231458 },
          220  + {"ZIPVFS_DIRECT_BYTES",    MSV_ZIPVFS, 8, 231459 },
          221  +#endif /* SQLITE_ENABLE_ZIPVFS */
          222  +};
          223  +#define MSV_NROW (sizeof(aMemstatColumn)/sizeof(aMemstatColumn[0]))
          224  +
          225  +/*
          226  +** Advance a memstat_cursor to its next row of output.
          227  +*/
          228  +static int memstatNext(sqlite3_vtab_cursor *cur){
          229  +  memstat_cursor *pCur = (memstat_cursor*)cur;
          230  +  int i;
          231  +  assert( pCur->iRowid<=MSV_NROW );
          232  +  while(1){
          233  +    i = (int)pCur->iRowid - 1;
          234  +    if( (aMemstatColumn[i].mNull & 2)!=0 || (++pCur->iDb)>=pCur->nDb ){
          235  +      pCur->iRowid++;
          236  +      if( pCur->iRowid>MSV_NROW ) return SQLITE_OK;  /* End of the table */
          237  +      pCur->iDb = 0;
          238  +      i++;
          239  +    }
          240  +    pCur->aVal[0] = 0;
          241  +    pCur->aVal[1] = 0;    
          242  +    switch( aMemstatColumn[i].eType ){
          243  +      case MSV_GSTAT: {
          244  +        sqlite3_status64(aMemstatColumn[i].eOp,
          245  +                         &pCur->aVal[0], &pCur->aVal[1],0);
          246  +        break;
          247  +      }
          248  +      case MSV_DB: {
          249  +        int xCur, xHiwtr;
          250  +        sqlite3_db_status(pCur->db, aMemstatColumn[i].eOp, &xCur, &xHiwtr, 0);
          251  +        pCur->aVal[0] = xCur;
          252  +        pCur->aVal[1] = xHiwtr;
          253  +        break;
          254  +      }
          255  +      case MSV_ZIPVFS: {
          256  +        int rc;
          257  +        rc = sqlite3_file_control(pCur->db, pCur->azDb[pCur->iDb],
          258  +                                  aMemstatColumn[i].eOp, (void*)&pCur->aVal[0]);
          259  +        if( rc!=SQLITE_OK ) continue;
          260  +        break;
          261  +      }
          262  +    }
          263  +    break;
          264  +  }
          265  +  return SQLITE_OK;
          266  +}
          267  +  
          268  +
          269  +/*
          270  +** Return values of columns for the row at which the memstat_cursor
          271  +** is currently pointing.
          272  +*/
          273  +static int memstatColumn(
          274  +  sqlite3_vtab_cursor *cur,   /* The cursor */
          275  +  sqlite3_context *ctx,       /* First argument to sqlite3_result_...() */
          276  +  int iCol                    /* Which column to return */
          277  +){
          278  +  memstat_cursor *pCur = (memstat_cursor*)cur;
          279  +  int i;
          280  +  assert( pCur->iRowid>0 && pCur->iRowid<=MSV_NROW );
          281  +  i = (int)pCur->iRowid - 1;
          282  +  if( (aMemstatColumn[i].mNull & (1<<iCol))!=0 ){
          283  +    return SQLITE_OK;
          284  +  }
          285  +  switch( iCol ){
          286  +    case MSV_COLUMN_NAME: {
          287  +      sqlite3_result_text(ctx, aMemstatColumn[i].zName, -1, SQLITE_STATIC);
          288  +      break;
          289  +    }
          290  +    case MSV_COLUMN_SCHEMA: {
          291  +      sqlite3_result_text(ctx, pCur->azDb[pCur->iDb], -1, 0);
          292  +      break;
          293  +    }
          294  +    case MSV_COLUMN_VALUE: {
          295  +      sqlite3_result_int64(ctx, pCur->aVal[0]);
          296  +      break;
          297  +    }
          298  +    case MSV_COLUMN_HIWTR: {
          299  +      sqlite3_result_int64(ctx, pCur->aVal[1]);
          300  +      break;
          301  +    }
          302  +  }
          303  +  return SQLITE_OK;
          304  +}
          305  +
          306  +/*
          307  +** Return the rowid for the current row.  In this implementation, the
          308  +** rowid is the same as the output value.
          309  +*/
          310  +static int memstatRowid(sqlite3_vtab_cursor *cur, sqlite_int64 *pRowid){
          311  +  memstat_cursor *pCur = (memstat_cursor*)cur;
          312  +  *pRowid = pCur->iRowid*1000 + pCur->iDb;
          313  +  return SQLITE_OK;
          314  +}
          315  +
          316  +/*
          317  +** Return TRUE if the cursor has been moved off of the last
          318  +** row of output.
          319  +*/
          320  +static int memstatEof(sqlite3_vtab_cursor *cur){
          321  +  memstat_cursor *pCur = (memstat_cursor*)cur;
          322  +  return pCur->iRowid>MSV_NROW;
          323  +}
          324  +
          325  +/*
          326  +** This method is called to "rewind" the memstat_cursor object back
          327  +** to the first row of output.  This method is always called at least
          328  +** once prior to any call to memstatColumn() or memstatRowid() or 
          329  +** memstatEof().
          330  +*/
          331  +static int memstatFilter(
          332  +  sqlite3_vtab_cursor *pVtabCursor, 
          333  +  int idxNum, const char *idxStr,
          334  +  int argc, sqlite3_value **argv
          335  +){
          336  +  memstat_cursor *pCur = (memstat_cursor *)pVtabCursor;
          337  +  pCur->iRowid = 1;
          338  +  pCur->iDb = 0;
          339  +  return memstatFindSchemas(pCur);
          340  +}
          341  +
          342  +/*
          343  +** SQLite will invoke this method one or more times while planning a query
          344  +** that uses the memstat virtual table.  This routine needs to create
          345  +** a query plan for each invocation and compute an estimated cost for that
          346  +** plan.
          347  +*/
          348  +static int memstatBestIndex(
          349  +  sqlite3_vtab *tab,
          350  +  sqlite3_index_info *pIdxInfo
          351  +){
          352  +  pIdxInfo->estimatedCost = (double)500;
          353  +  pIdxInfo->estimatedRows = 500;
          354  +  return SQLITE_OK;
          355  +}
          356  +
          357  +/*
          358  +** This following structure defines all the methods for the 
          359  +** memstat virtual table.
          360  +*/
          361  +static sqlite3_module memstatModule = {
          362  +  0,                         /* iVersion */
          363  +  0,                         /* xCreate */
          364  +  memstatConnect,            /* xConnect */
          365  +  memstatBestIndex,          /* xBestIndex */
          366  +  memstatDisconnect,         /* xDisconnect */
          367  +  0,                         /* xDestroy */
          368  +  memstatOpen,               /* xOpen - open a cursor */
          369  +  memstatClose,              /* xClose - close a cursor */
          370  +  memstatFilter,             /* xFilter - configure scan constraints */
          371  +  memstatNext,               /* xNext - advance a cursor */
          372  +  memstatEof,                /* xEof - check for end of scan */
          373  +  memstatColumn,             /* xColumn - read data */
          374  +  memstatRowid,              /* xRowid - read data */
          375  +  0,                         /* xUpdate */
          376  +  0,                         /* xBegin */
          377  +  0,                         /* xSync */
          378  +  0,                         /* xCommit */
          379  +  0,                         /* xRollback */
          380  +  0,                         /* xFindMethod */
          381  +  0,                         /* xRename */
          382  +  0,                         /* xSavepoint */
          383  +  0,                         /* xRelease */
          384  +  0,                         /* xRollbackTo */
          385  +};
          386  +
          387  +#endif /* SQLITE_OMIT_VIRTUALTABLE */
          388  +
          389  +int sqlite3MemstatVtabInit(sqlite3 *db){
          390  +  int rc = SQLITE_OK;
          391  +#ifndef SQLITE_OMIT_VIRTUALTABLE
          392  +  rc = sqlite3_create_module(db, "sqlite_memstat", &memstatModule, 0);
          393  +#endif
          394  +  return rc;
          395  +}
          396  +
          397  +#ifndef SQLITE_CORE
          398  +#ifdef _WIN32
          399  +__declspec(dllexport)
          400  +#endif
          401  +int sqlite3_memstat_init(
          402  +  sqlite3 *db, 
          403  +  char **pzErrMsg, 
          404  +  const sqlite3_api_routines *pApi
          405  +){
          406  +  int rc = SQLITE_OK;
          407  +  SQLITE_EXTENSION_INIT2(pApi);
          408  +#ifndef SQLITE_OMIT_VIRTUALTABLE
          409  +  rc = sqlite3MemstatVtabInit(db);
          410  +#endif
          411  +  return rc;
          412  +}
          413  +#endif /* SQLITE_CORE */
          414  +#endif /* !defined(SQLITE_CORE) || defined(SQLITE_ENABLE_MEMSTATVTAB) */