Running Python micro-benchmarks using the ChatGPT Code Interpreter alpha

Plus two podcast appearances and some thoughts on AI safety and open source LLMs

In this newsletter:

• Running Python micro-benchmarks using the ChatGPT Code Interpreter alpha

• Thoughts on AI safety in this era of increasingly powerful open source LLMs

• The Changelog podcast: LLMs break the internet

• Path to Citus Con: Working in public

Plus 6 links and 3 quotations and 3 TILs

Running Python micro-benchmarks using the ChatGPT Code Interpreter alpha - 2023-04-12

Today I wanted to understand the performance difference between two Python implementations of a mechanism to detect changes to a SQLite database schema. I rendered the difference between the two as this chart:

From start to finish, this entire benchmarking exercise took me less than five minutes - because ChatGPT did almost all of the work for me.

ChatGPT Code Interpreter alpha

I ran the benchmark using the new ChatGPT "Code Interpreter" alpha, which I recently gained access to, presumably due to being in the alpha for ChatGPT Plugins.

Code Interpreter mode provides ChatGPT with a single additional tool: it can now generate Python code and execute it in a restricted sandbox. The sandbox does not have network access and only has access to a set of pre-approved libraries. One of those libraries is matplotlib.pyplot, and it knows how to both render and display charts using it.

Here's the initial prompt I sent it:

In Python create an in-memory SQLite database with 100 tables each with 10 columns.

Time how long it takes to execute PRAGMA schema_version against that database 100 times.

Then add another 100 tables and time PRAGMA schema_version 100 times again.

Now run the whole benchmark a second time, but instead of PRAGMA schema_version time how long it takes to run hashlib.md5(db.execute("select group_concat(sql) from sqlite_master").fetchall()[0]).hexdigest() instead

Background - why compare these two things?

A bit of background on this. Datasette needs to detect any time a SQLite schema changes - which could happen if a table is added or altered in some way. It needs to know so it can update its own in-memory cache of the schema, in order to (eventually) power fast table search across all known databases.

SQLite has a neat mechanism for doing this: you can run PRAGMA schema_version and it will return a number that increments every time the schema changes. This is a very fast operation.

I'm currently investigating a confounding bug where a user has reported the error message "attempt to write a readonly database" when this PRAGMA schema_version read operation is executed.

I'm still investigating, but this could mean that I need to find an alternative way of detecting changes to the schema.

One option is to calculate an MD5 hash of the full schema of the database, using the following:

schema = db.execute(
    "select group_concat(sql) from sqlite_master"
).fetchall()[0]
hash = hashlib.md5(schema).hexdigest()

I can then compare that hash to the previous hash to see if the schema has changed.

The question I wanted to answer was how much of a performance hit this would represent - especially for databases with large number of tables.

Hence my desire to run a quick micro-benchmark.

ChatGPT's initial benchmark

ChatGPT started by writing the following:

import sqlite3
import time
import hashlib

# Define a function to create tables and run benchmarks
def run_benchmark(db, num_tables, num_columns, benchmark_query):
    # Start timing
    start_time = time.time()
    
    # Create tables
    for i in range(num_tables):
        columns = ', '.join([f"col{j} INTEGER" for j in range(num_columns)])
        db.execute(f"CREATE TABLE table{i} ({columns})")
    
    # Benchmark
    for i in range(100):
        if benchmark_query == "PRAGMA schema_version":
            db.execute("PRAGMA schema_version")
        else:
            hashlib.md5(db.execute("SELECT group_concat(sql) FROM sqlite_master").fetchall()[0][0].encode()).hexdigest()
    
    # Calculate time taken
    elapsed_time = time.time() - start_time
    return elapsed_time

# Initialize in-memory SQLite database
db = sqlite3.connect(":memory:")

# Run the first benchmark
benchmark1_time1 = run_benchmark(db, num_tables=100, num_columns=10, benchmark_query="PRAGMA schema_version")
benchmark1_time2 = run_benchmark(db, num_tables=100, num_columns=10, benchmark_query="PRAGMA schema_version")

# Run the second benchmark
db2 = sqlite3.connect(":memory:")
benchmark2_time1 = run_benchmark(db2, num_tables=100, num_columns=10, benchmark_query="hashlib.md5")
benchmark2_time2 = run_benchmark(db2, num_tables=100, num_columns=10, benchmark_query="hashlib.md5")

benchmark1_time1, benchmark1_time2, benchmark2_time1, benchmark2_time2

This is a pretty solid attempt at a benchmark! It has good comments, abstracts the two benchmarks into a single function and runs that function twice to test against a total of 200 tables.

Then it ran the code... and this happened:

It ran the code it generated... and got a OperationalError: table table0 already exists error.

That's because it ran that run_benchmark() function twice against the same database, and the function attempted to re-create a table it had created the first time.

But it didn't just stop at the error... it output an explanation of what it thought would happen, and then without any input from me wrote a second version of the benchmark script:

