Thread

Now the slim pages are loading very quickly for me.

The site’s morning traffic is slowing down and things are returning to normal. CPU is much lower and page load time is a ton faster.

————————

top - 11:22:19 up 1 day,  3:13,  1 user,  load average: 4.10, 4.70, 4.96
Tasks:  25 total,  1 running,  24 sleeping,  0 stopped,  0 zombie
Cpu0 : 28.7% us, 16.6% sy,  0.0% ni, 54.7% id,  0.0% wa,  0.0% hi,  0.0% si
Cpu1 : 33.4% us, 19.3% sy,  0.0% ni, 47.3% id,  0.0% wa,  0.0% hi,  0.0% si
Cpu2 : 35.6% us, 13.1% sy,  0.0% ni, 51.3% id,  0.0% wa,  0.0% hi,  0.0% si
Cpu3 : 47.7% us, 10.3% sy,  0.0% ni, 42.0% id,  0.0% wa,  0.0% hi,  0.0% si
Mem:  9338156k total,  598972k used,  8739184k free,      0k buffers
Swap:      0k total,      0k used,      0k free,      0k cached

  PID USER     PR NI VIRT RES SHR S %CPU %MEM   TIME+  COMMAND
3320 mysql   15   0 527m 90m 4936 S 114 1.0 814:41.22 mysqld
28475 nobody   16   0 29204 10m 3416 S   31 0.1   1:45.52 php-cgi
28606 nobody   15   0 29152 10m 2828 S   30 0.1   0:23.45 php-cgi
28509 nobody   16   0 30736 11m 3420 S   15 0.1   1:18.71 php-cgi
28554 nobody   16   0 31524 12m 2812 S   10 0.1   0:50.86 php-cgi
28605 nobody   16   0 28164 9.8m 3396 S   2 0.1   0:28.43 php-cgi

to further add, I tried this in Firefox where I hadn’t visited those links.

Hello World: ~10 seconds
anna.php: ~10 seconds
root / main site: ~25 seconds

The website gets 40% of its traffic in a few hour window and that window is where we are having problems. Usually Monday-Wednesday mornings from 8-11 central time. Any time after or before, everything is okay on the site.

Well, we can tell a few things.  A plain template (Hello World) loads very fast with lower loads.  Your template does still load on the slow side (10ish seconds), part of which may at times be graphical rendering and the like.

So some of it could be some optimization in the templates, but it really does appear to be somewhat of a server related issue.  I’d be very curious to see you load this on that VPS, and compare it to a totally clean installation of ExpressionEngine there, and see what we find out.

When you say… “curious to see you load this on that VPS”... What is the “this”? The complete website? The blank templates?

If it were me; I’d be loading a copy of your current site, as well as a fully clean install, into a development environment where no one would know the the troubleshooting that was going on.  That would give you a nice clean comparison as well.

Since we have been focused on MySQL as the problem on the dedicated server, I did a test this morning…

I moved the database to one of the VPSs that we have set up. So nginx and PHP were on the dedicated server and MySQL was on VPS.

During my test, the CPU for MYSQL on the VPS was about 40% with normal spikes.

During my test, the CPU on the dedicated server came down to very normal numbers.

During my test, the website still failed to load pages within any reasonable time.

So I’m concluding that there is a problem at the server level that neither I nor the host has been able to track down.

As a final test before I move to another host, I’m requesting that this host set up another vanilla dedicated server running apache, php, mysql. I’ll move everything to that server hopefully this afternoon and will see what happens tomorrow am.

Anna

You’re doing a lot of testing with this, Anna. I know it must be frustrating for you, but it is definitely the way to go.  Please do keep us in the loop!

Well we are on a vanilla dedicated server now running Apache. Same specs… 8 GB Fully Buffered DDR-2 RAM, Quad Core Woodcrest Xeon 2.33 Ghz.

One thing I continue to see is lots of temp tables in MySQL being written to disk? Here are the stats from my tuning script just since I’ve moved the website tonight:

Of 5625 temp tables, 33% were created on disk

Is this truly a problem as the host has indicated and could it be the source of the CPU overload?

