DEV Community: Ken Whitesell

In Pursuit of PostgreSQL Performance

Ken Whitesell — Sat, 20 Feb 2021 03:42:46 +0000

(Or: How someone who doesn’t really know what they’re doing manages to improve performance of lengthy queries.)

I’m working on a system that has more than 200 tables and about 2 GB of data. The system is built using Django and relies heavily upon the ORM. We don’t write SQL – all our queries are built through Django querysets. Some of the queries are fairly complex, involving 10 or more tables.

About a month ago, my co-worker discovered that at least 5 of the more complex queries were taking longer to run on our test server than on his development laptop. That seemed odd to me, so I tried to recreate the situation – and I did. Those queries ran faster (generally about half the time) on each of our laptops than on the server available to us.

Table 1 – base line results
| Query   | server | laptop |
| -----   | ------:| ------:|
| Query 1 |    31s |    17s |
| Query 2 |    44s |    24s |
| Query 3 | 1m 47s |    46s |
| Query 4 |    28s |    17s |

And so the hunt began… Knowing that the amount of data was only going to continue to increase, we had to get the run times for the longer queries down into the neighborhood of our development environments.

First step was to get copies of the actual queries being run to completely isolate the time needed to run the queries from the overhead introduced by Python and the Django ORM. These are some fairly big queries – the generate SQL for the two largest queries are about 16,000 characters. (At a full 100 characters per line, that means that this would be a 160-line query – and that’s full lines. In reality, formatting these queries ends up being 200+ lines.)

Then it was time to check the basics. We’re working with identical copies of source data. We’re building the database using the same scripts. After the database is built, we’re manually running a vacuum, reindex, analyze. We’ve got enough memory on these systems to set the buffers such that they’re larger than the data being accessed.

Time to look at the differences between these systems.

Admittedly, my knowledge an understanding of PostgreSQL internals is minimal at best. I’m a lot more comfortable with my knowledge of operating systems and file systems. So I wanted to start with what I know.

Now, I’ll admit to being a bit of a Linux-bigot. I refused to believe a Windows laptop running PostgreSQL could be more than twice as fast as a server running Linux on hardware significantly better than those laptops. At this point I’m discarding the OS difference as a significant factor

The server is running PostgreSQL in a docker container with the data stored in a docker volume. Change to mapping the data directory to a mapped directory on the host. No change. Change to using a PostgreSQL instance running on the host. Still no difference.

This server is actually a virtual machine running in a VMWare ESXI. The server image is Ubuntu 20.04 with the disk mapped as an LVM volume. Could it be an issue with the multiple layers of disk mapping causing the problems? What about the selection of the disk interface? (ATA vs SCSI) So we allocated two new “physical” drives to that image, one with each of the available interfaces. I didn’t bother partitioning either drive, I created file systems directly on the devices. Created tablespaces on those file systems, created databases… and still no difference.

The next identified differences were the versions of PostgreSQL being used. My laptop is running PostgreSQL 12, my co-worker’s laptop is running PostgreSQL 10, the test server is running PostgreSQL 13. Ahhh… is there a problem with 13? Back the test server back to version 12 – no difference. Install and test with version 10 – difference! A significant difference (~ 20%), but doesn’t come close to accounting for the entire difference – and backing down from 13 to 10 is not an acceptable answer. But at least I’ve got some direction to go to continue my search.

The next stage of my quest lasted for the better part of a week. I knew I had to do a lot of reading to try and understand what might be happening. I started out by looking at all the release notes and other available docs to see what might be causing this. One of the changes I saw made references to the query planner and how it uses the various settings to improve the execution plan.

So my next step was to run “explain analyze” on my queries across the difference versions. Bingo! The slow queries on the server were running sequential scans on tables where the faster systems were using indexes. I also saw where the JIT was taking more than 30 seconds of the total run time.

More research, more ideas. There’s a PostgreSQL mailing list just for performance issues and there’s a wealth of information in the archives. Between the mailing list and the docs, I identified some settings that may have been relevant.

