User Experience

How does my database read performance differ between shared memory, localhost, and client-server connections? When did my read rate change?

ProTop's "User Experience" is a proxy for the best possible inquiry throughput (record reads per second) that a single user might experience under ideal conditions when connected in one of three ways: via shared memory, a local client-server connection, or a remote connection. Ordinarily, we expect variations in all 3 measurements to increase or decrease similarly when the system is under load. If one of these metrics consistently behaves differently, it indicates that the connection type is experiencing a non-database resource contention - such as a network bottleneck.

These daemon processes measure database read performance, at a per-second rate, in 3 ways:

1. shared memory (SHM)
2. client-server via the localhost network connection (LH)
3. client-server over a true network connection, executed from another server in the LAN (CS).

Shared Memory

The pt3agent automatically covers the shared memory measurement.

NOTE: If you are running more than one user experience monitor from the same etc/schedule.*.cfg, be sure to give unique names to the log files.

Localhost

Measurement is done by a local and independently running _progres -p zippyng.p daemon process, typically launched through schedule.cfg. It creates a file:

$PROTOP/log/zippyng.[friendlyName].lh.log

Which is read by the appropriate pt3agent, uploading the data to the ProTop Portal.

To enable User Experience Localhost in etc/schedule.cfg, edit etc/schedule.[site].cfg and add the following line:

#### *nix

 0,15,30,45 * * * * zippyng.sh [friendlyName] lh [service|port] >> ${PTTMP}/tasksched.zippyng.[friendlyName].lh.err 2>&1 & [NOALERT] 

#### Windows

 0,15,30,45 * * * * cmd /c zippyng.bat [friendlyName] lh [service] >> %TMPDIR%\tasksched.zippyng.[friendlyName].lh.err 2>&1 & [NOALERT] 

Client-Server

Measurement is done by an independently running _progres -p zippyng.p daemon process started by ProTop installed . Like User Experience Localhost, the process is typically initiated via schedule.cfg and creates:

 [PROTOPDIR]/log/zippying.[friendlyName].cs.log 

However, since the corresponding pt3agent is not running on that remote server, the log file needs to be copied to the database server being tested so the appropriate pt3agent can upload the metric data to the ProTop Portal.

To enable User Experience client-server to run, follow these steps:

1. Install ProTop on another server in the LAN, preferably on the same server users are connecting to the database from (or app servers or PASOE instances etc). If the remote server is configured for ProTop with the same siteName, the dblist.cfg file will be downloaded from the ProTop Portal. Otherwise, copy the appropriate resource line from [PROTOPDIR]/etc/dblist.cfg on the DB server to [PROTOPDIR]/etc/dblist.cfg on the remote server.

2. Configure the remote copy command by copying [PROTOPDIR]/bin/zipenv.x to zipenv (%PROTOP%\bin\zipenv.batx to zipenv.bat on Windows) and edit the ZIPSYNC line as follows:

   *nix

   Replace [SERVER] and [LOGDIR] below with the server name or address of the DB server where the pt3agent is running, followed by the explicit path to your ProTop log directory (e.g. DBSRV:/opt/protop/log).

   NOTE: ssh keys must be set up to allow password-less ssh from the remote server to the DB server.

   export ZIPSYNC="rsync -avzqe ssh ${LOGDIR}/zippyng.${FRNAME}.cs.log [SERVER]:/[LOGDIR] 1>${TMPDIR}/zippyng.${FRNAME}.cs.err 2>&1" 

   Windows

   In the line below, replace [\\ServerName\...] with the correct UNC path to the %PROTOP%\log directory on the DB server where the pt3agent is running.

   Test permissions. The DBMonitor and zippy process will run using the SYSTEM account.

   Typically you will need to enable “Everyone - write” permissions (in French, “tout le monde”). Make a note of the " at the beginning of the line.

   "set ZIPSYNC=copy /y %LOGDIR%\zippyng.!FRNAME!.cs.log [\\ServerName\d$\protop\log] >> %PTTMP%\zipsync.copy.cs.err 2>&1" 

3. Uncomment and edit etc/schedule.[site].cfg and modify the zippyng.sh[.bat] line replacing the values in [brackets] with your values:

   *nix

   0,15,30,45 * * * * zippyng.sh [friendlyName] cs [host] [service|port] >> ${PTTMP}/tasksched.zippyng.cs.[friendlyName].err 2>&1 & [NOALERT] 

   Windows

   0,15,30,45 * * * * cmd /c zippyng.bat [friendlyName from dblist.cfg] cs [host] [service|port] >> %TMPDIR%\tasksched.zippyng.cs.[friendlyname].err 2>&1 & [NOALERT] 

4. Verify data flow to the portal by selecting the resource from the Portal Dashboard (click the resource name or right-click on the resource line and select Trend Data), and you will be directed to ProTop Trends. Here you should see User Exp data reported in the BogoMIPS graph on the Advanced Dashboard. It is the second graph under General info at the top of the page.

Parameter Files (pf) for User Experience Monitoring

If a resource has a type defined in [PROTOPDIR]/etc/dblist.cfg, and there is a parameter file named [PROTPDIR]/etc/type.pf, it is added to the _progres command line for all three types of user experience monitoring listed above.

Read Rate Changes Over Time

If users complain of unusual slowness, check the Trend Data - Advanced Dashboard for changes in the read rate for their type of connection as recorded in User Experience graphs.  You can see these in the center of the first row of graphs under BogoMIPS:

Troubleshooting Client Server from Windows to *nix

If you are having difficulty copying the *cs* log from Windows to *nix, try copying the log file to another version of itself.  Copy the second version (untouched by the zippyng process that creates it) back to your *nix database server.

Here's an example of the ZIPSYNC variable that does this:

SET "ZIPSYNC=copy %LOGDIR%\zippyng.!FRNAME!.log %LOGDIR%\zippyng.!FRNAME!.log2 /y & %PROTOP%\ubin\pscp -q -scp -pwfile pwd_pscp %LOGDIR%\zippyng.!FRNAME!.log2 root@servera:/app/protop_demo/log/%LOGDIR%\zippyng.!FRNAME!.log >> %PTTMP%\zipsync.copy.err 2>&1"