WordPress SQLite Database

Accidentally read an article about Docker SQLite WordPress by a Chinese author, quite like it, so wrote an introduction and installation instructions for those interested

My point of view is to use SQLite if you want to save RAM, run on systems with 256-512MB RAM, install on docker it is not optimized for this idea, if you like you can run it through docker, install it in one go

In this article, I install the shortened version of LCMP Minimal, using SQLite as database, mainly to run WordPress on VPS.

In practice, I see no problem with SQLite’s performance, except perhaps if your site has millions of comments, tens of thousands of posts, and a constant number of comments, then don’t worry about performance problems. On a smaller scale, around a thousand posts, SQLite can handle it comfortably. In fact, when installing many different products, I have seen some people using SQLite as a database, with data up to tens of GB, they said it still runs smoothly I also tried transferring all the current posts and comments on markknow.com to this SQLite version, and found that the database used was about… 10 MB.

Advantage:

No need to install MySQL / MariaDB as database, so it consumes less system resources, limits errors related to lack of RAM, database crash…
Backup and restore is extremely simple, just copy the original folder, upload to the new VPS and you’re done.

Disadvantages:

Configuration is usually quite complicated, but the plugin authors have already handled it all, the end user just needs to download and run it.
Any plugin related to the database will fail (Simply Static for example)
Performance may be degraded on pages with a large number of posts, Sales, or comments.

Since this is a very minimal version, I think it’s most suitable to install on Debian 11.

UpCloud VPS Configuration – Developer plans – €4.5/mo

Server-title”>OS Debian GNU/Linux 11 (Bullseye)
Caddy v2.8.4
PHP 7.4.33
CPU: 1
RAM: 1 GB
HDD: 20 GB
Bandwidth: 1TB
Location: Singapore

Install LCMP Minimal (SQLite)

Content-cập-nhập-os” dir=”auto” tabindex=”-1″>Update OS and reboot VPS

sudo apt update && sudo apt upgrade -y && sudo reboot

Install LCMP Minimal (SQLite)

sudo wget raw.githubusercontent.com/markknowdotcom/LCMP-Minimal/main/setup_sqlite.sh -O server.sh && sudo chmod +x server.sh && sudo ./server.sh

Install WordPress

Demo on domain sqlite.markknow.comreplace sqlite.markknow.com with your domain in the code below

Point DNS to VPS IP

1. Create Caddy WordPress configuration file

nano /etc/caddy/conf.d/sqlite.markknow.com.conf

Fill in the content below

www.sqlite.markknow.com {
    redir https://sqlite.markknow.com{uri}
}
sqlite.markknow.com {

# Set this path to your site's directory.
    root * /var/www/sqlite.markknow.com/htdocs
# ssl cloudflare
#    tls /etc/ssl/sqlite.markknow.com.pem /etc/ssl/sqlite.markknow.com.key

     encode zstd gzip

    @disallowed {
       path /xmlrpc.php
        path /wp-content/uploads/*.php
    }

    rewrite @disallowed /index.php

    # Serve a PHP site through php-fpm
    php_fastcgi unix//run/php/php7.4-fpm.sock

    
    @static {
        file
        path *.css *.js *.ico *.woff *.woff2
    }
    handle @static {
        header Cache-Control "public, max-age=31536000"
    }

    @static-img {
        file
        path *.gif *.jpg *.jpeg *.png *.svg *.webp *.avif
    }
    handle @static-img {
        header Cache-Control "public, max-age=31536000, immutable"
    }


    # Enable the static file server.
    file_server {
    precompressed zstd gzip
        index index.html
    }


    log {
        output file /var/log/caddy/ssl_access.log {
            roll_size 100mb
            roll_keep 3
            roll_keep_for 7d
        }
    }
    import wordpress_security
}

Ctrl+O -> Enter -> Ctrl+X to save and exit.

Restart Caddy

systemctl restart caddy

2. Install WordPress

Replace sqlite.markknow.com with your domain in the code below

mkdir -p /var/www/sqlite.markknow.com/htdocs
cd /var/www/sqlite.markknow.com/htdocs
wp core download --allow-root
wget --no-check-certificate https://raw.githubusercontent.com/aaemnnosttv/wp-sqlite-db/master/src/db.php -O /var/www/sqlite.markknow.com/htdocs/wp-content/db.php
mv /var/www/sqlite.markknow.com/htdocs/wp-config-sample.php /var/www/sqlite.markknow.com/htdocs/wp-config.php
chown -R caddy:caddy /var/www/sqlite.markknow.com/htdocs
find . -type d -exec chmod 755 {} ;
find . -type f -exec chmod 644 {} ;

Now you can type domain sqlite.markknow.com Go to browser to set up admin account

By default, the WordPress database will be located in the folder /wp-content/database

Speedtest Basic Test

To stabilize things, I will reboot the VPS once and then check some basic tests.

Because it doesn’t use MySQL / MariaDB as a database, WordPress installation only uses ~150 MB of RAM.

Run a 100 connections per second test from loader.io

Load performance is similar to when using MariaDB as database, average speed ~ 6s

I tried to backup the entire markknow.com, quickly tested on the WordPress core, the basic parts ran extremely smoothly, viewing articles in non-cached situations was almost instantaneous, a bit surprising because on MariaDB I had to tweak quite a few parameters to reach the default installation speed of SQLite

Some problems encountered are if you use a plugin that needs to connect to the database but the code is a bit stupid like the Archives section, like it loads posts by year and month, calculates the total posts in the year… the data loads a bit slowly, this part is simple, just find another plugin to run, there is no special problem

Conclude

Before, I was quite afraid of SQLite’s performance, but after using Arttalk as a comment section for markknow.com, running for nearly a year now, I have not encountered any performance-related problems. To be more precise, I think it is difficult for any blog site to reach the maximum limit of SQLite, because the blog is very basic, mostly for viewing articles, not to mention that most of us will cache via Cloudflare or any plugin, which is comfortable.

I think SQLite is a comfortable database, even too much for most needs of course, it should only be used on personal blogs, but for clients or rice, just run MySQL / MariaDB as usual for convenience, saving time handling errors (if any)

Overall satisfied with SQLite performance, maybe in a while, when WordPress officially supports SQLite, I will switch to using it for leisure.

Comment Policy: We truly value your comments and appreciate the time you take to share your thoughts and feedback with us.

Note: Comments that are identified as spam or purely promotional will be removed.

To enhance your commenting experience, consider creating a Gravatar account. By adding an avatar and using the same e-mail here, your comments will feature a unique and recognizable avatar, making it easier for other members to identify you.

Please use a valid e-mail address so you can receive notifications when your comments receive replies.

Login

Register