Performance

[chaporgin]

Hello, thank you for the library. I have questions regarding performance. I am using a database/SQL (so not LISTEN/NOTIFY). I also removed the UNLOGGED from CREATE TABLE statements (which should not affect the performance IIUC because I have only three workers).
I have three workers and db.r6g.xlarge with 4vCPU/32GiB and 20000 IOPS. I have a binary that wakes up every second and spawns a goroutine that is not using InsertTxMany, inserting a number of no-op jobs. The number is configurable, and I have tried with 50, 100, 350, and 500.
I see that the binary cannot keep up with a 1-second interval. An example is shown for 50 jobs per second. The code of the binary is the following:

func (l load) StartServer(ctx context.Context) {
	step := 0
	timer := time.NewTicker(time.Second)
	for {
		select {
		case <-timer.C:
			go l.step(ctx, step, 50)
			step += 1
		case <-ctx.Done():
			return
		}
	}
}

func (l load) step(ctx context.Context, stepNumber int, upTo int) {
	now := time.Now()
	p := pool.New().WithContext(ctx).WithCancelOnError()
	for i := 0; i < upTo; i++ {
		p.Go(func(ctx context.Context) error {
			return l.db.WithTx(ctx, rdb.TxOptions{F: func(ctx context.Context, tx *sqlx.Tx) error {
				_, err := l.rw.InsertTx(ctx, tx.Tx, neonriver.InsertJob{
					Args: loadjob.Args{
						ProjectID:  fmt.Sprintf("project-id-%d-%d", stepNumber, i),
						When:       now,
						StepNumber: stepNumber,
					},
				})
				return err
			}, FIsIdempotent: true})
		})
	}
	if err := p.Wait(); err != nil && !errors.Is(err, context.Canceled) {
		logger.Error(ctx, fmt.Errorf("insert job: %w", err))
		return
	}
	logger.Info(
		ctx,
		"step done",
		zap.Int("step", stepNumber),
		zap.Int("inserted", upTo),
		zap.Duration("duration", time.Since(now)),
	)
}

The driver is limited with 450 max open connections.

The database is in the same AWS region and VPC as the VM running the binary.

I see the spike in IOPS, which is OK (?), but the spike in CPU consumption and the insertion speed are unexpected, too, so I seem to be getting less than 50 TPS per second.
Do you know if this is expected? Would moving from polling to LISTEN/NOTIFY make the situation better? Any ideas are appreciated!

[bgentry]

@chaporgin hi, I'm wondering if you've tried our benchmarking tool river bench. It also executes no-op jobs, but importantly is tuned out of the box for more throughput. The most relevant part of that are the configs for FetchCooldown (minimum time to wait between two fetches, even if an insert notification was received) and FetchPollInterval (the standard polling loop interval for fetching jobs).

Together with the MaxWorkers setting for a given queue, these settings are directly tied to throughput: how many jobs can I fetch, and how often can I fetch them?

As far as the CPU usage you're seeing, I'm guessing it has more to do with how you're spawning N simultaneous transactions at the same moment and quickly committing them all.

Would moving from polling to LISTEN/NOTIFY make the situation better?

Notably, the database/sql driver today doesn't support LISTEN/NOTIFY—that's only in the pure pgx driver atm due to limitations with the dbsql interfaces. I still have #352 open as a proof-of-concept for how we could enable notify support within the dbsql driver (by combining it with the pgx driver to obtain a raw connection just for the notifier) but that's sort of on hold until we're sure it's needed.

I'd expect that the notifier allows you to tune your clients to poll less frequently but still have low latency when a new job is inserted, i.e. a situation where you don't tend to have a sitting backlog of jobs and want to lower the polling load on your database. I'm not sure it will matter much with throughput benchmarking though.