The Thanksgiving holiday and extended weekend have given me some time to spend on projects that have been tasking me. Primary among those tasks has been some way to figure out how to make our dear Serendipity35 run a bit faster.
Back in the summer, I discovered that the bottleneck through which Serendipity35 tried to squeeze its content was our backend database server MySQL. In July the loading of the database server was so heavy that I had to move it to a faster, less busy machine to prevent endless waits for pages to be delivered and to prevent random corruption of our main display pages. While this off-loading of the database processes stopped the random file corruption we were experiencing, it only sped up the delivery of our pages a little.
For about 5 years, when developing a new project, the database backend of choice for me has been PostgresQL. At first I didn't have any compelling reason to use PostgresQL over MySQL other than it had a simpler method of assigning database user and host permissions, but as time moved on, I discovered some things about the structure of PostgresQL that made me feel more comfortable in designing web sites and applications.
I'm not advocating using PostgresQL over MySQL in all database driven applications, but here, in an egg basket. is why PostgresQL can outperform MySQL in this blogging environment.
If we were sitting at the breakfast table on our Cyber Farm and we wanted to order an omelet and MySQL was our server du jour, everytime the cook wanted an egg MySQL would go and gather every egg on the farm in its basket and bring it to the cookÂ even if just one egg was requested. On a fairly large farm, the load of carryingÂ all theÂ eggs around all the time would considerably tire out (and slow down) our server.
If PostgresQL were our server and we ordered a 3 egg omelet, because it has a much smaller egg basket than MySQL it would have to make 3 trips from the chicken coop to the cook to deliver its 3 eggs. Having a small basket, though, and not having to gather every egg on the farm each time even one egg was requested, PostgresQL would make much faster trips and not need as many support resources. Our virtual breakfast could be enjoyed without waiting for the server to catch its breath and bring us our finished omelet.
Daily repastes aside, I decided I wanted to switch our server backend from MySQL to PostgresQL and bask in its glow of promised increased speed. But that database conversion process was slow and after many starts, stops, and hand edits, I couldn't get the data stored in the MySQL database to play nice with PostgresQL. I needed a fresh start and a different approach.
I built a new installation of Serendipity on my server at home and configured t to use PostgresQL as its backend server. After installing, by hand, Serendipity35's, users and categories, I set up an RSS feed to capture all of the current content and place it on my new "at home" server. You can see for yourself the increase in speed and page delivery. Not everything was a smooth transition, though. Articles had to be edited by hand to preserve the original author ID. phpPgAdmin saved my virtual bacon when it came to making those edits.
As of now the imported web log looks pretty good when viewed with Firefox, Mozilla, or Konqueror, but it loses its sidebar in Internet Explorer. The improper display in Internet Explorer may be a function of the Serendipity blogging software version 1.0.3a I installed. Serendipity35 runs on version 1.0.2
My next project for this rapidly evaporationg weekend will be creating an SQL from my home server's file that I can import into a PostgresQL server that supports Serendipity version 1.0.2 at NJIT, and soon make the switch to PostgresQL on this blog to improve its performance.
One project I'm NOT tackling this weekend is cleaning up my desk.