# Define a function to create tables and run benchmarks
def run_benchmark(db, num_tables, num_columns, benchmark_query, initial_table_index=0):
    # Start timing
    start_time = time.time()
    
    # Create tables
    for i in range(initial_table_index, initial_table_index + num_tables):
        columns = ', '.join([f"col{j} INTEGER" for j in range(num_columns)])
        db.execute(f"CREATE TABLE table{i} ({columns})")
    
    # Benchmark
    for i in range(100):
        if benchmark_query == "PRAGMA schema_version":
            db.execute("PRAGMA schema_version")
        else:
            hashlib.md5(db.execute("SELECT group_concat(sql) FROM sqlite_master").fetchall()[0][0].encode()).hexdigest()
    
    # Calculate time taken
    elapsed_time = time.time() - start_time
    return elapsed_time

# Initialize in-memory SQLite databases
db1 = sqlite3.connect(":memory:")
db2 = sqlite3.connect(":memory:")

# Run the first benchmark with PRAGMA schema_version
benchmark1_time1 = run_benchmark(db1, num_tables=100, num_columns=10, benchmark_query="PRAGMA schema_version")
benchmark1_time2 = run_benchmark(db1, num_tables=100, num_columns=10, benchmark_query="PRAGMA schema_version", initial_table_index=100)

# Run the second benchmark with hashlib.md5
benchmark2_time1 = run_benchmark(db2, num_tables=100, num_columns=10, benchmark_query="hashlib.md5")
benchmark2_time2 = run_benchmark(db2, num_tables=100, num_columns=10, benchmark_query="hashlib.md5", initial_table_index=100)

benchmark1_time1, benchmark1_time2, benchmark2_time1, benchmark2_time2

And this time it worked!

Adding a chart

This is a very solid answer to my question. I checked it through and it oll looks good - I would have written more-or-less the same thing, but it would have taken me a lot longer to do it.

So I asked it for a plot:

Plot those benchmark results over time on a chart

It wrote more code. ChatGPT writes and executes code in the same code session, so it had access to the variables that it had previously populated:

import matplotlib.pyplot as plt

# Data for benchmark results
benchmark1_times = [benchmark1_time1, benchmark1_time2]
benchmark2_times = [benchmark2_time1, benchmark2_time2]

# x-axis representing the number of tables at each benchmark
num_tables = [100, 200]

# Plotting the benchmark results
plt.plot(num_tables, benchmark1_times, label='PRAGMA schema_version', marker='o')
plt.plot(num_tables, benchmark2_times, label='hashlib.md5', marker='o')

# Labels and title
plt.xlabel('Number of Tables')
plt.ylabel('Time (seconds)')
plt.title('Benchmark Results Over Time')
plt.legend()

# Show the plot
plt.show()

I'm not very good at using the matplotlib charting libraries - I have to painstakingly piece everything together every time I use them. ChatGPT just did it.

Here's the chart it drew me:

This chart looks pretty good at first glance - it has an appropriate title, axis labels and a legend.

In terms of information density though it's rubbish. Charting just two points for each of the implementations, at 100 tables and 200 tables, is hardly worth visualizing at all.

So I fed it a final prompt:

Run the benchmark again in order to draw a better chart with measurements for every point between 1 and 200 tables

And that resulted in the chart I showed at the top of my post:

Here's the code it wrote:

# Define a function to create tables and run a single benchmark iteration
def run_single_benchmark(db, num_tables, num_columns, benchmark_query, initial_table_index=0):
    # Start timing
    start_time = time.time()
    
    # Create tables
    for i in range(initial_table_index, initial_table_index + num_tables):
        columns = ', '.join([f"col{j} INTEGER" for j in range(num_columns)])
        db.execute(f"CREATE TABLE table{i} ({columns})")
    
    # Benchmark
    if benchmark_query == "PRAGMA schema_version":
        db.execute("PRAGMA schema_version")
    else:
        hashlib.md5(db.execute("SELECT group_concat(sql) FROM sqlite_master").fetchall()[0][0].encode()).hexdigest()
    
    # Calculate time taken
    elapsed_time = time.time() - start_time
    return elapsed_time

# Initialize in-memory SQLite databases
db1 = sqlite3.connect(":memory:")
db2 = sqlite3.connect(":memory:")

# Lists to store benchmark results
benchmark1_times = []
benchmark2_times = []

# Run the benchmarks for every point between 1 and 200 tables
for num in range(1, 201):
    # Run the benchmark with PRAGMA schema_version
    benchmark1_time = run_single_benchmark(db1, num_tables=1, num_columns=10, benchmark_query="PRAGMA schema_version", initial_table_index=num-1)
    benchmark1_times.append(benchmark1_time)
    
    # Run the benchmark with hashlib.md5
    benchmark2_time = run_single_benchmark(db2, num_tables=1, num_columns=10, benchmark_query="hashlib.md5", initial_table_index=num-1)
    benchmark2_times.append(benchmark2_time)

# x-axis representing the number of tables at each benchmark
num_tables = list(range(1, 201))