I started out with making some trivial changes (turning JIT off was one of them), and immediately saw some improvements. It still wasn’t an answer, but I was starting to feel confident that I was heading in the right direction.

For the record, here is the list of settings I decided to focus in on. (Note, this is after making all the appropriate changes to the various buffer and cache settings.)

seq_page_cost
random_page_cost
cpu_tuple_cost
cpu_index_tuple_cost
cpu_operator_cost
parallel_tuple_cost
parallel_setup_cost

At this point I was getting kinda lost. My first experiments were showing some improvements, but I wasn’t seeing any direct relationship between the changes I was making and the degree (or lack) of improvement. I know it’s due to a lack of understanding of exactly what these settings represent, but it makes it difficult to know exactly what changes I’m looking for.

So without knowing what else to try, I decided to rely upon some brute-force evaluation. I put together a script that would change settings, restart PostgreSQL, run the queries, save the results, and repeat this process. From there it’s an issue of looking at all the results.

One thing I realized rather quickly is that the number of combinations for these settings quickly multiply out of control. If I were to test 3 values for each of those variables independently, that produces 2,187 combinations. If each test takes 1 minute, then the complete set of tests would last more than 36 hours. If I tried to run 5 values for each of those 7 variables, it would take 54 days to complete.
I needed to come up with a different approach. I ran more limited tests with a larger number of combinations of each. For example, I ran 25 tests of different combinations of the seq_page_cost and random_page_cost, while keeping all other values the same. I then took my best combinations from than, and ran 125 tests for each of 5 different values for cpu_tuple_cost, cpu_index_tuple_cost, and cpu_operator_cost. Take the best results of that, then go back and rerun the 25 earlier tests. Repeat this process until the results as to what the best values should be, stabilize.

Many groups of these changes produce very minor overall effects. I’d get large groups of test results that are all within a tenth of a second for a 30-second query. However, the results would show large clusters around certain timing results, which I was happy to see – the big gaps occur when PostgreSQL changes a table access from an index search to a sequential scan.

And so we get to the punchline. After all this, the values we’ve currently settled on are:

seq_page_cost = 0.01
random_page_cost = 0.01
cpu_tuple_cost = 0.01
cpu_index_tuple_cost = 0.5
cpu_operator_cost = 0.25
parallel_tuple_cost = 1
parallel_setup_cost = 100

Note: My understanding is that these are all relative to each other, and that they all could be rescaled. However, that doesn’t appear to be the case. I tried multiplying all values by 100 to make them all integers, but the run times of the tests failed. I’m guessing there’s another setting I’ve missed that would also need to be adjusted.

Table 2 – final results
| Query   | server before | After | Laptop |
| ------  | -------------:| -----:|-------:|
| Query 1 |           31s |    4s |    17s |
| Query 2 |           44s |    5s |    24s |
| Query 3 |        1m 47s |   33s |    46s |
| Query 4 |           28s |    4s |    17s |

So not only is it better on the server now, it’s significantly better!

If there are any lessons that I learned from this, it would be:

I am extremely grateful to the PostgreSQL team for their documentation and their mailing lists. There's no way I could have gotten this far without those resources.
There's a lot of information available to help resolve performance issues - it may just require some digging and reading.
There are hardware configurations where the PostgreSQL defaults are not optimal for complex data relationships.

Abusing the Django Admin app

Ken Whitesell — Mon, 25 May 2020 17:55:02 +0000

There are times when I need to investigate existing databases for ETL (Extract, Transform, Load) requirements. This research primarily involves just looking at the data to identify the implicit relationships that might exist between tables that aren't always captured within the database schema itself.

Two of the tools I rely upon for doing this are the Django inspectdb command to create a set of models that I can then browse through using the Django admin app, and the Django Extensions graph_models command to help me visualize these relationships. (Frequently I'll create my ETL process by creating a Django command, but that's perhaps a different post.)

