Re: Query performance inconsistant. - Mailing list pgsql-general

Tom Lane wrote:
> Jeff Davis <pgsql@j-davis.com> writes:
>> From what you described, I would not expect many locking problems. Are
>> there any other types of queries you run that may cause a lock?
>
> Row locks (SELECT FOR UPDATE/SHARE) are a possible problem, particularly
> if this is a pre-8.1 Postgres where exclusive row locks were used for
> foreign key constraints.
>
>             regards, tom lane

Tom,

I'm still having issues with this so lemme provide more information....
perhaps there is something obvious....

Here is the proc that has very inconsistent (anywhere from 25ms to
8000ms) performance:

CREATE FUNCTION acctmessage(_accttype character varying, _username
character varying, _ipaddress character varying, _nastimestamp
 timestamp with time zone, _sessionid character varying, _nassessionid
character varying, _nasipaddress character varying, _input
octets bigint, _outputoctets bigint, _inputgigawords integer,
_outputgigawords integer, _sessionlength bigint, _termcause charact
er varying, _nasidentifier character varying, _clientipaddress character
varying, _nasport character varying, _framedprotocol cha
racter varying, _servicetype character varying, _connectinfo character
varying) RETURNS void
    AS $$
DECLARE
    session_rec RECORD;
BEGIN

IF _clientipaddress <> '127.0.0.1' THEN

  INSERT into accounting_tab (
    acctType,
    userName,
    ipAddress,
    nasTimestamp,
    sessionId,
    nasSessionId,
    nasIpAddress,
    inputOctets,
    outputOctets,
    inputGigaWords,
    outputGigaWords,
    sessionLength,
    termCause,
    nasIdentifier,
    clientIpAddress,
    nasPort,
    framedProtocol,
    serviceType,
    connectInfo
  ) values (
    _acctType,
    _userName,
    _ipAddress,
    _nasTimestamp,
    _sessionId,
    _nasSessionId,
    _nasIpAddress,
    _inputOctets,
    _outputOctets,
    _inputGigaWords,
    _outputGigaWords,
    _sessionLength,
    _termCause,
    _nasIdentifier,
   _clientIpAddress,
    _nasPort,
    _framedProtocol,
    _serviceType,
    _connectInfo
  );

END IF;

SELECT INTO session_rec sessionId FROM radutmp_tab WHERE sessionId =
_sessionId;

IF session_rec.sessionId IS NULL AND _acctType = 'start' THEN

  INSERT into radutmp_tab (
    lastAcctType,
    userName,
    ipAddress,
    nasStartTimestamp,
    sessionId,
    nasSessionId,
    nasIpAddress,
    inputOctets,
    outputOctets,
    inputGigaWords,
    outputGigaWords,
    sessionLength,
    termCause,
    nasIdentifier,
    clientIpAddress,
    nasPort,
    framedProtocol,
    serviceType
  ) values (
    _acctType,
    _userName,
    _ipAddress,
    _nasTimestamp,
    _sessionId,
    _nasSessionId,
    _nasIpAddress,
    _inputOctets,
    _outputOctets,
    _inputGigaWords,
    _outputGigaWords,
    _sessionLength,
    _termCause,
    _nasIdentifier,
    _clientIpAddress,
    _nasPort,
    _framedProtocol,
    _serviceType
  ) ;

ELSIF session_rec.sessionId IS NOT NULL AND _acctType = 'stop' THEN

UPDATE
  radutmp_tab
SET
  lastAcctType = _acctType,
  nasStopTimestamp = _nasTimestamp,
  ipAddress = _ipAddress,
  sessionlength = _sessionlength,
  inputOctets = _inputOctets,
  outputOctets = _outputOctets,
  inputgigawords = _inputgigawords,
  outputgigawords = _outputgigawords,
  nasSessionId = _nasSessionId,
  nasIPAddress = _nasIPAddress,
  clientIPAddress = _clientIPAddress,
  nasPort = _nasPort,
  framedProtocol = _framedProtocol,
  termCause = _termCause
WHERE
  sessionId = _sessionId
AND
  userName = _userName
AND
  serviceType = _serviceType;

END IF;