When the host identified this as a potential problem, I spend several hours converting all the TEXT type columns in the exp_weblog_data table to VARCHAR. But this temp table number is still very high. Any thoughts on this?

Also, is there any documentation to assist with server tuning for a larger traffic website. If not, is this something that can be developed? I know Engine Hosting has it down but they don’t always meet a project’s needs. Best practices for configuring a server for an EE website would be a huge help and much appreciated. Without it non-sysadmin folks are blowing in the wind.

Anna

I think for the server end of things, you’ll really want to have a server pro go in and tweak things for a dynamic environment.  It’s an art and I don’t believe there is a ‘one size fits all’ solution.  Nevin did give me some general advice on a VPS setup here, and you may find this particularly useful, but for this site, it would likely be worth hiring an outside pro to come in and go over the setup.  There isn’t anything that makes EE unique with regard to server requirements, but you do need a setup designed for dynamic sites.

On the EE end of things, a good way to find possible slowdown spots is to turn ‘Template debugging’ on in ‘Admin- System Prefs- Output and Debug’- see if there are any spots that seem to be bogging down.  It may simply be the server being overwhelmed.  But it could give us some hints as well.

ETA- you might also want to take a look at the mysql docs on memory use, particularly max_heap_table_size and tmp_table_size.

Hey Robin,

Do you know why the queries are creating tmp tables?

The queries that is doing it the most, if not all the time, begins with:

SELECT DISTINCT(t.entry_id) FROM exp_weblog_titles AS t

From what I understand, when a query has to create a tmp table, that tmp table has to be written to disk and each writing of the tmp table needs to complete before the next one will start.

Is that your understanding?

Is that what the overload is?

Is this normal EE behavior?

Anna

MySQL performs its operations in memory, and only resorts to disk-based temp tables when an operation exceeds the limitations set in the MySQL environment.  My guess is that the two values Robin mentioned are quite low on this database server, but in all honesty, that’s a guess.  A MySQL expert (note on the page Robin links to that such advisement is in the Enterprise level MySQL support class) would need to monitor and assess this site’s database usage in order to make the proper recommendations on what changes to make.  A really cheap (read: free) thing you can try first is to find out what those two values are, and if they are low, increase them.  If that fails to help, you’ll probably need to get a consultant.

Hi Derek,

We are using a MySQL tuning script to tweak the my.cnf file. Our settings for these variables are high and could probably do higher. I will test increasing these number during the morning traffic tomorrow.

tmp_table_size = 256M
max_heap_table_size = 256M

In this turning script, it notes that BLOB and TEXT columns are not allow in memory tables and are instead written to disk. Is that your understanding of these column types?

——

Also, is it possible that having query caching on could cause such an overload problem? I turned it off this morning in EE and instantly the website was a lot faster. I don’t know though if that was the solution or just a coincidence.

Anna

I honestly don’t recall at the moment if those field types aren’t allowed in memory tables, though it doesn’t ring a bell of such a restriction on text fields.  BLOB, yes, TEXT no.  The former is mentioned specifically in MySQL’s memory documentation, the latter is not, and I do not know the internals well enough to say on which version if any that the restriction might also apply to the non-binary TEXT types.

If your caches are constantly being overwritten, then yes, the disk i/o could be outpacing the savings that caching would bring.  On a proper MySQL server, it will have its own caching and would be far better than a file based PHP mechanism.  EngineHosting, for example, always recommends turning database caching off in ExpressionEngine.

Some general comments from reading through this thread, though.  No, 260MB is not large.  We have users who easily surpass that, four to tenfold.  And if I’m reading your description correctly, you have MySQL running on the same machine as your file server.  That’s a big no-no, as they’re going to be fighting each other for resources, and tying up the storage media.

Yes, MYSQL and Apache are on the same server. But again, this is a big dedicated server and our traffic numbers don’t seem that enormous to be having these kinds of problems… About 20K unique visitors and 80K page loads a day.

I’m seeing on this website confirmation that using TEXT and BLOB column types forces “table creation on the disk because MEMORY storage engine does not support them”. Perhaps all this disk writing of tmp tables combined with the caching is the source of all our problems?