One of the features I rely upon in the Django admin - and probably the single biggest reason why I rely upon Django for this type of work - is the ability to create links for foreign keys to jump from model to model. Having that ability to just click through from row to row across tables saves me a tremendous amount of time.

Recently, I've been working on a data-migration project involving a database containing more than 200 tables spread across 3 different PostgreSQL schemas and a total of about 500GB of data. Additionally, the account available for me to access that database is strictly read-only.

This means that, at a minimum, I'm working with three different databases - the source database mentioned above, the target database, and the Django database - with the source and target databases occupying different schemas. (Handling that could also be a whole post by itself.)

When using the Django admin, one of the general requirements is to register your model classes with the admin - and, when necessary, customizing your ModelAdmin class to make it more useful for you. This becomes tedious when you're talking about 200 models that were generated by inspectdb. Having gone through the effort to clean up the models, I wasn't about to try to create 200 ModelAdmin classes.

So my first step was to find a way to automatically register all the models in an app. Django makes that easy enough:

admin.site.register(apps.all_models['ptcs'].values())

Every model in the 'ptcs' app is now registered.

This would work great, except the foreign keys all show up as "ModelName object (id)" in a drop-down list. Not only is that not particularly informational, but when you have 10,000 rows in a table being referenced, it really slows down rendering the page. The listing pages for the models also show this brief version which give you no clues at all as to the details contained within it.

This creates two problems then that needed to be solved -

Automatically create an __str__ method for every model to generate some type of informational representation of the model.
Change the rendering of the foreign key fields from a drop-down to a link.

Automatically generating an __str__ method for a model raises the question: "What fields to use?"

