Joomla 3.3.6 and Apache 2.2 100% CPU on saving big articles

[FakeMoth]

Hello, how can I diagnose what is happening? I noticed (this didn't happened before let's say 3.3.4) all of a sudden that when I am saving a large article Joomla is loading my webserver to 100%. And the save takes an eternity...

Problem is I can't pin point it in time, because I saw this only when tried "saving a large article", and I don't do that often.

Now the infos:
-what is a "large" article? anything above 3-4 Word/LibreOffice pages, 11-12 font.
-what extensions am I using? Well, a few, but pretty much on the homepage, just a a slideshow here and there. I try to keep this to a minimum and I am usually using the core stuff; so no K2 or anything like that, just the Joomla native editor and manager, in the backend.
-as I mentioned, never before I encountered this problem.
-this is happening on ALL my Joomla 3.3.6 (at least 10 of those) websites on my server, built by me.
-it is clearly related: when saving such an "large" article the top command shows 100% apache usage.
-this is a Joomla problem after the latest updates, or something is fishy on the server

The server is not a busy one (typical load 15-25% CPU and about 25% RAM, with the obvious spikes during backups or DOS attacks) and I am really keeping an eye on them: 2 x Quad Xeon, 24GB ECC RAM, IBM x3650, 2x15k SAS RAID 1 for /var and /, 4x10K SAS RAID5 for /home, CentOS 6.6 fully updated:

Code: Select all

[root@ns1 ~]# uname -a
Linux ns1.mumu.ro 2.6.32-504.1.3.el6.x86_64 #1 SMP Tue Nov 11 17:57:25 UTC 2014 x86_64 x86_64 x86_64 GNU/Linux
[root@ns1 ~]# rpm -q httpd
httpd-2.2.15-31.el6.centos.vm.x86_64
[root@ns1 ~]# rpm -q mysql
mysql-5.1.73-3.el6_5.x86_64
[root@ns1 ~]# rpm -q php
php-5.3.3-40.el6_6.x86_64
[root@ns1 ~]# lsb_release -i -r
Distributor ID: CentOS
Release:        6.6

As for the Joomla 3.3.6 websites I am using php 5.4.16 from the SCL repo.
I am also using mod_pagespeed and mod_security, but those are globally disabled for /administrator area. I stopped them for good just to test. The same. But no other problems, just this one, it flies for smaller articles or anything else.

What is happening here?

LATER EDIT: oh my, tested it right now, it is happening also on 2.5.x websites.

[itoctopus]

15% to 25% is a very high load, regardless of the number of cores that you have. We are managing a website hosted on a server with 48 cores and 32GB of RAM, and a load of 15 or more would kill the website.

Having said that - I think the cause of the load is that when a large article is being added to the website, Joomla (in its default settings) will add many entries to the smart search tables. Try disabling Smart Search and see if that solves the problem.

If that doesn't solve the problem, then check MySQL slow query log located under the /var/libraries/mysql . If it's not there then you will need to enabled in the /etc/my.cnf file (and then restart MySQL)

[FakeMoth]

Thanks for the answer. I don't follow you, how's 10-25% a big load, when this is not only the apache, but also, DNS, mail, the AV/SPAM scanning of emails and so on? This is the total load I mean. Or?

No intelligent search here, BTW. Setup the slow queries again, restarted mysqld, nothing is logged. A 5 Libre office pages article save is taking 1minute 33seconds. Now how about that?

[jisse]

Hi,

When write access is this slow, it could indicate something is wrong with the database itself. Slow query logs tells you nothing, but did you already check whether the tables themselves are accessible ok (for instance, a repair check through phpMyAdmin or so)? Another thing to check is dmesg (or whatever it is being called on your system). Perhaps the partition or disk is corrupted, which would need to show up in the Linux message log. I had this one time, when one of my hard drives was dying and I did not have SMART monitoring enabled.

[FakeMoth]

I was thinking about the HDD problems, because I have a predictive failure on one of them, and currently waiting for the replacement to arrive; but it's not in the databases array (RAID1), it's in the other one a RAID5. And I am using the hardware RAID. The defective one holds the files, mails and websites files, so on. Believe me, tried doing the same thing again and again, while watching every log available. I get nothing in them.

Yesterday started from scratch with the my.cnf. No difference. And also repaired/optimized all databases.

[FakeMoth]

Is anything in my config that could be causing that?

Code: Select all

[mysqld]
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock
user = mysql
character-set-server = utf8
default-character-set = utf8
symbolic-links = 0

back_log = 50
max_connections = 500
max_user_connections = 150
max_connect_errors = 9999999
table_cache = 5000
max_allowed_packet = 16M
binlog_cache_size = 1M
read_rnd_buffer_size = 8MB
sort_buffer_size = 4MB
read_buffer_size = 4MB
join_buffer_size = 4MB
thread_cache_size = 286
thread_concurrency = 16
query_cache_size = 512M
query_cache_limit = 8M
query_cache_min_res_unit = 2K
default_table_type = INNODB
thread_stack = 192K
transaction_isolation = REPEATABLE-READ
tmp_table_size = 384M
max_heap_table_size = 384M
key_buffer = 384M
interactive_timeout = 25
wait_timeout = 1800
connect_timeout = 10

[mysqld_safe]
log-error = /var/log/mysqld.log
pid-file = /var/run/mysqld/mysqld.pid

