{"id":350657,"date":"2023-05-10T13:12:37","date_gmt":"2023-05-10T12:12:37","guid":{"rendered":"https:\/\/blog.jetbrains.com\/?post_type=datagrip&p=350657"},"modified":"2023-05-15T10:07:42","modified_gmt":"2023-05-15T09:07:42","slug":"nextcloud-performance-hacks-with-eversql-and-datagrip","status":"publish","type":"datagrip","link":"https:\/\/blog.jetbrains.com\/zh-hans\/datagrip\/2023\/05\/10\/nextcloud-performance-hacks-with-eversql-and-datagrip","title":{"rendered":"Next\u0441loud Performance Hacks with EverSQL and DataGrip"},"content":{"rendered":"\n

Hi, I’m Pasha. In my everyday life, I’m a Developer Advocate for data engineering, but by night, I’m a geek. Like, a really geeky geek! I have used Linux since 2009 as my primary OS (BTW I use Arch<\/a>). If possible, I always prefer to host everything I use. So if you decide to drop me an email at me@asm0dey.site<\/a>, rest assured, I\u2019ll receive it on my self-hosted mail server. While self-hosting certainly has its benefits, it does have some downsides, like poorer performance, that should be addressed.

This is the story of how I \u201cfought\u201d with self-hosted MySQL to get better database performance.<\/p>\n\n\n\n

Nextcloud<\/h2>\n\n\n\n

Frankly, I don’t like Dropbox. For myself and a couple of friends, I self-host Nextcloud<\/a>, an alternative to Dropbox. Nextcloud is much more powerful, featuring a vast assortment of supported plugins, which the Nextcloud people refer to as \u201capplications<\/a>\u201d. It has an application for to-do lists, an application for creating polls, and an application for having Google-like Memories and whatnot. It\u2019s written in PHP and (more importantly) uses MySQL as a database.<\/p>\n\n\n\n

How many tables could there be in such an application? If I didn\u2019t know any better, I\u2019d say something like \u201ctwo: users and files\u201d. However, there are also some maintenance and versioning routines, so you probably also have to account for tasks, versions, preferences, and so on. Here\u2019s what it looks like in reality:<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

No joke \u2013 there are a whopping 199 tables! They mostly don\u2019t have references between them. I think this is a legacy of MyISAM, which didn\u2019t support references at all. By the way, if you know the actual references, you can create them right inside DataGrip with the Create virtual reference<\/em> action.<\/p>\n\n\n\n

What are all those tables? For example, here is everything related to files:<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

After careful consideration, we understand why it works this way. File locks are needed should one file be changed from different locations.<\/p>\n\n\n\n

Trash, for example, is also just pure metadata when you think about it \u2013 it has a different physical location from anything else in the store, and it should be possible to clean the trash entirely, partially, or maybe even recover it!<\/p>\n\n\n\n

Here\u2019s what the trash table looks like:<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

Hopefully, this image alone gives you an idea of how enormous the whole thing is. And since I\u2019m hosting it myself, I\u2019m solely responsible for its performance. This is usually a good thing because I can learn a lot and help a lot of people (after all, knowledge is what makes me a good Developer Advocate). But on the other hand, it means there\u2019s a lot of pressure on me! What if the performance becomes so poor that the server starts failing the people who rely on it?<\/p>\n\n\n\n

If you\u2019re still unsure about the quality of Nextcloud itself, I just want to say that it\u2019s an excellent piece of software! Many people and organizations use it successfully, myself included<\/strong>!<\/p>\n\n\n\n

But three weeks ago, I had some bad luck when my instance of Nextcloud became terribly slow. I didn\u2019t know what happened. I didn\u2019t change anything. \u201cToo much data\u201d would be one possible explanation, but I only have 150 GB of data, so what could have gone wrong?<\/p>\n\n\n\n

Debugging<\/h2>\n\n\n\n

The first step I took was connecting to the database of my Nextcloud. Of course, it\u2019s not exposed to the internet, so I needed to use an SSH tunnel to do this, as shown here:<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

It\u2019s so simple, and the configuration even supports parsing of the `~\/.ssh\/config` file! Then, I entered my login and password:<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

I realized the IDE is actually smarter than me: I constantly forget what arguments I should use with SSH to establish an SSH tunnel. I just had to click the \u201cTest connection\u201d link, and:<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

But you know what\u2019s even more impressive? This small notification:<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

To be honest, I sometimes forget that I use MariaDB, not MySQL. And I didn\u2019t even know that it has its own JDBC driver. You bet I want it!<\/p>\n\n\n\n

As a second step, I tried to understand what was causing my instance of Nextcloud to run so slowly. It\u2019s simple in MariaDB \u2013 even more straightforward than in Postgres (I\u2019m much more familiar with it than with DBs that resemble MySQL):<\/p>\n\n\n\n

SHOW PROCESSLIST;<\/pre>\n\n\n\n
Since I would rather not have to memorize vendor-specific clauses, I created a virtual view in DataGrip:<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
\"\"<\/figure>\n\n\n\n
What did I see in the output? Something like:<\/p>\n\n\n\n
UPDATE\n  `oc_authtoken`\nSET\n  `last_activity` = 1680386487\nWHERE\n  (`id` = 16684)\n  AND (`last_activity` < 1680386472);<\/pre>\n\n\n\n
I didn\u2019t have any idea what it meant, and to be honest, I wasn\u2019t sure how to optimize it. I wanted to start by checking indexes.<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
In my fevered imagination, having an index on the ID should make this query instantaneous. And yet, I was looking at this query for almost a minute!<\/p>\n\n\n\n
When I realized that this was happening with other queries, too, I started googling. But beyond the obvious \u201ccheck indexes,\u201d there was almost no information. I then started googling things like \u201cMySQL query optimizer\u201d. Of course, the first few results were about EXPLAIN and ANALYZE, but frankly, they\u2019re alien to me in MySQL. Even the nice visual representation I could see in EXPLAIN didn\u2019t help me much:<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
But then, a miracle was found. And its name was EverSQL<\/a>.<\/p>\n\n\n\n
EverSQL<\/h2>\n\n\n\n
EverSQL is a SaaS SQL optimizer<\/a> for several databases, including MySQL and its flavors, as well as Postgres and its flavors. When I signed up, I got one credit for free. Credits are a virtual currency for \u201cbuying\u201d single optimizations. What is required for optimization? Two things:<\/p>\n\n\n\n
    \n
  1. The query you want to improve (in my case, it was that UPDATE statement).<\/li>\n\n\n\n
  2. A database description in a special format: EverSQL generates a crazy SQL query that I won\u2019t even paste here. The query produces a huge JSON with the full description of the database: tables, columns, constraints, and indexes. No actual data goes to EverSQL, so you can rest assured that your data is not being shared.<\/li>\n<\/ol>\n\n\n\n
    The story wouldn\u2019t be any fun without a few failures, right? Well, I managed to screw up the second step 🤦🏽. I issued that huge SQL query on the wrong console and got a schema for a completely different database. Be careful, do not repeat my mistakes!<\/p>\n\n\n\n
    But I was brave and lucky, and when the team from EverSQL (the co-founders, no less) dropped me a line and asked if I had been able to solve my optimization question, I didn\u2019t blindly delete the message (as sometimes I do with marketing emails). Instead, I told them my sad story, and they were kind enough to give me several credits in order to experiment with EverSQL. On the second try, I was careful and issued the correct query in the correct DB schema. They even gave me some advice on how to improve my query. I just needed to add a new index. I no longer have the original recommendation, but it was something like this:<\/p>\n\n\n\n
    CREATE OR REPLACE INDEX oc_authtoken_idx_id_last_activity\n    ON oc_authtoken (id, last_activity);<\/pre>\n\n\n\n
    While it may seem obvious to those who know what’s going on, it’s still a mystery to me why this recommendation improves anything. However, the following chart tells you everything you need to know:<\/p>\n\n\n\n
    \"\"<\/figure>\n\n\n\n
    This is the average execution time. We can see that it hasn\u2019t spiked all that much recently, but at one point it was around nine seconds. No amount of indexes could save me if the disk was very busy. It\u2019s also worth noting that, if a lot of slow queries run simultaneously, they can negatively affect each other. So I needed a more systematic approach to improve my database performance.<\/p>\n\n\n\n
    For the usual code, we should run static analysis tools as frequently as possible to control certain code health metrics. The same goes for databases: We should monitor performance. I usually have some performance monitoring tools installed on my self-hosted applications, but not for MySQL. Well, I could check whether there was sufficient space on my spinning drive and enough memory. But that was definitely not enough.<\/p>\n\n\n\n
    This is where the second exciting feature of EverSQL came into play: sensors!<\/p>\n\n\n\n
    EverSQL sensors<\/h2>\n\n\n\n
    A sensor<\/a> is a small, non-intrusive daemon that monitors slow log, CPU, and additional external signals. Basically, the installation consisted of only two steps:<\/p>\n\n\n\n
      \n
    1. Changing the MySQL settings to publish a slow log to a particular destination, as described here<\/a>.<\/li>\n\n\n\n
    2. Installing the sensor (that is just a Python file) to a specific location.<\/li>\n<\/ol>\n\n\n\n
      After that, the sensor sent all the slow queries to EverSQL\u2019s servers so that they could analyze patterns and find bottlenecks. After a couple of weeks of reporting and some optimizations, the resulting dashboard looked like this:<\/p>\n\n\n\n
      \"\"<\/figure>\n\n\n\n
      Do you see the improvement basically everywhere? Hopefully, it\u2019s the result of adding approximately 8 indexes recommended by EverSQL. Some optimizations appeared later than others, so don\u2019t waste all your credits on the first day!<\/p>\n\n\n\n
      Then, after about a week of work, something even more interesting appeared on the dashboard!<\/p>\n\n\n\n
      \"\"<\/figure>\n\n\n\n
      Every \u201cShow me\u201d button turned on a filter on the dashboard. Yes, it recommended not only adding indexes but also deleting some, like these:<\/p>\n\n\n\n
      \"\"<\/figure>\n\n\n\n
      I also had to click this checkbox in the settings:<\/p>\n\n\n\n
      \"\"<\/figure>\n\n\n\n
      That\u2019s because I don\u2019t actually manage the code of Nextcloud. Otherwise, I would have probably gotten even more recommendations on how to tune my queries to make the database perform even better.<\/p>\n\n\n\n
      But what if you\u2019re writing the code and want it optimized? We\u2019ve got a solution for you. There is an EverSQL plugin for our IDEs!<\/p>\n\n\n\n
      EverSQL plugin for JetBrains IDEs<\/h2>\n\n\n\n
      The actual name of the plugin is \u201cSQL Optimizer,\u200b Indexing Advisor,\u200b MySQL,\u200b PostgreSQL,\u200b by EverSQL<\/a>\u201d. It is compatible with the whole spectrum of our IDEs, which is a smart move, since people work with supported databases from almost every language.<\/p>\n\n\n\n
      Here is what it looks like in Marketplace:<\/p>\n\n\n\n
      \"\"<\/figure>\n\n\n\n
      Its working principle is straightforward: Just type your query, select it, and in the context menu, choose \u201cOptimize\u201d.<\/p>\n\n\n\n
      \"\"<\/figure>\n\n\n\n
      After that, you will be taken to the same interface of EverSQL in your browser with a dialog like this:<\/p>\n\n\n\n
      \"\"<\/figure>\n\n\n\n
      There, you\u2019ll be able to see all the recommendations for optimization immediately.<\/p>\n\n\n\n
      In conclusion<\/h2>\n\n\n\n
      One\u2019s choice of tools is essential. For me, the choice is always apparent \u2013 if I can control something, I will. But sometimes having this control exposes gaps in my knowledge, and I have to use other tools to close those gaps.<\/p>\n\n\n\n
      DataGrip helps me be as effective as possible when working with databases.<\/p>\n\n\n\n
      Nextcloud allows me to take control over my data (and sync my photos to my<\/em> server, not Google\u2019s),<\/p>\n\n\n\n
      And EverSQL helps me where I\u2019m just not good enough \u2013 in the optimization of my queries in my production. The tool is definitely worth trying.<\/p>\n\n\n\n
      <\/p>\n","protected":false},"author":1234,"featured_media":352076,"comment_status":"closed","ping_status":"closed","template":"","categories":[89,2347],"tags":[8130,6784,91,8131],"cross-post-tag":[],"acf":[],"_links":{"self":[{"href":"https:\/\/blog.jetbrains.com\/zh-hans\/wp-json\/wp\/v2\/datagrip\/350657"}],"collection":[{"href":"https:\/\/blog.jetbrains.com\/zh-hans\/wp-json\/wp\/v2\/datagrip"}],"about":[{"href":"https:\/\/blog.jetbrains.com\/zh-hans\/wp-json\/wp\/v2\/types\/datagrip"}],"author":[{"embeddable":true,"href":"https:\/\/blog.jetbrains.com\/zh-hans\/wp-json\/wp\/v2\/users\/1234"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.jetbrains.com\/zh-hans\/wp-json\/wp\/v2\/comments?post=350657"}],"version-history":[{"count":8,"href":"https:\/\/blog.jetbrains.com\/zh-hans\/wp-json\/wp\/v2\/datagrip\/350657\/revisions"}],"predecessor-version":[{"id":354651,"href":"https:\/\/blog.jetbrains.com\/zh-hans\/wp-json\/wp\/v2\/datagrip\/350657\/revisions\/354651"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/blog.jetbrains.com\/zh-hans\/wp-json\/wp\/v2\/media\/352076"}],"wp:attachment":[{"href":"https:\/\/blog.jetbrains.com\/zh-hans\/wp-json\/wp\/v2\/media?parent=350657"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.jetbrains.com\/zh-hans\/wp-json\/wp\/v2\/categories?post=350657"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.jetbrains.com\/zh-hans\/wp-json\/wp\/v2\/tags?post=350657"},{"taxonomy":"cross-post-tag","embeddable":true,"href":"https:\/\/blog.jetbrains.com\/zh-hans\/wp-json\/wp\/v2\/cross-post-tag?post=350657"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}