Long queries time

Lucifix · Regular Joined: Mar 11, 2005 Posts: 62

I just found out that some quries in the NukeSentinel are loading quite long. Here is the query witch took 4 seconds to load:

# Query_time: 4 Lock_time: 0 Rows_sent: 1 Rows_examined: 82543
SELECT `c2c` FROM `nuke_nsnst_ip2country` WHERE `ip_lo`<='3557959793' AND `ip_hi`>='3557959793' LIMIT 0,1;

Does anyone have the same problem?

Raven · Posted: Thu Sep 11, 2008 11:48 pm

Hmmm. You might want to find another host. My results:
Query took 0.7775 sec

slackervaara · Worker Joined: Aug 26, 2007 Posts: 234

For me it took 0.24 sec.

Raven · Posted: Fri Sep 12, 2008 12:14 am

I should add that your results will vary due to server load and processes running at the time but 4 seconds is WAY off the curve.

Lucifix · Regular Joined: Mar 11, 2005 Posts: 62

Well if I run this query manualy query took 0.3170 sec... looks like this is happening only when there is rush on my site.

Guess this isn't the problem in table.

Thx!

Lucifix · Regular Joined: Mar 11, 2005 Posts: 62

hehe raven, just 1 min ahead of me Smile

Why does this query actualy do? Is it for tracking visitors?

Raven · Posted: Fri Sep 12, 2008 12:38 am

It's used to track IP countries in a couple of scripts.

Try profiling it under phpMyAdmin to see where it's hanging. My last try showed this:

Code:

Status Time
(initialization) 0.000023
Opening tables 0.000037
System lock 0.000009
Table lock 0.000015
init 0.000011
optimizing 0.000009
statistics 0.000013
preparing 0.000012
executing 0.0000699
Sending data 0.000027
end 0.000009
query end 0.000009
freeing items 0.000014
closing tables 0.000008
removing tmp table 0.000023
closing tables 0.000012
logging slow query 0.000009

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE nuke_nsnst_ip2country range PRIMARY PRIMARY 4 NULL 82969 Using where

Lucifix · Regular Joined: Mar 11, 2005 Posts: 62

I searched for profiling in phpMyAdmin but I can't find it Sad

Raven · Posted: Fri Sep 12, 2008 7:34 am

Profiling was added in v2.11.0.0 (2007-08-21)

In phpMyAdmin
1. Select your database
2. Select the SQL tab
3. Submit your query
4. The result screen should have a Profiling checkbox

5. Check it
6. Resubmit your query

Lucifix · Regular Joined: Mar 11, 2005 Posts: 62

Thx for explaining me raven, but I have phpMyAdmin version 2.11.6 but i don't see any profiling. I'll check in which version profiling is integrated.

Raven · Posted: Fri Sep 12, 2008 10:26 am

It was added in v2.11.0.0 (2007-08-21). You may have to activate it; I don't remember.

Lucifix · Regular Joined: Mar 11, 2005 Posts: 62

I'm checking in config.default.php file in includes libraries of phpmyadmin and don't know if this code enables profiling. Are you so kind and check your settings?

These are mine:

Code:

/*******************************************************************************
* Developers ONLY!
* To use the following, please install the DBG extension from http://dd.cron.ru/dbg/
*
* @global array $cfg['DBG']
*/
$cfg['DBG'] = array();

/**
* Make the DBG stuff available
*
* @global boolean $cfg['DBG']['enable']
*/
$cfg['DBG']['enable'] = false;