# Plotting the benchmark results
plt.plot(num_tables, benchmark1_times, label='PRAGMA schema_version', marker='.')
plt.plot(num_tables, benchmark2_times, label='hashlib.md5', marker='.')

# Labels and title
plt.xlabel('Number of Tables')
plt.ylabel('Time (seconds)')
plt.title('Benchmark Results Over Time')
plt.legend()

# Show the plot
plt.show()

Full transcript

The entire project was completed using just the three prompts listed above!

I used my Observable notebook to convert the ChatGPT transcript JSON (extracted using the Network pane in the browser tools) to Markdown, and pasted it into this Gist:

SQLite Benchmarking Timings

April 11, 2023 at 5:06:53 PM

Prompt:

In Python create an in-memory SQLite database with 100 tables each with 10 columns.

Time how long it takes to execute PRAGMA schema_version against that database 100 times.

Then add another 100 tables and time PRAGMA schema_version 100 times again.

Now run the whole benchmark a second time, but instead of PRAGMA schema_version time how long it takes to run hashlib.md5(db.execute("select group_concat(sql) from sqlite_master").fetchall()[0]).hexdigest() instead

---

Generated code:

import sqlite3
import time
import hashlib

# Define a function to create tables and run benchmarks
def run_benchmark(db, num_tables, num_columns, benchmark_query):
    # Start timing
    start_time = time.time()
    
    # Create tables
    for i in range(num_tables):
        columns = ', '.join([f"col{j} INTEGER" for j in range(num_columns)])
        db.execute(f"CREATE TABLE table{i} ({columns})")
    
    # Benchmark
    for i in range(100):
        if benchmark_query == "PRAGMA schema_version":
            db.execute("PRAGMA schema_version")
        else:
            hashlib.md5(db.execute("SELECT group_concat(sql) FROM sqlite_master").fetchall()[0][0].encode()).hexdigest()
    
    # Calculate time taken
    elapsed_time = time.time() - start_time
    return elapsed_time

# Initialize in-memory SQLite database
db = sqlite3.connect(":memory:")

# Run the first benchmark
benchmark1_time1 = run_benchmark(db, num_tables=100, num_columns=10, benchmark_query="PRAGMA schema_version")
benchmark1_time2 = run_benchmark(db, num_tables=100, num_columns=10, benchmark_query="PRAGMA schema_version")

# Run the second benchmark
db2 = sqlite3.connect(":memory:")
benchmark2_time1 = run_benchmark(db2, num_tables=100, num_columns=10, benchmark_query="hashlib.md5")
benchmark2_time2 = run_benchmark(db2, num_tables=100, num_columns=10, benchmark_query="hashlib.md5")

benchmark1_time1, benchmark1_time2, benchmark2_time1, benchmark2_time2

---

Execution output:

---------------------------------------------------------------------------
OperationalError                          Traceback (most recent call last)
Cell In[1], line 31
     29 # Run the first benchmark
     30 benchmark1_time1 = run_benchmark(db, num_tables=100, num_columns=10, benchmark_query="PRAGMA schema_version")
---> 31 benchmark1_time2 = run_benchmark(db, num_tables=100, num_columns=10, benchmark_query="PRAGMA schema_version")
     33 # Run the second benchmark
     34 db2 = sqlite3.connect(":memory:")

Cell In[1], line 13, in run_benchmark(db, num_tables, num_columns, benchmark_query)
     11 for i in range(num_tables):
     12     columns = ', '.join([f"col{j} INTEGER" for j in range(num_columns)])
---> 13     db.execute(f"CREATE TABLE table{i} ({columns})")
     15 # Benchmark
     16 for i in range(100):

OperationalError: table table0 already exists

---

ChatGPT:

It looks like we are trying to create tables with the same names as the ones that already exist in the database. This is causing an OperationalError. To resolve this, we need to change the table names in the second set of tables to avoid conflicts.

Additionally, I will update the code to use the benchmark_query parameter in a more intuitive way. We'll pass the actual query or function to be benchmarked to the run_benchmark function. Let me fix this issue and re-run the code.

---

Generated code:

# Define a function to create tables and run benchmarks
def run_benchmark(db, num_tables, num_columns, benchmark_query, initial_table_index=0):
    # Start timing
    start_time = time.time()
    
    # Create tables
    for i in range(initial_table_index, initial_table_index + num_tables):
        columns = ', '.join([f"col{j} INTEGER" for j in range(num_columns)])
        db.execute(f"CREATE TABLE table{i} ({columns})")
    
    # Benchmark
    for i in range(100):
        if benchmark_query == "PRAGMA schema_version":
            db.execute("PRAGMA schema_version")
        else:
            hashlib.md5(db.execute("SELECT group_concat(sql) FROM sqlite_master").fetchall()[0][0].encode()).hexdigest()
    
    # Calculate time taken
    elapsed_time = time.time() - start_time
    return elapsed_time

# Initialize in-memory SQLite databases
db1 = sqlite3.connect(":memory:")
db2 = sqlite3.connect(":memory:")

