MySQL usage?

[smack]

Hey folks -

Got a complaint from a sysadmin about the MySQL processes. Our client is on a shared server, and the SysAdmin was seeing another user experiencing slowdowns. He capped everyone to 8 processes, and that killed CMS. Now he's raised it to 25 per user, and everything seems fine.

Just curious - can anyone there give me any benchmarks about CMS MySQL process usage, and are there any ways of capping/tuning performance?

I'm doing my due diligence. It's running just fine on my servers, but we don't impose such stringent limitations. Any info I could pass along would be appreciated.

thanks -

s.

p.s. the site is www.craigslistfoundation.org - cool!

[smack]

Thanks Patricia -

I saw that thread - but a couple of sleeping connections *shouldn't* be such a problem. The usage was going a lot higher than that. And I worry that shutting down those two persistent connections might slow CMS down.

I guess the question then becomes, "Can CMS open a large number of persistent connections (that might make a SysAdmin nervous)?" And do they then hang around for a long time once they've been established?

FWIW, it seems to be behaving fine with a cap of 25.

s.

[Ted]

Honestly, I think this is a PHP thing.  CMSMS SHOULD use 2 connections on each page load.  One for index.php, and one for stylesheet.php.  I guess we should figure out a way to benchmark it.

However, I suppose it's a good idea to have persistent connections off by default.  Though people with a high load should have it on or it'll slow down the system.

[smack]

Hey Wishy -

First: I love, love, love CMS. I chose to migrate to CMS because of the feature set and the general impression that the dev brains behind it were top notch. I've since had two very experienced programmers take a look and both have been extremely impressed. Congratulations.

Anyhow, could you expand a bit on what you mean by "a PHP thing?" And are there any PHP tunings that would help? We've found that the PHP memory limit has to be bumped up if you're using a google search, but again this hasn't seemed excessive - 20MB seems fine, and I'm thinking of bumping it up to 32MB to see if that offers any performance enhancements. Thoughts?

Also, the SysAdmin in question was concerned that usage was exceeding 100 MySQL connections. If I understand correctly, under heavy loads, CMS MySQL process usage could ramp up significantly. Are all these connections then persistent? Or would CMS drop them as load dropped, and scale back to the original 2 persistent processes?

Sorry for the barrage of questions. I just want to understand as much as possible so I have ammunition for nosy SysAdmins. ;^)

Thanks -

s.

[Ted]

Thanks for the kind words.

On my test install (php 5.0.5), I 5 persistent connections to the database.  No matter how many times I refresh, I still have 5.  This is because ADODB closes the connection, but since it's persistent, php actually leaves it open and just waits for another connection from adodb.

So, I did a little more digging.  It looks like ADODB doesn't close the connections properly on PHP4.

Do a little test.  At the very bottom of index.php, put:

Code: Select all

if (isset($gCms->db))
{
    $db =& $gCms->db;
    $db->Close();
}

See if the amount of connections used goes down.  It should.  The other option is to set the mysql.max_persistent option to 30 or 40 instead of the default Unlimited.  But that could cause page timeouts if adodb isn't dropping the connections right.

Anyway, with proper connection handling, CMS should scale pretty well for the traffic that it gets.  5-10 connections should be more than enough for "regular" traffic.  Anything more than that definitely is a problem.

What did you mean by having to up the memory limit with the google search?  That should just make a link to google.

Thanks!

[smack]

Aha!

So it's an ADODB / PHP 4.x thing. That makes total sense, as the CraigslistFoundation site is on a 4.x environment. I'll see if they can provide me with a PHPMyAdmin interface to test this.

Is the code snippet you mention something I should leave in there to optimize for this environment? I can also ask the SysAdmin if he wants to play around with mysql.max_persistent - but I'd rather work on getting ADODB to behave better (or encourage them to upgrade to PHP 5.x!).

As for the Google issue, we're integrating the Google search directly into the site so that results are displayed using our templates, using NuSOAP. To do that PHP has to load a boatload of files (sorry I don't really know the details) and we were getting an "out of memory" condition, so we had to bump up the PHP memory limit.

Thanks -

s.

[Ted]

Oh, ok.  I was just making sure that CMSMS itself wasn't requiring you to raise the memory.  Cool.

To be honest, I'm going to throw that code at the bottom of index.php and stylesheet.php myself (unless I can find a better place) for the next release, as I really don't know how else to handle it.  It just never occurred to me that adodb wasn't handling it right....

[Ted]

Also, your site has a big phpinfo in index.php somewhere.  Might want to fix that. 

[smack]

Holy crap! thanks.

I put it in version.php just to debug - didn't realize that was included! Thanks.

I'll put the code in index.php.

s.

[smack]

Thanks!

Yes, we pride ourselves on our designs. Unfortunately, there are a couple of pages on this site where they've 'broken' the templates - we have to continually tell them not to copy and paste from Word, that sort of thing.

Now that we've discovered oneline=true we can make our templates a little more granular and more, ahem, idiot-proof. Here's hoping. 

Thanks for the validation tip. It's all good now!

s.

[jelle]

uhm, what about users of your site that have a non-default fontsize? Your menu spills over but there is plenty of real estate on either side of the page.
I guess that the missing stylesheets and smarty errors are related to the problem you posted?

[smack]

Jelle -

Can you provide more detail on this error? How can I reproduce so that I can fix? I suppose we need to add some "!important" tags for the menu declarations. Thanks for the tip.

s.

[jelle]

i'll try to show you, if the forum allows my snapshot png. Basically you have made the menu tabs fit so tight that when the page is to be displayed with a bigger font (min fontsize 11 or 12, not that big) the tabs flow to the next line.
The snapshots show either konq or firefox with a minimum fontsize of 11 and 12. (11 does not trigger this 'error' but is too smal for my taste).

(and after the anger subsides from using the gimp: I've cropped them as they were too big)


[attachment deleted by admin]

[smack]

Thanks Jelle -

I'll see what we can do. It's the age old problem of design vs. scalability.

Apparently we played around with !important tags, but since user CSS takes precedence, there's not much we can do, save for trying to make the whole design scale. That's tough. 

[jelle]

in this case it's design vs readability. Unless your site has much artistic value, i'd say that the design should not get in the way. It should help not hinder. But in this case it's just a small annoyance.
Did you fix the css menu statically in the menu or would a new toplevel also break things?

btw what is an !important tag?