Can anyone create a memcache plugin?

[Don Chambers]

There seem to be two queries that are responsible for most of the mySQL load.. and sometimes, that load can be sufficient to bring mysql to its knees even though the hardware is not at maximum.

Once upon a time, someone who knows a heck of a lot more about databases than I suggested that we have 2 queries that are responsible for most of the mysql processing:

This one seems to simply be getting recent entries:

Code: Select all

SELECT
                    ep_sticky.value AS orderkey,

                    e.id,
                    e.title,
                    e.timestamp,
                    e.comments,
                    e.exflag,
                    e.authorid,
                    e.trackbacks,
                    e.isdraft,
                    e.allow_comments,
                    e.last_modified,

                    a.realname AS author,
                    a.username AS loginname,
                    a.email
                     , e.body, e.extended

                FROM
                    serendipity_entries AS e
                    LEFT JOIN serendipity_authors a
                        ON e.authorid = a.authorid
                    LEFT JOIN serendipity_entrycat ec
                        ON e.id = ec.entryid
                    LEFT JOIN serendipity_category c
                        ON ec.categoryid = c.categoryid
                     LEFT OUTER JOIN serendipity_entryproperties  
ep_no_frontpage
                                                  ON (e.id =  
ep_no_frontpage.entryid AND ep_no_frontpage.property =  
'ep_no_frontpage')
LEFT OUTER JOIN serendipity_entryproperties ep_access
                                              ON (e.id =  
ep_access.entryid AND ep_access.property = 'ep_access')
LEFT JOIN serendipity_entryproperties ep_sticky
                                            ON (e.id =  
ep_sticky.entryid AND ep_sticky.property = 'ep_is_sticky')
                    WHERE isdraft = 'false' AND e.timestamp <=  
1225306200 AND  (ep_access.property IS NULL OR ep_access.value =  
'public') AND  (ep_no_frontpage.property IS NULL OR  
ep_no_frontpage.value
!= 'true')
                     GROUP BY e.id
            ORDER BY orderkey DESC, timestamp DESC
                      LIMIT 10;

And this one which seems to be getting the total number of entries:

Code: Select all

SELECT count(distinct e.id)
                FROM
                    serendipity_entries AS e
                    LEFT JOIN serendipity_authors a
                        ON e.authorid = a.authorid
                    LEFT JOIN serendipity_entrycat ec
                        ON e.id = ec.entryid
                    LEFT JOIN serendipity_category c
                        ON ec.categoryid = c.categoryid
                     LEFT OUTER JOIN serendipity_entryproperties  
ep_no_frontpage
                                                  ON (e.id =  
ep_no_frontpage.entryid AND ep_no_frontpage.property =  
'ep_no_frontpage')
LEFT OUTER JOIN serendipity_entryproperties ep_access
                                              ON (e.id =  
ep_access.entryid AND ep_access.property = 'ep_access')
LEFT JOIN serendipity_entryproperties ep_sticky
                                            ON (e.id =  
ep_sticky.entryid AND ep_sticky.property = 'ep_is_sticky')
                    WHERE isdraft = 'false' AND e.timestamp <=  
1225305900 AND  (ep_access.property IS NULL OR ep_access.value =  
'public') AND  (ep_no_frontpage.property IS NULL OR  
ep_no_frontpage.value
!= 'true');

If there is a way to make these more efficient, but all means lets do it. But he also said that, notwithstanding changes to how those queries work, that these sites would benefit tremendously if serendipity could work with memcached.

So, anyone interested in writing a plugin that will utilize memcache?

[garvinhicking]

Hi!

I've been optimizing those two queries for the last 5 years. I don't have any optimizing magic left, sadly. The only thing to do is remove entryproperty and privileges to make the query leaner, or pump up the mysql query cache...other than that, I'm very open to suggestions, but can't offer any on my own

Putting the query results into a memcache doesn't help so much, because the queries are dependant on the currently logged in user and the context of the blog page, plus being dependant on plugin data injection... The possibilities to put memcache into the equation are very limited due to the dynamic nature of that SQL query. It's not static, so it cannot be cached well, neither by mysql, nor memcache. Both only rock at static queries that have foreseeable results, whereas mysql queries might give different results even depending on the timestamp (to allow dynamic publishing, pagination, userlogin, language etc.).

Regards,
Garvin

[Don Chambers]

WP has a plugin using memcache that claims a 20 times improvement in traffic handling capability.... surely their approach isn't much difference than s9y.. is it?

Let me put this into perspective... on some of the sites that receive a lot of traffic, we can completely kill MySQL at around 10 pageviews per second, which is around 250 requests/sec. Apache never crashes - just MySQL. I'd like to believe there is some way to improve these statistics.

[FishNiX]

I would say the answer to your problem is not necessarily memcache (although it would help), it is a proper caching architecture for s9y. I would much rather see serendipity implement cache headers and a plugin which can tune them instead of just setting pragma nocache all over the place.

[garvinhicking]

Hi!

I would say the answer to your problem is not necessarily memcache (although it would help), it is a proper caching architecture for s9y. I would much rather see serendipity implement cache headers and a plugin which can tune them instead of just setting pragma nocache all over the place.

You can set $serendipity['CacheControl'] = false in serendipity_config_local.inc.php to prevent those "pragma" headers. The problem with those is that all dynamic output will then not update on a users browser, so if new comments come in, they might not show up for every user then.

Serendipity has a lot of dynamic flexibilities all over the place. It's not easy to maintain a cache for those, because plugins might do things to comments externally, and it's hard to decide which cache to flush with what. Like when flusing comment caches on arrival of a pingback could mean that it would update every minute or so, which might event result in more overhead than actual performance benefit...

The serendipity_event_simplecache plugin tries to do some global caching, but it's far from perfect. IF any actual developers might want to join helping hands with code contributions, conceptual ideas or so, that would be appreciated a lot.

Regards,
Garvin

[mdnava]

Hi there!

I had also high load issues, but I solved them using Squid as Reverse Proxy + Cache_Simple_Plugin... For this solution you need server access and a decent amount of Ram.

Let me know, I have some notes about getting it to work with s9y.

[LisaSylvia]

Hi there!

I had also high load issues, but I solved them using Squid as Reverse Proxy + Cache_Simple_Plugin... For this solution you need to read this Jes Extender review as server access and a decent amount of Ram.

Let me know, I have some notes about getting it to work with s9y.

Don where can we find this wordpress plugin? Might be worth a try.

[Don Chambers]

Don where can we find this wordpress plugin? Might be worth a try.

I cannot recall from memory LisaSylvia - but I would think you can find it via google. If not, let me know and I will find it again! Looking forward to your input!!