# Run the first benchmark with PRAGMA schema_version
benchmark1_time1 = run_benchmark(db1, num_tables=100, num_columns=10, benchmark_query="PRAGMA schema_version")
benchmark1_time2 = run_benchmark(db1, num_tables=100, num_columns=10, benchmark_query="PRAGMA schema_version", initial_table_index=100)

# Run the second benchmark with hashlib.md5
benchmark2_time1 = run_benchmark(db2, num_tables=100, num_columns=10, benchmark_query="hashlib.md5")
benchmark2_time2 = run_benchmark(db2, num_tables=100, num_columns=10, benchmark_query="hashlib.md5", initial_table_index=100)

benchmark1_time1, benchmark1_time2, benchmark2_time1, benchmark2_time2

---

Execution output:

(0.008260965347290039,
 0.006290912628173828,
 0.011242151260375977,
 0.014682292938232422)

---

ChatGPT:

Here are the results of the benchmarks:

For the first benchmark using PRAGMA schema_version:

• The time taken to create 100 tables and run PRAGMA schema_version 100 times: approximately 0.0083 seconds.
• The time taken to create another 100 tables (totaling 200 tables) and run PRAGMA schema_version 100 times: approximately 0.0063 seconds.

For the second benchmark using hashlib.md5:

• The time taken to create 100 tables and run hashlib.md5(db.execute("SELECT group_concat(sql) FROM sqlite_master").fetchall()[0]).hexdigest() 100 times: approximately 0.0112 seconds.
• The time taken to create another 100 tables (totaling 200 tables) and run hashlib.md5(db.execute("SELECT group_concat(sql) FROM sqlite_master").fetchall()[0]).hexdigest() 100 times: approximately 0.0147 seconds.

Please note that the time taken for these benchmarks may vary slightly with each run due to factors such as system performance and other processes running on the system.

---

Prompt:

Plot those benchmark results over time on a chart

---

Generated code:

import matplotlib.pyplot as plt

# Data for benchmark results
benchmark1_times = [benchmark1_time1, benchmark1_time2]
benchmark2_times = [benchmark2_time1, benchmark2_time2]

# x-axis representing the number of tables at each benchmark
num_tables = [100, 200]

# Plotting the benchmark results
plt.plot(num_tables, benchmark1_times, label='PRAGMA schema_version', marker='o')
plt.plot(num_tables, benchmark2_times, label='hashlib.md5', marker='o')

# Labels and title
plt.xlabel('Number of Tables')
plt.ylabel('Time (seconds)')
plt.title('Benchmark Results Over Time')
plt.legend()

# Show the plot
plt.show()

---

Execution output:

---

ChatGPT:

Here is the chart showing the benchmark results over time:

The x-axis represents the number of tables at each benchmark (100 and 200 tables), and the y-axis represents the time taken (in seconds) for each benchmark.

The blue line represents the benchmark using PRAGMA schema_version, and the orange line represents the benchmark using hashlib.md5.

As we can see from the chart, the time taken for both benchmarks generally increases as the number of tables increases. Additionally, the benchmark using hashlib.md5 takes longer than the one using PRAGMA schema_version for both the 100 and 200 table cases.

---

Prompt:

How much faster is PRAGMA schema_version

---

Generated code:

# Calculate the speedup factor for both cases (100 tables and 200 tables)
speedup_factor_100_tables = benchmark2_time1 / benchmark1_time1
speedup_factor_200_tables = benchmark2_time2 / benchmark1_time2

speedup_factor_100_tables, speedup_factor_200_tables

---

Execution output:

(1.3608762157638028, 2.3338891836580005)

---

ChatGPT:

The benchmark using PRAGMA schema_version is approximately 1.36 times faster than the benchmark using hashlib.md5 for the case with 100 tables. For the case with 200 tables, the benchmark using PRAGMA schema_version is approximately 2.33 times faster than the benchmark using hashlib.md5.

This indicates that as the number of tables increases, the speedup factor also increases, making PRAGMA schema_version a more efficient option, especially when dealing with a larger number of tables.

---

Prompt:

Run the benchmark again in order to draw a better chart with measurements for every point between 1 and 200 tables

---

Generated code:

# Define a function to create tables and run a single benchmark iteration
def run_single_benchmark(db, num_tables, num_columns, benchmark_query, initial_table_index=0):
    # Start timing
    start_time = time.time()
    
    # Create tables
    for i in range(initial_table_index, initial_table_index + num_tables):
        columns = ', '.join([f"col{j} INTEGER" for j in range(num_columns)])
        db.execute(f"CREATE TABLE table{i} ({columns})")
    
    # Benchmark
    if benchmark_query == "PRAGMA schema_version":
        db.execute("PRAGMA schema_version")
    else:
        hashlib.md5(db.execute("SELECT group_concat(sql) FROM sqlite_master").fetchall()[0][0].encode()).hexdigest()
    
    # Calculate time taken
    elapsed_time = time.time() - start_time
    return elapsed_time