# *** Slow Query Log ***
log-slow-queries = /var/log/mysql/slow-queries.log
long_query_time = 2
#slow_query_log = 1
log-queries-not-using-indexes

# *** MYISAM Specific options ***
myisam_sort_buffer_size = 64M

# *** INNODB Specific options ***
innodb_additional_mem_pool_size = 80M
innodb_buffer_pool_size = 8000M
#innodb_data_file_path = ibdata1:10G:autoextend
#innodb_flush_method = O_DIRECT
innodb_file_io_threads = 4
innodb_thread_concurrency = 16
innodb_flush_log_at_trx_commit = 0
innodb_log_buffer_size = 20M
innodb_log_file_size = 800M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 120
innodb_file_per_table = 1

[isamchk]
key_buffer=64M
sort_buffer=64M
read_buffer=16M
write_buffer=16M 

[myisamchk]
key_buffer=64M
sort_buffer=64M
read_buffer=16M
write_buffer=16M

Here is the tuner's report:

Code: Select all

[OK] Currently running supported MySQL version 5.1.73
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +CSV +InnoDB +MRG_MYISAM 
[--] Data in MyISAM tables: 239M (Tables: 2935)
[--] Data in InnoDB tables: 85M (Tables: 1165)
[--] Data in MEMORY tables: 0B (Tables: 50)
[!!] Total fragmented tables: 1189

-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 12h 49m 29s (1M q [21.813 qps], 31K conn, TX: 3B, RX: 185M)
[--] Reads / Writes: 69% / 31%
[--] Total buffers: 1.3G global + 20.2M per thread (500 max threads)
[OK] Maximum possible memory usage: 11.1G (47% of installed RAM)
[OK] Slow queries: 0% (0/1M)
[OK] Highest usage of available connections: 1% (7/500)
[OK] Key buffer size / total MyISAM indexes: 384.0M/42.5M
[OK] Key buffer hit rate: 99.7% (3M cached / 10K reads)
[OK] Query cache efficiency: 74.3% (492K cached / 663K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 38K sorts)
[!!] Joins performed without indexes: 16092
[!!] Temporary tables created on disk: 52% (39K on disk / 75K total)
[OK] Thread cache hit rate: 99% (7 created / 31K connections)
[OK] Table cache hit rate: 22% (5K open / 21K opened)
[OK] Open file limit used: 6% (6K/100K)
[OK] Table locks acquired immediately: 99% (368K immediate / 368K locks)
[!!] InnoDB  buffer pool / data size: 8.0M/85.4M
[OK] InnoDB log waits: 0
-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
    Enable the slow query log to troubleshoot bad queries
    Adjust your join queries to always utilize indexes
    Temporary table size is already large - reduce result set size
    Reduce your SELECT DISTINCT queries without LIMIT clauses
Variables to adjust:
    join_buffer_size (> 4.0M, or always use indexes with joins)
    innodb_buffer_pool_size (>= 85M)

I don't understand the "Variables to adjust" area, given the above config...

[FakeMoth]

Any ideeas?

Reduced:

Code: Select all

read_rnd_buffer_size = 2MB
sort_buffer_size = 1MB
read_buffer_size = 1MB
join_buffer_size = 1MB
........................
table_open_cache=64
table_definition_cache=256

Can someone test this? Just take a random big document +10pages, copy and paste in an article and save it.

[FakeMoth]

The problem still exists - I can't believe no one encountered this...

[itoctopus]

Have you tried cleaning up your #__assets table as describe here: http://www.itoctopus.com/creating-new-a ... sets-table

Also, have you tried disabling all the smart search plugins (if you're not using smart search).

Finally, have you tried switching from InnoDB to MyISAM. Regardless on how many articles are there praising InnoDB as faster, more reliable, etc..., MyISAM is, from our experience, way faster than InnoDB. The only table in your database that needs to be InnoDB is the #__session table, and it's better if it's of type MEMORY.

Hope this helps!

PS: You should really consider reducing the "normal" load on your server - perhaps modify your Joomla core to address inherent performance issues.

[David M]

Hi itoctopus, I will like to try cleaning my assets table by using the query you provide but is not working on mysql 5.5.42-cll (syntax error).

DELETE FROM #__assets WHERE `name` LIKE '%com_content.article.%';

This is what I get: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1

Would you be so kind to update it for mysql 5.5.42-cll please?

Thank you!

[David M]

Hi, the query is correct. I was doing it wrong.

Thank you!

[Nidzo2203]

Hi itoctopus, I will like to try cleaning my assets table by using the query you provide but is not working on mysql 5.5.42-cll (syntax error).

DELETE FROM #__assets WHERE `name` LIKE '%com_content.article.%';

This is what I get: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1

Would you be so kind to update it for mysql 5.5.42-cll please?

Thank you!

Hi, the query is correct. I was doing it wrong.

Thank you!

Can you explain what were you doing wrong? I get same error.

[sovainfo]

You need to replace the prefix in the tablename. The #_ is replaced by joomla in php, not in phpMyAdmin!

[Nidzo2203]

Thanks!

[cbahiana]

Just a word of caution on cleaning the assets table:

I have a large website with different user groups, each responsible for content on their respective content areas in a portal. I use Joomla's ACL to set which user group can publish where, so one group cannot publish in another's Category. They all create and manage their articles from the public portion of the portal. To help them manage content we use the "User Article Manager".

After running the assets cleaning query all users were unable to manage their contents through UAM. We found out that simply opening and saving an article re-enabled it on UAM (because it was reinserted in the assets table).