MySQL Forums
Forum List  »  Optimizer & Parser

Re: Executing same query in 1 moment
Posted by: Brano Gerzo
Date: November 21, 2011 12:42AM

Rick,

here is paste, you wanted, I am not sure, if you can find out something. The queries, which I got problem with are usually simple, for example like:

# Query_time: 6.244268 Lock_time: 0.000045 Rows_sent: 40 Rows_examined: 1573295
SET timestamp=1321857164;
SELECT SQL_CACHE SQL_CALC_FOUND_ROWS a.*
FROM subs_search_cache a
WHERE SubEnabled = 1 ORDER BY SubAddDate Desc LIMIT 0,40;

in this query can be other parameters, like SubLanguageID, but it is not so good for using key also, different LIMIT also can occur. I know, it would help a little to remove SubEnabled = 0 to different table, but I am not sure, how much, I guess this query just sorts rows by SubAddDate and then give me back all SubEnabled = 1 rows - which should be faster than 6 seconds I think...

http://pastebin.com/yrQFgkaU

On DB box I am running mysql, redis and mongo...anyway, ram:

SYSTEM MEMORY INFORMATION:
mem_wire: 5590437888 ( 5331MB) [ 22%] Wired: disabled for paging out
mem_active: + 13272596480 ( 12657MB) [ 53%] Active: recently referenced
mem_inactive:+ 4569399296 ( 4357MB) [ 18%] Inactive: recently not referenced
mem_cache: + 1022099456 ( 974MB) [ 4%] Cached: almost avail. for allocation
mem_free: + 465461248 ( 443MB) [ 1%] Free: fully available for allocation
mem_gap_vm: + 43712512 ( 41MB) [ 0%] Memory gap: UNKNOWN
-------------- ------------ ----------- ------
mem_all: = 24963706880 ( 23807MB) [100%] Total real memory managed
mem_gap_sys: + 788332544 ( 751MB) Memory gap: Kernel?!
-------------- ------------ -----------
mem_phys: = 25752039424 ( 24559MB) Total real memory available
mem_gap_hw: + 17764352 ( 16MB) Memory gap: Segment Mappings?!
-------------- ------------ -----------
mem_hw: = 25769803776 ( 24576MB) Total real memory installed

SYSTEM MEMORY SUMMARY:
mem_used: 19712843776 ( 18799MB) [ 76%] Logically used memory
mem_avail: + 6056960000 ( 5776MB) [ 23%] Logically available memory
-------------- ------------ ----------- ------
mem_total: = 25769803776 ( 24576MB) [100%] Logically total memory

I write already sql queries with locking, so it should work better now, I will see soon.

Options: ReplyQuote


Subject
Views
Written By
Posted
2151
November 19, 2011 09:27AM
924
November 20, 2011 04:42PM
954
November 20, 2011 10:31PM
847
November 22, 2011 12:05AM
Re: Executing same query in 1 moment
938
November 21, 2011 12:42AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.