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.
You'll need to have MySQL 5.7 and 8 installed. The former is somewhat tricky, because the
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") |
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) |
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.
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 | | |
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'); |
#!/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. |