# Initialize in-memory SQLite databases
db1 = sqlite3.connect(":memory:")
db2 = sqlite3.connect(":memory:")

# Lists to store benchmark results
benchmark1_times = []
benchmark2_times = []

# Run the benchmarks for every point between 1 and 200 tables
for num in range(1, 201):
    # Run the benchmark with PRAGMA schema_version
    benchmark1_time = run_single_benchmark(db1, num_tables=1, num_columns=10, benchmark_query="PRAGMA schema_version", initial_table_index=num-1)
    benchmark1_times.append(benchmark1_time)
    
    # Run the benchmark with hashlib.md5
    benchmark2_time = run_single_benchmark(db2, num_tables=1, num_columns=10, benchmark_query="hashlib.md5", initial_table_index=num-1)
    benchmark2_times.append(benchmark2_time)

# x-axis representing the number of tables at each benchmark
num_tables = list(range(1, 201))

# Plotting the benchmark results
plt.plot(num_tables, benchmark1_times, label='PRAGMA schema_version', marker='.')
plt.plot(num_tables, benchmark2_times, label='hashlib.md5', marker='.')

# Labels and title
plt.xlabel('Number of Tables')
plt.ylabel('Time (seconds)')
plt.title('Benchmark Results Over Time')
plt.legend()

# Show the plot
plt.show()

---

Execution output:

---

ChatGPT:

Here is the updated chart showing the benchmark results over time for every point between 1 and 200 tables:

The x-axis represents the number of tables at each benchmark point, and the y-axis represents the time taken (in seconds) for each benchmark.

The blue line represents the benchmark using PRAGMA schema_version, and the orange line represents the benchmark using hashlib.md5.

As we can see from the chart, the time taken for both benchmarks generally increases as the number of tables increases. However, the time taken for PRAGMA schema_version remains relatively stable and low compared to the benchmark using hashlib.md5. The benchmark using hashlib.md5 sees an increase in time as the number of tables grows, especially when the number of tables exceeds 150. This suggests that PRAGMA schema_version is a more efficient option for querying schema version information in SQLite databases.

---

Prompt:

Record the start time after the # Benchmark comment, then re-run and re-plot

---

Generated code:

# Define a function to create tables and run a single benchmark iteration
def run_single_benchmark(db, num_tables, num_columns, benchmark_query, initial_table_index=0):
    # Create tables
    for i in range(initial_table_index, initial_table_index + num_tables):
        columns = ', '.join([f"col{j} INTEGER" for j in range(num_columns)])
        db.execute(f"CREATE TABLE table{i} ({columns})")
    
    # Benchmark
    # Start timing
    start_time = time.time()
    if benchmark_query == "PRAGMA schema_version":
        db.execute("PRAGMA schema_version")
    else:
        hashlib.md5(db.execute("SELECT group_concat(sql) FROM sqlite_master").fetchall()[0][0].encode()).hexdigest()
    
    # Calculate time taken
    elapsed_time = time.time() - start_time
    return elapsed_time

# Initialize in-memory SQLite databases
db1 = sqlite3.connect(":memory:")
db2 = sqlite3.connect(":memory:")

# Lists to store benchmark results
benchmark1_times = []
benchmark2_times = []

# Run the benchmarks for every point between 1 and 200 tables
for num in range(1, 201):
    # Run the benchmark with PRAGMA schema_version
    benchmark1_time = run_single_benchmark(db1, num_tables=1, num_columns=10, benchmark_query="PRAGMA schema_version", initial_table_index=num-1)
    benchmark1_times.append(benchmark1_time)
    
    # Run the benchmark with hashlib.md5
    benchmark2_time = run_single_benchmark(db2, num_tables=1, num_columns=10, benchmark_query="hashlib.md5", initial_table_index=num-1)
    benchmark2_times.append(benchmark2_time)

# x-axis representing the number of tables at each benchmark
num_tables = list(range(1, 201))

# Plotting the benchmark results
plt.plot(num_tables, benchmark1_times, label='PRAGMA schema_version', marker='.')
plt.plot(num_tables, benchmark2_times, label='hashlib.md5', marker='.')

# Labels and title
plt.xlabel('Number of Tables')
plt.ylabel('Time (seconds)')
plt.title('Benchmark Results Over Time')
plt.legend()

# Show the plot
plt.show()

---

Execution output:

Code execution state reset.

---

ChatGPT:

It seems that there was an issue with the code execution, causing the code execution state to be reset. Let's try running the code again.

---

Generated code:

import sqlite3
import time
import hashlib
import matplotlib.pyplot as plt

