[riddle]

Thanks, Sam.

I’m not sure whether to even touch your last suggestion, about using the latest trunk rather than the latest release. I see the trunk code browser at https://trac.bbpress.org/browser/trunk but I don’t know where to find it as one downloadable archive. And surely the trunk has new bugs and unfinished features, which is why it hasn’t been turned into a new release, right?

Can you give us any hints about what has changed that plugin writers need to know about?

P.S. Where do I find BB_Query?

[riddle]

Thanks for the suggestions. It’s good to know that topicmeta can be queried directly in SQL rather than having to serially retrieve topics and address it through PHP data structures.

However, I still don’t know whether I’m clear on the concept. Is the point behind the topicmeta table to map individual topics to particular bits of metadata, on the basis of one (topic, metadata key, metadata value) tuple per row?

If so, how do you efficiently handle the case when you need multiple pieces of metadata per topic? A geolocation is actually two floating-point numbers (latitude and longitude). Ideally I’d like to be able to treat them as such in my queries without a lot of packing and unpacking to squeeze them into one field.

Finally, another SQL newb question: are there peformance or other reasons why I can’t use a join? Because I actually need to give the Google Maps API info from columns in the topics table, too. Setting aside the point about geolocations actually being two values, could I do something like this?

SELECT t.topic_id, topic_title, topic_poster_name, forum_id, meta_value

FROM bb_topics t, bb_topicmeta m

WHERE t.topic_id = m.topic_id

AND topic_status = 0

AND meta_key = ‘topic_geolocation’

AND meta_value [meets some other magic condition]

[riddle]

Hmm — actually, looking at SamBauers’ tip three posts back, I see a problem.

My intention is to build a Google Maps mashup plotting topics on a map. That requires me to be able to efficiently search the entire database for all points which fall within the bounding box of the map. (Actually that’s not quite true: I give Google’s API a list of points and it sifts through them for the ones that fit the current map view, but in either case I need to know the coordinates of a bunch of points at once.)

However, if I have to call get_topic() for every topic in the database in order to get its coordinates, that’s going to result in a ridiculous number of database hits every time I render a map. Right?

I believe I need a table, whether it’s bb_topics or one I create, which will let me access the coordinates all the topics in one query. Or is there an efficient way to do that using topicmeta and I’m missing it?

[riddle]

Cool, this is exactly the kind of bbPress-specific best practices I need to know and am not going to get from “PHP and MySQL in 30 Minutes”!

Is topicmeta documented anywhere? I’m looking at the code and can’t say it’s entirely clear…

[riddle]

Sorry — I missed dbDelta and didn’t have a WP install handy to compare with.

As for whether I need a new table: is it advisable for a plugin to mess around with adding columns to standard bbPress tables? I suppose I thought I could do less damage by creating my own.

My application: I want to add geolocation (latitude and longitude) to topics. From a strict DB schema point of view there’s no reason for my data not to go into the topics table.

[riddle]

More specifically: does bbPress have mechanisms for automatic table creation and updating like the ones described in the WordPress docs?

See: https://codex.wordpress.org/Creating_Tables_with_Plugins

Should I start my Foo plugin by creating a function foo_install()? And can that function call dbDelta()? (I don’t see dbDelta in my bbPress anywhere.) If not, what’s the recommended alternative model for creating tables to be used in plugins?

[riddle]

Thanks for all your help, CK! I’ll look into eaccelerator — although I think I’ll finish building the site first.

[riddle]

P.S. I wrote a quick and dirty perl script to do a bunch of page retrievals, grep for bbPress’s self-reporting string in the footer, and report median times. Let me know if anybody wants it.

[riddle]

Restart mystery solved: WestHost changed something so mysqld required an IP bind address in my.cnf. Once I formally reported the problem to WestHost support, they had it fixed in minutes and were even kind enough to apologize. (I sure wish that init.d gave some diagnostics!)

That done, I turned on query caching:

query_cache_type=1

query_cache_limit=1M

query_cache_size=32M

The result is significant improvement but there may be more I can do. I’m now getting a median response time of about 0.125 seconds for 8 queries on the top page and 0.205 seconds for 12 queries on a topic page.

In phpMyAdmin the indicators are greatly improved but I still have a couple in the red zone:

Handler_read_rnd_next 15

(“The number of requests to read the next row in the data file. This is high if you are doing a lot of table scans. Generally this suggests that your tables are not properly indexed or that your queries are not written to take advantage of the indexes you have.”)

Key_reads 6

(“The number of physical reads of a key block from disk. If Key_reads is big, then your key_buffer_size value is probably too small. The cache miss rate can be calculated as Key_reads/Key_read_requests.” My key buffer size is 16M.)

Opened_tables 2

(“The number of tables that have been opened. If opened tables is big, your table cache value is probably too small.” My table cache is 64.)

I’m inclined to say this is good enough for now, but I’m always open for further suggestions.

[riddle]

Just think, if Steve Jobs had had a different horticultural whim the day he named a certain product line, I could be typing this on a WineBook right now!

[riddle]

