My experience with WordPress SQLite

A few months back I wrote a post on how to get WordPress running with SQLite which received a great response and turned out to be something people were looking for quite some time.

This means instead of using a production-grade SQL Database you can use a file that can be queried as a DB, making the footprint smaller.

I would like to kjmtsh to make this possible by creating the SQLite Integration plugin.

This website was previously powered by WordPress and SQLite which was great. It did not require configuring of a MySQL database, it ran very well on my VPS, which was a $5 Digital Ocean droplet that did a great job serving the site as it was relatively new and had only 60 to 100 views a day.

It was all going well until I started using plugins to get some extra functionality and 3rd party services that made the file size bigger and took longer to query, over time the website start receiving more visitors, now 200 to 300 which made it slow, when multiple visits took place serving them took even longer and even the admin interface was affected by them.

The “vacuum” option worked well to reduce the file size, the main problem was not of the file size. It was of unused columns and tables created by plugins that were never deleted or cleared after disabling it and removing it from the system.

WordPress does not remove an unused information and does not give you an option by default.

I found that using cache plugins would be a temporary fix, which did fine, but then it started making the admin part sluggish and things, like drafting and editing previously published content, was a nightmare while the site still grew and had, even more, larger amounts of viewership and made it super slow a request would take 30 seconds to load.

I spent some good time doing research on troubleshooting this problem and it turned out that the SQLite setup is great for websites that have smaller viewership and those that don’t update very frequently.

This website has over 172 posts which no longer made it small and continues to grow.

The reason I was using SQLite was that MySQL would be too heavy on a single core 512 MB VPS and I did not expect to have a large amount of audience.

Right now this website runs Ghost which is a nice blogging platform, back to WordPress and is powered by MySQL Database and I have upgraded to a $10 VPS which offers 1GB of RAM.

WordPress was never designed for SQLite but can be a good alternative for those who don’t prefer or don’t have the resource to host a MySQL Database.

This would be the perfect solution for devices like Raspberry Pi.

It would fail if there is more than one editor working on the same or different content as SQLite does not do Async quite well and will be slow as hell.

Hope you enjoyed the read, leave a comment below to let me know what you think or if you are planning to go with SQLite.

WordPress app for Mac/Windows/Linux

WordPress recently announced their clients for Mac, Windows and Linux, this app gives you access to the WordPress dashboard as a native client to the supported platform you are using.

So now you can access all your self-hosted WordPress(requires Jetpack to be enabled) or your hosted WordPress blogs, with the beautifully designed WordPress dashboard.

You can get a copy of the client for Mac, Windows or Linux by simply visiting here, and downloading the client for your system.

Thank you for reading,

If you have any questions feel free to leave a comment below.

Front-end editor for WordPress

WordPress is a great CMS(Content Management System) to manage blogs, websites of various scales, over time it has gained popularity due to fulfilling demand of its users. It has gained features like autosave and many other features of the time.

The only thing it lacks at this point is a front-end editor, for the user. The user who prefers to use front-end instead of the backend, frontend editor has many advantages.

  • Preview of the how the page would actually look
  • Structure markup depending on the design.
  • Manage media(images, videos, etc) according to the design of the page
  • Easier to handle just for the user

Is it possible to get all of this right now? Yes it is, there is a plugin for that too…Its WA-Frontend a plugin currently in active development that lets you edit pages and post from the front end, it also allows you to manage settings and you can also access the autosave in the front end with version control, this lets you preview or revert to your earlier edited history, all you have to do is install this plugin and you are ready to go to the front-end to see it in action.

Here are a few screenshots:

Thank you for reading, for more WordPress content stay tuned.

Lightweight WordPress setup

WordPress is the most popular CMS used by a large number of websites, It is written in PHP which makes it an ideal choice for most people. It offers a lot of features and a really easy to use backend.

But, it performs well until the MySQL database is small and your WordPress setup does not live on to many plugins and has permalinks set correctly.

WordPress on a small Web Server cannot handle many visitors and might fail due to MySQL database crashing which at time can be a big problem,it can occur due to lack of free memory, if there are other things that use the MySQL database it will make WordPress slow if the website get a lot of traffic.

So, I decided to write a simple post, that will let you run WordPress on a small Web Server with optimum performance.

It is recommended that you do this in a clean WordPress setup and then migrate content to the newer install rather than tweaking the current WordPress install that is running in production, It is also better to test the environment before switching it to production, because there are many factors that can make a positive or negative performance difference.

Lets do it!

We will use SQLite instead of MySQL, this is recommended if the website is just a blog or does not serve or receive thousands of requests at a time, I have not tested this in high traffic, but has been rock solid stable on the amount of views I get for my website.

You can follow this guide to install and setup WordPress with Sqlite.

You can use WP Super Cache plugin and cache WordPress, so it does not have to query the database on every request, it is recommended that you set WP Super Cache only to update when a new comment is made or when new post or page is updated.

If you are running NGINX instead of using WP Super Cache you can use the build in micro-caching of NGINX also known as FastCGI cache which will cache WordPress for a fixed amount of time, this will cache the page on the first page request and will display stale content until timeout, this can do a big performance difference and can save you a great amount of system resource because it does not query the database on every request

I have written a NGINX config that can be used as an example while configuring your WordPress with NGINX setup.

I would also recommend to keep your WordPress install as clean as possible, don’t keep plugins that you don’t require, avoid plugins that are not longer being maintained and try to use a theme or write a theme that does most of the thing with the power of PHP templating.

Thanks for reading,

Have a question? leave a comment below.

WordPress with SQLite

WordPress is a free open source CMS (Content Management System) written in PHP and uses MySQL, WordPress is one of the most popular CMS, It does not support any other database other than MySQL, Due to a lack of support for any other Database, It sometimes turns into a problem when the user wants to have WordPress installed but does not have MySQL database for various reasons sometimes the Web Server is not powerful enough to handle MySQL or the hosting service does not offer MySQL in certain plans. Certain hosting service run the MySQL database in a separate data center which can sometimes be slow to fetch the data or might slow the WordPress system completely on higher traffic.

However, a WordPress plugin developer kjmtsh developed a plugin SQLite Integration which lets you run WordPress with SQLite, a file based alternative to MySQL which is faster, consumes lesser system resource. So now you can run WordPress with SQLite. _I have tried this plugin with a fresh WordPress install and did not try to migrate an existing MySQL based WordPress install. So I don’t recommend trying it on an existing WordPress install with MySQL.

Getting started

First, grab a copy of WordPress from here and expand the files in into Web root or wherever you website source is stored.

Now get the SQLite integration plugin from here and expand the files.

Now place the SQLite integration plugin folder in your wp-content/plugins directory.

Copy the db.php file found in the sqlite-integration folder to wordpress/wp-content folder.

Rename wordpress/wp-config-sample.php to wordpress/wp-config.php.

You can use these parameters to configure what the database should be named and where it should be located, by default the Database is named .ht.sqlite stored in wp-content/database/

SQLite database file name settings.

define('DB_FILE', 'your_database_name');  

Database storage directory

define('DB_DIR', '/home/youraccount/database_directory/');  

Visit your WordPress install and you should be greeted with a setup page.

You can enable the SQLite plugin on the plugins page and visit the Miscellaneous page and optimize the database every time there is a new plugin installed/removed or there is need to do a database cleanup.

optimize will drop the size of the database by only keeping the required content of posts, pages, and plugins, this will improve the performance of WordPress overall.

Now you have WordPress running on SQLite installed on your system.

If you require patches or issue tracking visit here.