Re: Native versus socket interface

On 11/30/2011 01:04 PM, [email protected] wrote:
> All things being equal, what factors should I consider to decide between
> the regular postgresql interface using libpq, and the non-libpq socket
> interface?  Are there performance considerations?
>
> I will not need to dump an image, so that part of the uffi requirement is
> not an issue for me.
>
>

At this point I would suggest using the postgresql-socket3 interface (as 
it supports command objects and parameters, and is based on cl-postgres, 
the "postmodern" project's backend).  Because socket3 is based on 
another widely used project, it seems very likely to receive updates, 
bug reports etc and allows us to not duplicate their excellent effort.

The postgresql-socket interface supports streaming (which is currently 
unsupported on postgresql-socket3, due to incompatibility between clsql 
and cl-postgres).  Postgresql-socket is based on version 2 of 
postgresql's socket api where as (as implied) postgresql-socket3 uses 
version 3 of the socket api.

Running the test suite against all three backends have about the same 
success rate (mostly different based on the streaming bits).

(dotimes (n 5) (clsql:run-tests)), repeated for each backend resulted in 
no significant differences among the postgres backends (within noise).


;; (:postgresql ("localhost" "clsql_test" "clsql_test" ""))
;; Evaluation took:
   ;; 77.819 seconds of real time
   ;; 28.589787 seconds of total run time (24.193512 user, 4.396275 system)
   ;; [ Run times consist of 5.612 seconds GC time, and 22.978 seconds 
non-GC time. ]
   ;; 36.74% CPU
   ;; 28,030 forms interpreted
   ;; 27,755 lambdas converted
   ;; 194,056,318,605 processor cycles
   ;; 2,642,283,696 bytes consed

  ;;(:postgresql-socket ("localhost" "clsql_test" "clsql_test" ""))
  ;; Evaluation took:
   ;; 78.418 seconds of real time
   ;; 27.873741 seconds of total run time (23.641477 user, 4.232264 system)
   ;; [ Run times consist of 5.880 seconds GC time, and 21.994 seconds 
non-GC time. ]
   ;; 35.55% CPU
   ;; 28,750 forms interpreted
   ;; 27,755 lambdas converted
   ;; 195,548,226,210 processor cycles
   ;; 2,670,233,392 bytes consed

;; (:postgresql-socket3 ("localhost" "clsql_test" "clsql_test" ""))
;; Evaluation took:
;;   78.147 seconds of real time
;;   28.409776 seconds of total run time (23.337459 user, 5.072317 system)
;;   [ Run times consist of 5.868 seconds GC time, and 22.542 seconds 
non-GC time. ]
;;   36.35% CPU
;;   28,540 forms interpreted
;;   27,305 lambdas converted
;;   194,871,711,450 processor cycles
;;   2,637,890,016 bytes consed

;;; Postgres, through ODBC.
;; (:odbc ("clsql_test_post" "clsql_test" ""))
   ;; 82.165 seconds of real time
   ;; 36.990312 seconds of total run time (31.933996 user, 5.056316 system)
   ;; [ Run times consist of 7.592 seconds GC time, and 29.399 seconds 
non-GC time. ]
   ;; 45.02% CPU
   ;; 2,561,324 forms interpreted
   ;; 29,405 lambdas converted
   ;; 204,892,389,188 processor cycles
   ;; 4 page faults
   ;; 3,613,534,288 bytes consed

;;; Microsoft SQLServer 2005 (talking over the network)
;  (:odbc ("clsql_test_pepy" "clsql_test" ""))
   ;; 133.968 seconds of real time
   ;; 56.411525 seconds of total run time (50.427151 user, 5.984374 system)
   ;; [ Run times consist of 9.612 seconds GC time, and 46.800 seconds 
non-GC time. ]
   ;; 42.11% CPU
   ;; 2,767,570 forms interpreted
   ;; 22,125 lambdas converted
   ;; 334,071,660,352 processor cycles
   ;; 5,224,738,464 bytes consed


;; (:mysql ("localhost" "clsql_test" "clsql_test" ""))
   ;; 72.517 seconds of real time
   ;; 30.161886 seconds of total run time (25.509595 user, 4.652291 system)
   ;; [ Run times consist of 7.772 seconds GC time, and 22.390 seconds 
non-GC time. ]
   ;; 41.59% CPU
   ;; 29,438 forms interpreted
   ;; 31,571 lambdas converted
   ;; 180,834,400,478 processor cycles
   ;; 2,815,757,296 bytes consed

; (:sqlite3 ("/tmp/clsql_test.sqlite3"))
;; Evaluation took:
;;   104.097 seconds of real time
;;   35.438215 seconds of total run time (23.629477 user, 11.808738 system)
;;   [ Run times consist of 5.880 seconds GC time, and 29.559 seconds 
non-GC time. ]
;;   34.04% CPU
;;   23,966 forms interpreted
;;   23,964 lambdas converted
;;   259,583,316,338 processor cycles
;;   2,275,981,056 bytes consed



mysql does about the best, sqlite fairs allright, and there is something 
wonky going on in the ODBC backend that is making SBCL interpret a lot 
more forms (a factor of 100), using a good deal more memory.



-- 
Nathan Bird
[email protected]
http://www.acceleration.net/
Custom Programming, Design, Hosting, and Broadband.