Время выполнения SQL-запросов в debug информации и место их вызова
расширенная отладка SQL запросов в Joomla
рейтинг: 8.8/10, голосов: 11
Часто требуется узнать какие же SQL-запросы и как долго выполняются у нас на сайте, что за расширения дают нагрузку на базу данных, какие из них нужно удалить или оптимизировать.
Стандартная Joomla-отладка выдаёт только список выполненных SQL-запросов:
- SELECT *, RAND() AS ordering
FROM jos_banner
WHERE showBanner = 1
AND (imptotal = 0 OR impmade < imptotal)
AND cid = 1
AND catid = 33
ORDER BY sticky DESC, ordering
LIMIT 0, 1
- UPDATE jos_banner
SET impmade = impmade + 1
WHERE bid = 7
Эту ситуацию можно исправить, но придется использовать хак части ядра Joomla.
Добавим в debug информацию о времени выполнения каждого SQL-запроса и место вызова этого запроса. Для этого необходимо отредактировать файл
<корень сайта>/libraries/joomla/database/database/mysql.php : 206
либо для базы данных MySQLi
<корень сайта>/libraries/joomla/database/database/mysqli.php : 222
В оригинале функция выглядит примерно следующим образом:
/**
* Execute the query
*
* @access public
* @return mixed A database resource if successful, FALSE if not.
*/
function query()
{
if (!is_resource($this->_resource)) {
return false;
}
// Take a local copy so that we don't modify the original query and cause issues later
$sql = $this->_sql;
if ($this->_limit > 0 || $this->_offset > 0) {
$sql .= ' LIMIT ' . max($this->_offset, 0) . ', ' . max($this->_limit, 0);
}
if ($this->_debug) {
$this->_ticker++;
$this->_log[] = $sql;
}
$this->_errorNum = 0;
$this->_errorMsg = '';
$this->_cursor = mysql_query( $sql, $this->_resource );
if (!$this->_cursor)
{
$this->_errorNum = mysql_error( $this->_resource );
$this->_errorMsg = mysql_error( $this->_resource )." SQL=$sql";
if ($this->_debug) {
JError::raiseError(500, 'JDatabaseMySQL::query: '.$this->_errorNum.' - '.$this->_errorMsg );
}
return false;
}
return $this->_cursor;
}
Используя функцию debug_backtrace и стандартную функцию microtime() добавим необходимую нам информацию в
<?php
function query() {
if (!is_resource($this->_resource)) {
return false;
}
// Take a local copy so that we don't modify the original query and cause issues later
$sql = $this->_sql;
if ($this->_limit > 0 || $this->_offset > 0) {
$sql .= ' LIMIT '.$this->_offset.', '.$this->_limit;
}
$this->_errorNum = 0;
$this->_errorMsg = '';
if ($this->_debug) {
$sql_start = round(microtime(), 6);
};
$this->_cursor = mysql_query( $sql, $this->_resource );
if ($this->_debug) {
$sql_end = round(microtime(), 6);
$this->_ticker++;
$backtrace = debug_backtrace();
$bcktrc_add = '';
if (is_array($backtrace)) {
for ($i = 1; $i < 3; $i++) {
if (isset($backtrace[$i]) and is_array($backtrace[$i])) {
if (isset($backtrace[$i]['file']) and isset($backtrace[$i]['line'])) {
$bcktrc_add .= str_replace(JPATH_BASE, '', $backtrace[$i]['file']).':'.$backtrace[$i]['line']."\n";
}
}
}
}
$this->_log[] = $bcktrc_add.'Execute time:'.round(($sql_end - $sql_start), 6)." sec.\n".$sql;
}
if (!$this->_cursor) {
$this->_errorNum = mysql_error( $this->_resource );
$this->_errorMsg = mysql_error( $this->_resource )." SQL=$sql";
if ($this->_debug) {
JError::raiseError(500, 'JDatabaseMySQL::query: '.$this->_errorNum.' - '.$this->_errorMsg );
}
return false;
}
return $this->_cursor;
}
После данных изменений мы уже получим нужную нам информацию:
- /components/com_banners/models/banner.php:79
/modules/mod_banners/helper.php:39
Execute time:0.000818 sec.
SELECT *, RAND() AS ordering
FROM jos_banner
WHERE showBanner = 1
AND (imptotal = 0 OR impmade < imptotal)
AND cid = 1
AND catid = 33
ORDER BY sticky DESC, ordering
LIMIT 0, 1
- /modules/mod_banners/helper.php:40
/modules/mod_banners/mod_banners.php:23
Execute time:0.000117 sec.
UPDATE jos_banner
SET impmade = impmade + 1
WHERE bid = 7
Замечание: Иногда может потребоваться увеличить глубину вывода точек вызова функций (например Community Builder создаёт свой класс для работы с базой данных, поэтому потребуется заглянуть поглубже), для этого в нашей новой функции в строчке
for ($i = 1; $i < 3; $i++) {
цифру "3" увеличиваем например до "6", тогда будет выведено "5" ("6"-1) точек вызова.
А как это же сделать, но для Joomla 2.5? Я не найду ни файлов /libraries/joomla/database/database/mysql.php ни функции query()...
Компания ООО «ЭкоЮгСтройКлимат» stroyklimat.ru34.com - продажа, монтаж, ремонт и обслуживание холодильного, климатического, вентиляционного и котельного оборудования в Волгограде, Волгоградской области и регионах РФ.
Профессиональное оборудование – очень важный элемент в системе функционирования предприятий общественного питания, торговли, развлекательных центров. Каждая более-менее крупная организация имеет свое штатное профессиональное оборудование. Но если ремонт профессионального оборудования требуется крупному предприятию, для которого от качественной его работы зависит вообще возможность работать, скорость выполнения ремонтных работ становится крайне важной.
Дейтяльность нашей компании:
*продажа торгового холодильного оборудования stroyklimat.ru34.com/kypit/kypit-holodilnoe-oborudovanie
*обслуживание, ремонт, монтаж торгового холодильного оборудования stroyklimat.ru34.com/montazh/montazh-holodilnogo-oborudovaniya
*продажа сплит-систем и промышленных систем кондиционирования воздуха.
*продажа, обслуживание, ремонт, монтаж систем вентиляции stroyklimat.ru34.com/services/montaj_sistem_ventilyatcii
*проектирование, поставка, монтаж, обслуживание, ремонт котельных и котельного оборудования stroyklimat.ru34.com/services/kotelnie/montaj_kotelnogo_oborudovaniya
*проектирование и монтаж металлоконструкций.
ООО «ЭкоЮгСтройКлимат» готово выполнить любой сложности монтаж и ремонт профессионального оборудования в Волгограде, Волгоградской Области и регионах РФ. Главное, что отличает нашу компанию на рынке – это оперативность выполнения работ. Многолетний накопленный опыт (мы работаем с 2010 года) и собранный штат профессиональных мастеров – профессионалов своего дела, позволяет нам проводить обслуживание профессионального оборудования в кратчайшие сроки. С нами у вас не будет задержек в производстве, а договор на постоянное сотрудничество, заключаемый с нашими заказчиками, стабилизирует отношения и формирует доверие.
Более подробную информацию вы можете найти по ссылкам на нашем сайте stroyklimat.ru34.com .
Ищете где приобрести мебель в г.Волгограде?
Интернет магазин "Где Мебель" в г.Волгограде gde-mebel.com/ всегда готов предложить огромный ассортимент мебели от ведущих мебельных производств России и СНГ.
Наш интернет магазин готов помочь вам выбрать мебель, которая бы отлично радовала глаз, вписывалась в Ваш интерьер и при этом не тяготила кошелёк.
Интернет каталог мебели нашего интернет-магазина в г.Волгограде gde-mebel.com/catalog/ огромен. Мы предлагаем много мебели, предоставляя вам большой асортимент мебели на любой вкус.
Интернет-магазин "Где Мебель" в г.Волгограде - мы всегда вам рады!
На прошлой неделе Я выиграл много денег в этом уважаемом
онлайн-клубе
Hello again!
I have gotten an email saying that my original message was not delivered so here goes again. Please confirm receipt.
As mentioned in my original message, I would like to contribute an article to your blog on how cryptocurrencies are reshaping the adult and more particularly the escort industry. I had a friend who ran an escort agency in the UK and he always had problems with the authorities, banks and even payment processors. I genuinely feel that cryptocurrencies have the potential to liberalise the escort industry and replace the anachronistic banking model which no longer works in today's modern society.
You can find the article on my G-Drive:
https://drive.google.com/drive/folders/13pbNYoRoWLMHnrU1ySnxF6Zq7JMXsSI3?usp=sharing
Whilst writing this article, I contacted a couple of websites to get some references and one website has even provided me with some images and have given their permission for you to use them. I have included a short bio about myself. I hope your audience enjoys reading my article.
I can potentially write a couple more articles as and when I get some free time. It is crazy busy at work after all the festivities!
Have a fab day!
Regards
Tony
Hi, I think your web site might be having browser compatibility problems.
When I take a look at your web site in Safari, it looks fine but when opening in IE, it's
got some overlapping issues. I merely wanted to provide
you with a quick heads up! Apart from that, fantastic website!
Howdy would you mind sharing which blog platform
you're using? I'm planning to start my own blog soon but I'm having a hard time choosing between BlogEngine/Wordpress/B2evolution and
Drupal. The reason I ask is because your design and
style seems different then most blogs and I'm looking for something completely unique.
P.S Apologies for getting off-topic but I had to ask!
You really make it seem so easy with your presentation but I find this matter to
be really something which I think I would never understand.
It seems too complex and extremely broad for me. I'm looking forward for your next post,
I'll try to get the hang of it!
I do not even know how I ended up right here, however I thought this
put up used to be great. I do not recognize who
you're but certainly you're going to a well-known blogger should you are not already.
Cheers!
I constantly emailed this webpage post page to all my friends, as
if like to read it after that my links will too.
Excellent web site you've got here.. It's hard to find high-quality writing like yours these days.
I honestly appreciate people like you! Take care!!
Hi would you mind stating which blog platform you're using?
I'm looking to start my own blog in the near future but I'm having a tough time deciding between BlogEngine/Wordpress/B2evolution and
Drupal. The reason I ask is because your layout seems different then most blogs
and I'm looking for something completely unique.
P.S Sorry for getting off-topic but I had to ask!
Its like you read my mind! You appear to know a lot about this, like you wrote the book in it or something.
I think that you can do with a few pics to drive the message home a bit, but other than that,
this is magnificent blog. An excellent read.
I'll definitely be back.
Hey, I think your site might be having browser compatibility issues.
When I look at your website in Opera, it looks fine but when opening in Internet Explorer,
it has some overlapping. I just wanted to give you a quick heads up!
Other then that, fantastic blog!
Thanks very interesting blog!
Amazing! This blog looks just like my old one! It's
on a completely different subject but it has pretty much
the same page layout and design. Excellent choice of colors!
I constantly emailed this website post page to all my associates,
as if like to read it afterward my links will too.
I always emailed this webpage post page to all my associates, for the reason that if
like to read it afterward my friends will too.
Hello, its nice post about media print, we all know media is a
fantastic source of facts.
Its like you read my mind! You appear to know a lot about this, like you wrote
the book in it or something. I think that you could do with some pics to drive the message home a little bit, but instead of that, this is excellent blog.
An excellent read. I'll certainly be back.
Hi to every single one, it's in fact a good for me to visit this
web site, it contains helpful Information.
If you would like to improve your knowledge just
keep visiting this web page and be updated with the hottest gossip posted here.
Your style is very unique compared to other folks
I've read stuff from. Thanks for posting when you've got the opportunity,
Guess I'll just bookmark this blog.
Its not my first time to pay a visit this web page, i
am browsing this web page dailly and get nice information from here daily.
certainly like your website but you need to test the spelling on several of your posts.
Many of them are rife with spelling problems and I to find
it very troublesome to tell the reality however I
will surely come again again.
Good post. I learn something new and challenging on websites I stumbleupon on a daily
basis. It's always useful to read content from other authors and practice something
from other sites.
I'm not sure exactly why but this weblog is loading incredibly slow for me.
Is anyone else having this issue or is it a
problem on my end? I'll check back later and see
if the problem still exists.
I absolutely love your site.. Pleasant colors & theme. Did you make this web site yourself?
Please reply back as I'm trying to create my own website and would love to know where you got this from or what the theme is called.
Appreciate it!
We are a group of volunteers and opening a new scheme
in our community. Your website offered us with valuable info to work
on. You've done a formidable task and our whole group might be grateful to you.
Right away I am going to do my breakfast, when having my breakfast coming over again to read
further news.
Oh my goodness! Amazing article dude! Thank you, However I am encountering troubles with your RSS.
I don't understand why I can't subscribe to it.
Is there anybody else having the same RSS issues? Anyone who knows the answer can you kindly respond?
Thanx!!
Link exchange is nothing else except it is simply placing the other person's website link on your page at suitable place and other person will also
do similar in support of you.
As the admin of this site is working, no doubt
very soon it will be well-known, due to its feature contents.
It's great that you are getting ideas from this paragraph as well as from our dialogue made at this time.
I've been surfing on-line greater than three hours
nowadays, but I by no means found any interesting article like yours.
It is beautiful worth sufficient for me. In my view, if all web owners and bloggers made
excellent content material as you probably did, the net can be much more
useful than ever before.
This is really interesting, You're a very skilled blogger.
I have joined your rss feed and look forward to seeking more
of your magnificent post. Also, I've shared your site in my social networks!
Thanks for sharing your info. I really appreciate your efforts and I will be waiting for your next write ups thank you once again.
Hey would you mind letting me know which hosting company you're utilizing?
I've loaded your blog in 3 different browsers and I must say this blog loads a lot faster then most.
Can you recommend a good hosting provider at a reasonable
price? Kudos, I appreciate it!
Greetings from Carolina! I'm bored at work so I decided to
browse your site on my iphone during lunch break.
I love the info you provide here and can't
wait to take a look when I get home. I'm shocked at how fast your blog loaded on my phone ..
I'm not even using WIFI, just 3G .. Anyways, great site!
Hello, after reading this amazing piece of writing i am too cheerful to share my know-how here with colleagues.
Yes! Finally someone writes about sling tv https://asksylphoflight.tumblr.com/ .
Terrific article! That is the type of information that are supposed to be shared around the internet.
Disgrace on Google for not positioning this submit higher!
Come on over and talk over with my web site . Thank you =)
Great delivery. Outstanding arguments. Keep up the good work.
This is a very good tip especially to those fresh to the blogosphere.
Simple but very accurate info… Many thanks
for sharing this one. A must read post!
After I initially commented I appear to have clicked the
-Notify me when new comments are added- checkbox and now every time a comment
is added I receive four emails with the exact same comment.
Is there a means you can remove me from that service?
Thank you!
I'm extremely impressed with your writing abilities as well as with
the layout on your weblog. Is that this a paid theme or did you modify
it your self? Either way stay up the excellent high quality writing, it's rare to see a nice blog like this one today..
Nice blog here! Additionally your web site quite a bit up fast!
What web host are you using? Can I get your associate hyperlink
in your host? I desire my web site loaded up as fast as yours lol
You actually make it seem so easy with your presentation but I find this topic to be really something that I think I would never understand.
It seems too complicated and very broad for me. I am looking forward for your next post, I will try to get
the hang of it!
Thank you, I've recently been searching for info about this subject for a long time and yours is the greatest I have came upon so
far. But, what concerning the conclusion? Are you sure about the source?
This is really interesting, You are a very skilled blogger.
I have joined your feed and look forward to seeking more
of your excellent post. Also, I've shared your site in my social networks!
Howdy would you mind letting me know which webhost
you're utilizing? I've loaded your blog in 3 completely different internet browsers and I must say this blog loads a lot faster then most.
Can you suggest a good internet hosting provider at a honest price?
Cheers, I appreciate it!
Hi, after reading this awesome post i am as well glad to share my familiarity here with colleagues.