Skip to content

Instantly share code, notes, and snippets.

View lukeasrodgers's full-sized avatar

Luke Rodgers lukeasrodgers

View GitHub Profile
@lukeasrodgers
lukeasrodgers / pg_tuning_notes.md
Last active July 16, 2024 08:28
postgres conf - tuning notes

269 tunable settings, these 20 are most helpful

max_connections

  • set to useful peak
  • above 200, look at conn pooler
  • consider superuser_reserved_connections

shared_buffers - shared RAM

  • amt of memory pg takes for itself
  • generally, avail ram ÷ 4
@lukeasrodgers
lukeasrodgers / timings.md
Last active February 24, 2024 01:19
time do do things

databases

pg

  • dropping not null constraint on table with 800k rows, approx 35 columns: 32ms (8 core / 16 G RAM mbp)
  • counting table with 5.1M records: 42s (on heroku)
  • adding index on column, all values NULL: 42s (on heroku)
  • adding index concurrently on updated_at column, about 17M rows, aptible postgres ~800 IOPS: 455.8086s
  • adding index concurrently on 3 cols, item_type, event_type, updated_at, ~70M rows: 1.5hours
  • removing index concurrently, one index, table about 20M rows: 80s
  • adding index concurrently on 2 cols, banking_system_id (string, non-fk), platform (string), ~30M rows, about 3k IOPS: ~35 mins
  • adding index (employee_id, date, amount) non-concurrently on 60M row table, rds db.t3.large: about 1.5 mins
# TYPE /a in vim
select(0x1, 0x7FFF5DD1C850, 0x7FFF5DD1C7D0, 0x7FFF5DD1C750, 0x7FFF5DD1C8D8) = 0 0
lseek(0x3, 0x2000, 0x0) = 8192 0
dtrace: error on enabled probe ID 2132 (ID 320: syscall::write:return): invalid kernel access in action #12 at DIF offset 92
select(0x1, 0x7FFF5DD1C720, 0x7FFF5DD1C6A0, 0x7FFF5DD1C620, 0x7FFF5DD1C7A8) = 0 0
fsync(0x3, 0x7FFF5DD1C720, 0x7FFF5DD1C6A0) = 0 0
select(0x1, 0x7FFF5DD1C770, 0x7FFF5DD1C6F0, 0x7FFF5DD1C670, 0x7FFF5DD1C7F8) = 0 0
select(0x1, 0x7FFF5DD1C850, 0x7FFF5DD1C7D0, 0x7FFF5DD1C750, 0x0) = 1 0
alert();
@lukeasrodgers
lukeasrodgers / mysql_dummydata.sql
Created September 12, 2016 22:09
some misc procedures for creating test/dummy data in mysql
/* quickly/hackily create randomish string data with md5 */
DELIMITER $$
DROP PROCEDURE IF EXISTS user_promos;
CREATE PROCEDURE user_promos()
BEGIN
DECLARE i INT DEFAULT 1;
WHILE i < 5000 DO

Keybase proof

I hereby claim:

  • I am lukeasrodgers on github.
  • I am lukeasrodgers (https://keybase.io/lukeasrodgers) on keybase.
  • I have a public key whose fingerprint is 9E9C 2A25 69EA CF84 DF8D 6B8A 0CA0 92B0 E2F9 8F01

To claim this, I am signing this object:

http://bonesmoses.org/2016/07/15/pg-phriday-a-postgres-persepctive-on-mongodb/
CREATE TABLE sensor_log (
id SERIAL NOT NULL PRIMARY KEY,
location VARCHAR NOT NULL,
reading BIGINT NOT NULL,
reading_date TIMESTAMP NOT NULL
);
\timing
@lukeasrodgers
lukeasrodgers / ruby-redis-utils.rb
Last active July 11, 2016 19:05
scan-style iteration over redis list in ruby, making multiple roundtrips
# useful if k is a very large list, and you don't want to do it all in one request
# note that consistency will not be guaranteed here (i.e. list can change during iteration)
def scan_list(k, range=1000, &block)
i = 0
result = []
while l = HLR.lrange(k, i, i+range)
break if l.size == 0
if block_given?
yield l
else
@lukeasrodgers
lukeasrodgers / codereview.md
Last active December 21, 2021 14:10
code review

config

  • ensure config is loaded/tested in correct env

arithmetic/math

  • check for divide by zero
  • is integer division/rounding accounted for?
  • what happens if inputs are very small/very large? or negative?
  • what happens if results are negative (even though this "should never happen")
  • integer overlow (in languages where matters)
  • catastrohpic cancellation and other floating point concerns
# List users by average and maximum session length.
SELECT person, max(client.runtime_ms), avg(client.runtime_ms)
FROM item_occurrence
GROUP BY 1
ORDER BY 2 DESC
# List active date ranges for each deploy.
SELECT client.javascript.code_version, min(timestamp), max(timestamp)
FROM item_occurrence
GROUP BY 1