# Define a function to create tables and run a single benchmark iteration
def run_single_benchmark(db, num_tables, num_columns, benchmark_query, initial_table_index=0):
    # Create tables
    for i in range(initial_table_index, initial_table_index + num_tables):
        columns = ', '.join([f"col{j} INTEGER" for j in range(num_columns)])
        db.execute(f"CREATE TABLE table{i} ({columns})")
    
    # Benchmark
    # Start timing
    start_time = time.time()
    if benchmark_query == "PRAGMA schema_version":
        db.execute("PRAGMA schema_version")
    else:
        hashlib.md5(db.execute("SELECT group_concat(sql) FROM sqlite_master").fetchall()[0][0].encode()).hexdigest()
    
    # Calculate time taken
    elapsed_time = time.time() - start_time
    return elapsed_time

# Initialize in-memory SQLite databases
db1 = sqlite3.connect(":memory:")
db2 = sqlite3.connect(":memory:")

# Lists to store benchmark results
benchmark1_times = []
benchmark2_times = []

# Run the benchmarks for every point between 1 and 200 tables
for num in range(1, 201):
    # Run the benchmark with PRAGMA schema_version
    benchmark1_time = run_single_benchmark(db1, num_tables=1, num_columns=10, benchmark_query="PRAGMA schema_version", initial_table_index=num-1)
    benchmark1_times.append(benchmark1_time)
    
    # Run the benchmark with hashlib.md5
    benchmark2_time = run_single_benchmark(db2, num_tables=1, num_columns=10, benchmark_query="hashlib.md5", initial_table_index=num-1)
    benchmark2_times.append(benchmark2_time)

# x-axis representing the number of tables at each benchmark
num_tables = list(range(1, 201))

# Plotting the benchmark results
plt.plot(num_tables, benchmark1_times, label='PRAGMA schema_version', marker='.')
plt.plot(num_tables, benchmark2_times, label='hashlib.md5', marker='.')

# Labels and title
plt.xlabel('Number of Tables')
plt.ylabel('Time (seconds)')
plt.title('Benchmark Results Over Time')
plt.legend()

# Show the plot
plt.show()

---

Execution output:

---

ChatGPT:

Here is the updated chart showing the benchmark results over time for every point between 1 and 200 tables:

The x-axis represents the number of tables at each benchmark point, and the y-axis represents the time taken (in seconds) for each benchmark.

The blue line represents the benchmark using PRAGMA schema_version, and the orange line represents the benchmark using hashlib.md5.

As we can see from the chart, the time taken for both benchmarks generally increases as the number of tables increases. However, the time taken for PRAGMA schema_version remains relatively stable and low compared to the benchmark using hashlib.md5. The benchmark using hashlib.md5 sees an increase in time as the number of tables grows. This suggests that PRAGMA schema_version is a more efficient option for querying schema version information in SQLite databases.

---

view raw sqlite-pragma-md5.md hosted with ❤ by GitHub

