Joomla! Discussion Forums

Hello.

Host says site consumes too many shared server resources. Says:

For the last couple of days the MySQL service was causing issues and intermittent errors to almost all shared hosting account users. Our System Administrators carefully investigated the matter and found the cause for this.

It seems that your database is generating slow queries and causes the MySQL to stall:

| 17382 | mchporta_db | localhost | mchporta_db | Query | 4798 | Locked | SELECT * FROM jos_session WHERE session_id = '7lj75k26u12j3414ftqp4ac0v1' |
| 17414 | mchporta_db | localhost | mchporta_db | Query | 4786 | Locked | SELECT * FROM jos_session WHERE session_id = '9qjgtl4oblh0vlmsgonp7gojo7' |
| 17451 | mchporta_db | localhost | mchporta_db | Query | 4773 | Locked | SELECT * FROM jos_session WHERE session_id = 'lbhhmmlui2s5n95khaja2aadl0' |
| 17469 | mchporta_db | localhost | mchporta_db | Query | 4760 | Locked | SELECT * FROM jos_session WHERE session_id = 'nbqja0e193o276cvvla2dcnc63' |
| 17560 | mchporta_db | localhost | mchporta_db | Query | 4728 | Locked | SELECT * FROM jos_session WHERE session_id = '8qpf5v8rkf4arp0bj91dr4u0u3' |
| 18101 | mchporta_db | localhost | mchporta_db | Query | 4618 | Locked | select * from `jos_session` |
| 18163 | mchporta_db | localhost | mchporta_db | Query | 4605 | Locked | select * from `jos_session` |
| 18222 | mchporta_db | localhost | mchporta_db | Query | 4592 | Locked | select * from `jos_session` |
| 18254 | mchporta_db | localhost | mchporta_db | Query | 4579 | Locked | select * from `jos_session` |
| 18336 | mchporta_db | localhost | mchporta_db | Query | 4558 | Locked | SELECT * FROM jos_session WHERE session_id = '9pcv0vjitdkoaob4v9vsa931m1' |
| 18597 | mchporta_db | localhost | mchporta_db | Query | 4469 | Locked | SELECT * FROM jos_session WHERE session_id = 'r3issljlgcm15cp1nfscvahte5' |
| 18732 | mchporta_db | localhost | mchporta_db | Query | 4410 | Locked | SELECT * FROM jos_session WHERE session_id = 'k1271nfsuhkvg1i8fabd2ronh0' |
| 18742 | mchporta_db | localhost | mchporta_db | Query | 4405 | Locked | SELECT * FROM jos_session WHERE session_id = '9da1jtkp4p5gf5j4rfkm7bm683' |
| 18759 | mchporta_db | localhost | mchporta_db | Query | 4398 | Locked | SELECT * FROM jos_session WHERE session_id = 'mq0l3316giih51s3bgaahrp5q3' |
| 18821 | mchporta_db | localhost | mchporta_db | Query | 4369 | Locked | SELECT * FROM jos_session WHERE session_id = 'j56lc8vtu2d2kiuiqukaolpvi2' |
| 18849 | mchporta_db | localhost | mchporta_db | Query | 4357 | Locked | SELECT * FROM jos_session WHERE session_id = 'e0vu3sh2c5m1apjbbg0mr575v4' |
| 18878 | mchporta_db | localhost | mchporta_db | Query | 4346 | Locked | SELECT * FROM jos_session WHERE session_id = 'men9d7gtv5kpjebljj6spe7g45' |
| 18903 | mchporta_db | localhost | mchporta_db | Query | 4334 | Locked | SELECT * FROM jos_session WHERE session_id = 'eed2o29qmv7st02n6hsubic6v7' |
| 19042 | mchporta_db | localhost | mchporta_db | Query | 4275 | Locked | SELECT * FROM jos_session WHERE session_id = '4mrc0os145uou9nfe3q8el7dd6' |
| 19065 | mchporta_db | localhost | mchporta_db | Query | 4263 | Locked | SELECT * FROM jos_session WHERE session_id = 'il2r3e3mqek9i0tg1lkgeifrn5' |
| 19101 | mchporta_db | localhost | mchporta_db | Query | 4251 | Locked | SELECT * FROM jos_session WHERE session_id = '0rtm3k6qr553pi8u8m847so3j7' |
| 19131 | mchporta_db | localhost | mchporta_db | Query | 4239 | Locked | SELECT * FROM jos_session WHERE session_id = 'ut6a5sjspmsoh82j9u5of5flf7' |
| 19294 | mchporta_db | localhost | mchporta_db | Query | 4182 | Locked | SELECT * FROM jos_session WHERE session_id = 'j755qf4mksusgndc5e9mfh4og0' |
| 19305 | mchporta_db | localhost | mchporta_db | Query | 4179 | Locked | SELECT * FROM jos_session WHERE session_id = 'p7svv27j9teua98ommsp677b32' |
| 19323 | mchporta_db | localhost | mchporta_db | Query | 4170 | Locked | SELECT * FROM jos_session WHERE session_id = '6ul0fdjipmj3arcrt5tqfqv8e5' |
| 19330 | mchporta_db | localhost | mchporta_db | Query | 4167 | Locked | SELECT * FROM jos_session WHERE session_id = 'hgei333lql0m43j9mqo15rn8c0' |
| 19342 | mchporta_db | localhost | mchporta_db | Query | 4160 | Locked | SELECT * FROM jos_session WHERE session_id = '8vis2jpc4emn5pqflt4fjvtrs2' |
| 19344 | mchporta_db | localhost | mchporta_db | Query | 4158 | Locked | SELECT * FROM jos_session WHERE session_id = '14o9qrn16itcbfa617hmcjopm1' |
| 19349 | mchporta_db | localhost | mchporta_db | Query | 4155 | Locked | SELECT * FROM jos_session WHERE session_id = 'bahjn0rl593oj4o15su4pt3bo0' |
| 19363 | mchporta_db | localhost | mchporta_db | Query | 4150 | Locked | SELECT * FROM jos_session WHERE session_id = '80aj5037mnh0a9j3osud21bkn7' |
| 19373 | mchporta_db | localhost | mchporta_db | Query | 4146 | Locked | SELECT * FROM jos_session WHERE session_id = '1ksfojcpp10fsnvodj27lfue70' |
| 19383 | mchporta_db | localhost | mchporta_db | Query | 4141 | Locked | SELECT * FROM jos_session WHERE session_id = 'etb8i5r16mimd5amtohl5bmlr5' |
| 19387 | mchporta_db | localhost | mchporta_db | Query | 4139 | Locked | SELECT * FROM jos_session WHERE session_id = '6rn3s1nsnhr9knig4s54l5hjp0' |
| 19409 | mchporta_db | localhost | mchporta_db | Query | 4130 | Locked | SELECT * FROM jos_session WHERE session_id = '23pqenjfhgdguq50hth2ujulh5' |
| 19416 | mchporta_db | localhost | mchporta_db | Query | 4127 | Locked | SELECT * FROM jos_session WHERE session_id = 'nshc8cqgjm4vonr495qjv9tfr5' |
| 19432 | mchporta_db | localhost | mchporta_db | Query | 4118 | Locked | SELECT * FROM jos_session WHERE session_id = 'g652c7b0ungtamhg1v4m7c4nt2' |
| 19435 | mchporta_db | localhost | mchporta_db | Query | 4115 | Locked | SELECT * FROM jos_session WHERE session_id = 'h28rufnvd4f8cv8fd9t4ajjs83' |
| 19452 | mchporta_db | localhost | mchporta_db | Query | 4106 | Locked | SELECT * FROM jos_session WHERE session_id = 'jocv7i0cnon63p0hoise8m18g2' |
| 19539 | mchporta_db | localhost | mchporta_db | Query | 4058 | Locked | SELECT * FROM jos_session WHERE session_id = 'njbgonrp1d4e2llsnu2gkeoq63' |
| 19568 | mchporta_db | localhost | mchporta_db | Query | 4047 | Locked | SELECT * FROM jos_session WHERE session_id = '8uvuckbvakk3fbsivfft377j70' |
| 19569 | mchporta_db | localhost | mchporta_db | Query | 4046 | Locked | SELECT * FROM jos_session WHERE session_id = '9dktdh3jeisjh6ca62b67cvct4' |
| 19600 | mchporta_db | localhost | mchporta_db | Query | 4035 | Locked | SELECT * FROM jos_session WHERE session_id = 'fpd97nn9d6e6qreov6f69n26j7' |
| 19602 | mchporta_db | localhost | mchporta_db | Query | 4034 | Locked | SELECT * FROM jos_session WHERE session_id = 'mhr96ftf52mflt0oe9sfspafk6' |
| 19874 | mchporta_db | localhost | mchporta_db | Query | 3922 | Locked | SELECT * FROM jos_session WHERE session_id = '1oits4lcked77asgu4bjt0iaq2' |
| 19891 | mchporta_db | localhost | mchporta_db | Query | 3917 | Locked | SELECT * FROM jos_session WHERE session_id = 'tf8d073pl405g0quhve362kne7' |
| 20221 | mchporta_db | localhost | mchporta_db | Query | 3810 | Locked | SELECT * FROM jos_session WHERE session_id = '1cjta26u153uoalbtvfss94kg0' |
| 20285 | mchporta_db | localhost | mchporta_db | Query | 3784 | Locked | SELECT * FROM jos_session WHERE session_id = 'o4cja8q5tsjq2q6ijas4h5c2o1' |
| 20317 | mchporta_db | localhost | mchporta_db | Query | 3771 | Locked | SELECT * FROM jos_session WHERE session_id = 'jqrs88ordv54rrroj5f5337nu7' |
| 20445 | mchporta_db | localhost | mchporta_db | Query | 3724 | Locked | SELECT * FROM jos_session WHERE session_id = 'jp0gd3j8kdsongdejd3ncjslk1' |
| 20597 | mchporta_db | localhost | mchporta_db | Query | 3671 | Locked | SELECT * FROM jos_session WHERE session_id = '7638c4eaptqtit9ba1a4pp8l73' |
| 21119 | mchporta_db | localhost | mchporta_db | Query | 3446 | Locked | SELECT * FROM jos_session WHERE session_id = 'viqg9lrnjvd52e2bf83b7aml91' |
| 21146 | mchporta_db | localhost | mchporta_db | Query | 3431 | Locked | SELECT * FROM jos_session WHERE session_id = 'nh5o736i118p91jddg287iu3j2' |
| 21203 | mchporta_db | localhost | mchporta_db | Query | 3400 | Locked | SELECT * FROM jos_session WHERE session_id = 'qi35u2cv95ke5ka860dn7shcn5' |
| 21213 | mchporta_db | localhost | mchporta_db | Query | 3394 | Locked | SELECT * FROM jos_session WHERE session_id = 'fo6difmgu13ve2o6hmj9g9bke3' |
| 21224 | mchporta_db | localhost | mchporta_db | Query | 3390 | Locked | SELECT * FROM jos_session WHERE session_id = '1b5fir3mpu8l4qrh2hqfsovu31' |
| 21239 | mchporta_db | localhost | mchporta_db | Query | 3383 | Locked | SELECT * FROM jos_session WHERE session_id = 'kt9qe6v1u62jvqc1j7mojk97k0' |
| 21356 | mchporta_db | localhost | mchporta_db | Query | 3323 | Locked | SELECT * FROM jos_session WHERE session_id = 'l91dkeqmf6oh696lh0qp95pgs0' |
| 21365 | mchporta_db | localhost | mchporta_db | Query | 3317 | Locked | SELECT * FROM jos_session WHERE session_id = 'igo514mk046qddmhua8ui5lah5' |
| 21414 | mchporta_db | localhost | mchporta_db | Query | 3294 | Locked | SELECT * FROM jos_session WHERE session_id = '4m3bokjadi6fpoe9dbtqbvueo6' |
| 21443 | mchporta_db | localhost | mchporta_db | Query | 3281 | Locked | SELECT * FROM jos_session WHERE session_id = 'pr650b5rlldnd68p907fr6f9i6' |
| 21489 | mchporta_db | localhost | mchporta_db | Query | 3257 | Locked | SELECT * FROM jos_session WHERE session_id = '8rvf8pm5mhp4bek7vikcum3eg2' |
| 21597 | mchporta_db | localhost | mchporta_db | Query | 3207 | Locked | SELECT * FROM jos_session WHERE session_id = 't6s4huotfnnmai67tpnab3qpa5' |
| 21801 | mchporta_db | localhost | mchporta_db | Query | 3134 | Locked | SELECT * FROM jos_session WHERE session_id = 'tl68robjfrugd20piitcfrhmi6' |
| 21802 | mchporta_db | localhost | mchporta_db | Query | 3133 | Locked | SELECT * FROM jos_session WHERE session_id = 've6ob1aelfla4ccibqa3682412' |
| 21849 | mchporta_db | localhost | mchporta_db | Query | 3113 | Locked | SELECT * FROM jos_session WHERE session_id = '63hcr7m4nt28pee0lsalbjm102' |
| 21865 | mchporta_db | localhost | mchporta_db | Query | 3106 | Locked | SELECT * FROM jos_session WHERE session_id = 'bqnckbp6ef8odgecdna5edet76' |
| 21950 | mchporta_db | localhost | mchporta_db | Query | 3073 | Locked | SELECT * FROM jos_session WHERE session_id = '3tvlietfm3ilfk1ah8q7httsi4' |
| 21982 | mchporta_db | localhost | mchporta_db | Query | 3062 | Locked | SELECT * FROM jos_session WHERE session_id = '5kv389spchevg0qrrkap397dv4' |
| 21987 | mchporta_db | localhost | mchporta_db | Query | 3061 | Locked | SELECT * FROM jos_session WHERE session_id = 'tm3slfpuftssjlbeeor3535s81' |
| 21997 | mchporta_db | localhost | mchporta_db | Query | 3055 | Locked | SELECT * FROM jos_session WHERE session_id = 'tiag6el4r5jst07m5npaprc3s6' |
| 22008 | mchporta_db | localhost | mchporta_db | Query | 3052 | Locked | SELECT * FROM jos_session WHERE session_id = 'khnc5i9s63nr9fc6flue06b465' |
| 22018 | mchporta_db | localhost | mchporta_db | Query | 3049 | Locked | SELECT * FROM jos_session WHERE session_id = 'ksqvf7tbtg9fice4s3ofp47rt3' |
| 22019 | mchporta_db | localhost | mchporta_db | Query | 3049 | Locked | SELECT * FROM jos_session WHERE session_id = 'gmf7vbm06gran3i6mphmj53lg6' |
| 22057 | mchporta_db | localhost | mchporta_db | Query | 3037 | Locked | SELECT * FROM jos_session WHERE session_id = '67d0racpnkpn5lk47ll8v2cu72' |
| 22227 | mchporta_db | localhost | mchporta_db | Query | 2976 | Locked | SELECT * FROM jos_session WHERE session_id = 'arlgda563fiea9drvo1i2laft0' |
| 22260 | mchporta_db | localhost | mchporta_db | Query | 2965 | Locked | SELECT * FROM jos_session WHERE session_id = 'a3e9cikfk8ckfrh3vv3rtoj950' |
| 22272 | mchporta_db | localhost | mchporta_db | Query | 2960 | Locked | SELECT * FROM jos_session WHERE session_id = '4bjmg056g1undittaeebfvi7f1' |
| 22295 | mchporta_db | localhost | mchporta_db | Query | 2953 | Locked | SELECT * FROM jos_session WHERE session_id = 'opnoqv9ocv9mh4q381ju3ohhi1' |
| 22331 | mchporta_db | localhost | mchporta_db | Query | 2941 | Locked | SELECT * FROM jos_session WHERE session_id = 'um5ohhs8tv3oc3qdddv6qaam27' |
| 22372 | mchporta_db | localhost | mchporta_db | Query | 2923 | Locked | SELECT * FROM jos_session WHERE session_id = 'nurjhefksgdp5s0ol74e4kk0g5' |
| 22445 | mchporta_db | localhost | mchporta_db | Query | 2886 | Locked | SELECT * FROM jos_session WHERE session_id = 'l0em56dbgfd453475a2ep6hs72' |
| 22457 | mchporta_db | localhost | mchporta_db | Query | 2882 | Locked | SELECT * FROM jos_session WHERE session_id = 'isqe8u2g9kvlbhpf47ho6fqnh3' |
| 22688 | mchporta_db | localhost | mchporta_db | Query | 2792 | Locked | SELECT * FROM jos_session WHERE session_id = 'dhihubldicl4f5to9k2td50da6' |
| 23060 | mchporta_db | localhost | mchporta_db | Query | 2705 | Locked | SELECT * FROM jos_session WHERE session_id = 'kms5fm8noe3pcg7qlaog8qlnd7' |
| 23093 | mchporta_db | localhost | mchporta_db | Query | 2699 | Locked | select * from `jos_session` |
| 23129 | mchporta_db | localhost | mchporta_db | Query | 2686 | Locked | select * from `jos_session` |
| 23171 | mchporta_db | localhost | mchporta_db | Query | 2674 | Locked | select * from `jos_session` |
| 23200 | mchporta_db | localhost | mchporta_db | Query | 2661 | Locked | select * from `jos_session` |
| 23254 | mchporta_db | localhost | mchporta_db | Query | 2643 | Locked | SELECT * FROM jos_session WHERE session_id = 'njrof1nvt1dl8javo906qi66k0' |
| 23323 | mchporta_db | localhost | mchporta_db | Query | 2632 | Locked | SELECT * FROM jos_session WHERE session_id = 'ulaa7u4aq29sr6pq1a15j2se34' |
| 23372 | mchporta_db | localhost | mchporta_db | Query | 2620 | Locked | SELECT * FROM jos_session WHERE session_id = '7ojjb1et440thtligu8tvk55a1' |
| 23416 | mchporta_db | localhost | mchporta_db | Query | 2608 | Locked | SELECT * FROM jos_session WHERE session_id = '559adc1ei7utrpmbcgm2kaaqn7' |
| 23581 | mchporta_db | localhost | mchporta_db | Query | 2548 | Locked | SELECT * FROM jos_session WHERE session_id = 'o8l10qv7msuff8ta8ml4k5cau6' |
| 23589 | mchporta_db | localhost | mchporta_db | Query | 2543 | Locked | SELECT * FROM jos_session WHERE session_id = 'hseiofhg8agfmn5r2ap3tub8s5' |
| 23608 | mchporta_db | localhost | mchporta_db | Query | 2536 | Locked | SELECT * FROM jos_session WHERE session_id = '1ib0dkfee1b5asohlrotmfi2v0' |
| 23639 | mchporta_db | localhost | mchporta_db | Query | 2524 | Locked | SELECT * FROM jos_session WHERE session_id = 'n7fp62jbm7673pqs3l6tghcoj3' |
| 23677 | mchporta_db | localhost | mchporta_db | Query | 2512 | Locked | SELECT * FROM jos_session WHERE session_id = 'h5qgbmiav06tv324sglifeh066' |
| 23869 | mchporta_db | localhost | mchporta_db | Query | 2464 | Locked | SELECT * FROM jos_session WHERE session_id = '5rnavthea75vttumjfd92t2u52' |
| 23908 | mchporta_db | localhost | mchporta_db | Query | 2453 | Locked | SELECT * FROM jos_session WHERE session_id = '7hc44kh585i6bbn9hl7n8evam1' |
| 23937 | mchporta_db | localhost | mchporta_db | Query | 2441 | Locked | SELECT * FROM jos_session WHERE session_id = 'uh77p4g3602d3q8c9q73q5q2o7' |


