Linux: January 2007 Archives
If your website is slow then people will either get annoyed with it or simply not visit it. Bottom line - you can lose money.
I met Donncha in real life for the first time a few months ago and one of the things that I was anxious to talk to him about was Wordpress' speed or lack thereof, as both my own sites and those of clients etc., seemed to suffer from performance issues.
This site has been known to do over 100 gigabytes of transfer in a month, so performance can become an important factor.
Donncha, who works for Wordpress fulltime, was adamant that WP itself was not to blame. As I had previously suspected the culprits were badly written plugins. Unfortunately this is one of the downsides to PHP.
It's simply TOO easy to write bad PHP code. If even I can release a modified plugin (and I cannot program to save my life) then you know what I mean!
Evaluating Performance
Prior to the reinstall on this site the number of SQL queries per page was outrageous, with some pages requiring well in excess of 100 SQL queries to render! If you look in the page footer now you can see that it's been cut back significantly.
Server Side Options
On the server-side I'm using MySQL 4 - simply because it ships with Ubuntu. The Ubuntu config includes some caching already, but the max clients setting had to be increased significantly, as the server would become either slow or simply unresponsive at least once every 24 hours. The fix for that was simply to "borrow" a config from a much busier server :) On a sidenote. If your site is really important to you and is a major source of revenue, be that direct (sales / affiliate sales / advertising revenue) or indirect (such as business referrals worth thousands of Euro) then going dedicated or at least semi-dedicated is the only sane option. You simply cannot run an important site in a shared environment.
Apache could be optimised further by simply removing unwanted / unneeded modules OR, as some people have suggested, by replacing it completely with a lighter HTTPD.
Php could be sped up using a caching mechanism. As none of the open source PHP caching solutions seem to be available as Debian / Ubuntu packages I have decided not to try this method as yet, however, if anyone has done so I'd love to hear about your experiences. Instead of upgrading the backend, and leaving loads of old files lying around the place, a clean slate definitely helps. Now, in my case, getting a "clean slate" was not intentional, however it has paid dividends. If you cannot think of a valid reason to use a plugin, then you can probably do without it. There is no reason to have more than a couple of plugins enabled. If you need tons of plugins then you probably should consider using something else or maybe writing your own software...
One of the plugins I find particularly useful is Brian's latest comments, however the "stock" version is rather heavy, as it generates SQL queries on each and every page load. Donncha "Wordpress" O Caoimh rewrote it so that it's a hell of a lot saner. (thanks!)
Another plugin that was worth enabling is Wp-Cache. The latest version includes some improvements supplied by Mr O'Caoimh :)
What both of these plugins do is create static files on the physical disk instead of relying on MySQL to do all the work. (This is one of the reasons why some sites such as search.ie or blog.blacknight.ie use the Perl scripts that they do!)
I'm not sure what else I can do at this point to speed things up, as I've effectively taken the number of SQL queries per page from an alltime high of about 110 to about 30 (or less) If anyone has any other suggestions I'd love to give them a go
I met Donncha in real life for the first time a few months ago and one of the things that I was anxious to talk to him about was Wordpress' speed or lack thereof, as both my own sites and those of clients etc., seemed to suffer from performance issues.
This site has been known to do over 100 gigabytes of transfer in a month, so performance can become an important factor.
Donncha, who works for Wordpress fulltime, was adamant that WP itself was not to blame. As I had previously suspected the culprits were badly written plugins. Unfortunately this is one of the downsides to PHP.
It's simply TOO easy to write bad PHP code. If even I can release a modified plugin (and I cannot program to save my life) then you know what I mean!
Evaluating Performance
Prior to the reinstall on this site the number of SQL queries per page was outrageous, with some pages requiring well in excess of 100 SQL queries to render! If you look in the page footer now you can see that it's been cut back significantly.
Server Side Options
On the server-side I'm using MySQL 4 - simply because it ships with Ubuntu. The Ubuntu config includes some caching already, but the max clients setting had to be increased significantly, as the server would become either slow or simply unresponsive at least once every 24 hours. The fix for that was simply to "borrow" a config from a much busier server :) On a sidenote. If your site is really important to you and is a major source of revenue, be that direct (sales / affiliate sales / advertising revenue) or indirect (such as business referrals worth thousands of Euro) then going dedicated or at least semi-dedicated is the only sane option. You simply cannot run an important site in a shared environment.
Apache could be optimised further by simply removing unwanted / unneeded modules OR, as some people have suggested, by replacing it completely with a lighter HTTPD.
Php could be sped up using a caching mechanism. As none of the open source PHP caching solutions seem to be available as Debian / Ubuntu packages I have decided not to try this method as yet, however, if anyone has done so I'd love to hear about your experiences. Instead of upgrading the backend, and leaving loads of old files lying around the place, a clean slate definitely helps. Now, in my case, getting a "clean slate" was not intentional, however it has paid dividends. If you cannot think of a valid reason to use a plugin, then you can probably do without it. There is no reason to have more than a couple of plugins enabled. If you need tons of plugins then you probably should consider using something else or maybe writing your own software...
One of the plugins I find particularly useful is Brian's latest comments, however the "stock" version is rather heavy, as it generates SQL queries on each and every page load. Donncha "Wordpress" O Caoimh rewrote it so that it's a hell of a lot saner. (thanks!)
Another plugin that was worth enabling is Wp-Cache. The latest version includes some improvements supplied by Mr O'Caoimh :)
What both of these plugins do is create static files on the physical disk instead of relying on MySQL to do all the work. (This is one of the reasons why some sites such as search.ie or blog.blacknight.ie use the Perl scripts that they do!)
I'm not sure what else I can do at this point to speed things up, as I've effectively taken the number of SQL queries per page from an alltime high of about 110 to about 30 (or less) If anyone has any other suggestions I'd love to give them a go
Rather than try to reinvent the wheel and in order to keep in tune with the Open Source / Technical flavour of Skycon....
I've decided to give the talk on email filtering using MailScanner...
Since that sounds very uninspired I'll see if I can come up with a sexier title.
How about:
Mine's big enough! - Filtering 50,000 users' mail using open source technology
It doesn't have a great ring to it...
OR
Phishers and phuckers
Neither exactly scream "inspired!"
Laura has put a gun to my head.
I have to decide on a topic for my talk at Skycon and I've got about 24 hours to do it.
For once I won't be the only representative from Blacknight presenting!! Paul, in a possible moment of madness, also agreed to give a talk.
As he's a hardcore geek he'll probably be right at home, whereas I'm a little more wary.
What should I give a talk on??
Bearing in mind that it's quite a technical conference, judging by the timetable, what would people recommend?
I was contemplating doing something on email filtering. (The last talk I gave in UL was on SEO)
Any thoughts?
If any of you are wondering why the theme has changed completely and several of the plugins etc., have vanished... Well I just taught myself a nice little lesson about the dangers of using the following:
rm -r -f *
I successfully cleaned up my blog by killing it :)
Oh well! It was about time I cleaned up, as there were plugins and God only knows what else dating back about 3 years.
The main page now has a mere 26 queries, instead of the massive 109 it had previously.
I'll be playing around with the layout a bit as I need to put some things back in, but I will make a BIG effort to keep it simple this time. (That probably won't work, but at least I can start out trying!)
Does anyone know of a tool for working with CSV files and then importing them into MySQL?
I've got a flatfile database which has a series of fields that I need to import into MySQL.
Most of the fields in the orginal will map to new ones in the MySQL, while some on both sides will be ignored
There's about a thousand entries in the database, so I'd rather not do it manually, as it's not simply a matter of copying and inserting - I will also have to manually change one element associated with each entry....
Any suggestions would be welcome if they run on windows or linux (I don't have OSX yet)
