Skip to content

Instantly share code, notes, and snippets.

View stephanGarland's full-sized avatar

Stephan Garland stephanGarland

View GitHub Profile
@stephanGarland
stephanGarland / macos_local_mysql_multiple_versions.md
Created May 8, 2025 14:20
Run local MySQL 5.7 and 8 simultaneously on MacOS via Homebrew

Prerequisites

Mac

This has been tested only on ARM (Apple Silicon) Macs. YMMV on x86. I don't see why it wouldn't work, but you may need to change some directory paths.

MySQL

You'll need to have MySQL 5.7 and 8 installed. The former is somewhat tricky, because the

@stephanGarland
stephanGarland / macos_modify_mysql_configs.py
Created May 8, 2025 13:58
Modify MySQL config files on MacOS as installed by Homebrew to alllow simultaneous 5.7 and 8.0 installations
import configparser
import plistlib
import shutil
import subprocess
from enum import Enum
from pathlib import Path
from string import Template
from typing import NamedTuple, Optional
EXEC_START_TMPL = Template("/opt/homebrew/opt/mysql@${mysql_ver}/bin/mysqld_safe")
@stephanGarland
stephanGarland / mysql_57_varchar_enum_json.md
Created February 7, 2025 17:37
Comparing VARCHAR, ENUM, and JSON indexed performance in MySQL 5.7

Environment

System

  • Macbook Pro M3
  • RAM: 36 GiB
  • SSD: 500 GB

MySQL

@stephanGarland
stephanGarland / sqlalchemy_enum.py
Created December 20, 2024 18:39
Demonstration of lookup tables with SQLAlchemy that automatically update with new entries
import itertools
import uuid
from functools import lru_cache
from typing import Dict, List, Optional, Union
from sqlalchemy import (Column, DateTime, ForeignKey, Integer, String,
create_engine)
from sqlalchemy.dialects import mysql
from sqlalchemy.orm import (Session, declarative_base, relationship,
sessionmaker)
@stephanGarland
stephanGarland / uuid_arc4random_vs_random.md
Created July 14, 2024 15:01
Demonstrating arc4random vs. ul_random_get_bytes performance difference

Results

All programs compiled with -O3, using clang v15.0.0 on MacOS, and gcc 10.2.1 on Linux. Mac was an M1 Air base model (8 GiB RAM), with low power mode disabled. Linux server was a Debian 11 VM with a Xeon E5-2650 v2 @ 2.60GHz, and 64 GiB of PC3-12800R RAM.

Linux

❯ ./uuid_runner.sh
@stephanGarland
stephanGarland / pg_bench_jsonb_indices.md
Last active March 29, 2024 17:10
Comparing Postgres access speeds on JSONB with various indices

Environment

System

  • OS: Debian 12 VM on Proxmox VE 8
  • CPUs: 8x Xeon E5-2620 v2
  • RAM: 8 GiB DDR3
  • HDD: Samsung PM863 NVMe in Ceph over Mellanox Infiniband 56 Gbps IPoIB
    • Virtualized via scsi-virtio-single, aio=native, io_threads=enabled
  • FS: ext4 (rw,noatime)
@stephanGarland
stephanGarland / comparing_distinct_limit.md
Created February 22, 2024 18:35
Demonstrating DISTINCT with LIMIT on MySQL and Postgres

Introduction

MySQL's documentation states:

When combining LIMIT row_count with DISTINCT, MySQL stops as soon as it finds row_count unique rows.

The question was asked, how is this deterministic / accurate? My hypothesis is that it's due to MySQL's use of a clustering index, as opposed to Postgres' heap storage. I think that, given a monotonic PK such as an AUTO_INCREMENT (or perhaps any index), it's able to use that to guarantee determinism.

@stephanGarland
stephanGarland / postgres_clustering.md
Created February 9, 2024 23:37
Demonstrating Postgres' clustering operation

This table has 1,000,000 rows, consisting of a UUIDv4 PK, a random int of range (1,1000000), and ~1 KiB of Lorem Ipsum text.

postgres=# \d+ uuid_pk
                                          Table "public.uuid_pk"
 Column  |  Type   | Collation | Nullable | Default | Storage  | Compression | Stats target | Description
---------+---------+-----------+----------+---------+----------+-------------+--------------+-------------
 id      | uuid    |           | not null |         | plain    |             |              |
 user_id | integer |           | not null |         | plain    |             |              |
 lorem   | text    |           | not null |         | extended |             |              |
@stephanGarland
stephanGarland / tree_insert.sql
Created February 6, 2024 22:13
Example of a tree-like structure in SQL (specifically Postgres)
SET search_path TO 'example';
INSERT INTO genre (name) VALUES ('Rock'), ('Classic Rock'), ('Metal'), ('Progressive Metal'), ('Arena Rock'), ('Alternative Metal'), ('Grunge');
INSERT INTO category (name) VALUES ('Book'), ('Music');
INSERT INTO format (name) VALUES ('Compact Disc'), ('Vinyl');
INSERT INTO band (year_formed, name) VALUES (1985, 'Majesty'), (1988, 'Dream Theater'), (1990, 'Tool'), (1970, 'Queen'), (1987, 'Alice in Chains');
INSERT INTO artist (first_name, last_name) VALUES ('John', 'Petrucci'), ('John', 'Myung'), ('James', 'LaBrie'), ('Jordan', 'Ruddess'), ('Mike', 'Portnoy'), ('Mike', 'Mangini');
INSERT INTO artist (first_name, last_name, prefix, suffix) VALUES ('Brian', 'May', 'Sir', 'CBE');
@stephanGarland
stephanGarland / calc_innodb_buf_pool.py
Last active May 8, 2025 13:53
Designed for use with Ansible, but works on its own as well – calculate the tricky parts of InnoDB's buffer pool
#!/usr/bin/env python3
# LICENSE
# This Source Code Form is subject to the terms of the Mozilla Public License, v. 2.0.
# If a copy of the MPL was not distributed with this file, You can obtain one at http://mozilla.org/MPL/2.0/.
# Copyright 2024 Stephan Garland
"""
Calculates various parameters for the InnoDB buffer pool based on a simple input.