Slow queries were found:
Executed 794 min ago for 30 sec on Database --> mchporta_db.
select * from `jos_content
Executed 765 min ago for 2 sec on Database --> information_schema.
select * from `TABLE_STATISTICS
Executed 757 min ago for 22 sec on Database --> mchporta_db.
select * from `jos_content
Executed 706 min ago for 2 sec on Database --> information_schema.
select * from `TABLE_STATISTICS
Executed 701 min ago for 22 sec on Database --> mchporta_db.
select * from `jos_content
Executed 615 min ago for 23 sec on Database --> mchporta_db.
select * from `jos_content
Executed 585 min ago for 2 sec on Database --> information_schema.
select * from `TABLE_STATISTICS
Executed 584 min ago for 22 sec on Database --> mchporta_db.
select * from `jos_content
Executed 523 min ago for 21 sec on Database --> mchporta_db.
select * from `jos_content
Executed 406 min ago for 22 sec on Database --> mchporta_db.
select * from `jos_content
Executed 396 min ago for 29 sec on Database --> mchporta_db.
select * from `jos_content
Executed 345 min ago for 2 sec on Database --> information_schema.
select * from `TABLE_STATISTICS
Executed 286 min ago for 21 sec on Database --> mchporta_db.
select * from `jos_content
Executed 286 min ago for 3 sec on Database --> information_schema.
select * from `COLUMNS
Executed 263 min ago for 23 sec on Database --> mchporta_db.
select * from `jos_content
Executed 221 min ago for 22 sec on Database --> mchporta_db.
select * from `jos_content
Executed 105 min ago for 19 sec on Database --> mchporta_db.
select * from `jos_content
Executed 46 min ago for 19 sec on Database --> mchporta_db.
select * from `jos_content
Executed 24 min ago for 8023 sec on Database --> mchporta_db.
DELETE FROM jos_session WHERE ( time < '1254446308

I'm not sure what is causing it. Any suggestions on how to fix this?

Thanks in advance

Seems you have a very busy site, perhaps not optimized, or receiving large amounts of traffic. All those locked queries can't be good either.

Have you considered hiring someone to take a look at your site? Is your version of Joomla up to date?

Thanks for the reply Brad.

Site running 1.5.14
2,000 visitors average daily
No problems with this site for 2 years up until last month. (did not add or remove any mods/plugins, did no updates to the site)

DB has been Optimized, Mods/Plugins Optimized.

Not sure why all these lockups are happening as well.

I would be glad to hire somebody to take a look at the site. Let me know if you are interested or if you have somebody in mind.

Hi xlzoom,
You are not alone!

We have also seen un-usual resource issues over the last week or so - so much that our server takes a deep dive every 4 hour.
We have done the same as you optimized DB, un-installed many modules and plugins. Optimized the site (CSS, PHP, images) but not using any server related optimization like Gzip.

This is how our log looks like - only thing I can see we have in common is:
SELECT * FROM jos_session WHERE session_id =

The rest of our problems looks like JoomGallery and one Sobi issue.
(Server is located at JaguarPC) it a VPS