Feedreader.com - query performance - Comments

done that, thanks ingmar i'm

Vidar2 — Thu, 24 Jan 2008 16:20:21 +0200

done that, thanks ingmar

i'm really pleased with FR mySQLed now :-)

this is just great, criteria: "feedname contains" is essential to me that's why i'm so glad we fixed it and also why i have been asking for criteria "not contain" as well...almost all my smartfeeds contain "feedname contains"

happy regards Vidar

Greetings, no no, everything

ingmar — Thu, 24 Jan 2008 16:04:11 +0200

Greetings,

no no, everything is ok, I was explaining why this field is currently varchar
(1024). You can shorten this field, strange that performance is changed...to better way. You can also create index on this field, but you must use then

CREATE INDEX feed_name ON feeds(title(255));

Ingmar
------
FR dev

ingmar, can you please

Vidar2 — Thu, 24 Jan 2008 15:17:58 +0200

ingmar,

can you please explain again ? something not good with this change ? no happy ? :-)
thanks

//vidar

Greetings, don't forget,

ingmar — Thu, 24 Jan 2008 14:41:47 +0200

Greetings,

don't forget, that FR is using UTF8. If title is 512 and one UTF8 char can be also 3 chars, then actual size is 170 char.

Also Feeds->Title has no index, reason most simple, it's not commonly used. Do save dataspace no index is added for this field.

Ingmar
------
FR dev

seems to be fixed :-) i

Vidar2 — Thu, 24 Jan 2008 14:17:10 +0200

seems to be fixed :-)

i changed title in tbl_feeds from varchar(1024) -> (512)

and it's a huge difference when having smartfeeds with feedname contain

i will do some more testing

;-) Vidar

i have to update firebird

Vidar2 — Thu, 24 Jan 2008 00:09:42 +0200

i have to update firebird db..so i will give you an update in a few days..

Greetings,

ingmar — Wed, 23 Jan 2008 22:14:00 +0200

Greetings,

this index "was lost" in porting progress. All other indexes are present, even on article title. Currently we are testing different row reading levels, with dirty read, no waiting is done...but it's not good choise. So we keep searching more tricks.

With FB and requery, it depens quite a bit, how many feeds are currenlty updated. FB has quite strict row locking system. And this can provide very different speeds when opening query's (if there is heavy load, feeds are updated).

How much slower is MYSQL currently, compared to Firebird.If you click on feeds, how fast is MYSQL and Firebird.

And also can you copy current innodb settings.

Ingmar
------
FR dev

there was no index on

Vidar2 — Wed, 23 Jan 2008 13:57:17 +0200

there was no index on feed_id in tbl_articles so i added one...and it's mucho faster now...

i still need to use the original settings (my.ini), otherwise computer go bezerk

"old" FR is still faster so hopefully we can tweak mysqldb some more i will do some more testing and come back to you with result...but please advice me if you have some thoughts :-) (for example index on title in tbl_feeds ?)

regarding "old" FR, wich is deadly fast, but for some reason just stops responding (fetching 100 years) can there be something with REquery...it seems to me that this error happens when i look in a smartfeed and at the same time more articles are coming into the DB, FR wants to requery and requery and seems to loose it at that point...any ideas ?

//vidar

Greetings,

ingmar — Tue, 22 Jan 2008 20:52:36 +0200

Greetings,

could you create following index in MYSQL

CREATE INDEX delIndx ON articles(deleted)

this is common query in FR

select a.title
from articles a,feeds f
where f.feed_id=178
and a.feed_id=f.feed_id
and a.deleted=0

if you test performance, then use count for that...

select count(a.id)
from articles a,feeds f
where f.feed_id=178
and a.feed_id=f.feed_id
and a.deleted=0

Ingmar
------
FR dev

sorry no difference, these

Vidar2 — Tue, 22 Jan 2008 16:51:03 +0200

sorry no difference,

these are some figures:

firebird db tbl_articles contains 93 291 records

52 rows fetched (31ms) using SQL manager 2005 lite for Firebird

mysql db tbl_articles contains 135 365 records

415 rows fetched in 11,982 seconds
using Mysql query browser

query

SELECT * FROM feedreader.articles WHERE feed_id='2161'

maybe mysql does not like my computer :-(

Greetings, Tom has updated

ingmar — Tue, 22 Jan 2008 16:04:20 +0200

Greetings,

Tom has updated again following debug feedreader.exe

I belive, there is some index issues. MYSQL is quite fast database or is really Firebird beaten MYSQL:)))

Ingmar
------
FR dev

but i'm not sure that

Vidar2 — Tue, 22 Jan 2008 12:44:14 +0200

but i'm not sure that counting is the issue...

if i just do some small tests (with original settings)

a direct query in access against tbl_articles
with selection on feed_Id number

the firebird is 50->100 times faster than the sql

//Vidar

sorry but i receive a not

Vidar2 — Tue, 22 Jan 2008 12:15:42 +0200

sorry but i receive a not correct serial key mess when starting this exe

//vidar

Can you please take this

tom-admin — Tue, 22 Jan 2008 11:25:58 +0200

Can you please take this file http://www.feedreader.com/files/feedreader.exe

and replace your old mysql executable with this one.

Now please do two things. First let FR run as usually and then copy from left lower corner the number that are coming out. You can copy 20 last ones or so. Number are created only if you have some feeds that have new items while updating. Basically this number is time in seconds to do refreshing of unreadcounts.

Now if you have those numbers please add disableunreadcount=1 to feedreader.ini under [misc] section. Now unreadcounts are not updated (and you miss the correct unreadcount numbers) but at the same time performance may be gained. Can you comment on how your system works then unreadcounts are not updated.

What comes to mysql configuration variables then even if you reverted from ingmar's configuration options you should still use

innodb_flush_log_at_trx_commit = 0

it takes down IO load.

Greetings
Toomas

sorry, no difference

Vidar2 — Fri, 18 Jan 2008 12:29:30 +0200

sorry, no difference

query performance

Vidar2 — Wed, 16 Jan 2008 14:50:30 +0200

greetings,

i'm not sure what to do or if something is wrong but...

i tried ingmar's tip to change some settings in the my.ini file
i raised "innodb_buffer_pool_size=" to high (200) and the queries is much faster,
problem is CPU goes bezerk :-(

so i went back to original settings, and CPU is neat again

but the performance for some queries is not good...and sorry but it's hard to understand why "old" FR is faster than this one..

everything is OK if i set Article age newer than 12 Hours...but if i raise that to 1 day the query takes "forever"

this becomes very noticeable when i don't have any criteria...only a plain feed takes over 10 seconds !!! to fetch...of course i could set the 12 hour criteria on all smartfeeds but should that be necessary ?

i hope you have any ideas :-)

i'm running a CPU 2,66 ghz with 2 GB RAM (i have no other apps running)
my db is containing +900 feeds and DB right now contains 80K articles

regards Vidar