#3837: Inefficient sql query
--------------------------------+-------------------------------------------
Reporter: andres@anarazel.de | Owner: jonas
Type: enhancement | Status: new
Priority: normal | Milestone:
Component: general | Version: 0.10
Severity: normal | Keywords:
--------------------------------+-------------------------------------------
The sql query in get_youngest_rev_in_cache is quite inefficient and does a
full table scan in the revision table which scales quite bad.
The test repository used for the following numbers is the repository from
crystalspace3d.org which has around 25000 revisions and is 1.5gb big.
I benchmarked with apachebench with 3 times 20 requests. Everything one
with a concurrency of 4 and without. As the concurrency didnt make any
significant difference and as they vary much more over each series of
requests, i didnt wrote the numbers down.
What i requested was the timeline with a history depth of 5 days.
Hardware is a pentium 4 system, with a raid 1 disk system and 1 gig of
ram.
The following numbers are for sqlite. With postgres its very much more
visible, but i thought that sqlite is more wildly used.
Original query:
1.01 requests/s
SELECT MAX(CAST(rev as int)) FROM revision
1.2 requests/s
SELECT MAX(rev_int) FROM revision
with rev_int beeing a column which copied rev as an int and an seperate
index over it. The problem is, that sqlite doesnt seems to support indices
over expressions which eg postgres does.
3.66 requests/s
The effect is also visible when using a history depth of 90 days but the
differences arent _that_ big as before. I didnt really measure those as
its sometimes below 0.11 requests/s ...
The problem with this is, that the CAST() syntax is only supported with
sqlite3 and not 2. Although not tested mysql seems to support that for
some time.
And indices over expressions are not supported in sqlite at all. Which is
a pity because else you could just add an index like CREATE INDEX
revision_rev_asint ON revision ((CAST(rev as int)).
The only even halfway practical solution i currently see is an extra
column + trigger. But thats very hackish.
Any other idea?
--
Ticket URL: <http://trac.edgewall.org/ticket/3837>
The Trac Project <http://trac.edgewall.org/>
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups "Trac Tickets" group.
To post to this group, send email to trac-tickets@googlegroups.com
To unsubscribe from this group, send email to trac-tickets-unsubscribe@googlegroups.com
For more options, visit this group at http://groups.google.se/group/trac-tickets
-~----------~----~----~----~------~----~------~--~---
--------------------------------+-------------------------------------------
Reporter: andres@anarazel.de | Owner: jonas
Type: enhancement | Status: new
Priority: normal | Milestone:
Component: general | Version: 0.10
Severity: normal | Keywords:
--------------------------------+-------------------------------------------
The sql query in get_youngest_rev_in_cache is quite inefficient and does a
full table scan in the revision table which scales quite bad.
The test repository used for the following numbers is the repository from
crystalspace3d.org which has around 25000 revisions and is 1.5gb big.
I benchmarked with apachebench with 3 times 20 requests. Everything one
with a concurrency of 4 and without. As the concurrency didnt make any
significant difference and as they vary much more over each series of
requests, i didnt wrote the numbers down.
What i requested was the timeline with a history depth of 5 days.
Hardware is a pentium 4 system, with a raid 1 disk system and 1 gig of
ram.
The following numbers are for sqlite. With postgres its very much more
visible, but i thought that sqlite is more wildly used.
Original query:
1.01 requests/s
SELECT MAX(CAST(rev as int)) FROM revision
1.2 requests/s
SELECT MAX(rev_int) FROM revision
with rev_int beeing a column which copied rev as an int and an seperate
index over it. The problem is, that sqlite doesnt seems to support indices
over expressions which eg postgres does.
3.66 requests/s
The effect is also visible when using a history depth of 90 days but the
differences arent _that_ big as before. I didnt really measure those as
its sometimes below 0.11 requests/s ...
The problem with this is, that the CAST() syntax is only supported with
sqlite3 and not 2. Although not tested mysql seems to support that for
some time.
And indices over expressions are not supported in sqlite at all. Which is
a pity because else you could just add an index like CREATE INDEX
revision_rev_asint ON revision ((CAST(rev as int)).
The only even halfway practical solution i currently see is an extra
column + trigger. But thats very hackish.
Any other idea?
--
Ticket URL: <http://trac.edgewall.org/ticket/3837>
The Trac Project <http://trac.edgewall.org/>
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups "Trac Tickets" group.
To post to this group, send email to trac-tickets@googlegroups.com
To unsubscribe from this group, send email to trac-tickets-unsubscribe@googlegroups.com
For more options, visit this group at http://groups.google.se/group/trac-tickets
-~----------~----~----~----~------~----~------~--~---