(I like using "secret" Gists to share this kind of thing because they are automatically marked by GitHub as <meta name="robots" content="noindex"> - which should hopefully prevent them from polluting the web with LLM-generated content. Though I've pasted a LOT of ChatGPT-written code into this search-indexed blog entry in this particular case.)

A really weird kind of intern

Here's another analogy for large language models (since we can never have too many of those). It honestly feels a little like having a coding intern, with a strange set of characteristics:

• They've read and memorized all of the world's public coding documentation, though they stopped reading in September 2021.

• They're unbelievably fast - they take just seconds to output entire programs from scratch.

• If their code produces an error they can read the error message and attempt to update their code to fix it.

• They won't argue with you. That's not necessarily a good thing: if you ask them to build something that's clearly a bad idea they'll likely just go ahead and do it anyway.

• They respond incredibly well to feedback - they'll apply it within seconds.

• You have to know how to prompt them. I've got pretty good at this now, but it takes a lot of trial and error, and I think requires a pretty deep understanding of how they work.

• You have to closely review EVERYTHING they do.

• They work for free.

And of course, they have zero understanding of anything at all. They're a next-token-predicting machine with an unimaginably large training set.

The fact they can do even a fraction of the things they can do is, quite frankly, unbelievable. I'm still not sure I believe it myself.

---

Thoughts on AI safety in this era of increasingly powerful open source LLMs - 2023-04-10

This morning, VentureBeat published a story by Sharon Goldman: With a wave of new LLMs, open source AI is having a moment — and a red-hot debate. It covers the explosion in activity around openly available Large Language Models such as LLaMA - a trend I've been tracking in my own series LLMs on personal devices - and talks about their implications with respect to AI safety.

I talked to Sharon for this story last week. Here's the resulting excerpt:

The latest wave of open-source LLMs are much smaller and not as cutting-edge as ChatGPT, but “they get the job done,” said Simon Willison, an open-source developer and co-creator of Django, free and open-source, Python-based web framework.

“Before LLaMA came along, I think lots of people thought that in order to run a language model that was of any use at all, you needed $16,000 worth of video cards and a stack of 100 GPUs,” he told VentureBeat. “So the only way to access these models was through OpenAI or other organizations.”

But now, he explained, open-source LLMs can run on a laptop. “It turns out maybe we don’t need the cutting-edge for a lot of things,” he said.

To expand on this point: when I said "It turns out maybe we don’t need the cutting-edge for a lot of things" I was thinking specifically about tricks like the ReAct pattern, where LLMs are given the ability to use additional tools to run things like calculations or to search for information online or in private data.

This pattern is getting a LOT of attention right now: ChatGPT Plugins is one implementation, and new packages are coming out every few days such as Auto-GPT that implement variations on this theme.

An open question for me: how powerful does your LLM need to be in order to run this pattern? My hunch is that if you have an LLM that is powerful enough to produce reasonable summaries of text, it should also be powerful enough to use as part of that pattern.

Which means that a LLM running on a laptop should be enough to create truly impressive tool-enabled AI assistants - without any need to rely on cloud AI providers like OpenAI.

However, the ethical implications of using these open source LLM models are complicated and difficult to navigate, said Willison. OpenAI, for example, has extra filters and rules in place to prevent writing things like a Hitler manifesto, he explained. “But once you can run it on your own laptop and do your own additional training, you could potentially train a fascist language model — in fact, there are already projects on platforms like 4chan that aim to train ‘anti-woke’ language models,” he said.

This is concerning because it opens the door to harmful content creation at scale. Willison pointed to romance scams as an example: Now, with language models, scammers could potentially use them to convince people to fall in love and steal their money on a massive scale,” he said.

Currently, Willison says he leans towards open source AI. “As an individual programmer, I use these tools on a daily basis and my productivity has increased, allowing me to tackle more ambitious problems,” he said. “I don’t want this technology to be controlled by just a few giant companies; it feels inherently wrong to me given its impact.”

I wrote about this more here: AI-enhanced development makes me more ambitious with my projects

This is yet another example of a theme I keep coming back to: in AI, multiple things are true at the same time. The potential for harm is enormous, and the current systems have many flaws - but they are also incredibly empowering on an individual level if you can learn how to effectively use them.

But, he still expressed concern. “What if I’m wrong?” he said. “What if the risks of misuse outweigh the benefits of openness? It’s difficult to balance the pros and cons.”

This is a real challenge for me. Sci-fi paperclip scenarios aside, most of the arguments I hear from AI critics feel entirely correct to me. There are so many risks and harmful applications of this technology.

Maybe we can regulate its use in a way that helps mitigate the worst risks... but legislation is difficult to get right, and the pace at which AI is moving appears to be far beyond that of any governmental legislative process.

My current plan is to keep helping people learn how to use these tools in as positive and productive a way as possible. I hope I don't come to regret it.

---

The Changelog podcast: LLMs break the internet - 2023-04-08

I'm the guest on the latest episode of The Changelog podcast: LLMs break the internet. It's a follow-up to the episode we recorded six months ago about Stable Diffusion.

This time, we spent the whole episode talking about large language models: ChatGPT, GPT-4, Bing, Bard, Claude, LLaMA and more.

I listened to this again today while walking the dog. It's good! It's the best representation of my current thinking about this wild AI-enhanced world we are rapidly entering.

We start the episode by reviewing my predictions from six months ago. I said that search engines like Google would have LLM features within two years - Bing and Bard are live already, so I over-shot on that one. I also said that there would be LLM tools for creating 3D worlds within six months. When we recorded the episode last week I hadn't seen any that quite matched my prediction... and then yesterday Pete Huang posted a Twitter thread listing six of them!

There's a lot of other stuff in there: the full episode is 1 hour and 40 minutes long.

I'll quote one section in particular, from part way through my answer to the question Where should someone start with this? (direct link to audio).

This is the thing I worry that people are sleeping on. People who think “these language models lie to you all the time” (which they do) and “they will produce buggy code with security holes” - every single complaint about these things is true, and yet, despite all of that, the productivity benefits you get if you lean into them and say OK, how do I work with something that’s completely unreliable, that invents things, that comes up with APIs that don’t exist… how do I use that to enhance my workflow anyway?

And the answer is that you can get enormous leaps ahead in productivity and in the ambition of the kinds of projects that you take on, if you can accept both things are true at once at once: it can be flawed, and lying, and have all of these problems… and it can also be a massive productivity boost.

Here are four illustrative examples of things I've used LLMs for as a huge productivity booster in just the past few weeks.

I also gave my review of Google Bard at 1:14:46 which I think deserves a listen.

---

Path to Citus Con: Working in public - 2023-04-08

I participated in a panel discussion this week for path to Citus Con, a series of Discord audio events that are happening in the run up to the Citus Con 2023 later this month.

The topic was "Working in public on open source", and Citus developer (and pg-cron creator) Marco Slot and myself were interviewed by Claire Giordano and Pino de Candia.

The full hour-long audio conversation is now available on YouTube.

I ran it through Whisper to create my own transcript. Here's my answer to a question about the benefits of working in public:

The biggest thing for me is that I never want to have to solve the same problem twice, ever.

That's the most frustrating thing: when you sit down to solve a problem and you think wow, I solved this before and now I'm gonna have to waste my time figuring it out all over again.

A lot of the problems that I solve when I'm engineering are problems that can be captured in some kind of form. Maybe it's a commit message with a commit that updates something. Maybe it's a few notes. Maybe it's just a sketch in an issue description of the approach that I was going to take.

I found that having those out there, just having those in a system massively increases my productivity. Defaulting to putting them in public, partly it's sort of an insurance scheme.

I've worked for companies where I did everything in private. And then I left those companies and I've lost all of that work!

Everything that I do in public that has an open source license attached to it is just out there: I will never have to think about those things ever again.

That's a problem that I've solved once and will never have to go back and revisit.

And I love that.

I feel like the work that I'm doing is constantly adding up to me having more capabilities and more tools in my tool belt.

It's actually very selfish.

I have this website, my TIL website - and I just published my 400th note there.

And on the one hand, it is for other people, so that if somebody else needs to figure out how to copy a table from one SQLite database to another, and they do a Google search, they'll land on my site, and it'll solve the problem for them.

But mainly it's for me.

The fact that I'm publishing causes me to increase the quality of the notes a little bit, so they make more sense to other people.

But it also means they make more sense to me when I come back in a year's time and I've forgotten everything.

So yeah, I feel like you can actually be very selfish in your motivations and still do all of this stuff in public in a way that benefits other people.

Here are links to my posts that I referenced during the discussion:

• What to blog about

• Bing: “I will not harm you unless you harm me first”

• It doesn’t take much public creativity to stand out as a job candidate

---

Quote 2023-04-07

The progress in AI has allowed things like taking down hate speech more efficiently - and this is due entirely to large language models. Because we have large language models [...] we can do a better job than we ever could in detecting hate speech in most languages in the world. That was impossible before.

Yann LeCun

---

TIL 2023-04-09 Saving an in-memory SQLite database to a file in Python:

I was messing around in Python with an in-memory SQLite database, when I decided I actually wanted to save my experimental database to a file so I could explore it using Datasette. …

---

TIL 2023-04-09 Unix timestamp in milliseconds in SQLite:

I wanted to retrieve the time in milliseconds since the Unix epoch in SQLite. …

---

Link 2023-04-10 On Endings: Why & How We Retired Elm at Culture Amp: Culture Amp made extensive use of Elm - a ML-like functional language that compiles to JavaScript - between 2016 and 2020 while building their company's frontend. They eventually decided to move away from it, for reasons described at length in this post primarily relating to its integration with React. This piece is worth reading mainly as a thoughtful approach to engineering management challenge of deprecating a well-loved piece of technology from the recommended stack at a company.

---

Link 2023-04-10 Floor796: "An ever-expanding animation scene showing the life of the 796th floor of the huge space station" by Russian artist 0x00, who built their own custom browser-based pixel animation tool with which they are constructing this project. Absolutely crammed with pop culture references and easter eggs. The "Changes" link at the top shows almost daily updates, with links to jump to the latest content.

---

Link 2023-04-10 AI is flooding the workplace, and workers love it: The microwave kiln pottery project I helped Natalie with gets a mention in this story about people who are putting AI tools to use.

---

Link 2023-04-10 The AI singularity is here: Can't say I'm a fan of the headline, but the subhead "The time to figure out how to use generative AI and large language models in your code is now" is much more illustrative of the story. I'm referred to in this one as "One of the most outspoken advocates for LLM-enhanced development" which is a bit of a surprise!

---

TIL 2023-04-10 Creating desktop backgrounds using Midjourney:

I decided to create a new desktop background for my Mac using Midjourney. My laptop has a 16:10 aspect ratio and a retina screen, so I wanted as high a resolution image as possible. …

---

Link 2023-04-11 How we're building a browser when it's supposed to be impossible: Andreas Kling: "The ECMAScript, HTML, and CSS specifications today are (for the most part) stellar technical documents whose algorithms can be implemented with considerably less effort and guesswork than in the past." The Ladybird project is such an inspiration, and really demonstrates the enormous value of the work put in by web standards spec authors over the last twenty years.

---

Quote 2023-04-11

My strong hunch is that the GIL does not need removing, if a) subinterpreters have their own GILs and b) an efficient way is provided to pass (some) data between subinterpreters lock free and c) we find good patterns to make working with subinterpreters work.

Armin Ronacher

---

Link 2023-04-11 Sheepy-T - an LLM running on an iPhone: Kevin Kwok has a video on Twitter demonstrating Sheepy-T - his iPhone app which runs a full instruction-tuned large language model, based on EleutherAI's GPT-J, entirely on an iPhone 14. I applied for the TestFlight beta and I have this running on my phone now: it works!

---

Quote 2023-04-11

I literally lost my biggest and best client to ChatGPT today. This client is my main source of income, he’s a marketer who outsources the majority of his copy and content writing to me. Today he emailed saying that although he knows AI’s work isn’t nearly as good as mine, he can’t ignore the profit margin. [...] Please do not think you are immune to this unless you are the top 1% of writers. I just signed up for Doordash as a driver. I really wish I was kidding.

u/Ashamed_Apricot6626

---