Chris, thanks for the tips. Yes, WestHost is a little peculiar in what it chooses to make available to shell customers — there’s no tcsh, either.

“/etc/rc.d/init.d/mysqld restart” was what killed my MySQL and “/etc/rc.d/init.d/mysqld start” is a no-op. Sigh. I’ve got a query in to WestHost support.

And thanks for the warning about the diagnostics. Makes sense. I’ll disable them for now.

[riddle]

Well, drat, while trying to get mysqld to reread my.cnf, I managed to kill it. So any of you kind souls reading this, please try back in a while (or tell me how to restart mysqld!).

[riddle]

I do have access to my.cnf so I’m now off educating myself about the basics of mySQL configuration so I can enable caching without screwing something up.

Meanwhile, though, I have some more data points. In phpMyAdmin > Runtime Information the following variables are highlighted in red, meaning they are outside desirable range.

Slow_queries 5

Handler_read_rnd 39k

Handler_read_rnd_next 437k

Slow_launch_threads 1

Created_tmp_disk_tables 56

Opened_tables 84

The last is supposed to be a strong indication that my table cache value is too small (no surprise since it’s zero!). phpMyAdmin provides useful tips in interpreting these values, but as a mySQL newbie I’m still having trouble deciding how to act on them. Suggestions welcome.

As always, thanks!

[riddle]

I managed to hack the breadcrumbs code in Kakumei to properly show one level of subforums. I’m not yet a plugin hacker, nor am I sure whether this sort of cosmetic tinkering can be handled by plugins as opposed to editing the themes, but if anybody’s interested I can try to extract enough fragments to help you get started retracing my steps.

[riddle]

Thanks, CK. I’m a trusting soul, so here you go (on the same server and database):

http://riddle.whsites.net/bbpress/phpinfo.php

http://riddle.whsites.net/bbpress/static.html

http://riddle.whsites.net/cgi-bin/perlinfo.cgi

If I’ve just opened a big security hole, I hope someone will tell me!

I’m paying about $12 a month so this shouldn’t be absolute bottom-of-the-barrel service.

As for the shell: yes, I’m an old Unix guy, I built the site in SSH and vi. There’s no top, w or uptime in my path. If there’s a Red Hat-specific bin directory where I should look, I’ll do so.

[riddle]

I settled on Patrick Hunlock’s wonderful client-side javascript to pull a Yahoo Pipes JSON feed into the page. It doesn’t cache anything on the server, which of course is good for the server but causes a noticeable delay for the user. C’est la vie.

You can see it in action at (please don’t link to this site yet):

http:// muellerfever dot com /news.php

[riddle]

Thanks for all the comments!

I’m using WestHost. I am (naively, I know) trying to keep my site out of the search engines for another week or so, so please don’t link to it:

http:// muellerfever dot com

(I’m not using robots.txt because I don’t know how long crawlers cache the “do not harvest” status and I do want to announce the site soon.)

WestHost seems not to have top, w or uptime where I can get at them. I don’t know anything about the size of their pipes.

I chose WestHost because they came highly recommended and support Rails, which I may use for another project. I also have a HostingMatters account if I have reason to believe that would be faster.

[riddle]

Thanks! I’ll check it out.

[riddle]

Mistainu, by “breadcrumb structure” do you mean that the family tree of a sub-forum should be displayed? That’s high on my wishlist.

In other words, right now a subforum’s breadcrumbs look like:

Food Forums – winesaps

When I wish they looked like:

Food Forums – fruit – apples – winesaps

I’m reluctant to use subforums at all if I can’t give the user good feedback about where they are in the forum structure.

So: does your breadcrumb code address this problem? And if so, is it coherent enough that I can glue it into place myself without being an expert about bbPress internals? (I do know enough PHP to be dangerous.)

[riddle]

Thanks, mdawaffe!

[riddle]

This would be very high on my wishlist, too. I’m kind of surprised it’s not in the core functionality.

[riddle]

Thanks, Jaithn. I’ve actually looked at bbPortal and was left puzzled as to what in the heck it’s supposed to do. As someone pointed out elsewhere, a brief description of its purpose and some screenshots could do wonders. But your message is a sign that I should look at it again.

[riddle]

Aha, more info about the post-to-subforum error. Details are in Trac at https://trac.bbpress.org/ticket/674 but it appears that Kakumei may be scribbling on a global $forum_id variable deep in the bowels of bb-templates/kakumei/forum.php.

Fel64, when you couldn’t repeat the problem, were you using Kakumei?

I’m such a bbPress newb that I don’t know whether Kakumei falls outside the purview of bugs we should be reporting through Trac. Suggestions welcome.

[riddle]

Thanks for the encouragement.

Actually, fel64, your suggestion of reverse-engineering the HTML skeleton of a bbPress page to make standalone pages that can use the same stylesheets makes perfect sense and is probably what I’ll end up doing.

[riddle]

Okay, I posted my issues to Trac. I’m a Trac newbie, so please be gentle if I broke conventions somehow. Feel free to correct or add to my descriptions:

https://trac.bbpress.org/ticket/672

https://trac.bbpress.org/ticket/673

https://trac.bbpress.org/ticket/674