I took a sledgehammer to this issue and decided that the first 20 characters of the first four character fields in the model would be concatenated to the id field to make the representation. I had no idea what I was going to get, but I figured it would be a good start. (I figured that without any real context, other field types weren't going to provide useful information at first.) This is what I came up with.

[Disclaimer - folks, please don't do this at home, work, school, or anywhere else where some innocent person would want to gouge out their eyes after seeing this.]

from django.apps import apps
from django.contrib import admin
from django.db.models import fields
from django.urls import reverse
from django.utils.safestring import mark_safe

class MyModelAdmin(admin.ModelAdmin):

    fixed_field_names = {
        'ptcsuser': ['username', 'firstname', 'lastname'],
    }

    def get_url_factory(self, self_class, fk_field):
        def get_url(instance):
            ref_meta = getattr(self_class, fk_field).field.related_model._meta
            fk = getattr(instance, fk_field)
            return mark_safe('<a href="%s">%s</a>' % (
                reverse(
                    'admin:{0}_{1}_change'.format(ref_meta.app_label, ref_meta.model_name),
                    args=(fk.pk,)
                ), fk)
            )
        get_url.short_description = fk_field
        return get_url

    @staticmethod
    def make_str(model, field_list):
        def print_fields(self):
            field_values = [self.pk]
            for field_name in field_list:
                field_value = getattr(self, field_name, "  ")
                if field_value:
                    field_values.append(field_value[:20])
                else:
                    field_values.append("  ")
            return(" : ".join(["%s"]*len(field_values))
                    % tuple(field_values))
        return print_fields

    def __init__(self, model, admin_site):
        self.exclude = [str(f).split('.')[-1]
            for f in model._meta.fields
            if isinstance(f, fields.related.ForeignKey)
        ]

        self.readonly_fields = []
        for fk_field in self.exclude:
            fk_function = 'get_url_for_'+fk_field
            setattr(self, fk_function, self.get_url_factory(model, fk_field))
            self.readonly_fields.append(fk_function)

        super().__init__(model, admin_site)

        if model._meta.model_name in self.fixed_field_names:
            self.char_fields = self.fixed_field_names[model._meta.model_name]
        else:
            self.char_fields = [str(f).split('.')[-1]
                for f in model._meta.fields
                if isinstance(f, fields.CharField)
            ][:4]

        if self.char_fields:
            model.__str__ = self.make_str(model, self.char_fields)

# Register your models here.
admin.site.register(apps.all_models['ptcs'].values(), MyModelAdmin)

Starting from the bottom (which is where this actually starts), I've changed the admin.site.register call to specify my custom MyModelAdmin class.

The __init__ method starts by building the list of fields to be excluded from the automatic field generation process.

        self.exclude = [str(f).split('.')[-1]
            for f in model._meta.fields
            if isinstance(f, fields.related.ForeignKey)
        ]

This list comprehension iterates through all the fields defined in the model, identifying the fields that are ForeignKey fields. The actual field name is something like app.ModelName.field_name, and so I just want that last component -> everything after the last period.

Since I'm going to be creating the FK fields as links, I don't want the select boxes built. Next, I take that list of fields and generate a list of function calls named "get_url_for_" + the name of the FK field.

        self.readonly_fields = []
        for fk_field_name in self.exclude:
            fk_function_name = 'get_url_for_'+fk_field_name
            setattr(self, fk_function_name, self.get_url_factory(model, fk_field_name))
            self.readonly_fields.append(fk_function)

This iterates through the exclude list created above. It creates a name for the function ('get_url_for_'+fk_field_name), then calls the get_url_factory method to create the function, and assigns that function to the current MyModelAdmin instance with the created name.
(e.g. if the field name is manager, then it would create a function named get_url_for_manager in this ModelAdmin instance.)

The get_url_factory method creates a function to build the Django-admin-style url from the FK referenced model and instance when rendering the detail view. Since this field is just a link to the real instance, it's defined as a read-only field in the manager.

    def get_url_factory(self, self_class, fk_field):
        def get_url(instance):
            ref_meta = getattr(self_class, fk_field).field.related_model._meta
            fk = getattr(instance, fk_field)
            return mark_safe('<a href="%s">%s</a>' % (
                reverse(
                    'admin:{0}_{1}_change'.format(ref_meta.app_label, ref_meta.model_name),
                    args=(fk.pk,)
                ), fk)
            )
        get_url.short_description = fk_field
        return get_url

Once that's done, the last thing to do is to create the __str__ method for the model.

        if model._meta.model_name in self.fixed_field_names:
            self.char_fields = self.fixed_field_names[model._meta.model_name]
        else:
            self.char_fields = [str(f).split('.')[-1]
                for f in model._meta.fields
                if isinstance(f, fields.CharField)
            ][:4]

        if self.char_fields:
            model.__str__ = self.make_str(model, self.char_fields)

If you noticed at the top of the first listing, I've defined a dictionary that maps a table name to a list of field names. Once I've started to gather some useful information about these tables, I can manually identify which fields I want to use in the __str__ method (the first if statement).
If I haven't defined an entry for the table, I build a list of the CharFields in that model and take the first four.

If I have anything in that char_fields list, I then call my make_str function to create the __str__ method on the model. (There are two cases where char_fields is an empty list - either I've defined it as empty in the fixed_field_names dictionary, or there are no CharFields in the table.)

    @staticmethod
    def make_str(model, field_list):
        def print_fields(self):
            field_values = [self.pk]
            for field_name in field_list:
                field_value = getattr(self, field_name, "  ")
                if field_value:
                    field_values.append(field_value[:20])
                else:
                    field_values.append("  ")
            return(" : ".join(["%s"]*len(field_values))
                    % tuple(field_values))
        return print_fields

This function takes the list of fields and creates a function that builds the list of values to be displayed - the pk and the first 20 characters of each field in field_list. It joins them all together and returns the resulting string.

Voila!

Footnote: The sharp-eyed among you may ask why I didn't just use the admin_generator command in the Django extensions module. I tried that at first - but it generates one admin model per data model, and I realized very quickly that I would end up needing to manually tweak 100+ tables to get it to be usable for me. This ended up being a lot less work overall. (In real terms, less work doing that than writing this blog post!)