END;
$$
    LANGUAGE plpgsql;

It looks long, but it's really pretty simple, it inserts data into the
accounting_tab and then updates or inserts into the radutmp_tab table
based on whether the session ID is known or not.

Here are the tables:

               Table "public.accounting_tab"
     Column      |           Type           |   Modifiers
-----------------+--------------------------+---------------
 sessionid       | character varying(32)    | not null
 nassessionid    | character varying(32)    | not null
 accttype        | character varying(6)     | not null
 username        | character varying(20)    | not null
 nastimestamp    | timestamp with time zone |
 nasipaddress    | character varying(15)    | not null
 nasidentifier   | character varying(15)    |
 clientipaddress | character varying(15)    | not null
 servicetype     | character varying(6)     | not null
 sessionlength   | bigint                   | default 0
 inputoctets     | bigint                   | default 0
 outputoctets    | bigint                   | default 0
 inputgigawords  | integer                  | default 0
 outputgigawords | integer                  | default 0
 nasport         | character varying(32)    |
 ipaddress       | character varying(32)    |
 framedprotocol  | character varying(32)    |
 termcause       | character varying(32)    |
 timestamp       | timestamp with time zone | default now()
 connectinfo     | character varying(100)   |
Indexes:
    "accounting_nasidentifier_idx" btree (nasidentifier)
    "accounting_nastimestamp_idx" btree (nastimestamp)
    "accounting_sessionid_idx" btree (sessionid)
    "accounting_timestamp_idx" btree ("timestamp")
    "accounting_username_idx" btree (username)

                  Table "public.radutmp_tab"
      Column       |           Type           |   Modifiers
-------------------+--------------------------+---------------
 sessionid         | character varying(32)    | not null
 nassessionid      | character varying(32)    | not null
 lastaccttype      | character varying(6)     | not null
 username          | character varying(20)    | not null
 nasstarttimestamp | timestamp with time zone |
 nasstoptimestamp  | timestamp with time zone |
 nasipaddress      | character varying(15)    | not null
 nasidentifier     | character varying(15)    |
 clientipaddress   | character varying(15)    | not null
 servicetype       | character varying(6)     | not null
 sessionlength     | bigint                   | default 0
 inputoctets       | bigint                   | default 0
 outputoctets      | bigint                   | default 0
 inputgigawords    | integer                  | default 0
 outputgigawords   | integer                  | default 0
 nasport           | character varying(32)    |
 ipaddress         | character varying(32)    |
 framedprotocol    | character varying(32)    |
 termcause         | character varying(32)    |
 timestamp         | timestamp with time zone | default now()
Indexes:
    "radutmp_tab_pkey" PRIMARY KEY, btree (sessionid)
    "radutmp_nasstoptimestamp_idx" btree (nasstoptimestamp)
    "radutmp_servicetype_idx" btree (servicetype)
    "radutmp_username_idx" btree (username)

As you can see the tables are pretty simple and don't have any foreign
keys, but they are large.  accounting_tab is 1.8mill rows and
radutmp_tab is 200k rows.

I should note that This query gets run quite a bit as well:

SELECT COUNT(*) FROM radutmp_tab WHERE UserName='username' AND
nasStopTimestamp IS NULL and serviceType = 'servicetype'

It tells me if there is an active session or not.

This setup, with concurrency, is returning very inconsistent query
performance.  Sometimes its very fast, other times it's slow and waits.
 This makes me think I have a table locking issue, but I'm not sure
since pg_locks rarely reports more than this:

    locktype    | database | relation | page | tuple | transactionid |
classid | objid | objsubid | transaction | pid  |      mode       | granted

---------------+----------+----------+------+-------+---------------+---------+-------+----------+-------------+------+-----------------+---------
 relation      |    16395 |    10342 |      |       |               |
      |       |          |    30641038 | 8720 | AccessShareLock | t
 transactionid |          |          |      |       |      30641038 |
      |       |          |    30641038 | 8720 | ExclusiveLock   | t

Do you see anything wrong with how I'm doing this?  Perhaps it's time to
get faster hardware, but it doesn't seem like the box is that loaded.

Thanks,
schu