What not to do with MySQL

colak · 2006-08-13 08:39:10

here is an article highly linked in delicious which I thought that might be of use here too

Sencer · 2006-08-13 09:42:26

I don’t think it holds much value to regular, completely DB-Driven aplications. Lazy Loading is interesting when you only seldomly use the db – but in that case you wouldn’t create a connection object on every page request in the first place. IMHO his advice does not apply to applications that hold everything in the database. Even if you lazyloaded with WP or Textpattern or s9y or… you would still establish a connection on every pageview. And that model still scales very well and very high. It’s used by all highly db-driven applications. Be it any php based forum software (vBulletin, phpBB, punBB etc.) or publishing systems.

Caches (where needed) are currently available as plugins, because they are not always 100% transparent, and so users should decide whether they need them or not. But it’s thinkable that the functionality will eventually end up in core (as an optional feature).

zem · 2006-08-13 23:08:20

The main issue they’re trying to solve is, heavy traffic using up all available MySQL connections and bringing down a site.

When was the last time you saw a Textpattern site fall over because of a spike in traffic?

TheEric · 2006-08-14 02:26:52

zem wrote:

When was the last time you saw a Textpattern site fall over because of a spike in traffic?

I can’t quite, in recent memory recall a TXP being linked by Digg or /.

Mary · 2006-08-14 02:48:34

The Txp template competition was. SonSpring was. Erratic Wisdom was. The latter made the comment that the site went down sporadically, so he probably would have benefitted from a cache plugin.

hcgtv · 2006-08-14 05:37:39

I’d hate to get Digged or Slashdotted, I really see no benefit from it.

All of a sudden you get 10,000 visitors who are just coming to quickly scan what you have and then as soon as they came, they’re gone to go back and continue reading the next story or link because they’re in a hurry and time’s-a-wasting.

zem · 2006-08-14 07:57:52

Just tried some quick benchmarking. Depending on the complexity of the templates and the server hardware, I’m consistenly measuring between about 4.5 and 14 requests per second with minimal failures (2% or less).

That’s running stock Textpattern 4.0.3 or 4.0.4 in typical circumstances: shared hosting, plenty of plugins, custom templates with article_custom and linklist tags in the sidebar, no caching at all.

4.0 current from svn is faster (20%?) and easier on the mysqld process.

So, worst case, those figures suggest a spike of 10000 hits in an hour should be a walk in the park.

With zem_cache wrapped around the heavy duty template code (article lists in particular), I’m seeing upwards of 25 requests/s.

Last edited by zem (2006-08-14 08:01:27)

Destry · 2006-08-14 10:58:55

zem wrote:

With zem_cache wrapped around the heavy duty template code (article lists in particular), I’m seeing upwards of 25 requests/s.

zem, where is the zem_cache? Don’t see it here. I’d like to get it into the archive next to Sencer’s asy_jpcahe plugin.

hcgtv · 2006-08-14 19:21:22

zem wrote:

So, worst case, those figures suggest a spike of 10000 hits in an hour should be a walk in the park.

That’s impressive, what did you use to benchmark it, Apache Bench or a homegrown solution?

Mary · 2006-08-14 22:01:33

Apache Bench, I believe.

zem · 2006-08-15 01:11:54

Apache Bench. 100 requests, 25 concurrent, repeated several times for consistency.

I got similar results on front pages and on individual article pages (the most likely candidate for a slashdotting or similar). Expect lower speeds and more failures on very long archive lists, particularly if you’re using an unoptimized plugin.

hcgtv · 2006-08-15 05:54:25

zem wrote:

Apache Bench. 100 requests, 25 concurrent, repeated several times for consistency.

I’m going to give it a whirl on my Debian Sid server and see what I get. I’m curious if Apache2, MySQL 5.0.x and PHP 5.1.x offer any advantages over previous versions. I’m still running my VPS on the safe side so I need a compelling reason to upgrade it.

Textpattern CMS

Textpattern CMS support forum

#1 2006-08-13 08:39:10

What not to do with MySQL

#2 2006-08-13 09:42:26

Re: What not to do with MySQL

#3 2006-08-13 23:08:20

Re: What not to do with MySQL

#4 2006-08-14 02:26:52

Re: What not to do with MySQL

#5 2006-08-14 02:48:34

Re: What not to do with MySQL

#6 2006-08-14 05:37:39

Re: What not to do with MySQL

#7 2006-08-14 07:57:52

Re: What not to do with MySQL

#8 2006-08-14 10:58:55

Re: What not to do with MySQL

#9 2006-08-14 19:21:22

Re: What not to do with MySQL

#10 2006-08-14 22:01:33

Re: What not to do with MySQL

#11 2006-08-15 01:11:54

Re: What not to do with MySQL

#12 2006-08-15 05:54:25

Re: What not to do with MySQL

Board footer