Geeklog - Performance Tuning

Performance Tuning

Thursday, December 20 2001 @ 06:13 AM EST
Contributed by: Tony
Views: 3,701

Tomorrow I am planning on releasing Geeklog 1.3.1 which will be nothing more than a bunch of minor bug fixes. I'd like to see the release after that really get after improving performance in Geeklog. I can categorize Geeklog's performance problems into two categories: 1) Database-related and 2) Template-related.

I know some of you Geeklog Admins have good PHP Kung-fu so I'd like to see what some of you are capable of doing to help boost Geeklog's performance. It doesn't matter how big or small the change is as long as it speeds things up. Take a page or two (or however many) and see if you can squeeze out some speed. If you can get something that impoves the speed by at least 0.1 of a second then post here how you did it. Any posted suggestions will be looked at and possibly added to the first post 1.3.1 release. To help, you can use the timer class in your /path/to/geeklog/system/classes/ directory or you can simply use the time output at the bottom of every page.

Let's see if we can't speed this bad-boy up!

Performance Tuning | 24 comments | Create New Account

The following comments are owned by whomever posted them. This site is not responsible for what they say.

It\'s about damned time :)

Authored by: Anonymous on Thursday, December 20 2001 @ 10:16 PM EST

I had gone through and optimized most of the MySQL calls in the 1.3 beta, and had greatly improved the performance. Then, I upgraded to 1.3 final and was back to slowness hell. Looks like it\'s time to start again :). I\'ll wait for 1.3.1 this time...
(IMO, this should be the featured article)

Authored by: Tony on Friday, December 21 2001 @ 07:17 AM EST

PLEASE submit your suggestions and speeding up Geeklog here! Looking forward to getting your suggestions.

Authored by: Anonymous on Friday, December 21 2001 @ 10:22 AM EST

use table type HEAP for the sessions table.
That should speed things up for you.

Authored by: Anonymous on Saturday, December 22 2001 @ 01:12 PM EST

It is my opinion that you should continue as you have with releases and let people optimize on their own as each server setup is different and should require custom tuning in each case.

Here are a few things that may help:

1. ob_start("ob_gzhandler"); - gzip's script output. The only problem is that it's incompatible with ancient browsers also it can be an issue if your server doesnt have a lot of cpu power.

2. zend optimizer. nuff said.

3. http://apc.communityconnect.com/. for debian users 'apt-get install php4-apc'. An alternative to zend cache and free as in beer.

4. If using postgres implement stored procedures/views etc.

5. Don't rely on tony to speed your hardware up he's not a magician, invest in new gear or look into hosting packages with more power.

-- matt

Authored by: matrix9180 on Saturday, December 22 2001 @ 07:19 PM EST

Compressing the output, using zend, these are all fine, but do jack *censored* when the major problem is with the MySQL calls... no where in the code is there one LIMIT set on the calls... so the code goes through the database, finds what it\'s looking for, and KEEPS LOOKING! wasting time, slowing *censored* down...

-----
.:Matrix9180:.
[Customize.org Admin]
[DeskMod.com - It\'s BACK!]

Authored by: Anonymous on Sunday, December 23 2001 @ 03:10 PM EST

Then stop using mysql and start using postgres and implement stored procedures, a cached query is just sitting there waiting to spit itself out in your code where as this request is made each time a page is refreshed with mysql only highly dynamic pages like the display of an article with comments wouldnt need to use them. Point is, yes query's should be optimized as best as possible but there will come a limit at which it can physically serve up the data while geeklog remains as featureful as it is, be that a hardware or application barrier it does exist and will present itself under heavy loads. Geeklog first and foremost has to do it's job, optimization should be a secondary concern.

Authored by: matrix9180 on Sunday, December 23 2001 @ 03:30 PM EST

it does exist and will present itself under heavy loads.\\
NOT IF THE CODE IS OPTIMIZED! UNOPTIMIZED CODE IS HARDER ON HARDWARE/SOFTWARE. IT DOESN\'T MATTER WHAT DATABASE YOU\'RE USING. NOT EVERYONE HAS THEIR OWN SERVER THEY CAN SET UP HOWEVER THEY WANT. OFF.

-----
.:Matrix9180:.
[Customize.org Admin]
[DeskMod.com - It\'s BACK!]

Authored by: Anonymous on Monday, December 24 2001 @ 12:33 PM EST

Ok then, you have the source to GL you go ahead and rip it down and take out what you dont want, there's no need to whine about how there are so many mysql calls after all this site performs pretty damn well, as do other 1.3 sites i've seen. You can only optimize a query so much you know, just as print "hello world" has a limit as to the speed it will execute you have to understand that. Sure there are a lot of query's but geeklog has a LOT of features, if you have a problem with them take them out, the code is in your hands by all means make it faster but dont command that someone else make it faster with expense to features, tony works his ass off building things people want, it's not your decision to make for people who use them all. Make no mistake about mysql either, it's a toy of a db sure it's fast but when there's real work to be done it's not even a candidate for consideration along with it's mangling (oh wait they call those "extensions") of ansi sql standards it's long term use is laughable. Lets face it, people choose mysql for it's ease of use, does that make it qualified to do what it was designed for? You would do well to ween yourself from it's death grip. Of course this may change with version 4 but there are other more mature proven solutions out there. I hold most of my views from the perspective of a systems administrator and I'm sure it shows as hardware and software configuration are part of my job description. Just dont let people fool you into thinking there's a single silver bullet that will make all your problems go away as you seem to think it will. There are numerous approaches to this non-issue, not everyone will choose your solution so lets give them information and let them choose for themselves.

Authored by: matrix9180 on Monday, December 24 2001 @ 02:25 PM EST

I\'m not saying that any features need to be taken out to increase speed... just that some things can be done a lot better.... you want to talk about features slowing code down? Please, go over to my friend Dave Gorman\'s site, www.deskmod.com, get an account, and tell me then that things can\'t be faster and that there are limits... at any given time, DeskMod has an average of 30 logged in users, with countless anon users, and the code still executes in well under a second. When the site relaunched on the 12th of December, at one point there were 130 logged in users, and the site still had page rendering times of less than a second. I\'M NOT SAYING THERE IS ANYTHING WRONG WITH TONY\'S CODE, I L0VE IT. IT\'S JUST THAT THERE ARE MANY THINGS THAT CAN BE DONE TO SPEED IT UP. I DIDN\'T SAY ANYTHING ABOUT REMOVING FEATURES. USING POSTGRESSQL WILL HAVE ITS LIMITS TOO. NOT EVERYONE CAN GO INSTALL POSTGRES ON THEIR SERVER. A LOT CAN BE DONE USING MYSQL TO MAKE IT FASTER. QUIT ARGUING WITH ME. THATS ALL YOU WANT TO DO. IF YOU WANNA ARGUE WITH ME, SEND ME AN INSTANT MESSAGE. MY SCREEN NAME IS MATRIX9180. ALSO, TALK TO THE OWNER OF DESKMOD ABOUT SPEEDY CODE. HIS SCREEN NAME IS G0RMAN.
HAVE A NICE DAY.

-----
.:Matrix9180:.
[Customize.org Admin]
[DeskMod.com - It\'s BACK!]

Authored by: Anonymous on Monday, December 24 2001 @ 08:36 PM EST

Right, the all caps response dictates that you\'re a reasonable person... and one worthy of conversation. Lowering yourself to personal insults is pretty indicative of your maturity level. Why do you care who I am, I seek no respect or approval rating, just discuss the issues and try to refrain from cursing in such a negative context, it makes you appear juvenile.

Authored by: Falkware on Wednesday, December 26 2001 @ 02:06 AM EST

In my opinion both of you need to just relax. You\'re out of line and off topic, which serves very little purpose to further developing Geeklog.

Neither of you can really sway Tony\'s opinion of what he sees fit for Geeklog, so stop it. I\'m sure he\'s already been thinking about SQL optimizing, but is also giving you, the users, a chance to toss in your 2 cents. Something I believe both of you did about 4 replies ago.

I appreciate it if you could both take your argument somewhere else, where it can better be resolved and we can get back to what matters here, the optimization of geeklog.

Merry Christmas

Authored by: Anonymous on Wednesday, December 26 2001 @ 10:36 PM EST

Looks like it\'s time for me to fork GeekLog off into another project. Things don\'t go your way, fork it and go your own way. =)
See ya \'round, guys...
Ya gotta love the GPL.
latez

Authored by: pomes on Friday, December 28 2001 @ 02:58 AM EST

I am duly impressed by your ability to name drop. Not every yahoo jagoff on the internet can do that.

-----
boufdot.org - powered by Geeklog!

Authored by: matrix9180 on Monday, December 24 2001 @ 02:29 PM EST

Also, I would REALLY appreciate it if you would actually sign in here, instead of hiding under the protection of \"anonymous user\". You think I\'m a dumbass? you wanna flame me, send me hate mail, try to convince me that code doesn\'t need to be optimized, put words in my mouth? go right the *censored* ahead, my AIM name is Matrix9180, my email is admin@phatskinz.org, or matrix9180@hotmail.com. I\'m on DeskMod.com under the name Matrix9180, as well as at Customize.org, you can reach me any of these places in private. quit showing your ignorance here.

-----
.:Matrix9180:.
[Customize.org Admin]
[DeskMod.com - It\'s BACK!]

MySQL calls

Authored by: Anonymous on Friday, December 21 2001 @ 12:42 PM EST

The biggest performance killer with GeekLog I\'ve found is with the MySQL calls. Going through and adding limits to everything you can will greatly help. Think about it. You\'re looking for one thing. You find it. You stop. Without limits, MySQL finds what it\'s looking for, and keeps looking. A major waste of time, and a performance killer. All of my speed improvements were done by setting limits on the MySQL calls (just add \'LIMIT $whatevernumber\' to the end of the calls)

(forgot to login)

Authored by: matrix9180 on Friday, December 21 2001 @ 12:48 PM EST

Those Anonymous comments were by me (execpt for the reply to you, tony... I didn\'t write that)
I forgot to login ;)

.:Matrix9180:.
[Customize.org Admin]
[DeskMod.com - It\'s BAAACK!!!]

I suck!

Authored by: Jason on Sunday, December 23 2001 @ 09:31 PM EST

Hey Tony, I just wanted to say you\'ve done a great job.

I\'m sorry I\'ve been so hidden for so long, but my life is, er, weird! :-) Been working on my new column and my first novel! Not too mention politics are work are keeping me QUITE busy (but I find it fun, is that wrong?).

My suggestions are 1) don\'t let religous arguments rule the day (ie. MySQL vs Postrges, etc..) and 2) keep doing what your doing.!

Refining the SQL calls is the best way to improve profomance. Optimize tables and rework things if necessary.

Geeklog is looking great! My many thanks to all of you for carrying the torch!

God bless and Merry Chirstmas!
- Jason

Tony...

Authored by: matrix9180 on Sunday, December 23 2001 @ 09:54 PM EST

Tony, if you can give me your code (I\\\'m sure you\\\'ve done more since u updated CVS), I\\\'ll go ahead and make my changes for you to see... I have some ideas as to how to make many different things work faster, and a more advanced blocks system... contact me and I\\\'ll give you details. you can reach me on AIM... my screen name is Matrix9180, or email me, matrix9180@hotmail.com, or if you have an account at deskmod.com, send me an ISM...
the performance boost/ advanced features I have in mind might be what is needed to put GeekLog on top among the other CMSs.

-----
.:Matrix9180:.
[Customize.org Admin]
[DeskMod.com - It\\\'s BACK!]

Holy Cow!

Authored by: Tony on Wednesday, December 26 2001 @ 02:25 PM EST

First off, let\'s not get caught up in the Coke vs. Pepsi (i.e. MySQL vs. Postgresql). Reasons for not using the MySQL limit call is Geeklog is planning to support databases other than MySQL (e.g. Postgresql).

A couple of things I know that can help is a) zend optimizer b) sql modifications and c) reevaluating indexes/keys

Also, I\'m still a big fan of caching the SQL calls using some of the tools. I\'m a bit hesitant to use something like APC just because it would make Geeklog harder to install.

I\'ll start with the Zend Optimizer and then post my findings here. After that I\'ll look at adding some new indexes and see if that doesn\'t help

Keep the ideas coming!!!

Is 1.3.1 out yet?

Authored by: Anonymous on Friday, December 28 2001 @ 10:58 AM EST

It\'s not in sourceforge.

thanks.

Here\'s a way to help immediately

Authored by: Tony on Friday, January 04 2002 @ 07:00 AM EST

On my site, Iowa Outdoors, I have 474 stories and index.php was taking about 2.37 seconds to generate (yes, seconds). That is absolutely terrible. I added an index for tid in the stories table and that shaved .3 seconds off. To do that with MySQL execute this:

CREATE INDEX stories_tid ON stories(tid(20))

FYI, I don't think you'll see major performance issues with Geeklog until you get a lot of data in your database. I'll be researching more indexes that might help.

Another huge time saver

Authored by: Tony on Friday, January 04 2002 @ 08:10 AM EST

Ok, this one is a bit more complicated but this ended up saving me .7 of second. I wanted to analyze all the SQL queries being executed by Geeklog to see if anything would stand out at me. To do this I created a simple table called sql_queries with two fields: 1) sql_id (auto increment) 2) sql_query (the actual sql executed) . I then added a mysql_query call to mysql.class.php to insert all sql in the table and what I found was a bit of a surprise.

The following SQL query was executed 53 times:

SELECT ug_main_grp_id,grp_name FROM group_assignments,groups WHERE grp_id = ug_main_grp_id AND ug_uid = **my_user_id**;

Obviously that alarmed me. I found out the culprit was SEC_inGroup() in lib-security. That function is called all over the place and it calls itself recursively compounding the number of SQL queries. I then noticed that the function above it SEC_getUserGroup() could be called once early on for the user and could server as a sort of cache for SEC_inGroup. So to fix this in lib-common.php after this line:

$_RIGHTS = explode(',',SEC_getUserPermissions());

I added this: // Cache the groups this user belongs to (saves us time later) $_GROUPS = SEC_getUserGroups($_USER['uid']);

The I changed SEC_inGroup() to use $_GROUPS like this.

Now that query only gets executed once! If you need to have the performance gain now then great, you can edit the files accordingly. Needless to say this enhancement will be in 1.3.1

Another good index to add

Authored by: Tony on Friday, January 04 2002 @ 08:54 AM EST

After researching Geeklog\'s queries there are a number of queries like:

SELECT ug_main_grp_id,grp_name FROM group_assignments,groups WHERE grp_id = ug_main_grp_id AND ug_grp_id = <some_group_id>

This query can be improved by adding an index on ug_grp_id:

CREATE INDEX group_assignments_ug_grp_id ON group_assignments(ug_grp_id);

This shaved about .26 of a second off.

Small Gainer...

Authored by: Tony on Friday, January 04 2002 @ 09:10 AM EST

In config.php (or during the installation) if you turn of $_CONF[\'showstorycount\'] and $_CONF[\'showsubmissioncount\'] it will save you about .09 of a second with approximately 450 stories in your database.

Resources

Sections

User Functions

What's New

STORIES

COMMENTS last 2 days

TRACKBACKS last 2 days

NEW FILES last 14 days

LINKS last 2 weeks

Older Stories

Monday 17-Mar

Tuesday 08-Jan

Saturday 29-Dec

Tuesday 04-Dec

Sunday 04-Nov

Performance Tuning

What's Related

Story Options