CachedModel extremely slow

Rails
1

I’m trying to speed up my app by caching models so that the SQL server
gets a lower hit rate.

I’ve installed memcache-client and cached_model, and I’ve tried
different setups to speed everything up. I’ve added this to
config/environments/production.rb:

require ‘cached_model’
CACHE = MemCache.new(%w[psrv4:4000 psrv4:4001 psrv4:4002 psrv4:4003
psrv4:4004 psrv4:4005 psrv4:4006 psrv4:4007 psrv4:4008 psrv4:4009],
:namespace => ‘railstest_production’)

I’ve added this to config/environments/development.rb:

require ‘cached_model’
CACHE = MemCache.new(‘psrv4:3002’, :namespace =>
‘railstest_development’)

And, all models now have “< CachedModel” instead of “<
ActiveRecord::Base”.

Okay, I should be set, I think. Yet, without this caching turned on, I
got an average of 29.26 pages/sec, now I’m down to ~0.5 pages/sec!

What gives? Bombarding my 10 production Mongrels with request leaves
the total CPU utilization at around 0%. Something’s up, but I dunno
what?

This is on Windows, btw. (I know, I know)

Has anyone experienced this and have any suggestions? I’ve tried
different setups with the environment.rb files, like changing to
“localhost” instead of “psrv4”, which didn’t help. Also tried filling
in more options like some on the net do.

Help!

Thanks,
Daniel :slight_smile:

2

Daniel Smedegaard B. wrote the following on 27.11.2006 11:52 :

I’m trying to speed up my app by caching models so that the SQL server
gets a lower hit rate.

This might seem obvious, but are the memcache servers really running and
listening on these ports? What are the logs telling in development mode
(do you see memcache-client debug messsages)?

Lionel

3

Lionel B. wrote:

Daniel Smedegaard B. wrote the following on 27.11.2006 11:52 :

I’m trying to speed up my app by caching models so that the SQL server
gets a lower hit rate.

This might seem obvious, but are the memcache servers really running and
listening on these ports? What are the logs telling in development mode
(do you see memcache-client debug messsages)?

Hmm… Sorry, I don’t know! :expressionless: How do I find out if the servers are
running?

Here’s one from the log (there are no messages matching “memcache”):

Processing ItemsController#show (for 10.10.10.110 at 2006-11-27
11:37:14) [GET]
Session ID: cf1eb549ae1b91820ef4b280148f86a9
Parameters: {“action”=>“show”, “id”=>“9780192828606”,
“controller”=>“items”}
[4;36;1mItem Columns (0.078000) [0m [0;1mSELECT COLUMN_NAME as
ColName, COLUMN_DEFAULT as DefaultValue, DATA_TYPE as ColType,
IS_NULLABLE As IsNullable, COL_LENGTH(‘Item’, COLUMN_NAME) as Length,
COLUMNPROPERTY(OBJECT_ID(‘Item’), COLUMN_NAME, ‘IsIdentity’) as
IsIdentity, NUMERIC_SCALE as Scale FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = ‘Item’ [0m
[4;35;1mSQL (0.016000) [0m [0mSELECT COLUMN_NAME as ColName,
COLUMN_DEFAULT as DefaultValue, DATA_TYPE as ColType, IS_NULLABLE As
IsNullable, COL_LENGTH(‘Item’, COLUMN_NAME) as Length,
COLUMNPROPERTY(OBJECT_ID(‘Item’), COLUMN_NAME, ‘IsIdentity’) as
IsIdentity, NUMERIC_SCALE as Scale FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = ‘Item’ [0m
[4;36;1mItem Load (9.375000) [0m [0;1mSELECT TOP 1 * FROM
PolyBook.Item WHERE (PolyBook.Item.Itemnumber = 9780192828606) [0m
Rendering within layouts/items
Rendering items/show
[4;35;1mPublisher Columns (0.000000) [0m [0mSELECT COLUMN_NAME as
ColName, COLUMN_DEFAULT as DefaultValue, DATA_TYPE as ColType,
IS_NULLABLE As IsNullable, COL_LENGTH(‘Publisher’, COLUMN_NAME) as
Length, COLUMNPROPERTY(OBJECT_ID(‘Publisher’), COLUMN_NAME,
‘IsIdentity’) as IsIdentity, NUMERIC_SCALE as Scale FROM
INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = ‘Publisher’ [0m
[4;36;1mSQL (0.000000) [0m [0;1mSELECT COLUMN_NAME as ColName,
COLUMN_DEFAULT as DefaultValue, DATA_TYPE as ColType, IS_NULLABLE As
IsNullable, COL_LENGTH(‘Publisher’, COLUMN_NAME) as Length,
COLUMNPROPERTY(OBJECT_ID(‘Publisher’), COLUMN_NAME, ‘IsIdentity’) as
IsIdentity, NUMERIC_SCALE as Scale FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = ‘Publisher’ [0m
[4;35;1mPublisher Load (0.000000) [0m [0mSELECT TOP 1 * FROM
PolyBook.Publisher WHERE (PolyBook.Publisher.PublisherID = 1) [0m
[4;36;1mSeries Columns (0.016000) [0m [0;1mSELECT COLUMN_NAME as
ColName, COLUMN_DEFAULT as DefaultValue, DATA_TYPE as ColType,
IS_NULLABLE As IsNullable, COL_LENGTH(‘Series’, COLUMN_NAME) as Length,
COLUMNPROPERTY(OBJECT_ID(‘Series’), COLUMN_NAME, ‘IsIdentity’) as
IsIdentity, NUMERIC_SCALE as Scale FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = ‘Series’ [0m
[4;35;1mSQL (0.031000) [0m [0mSELECT COLUMN_NAME as ColName,
COLUMN_DEFAULT as DefaultValue, DATA_TYPE as ColType, IS_NULLABLE As
IsNullable, COL_LENGTH(‘Series’, COLUMN_NAME) as Length,
COLUMNPROPERTY(OBJECT_ID(‘Series’), COLUMN_NAME, ‘IsIdentity’) as
IsIdentity, NUMERIC_SCALE as Scale FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = ‘Series’ [0m
[4;36;1mSeries Load (0.000000) [0m [0;1mSELECT PolyBook.Series.*
FROM PolyBook.Series INNER JOIN PolyBook.SeriesItem ON
(PolyBook.Series.SeriesID) = (PolyBook.SeriesItem.SeriesID) WHERE
((PolyBook.SeriesItem.ChildItemnumber = ‘9780192828606’)) [0m
[4;35;1mAuthor Columns (0.015000) [0m [0mSELECT COLUMN_NAME as
ColName, COLUMN_DEFAULT as DefaultValue, DATA_TYPE as ColType,
IS_NULLABLE As IsNullable, COL_LENGTH(‘Author’, COLUMN_NAME) as Length,
COLUMNPROPERTY(OBJECT_ID(‘Author’), COLUMN_NAME, ‘IsIdentity’) as
IsIdentity, NUMERIC_SCALE as Scale FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = ‘Author’ [0m
[4;36;1mSQL (0.000000) [0m [0;1mSELECT COLUMN_NAME as ColName,
COLUMN_DEFAULT as DefaultValue, DATA_TYPE as ColType, IS_NULLABLE As
IsNullable, COL_LENGTH(‘Author’, COLUMN_NAME) as Length,
COLUMNPROPERTY(OBJECT_ID(‘Author’), COLUMN_NAME, ‘IsIdentity’) as
IsIdentity, NUMERIC_SCALE as Scale FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = ‘Author’ [0m
[4;35;1mAuthor Load (0.016000) [0m [0mSELECT PolyBook.Author.*
FROM PolyBook.Author INNER JOIN PolyBook.AuthorRankTypeItem ON
(PolyBook.Author.AuthorID) = (PolyBook.AuthorRankTypeItem.AuthorID)
WHERE ((PolyBook.AuthorRankTypeItem.Itemnumber = ‘9780192828606’))
ORDER BY AuthorRank [0m
[4;36;1mAudienceRangeCode Columns (0.015000) [0m [0;1mSELECT
COLUMN_NAME as ColName, COLUMN_DEFAULT as DefaultValue, DATA_TYPE as
ColType, IS_NULLABLE As IsNullable, COL_LENGTH(‘AudienceRangeCode’,
COLUMN_NAME) as Length, COLUMNPROPERTY(OBJECT_ID(‘AudienceRangeCode’),
COLUMN_NAME, ‘IsIdentity’) as IsIdentity, NUMERIC_SCALE as Scale FROM
INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = ‘AudienceRangeCode’ [0m
[4;35;1mSQL (0.031000) [0m [0mSELECT COLUMN_NAME as ColName,
COLUMN_DEFAULT as DefaultValue, DATA_TYPE as ColType, IS_NULLABLE As
IsNullable, COL_LENGTH(‘AudienceRangeCode’, COLUMN_NAME) as Length,
COLUMNPROPERTY(OBJECT_ID(‘AudienceRangeCode’), COLUMN_NAME,
‘IsIdentity’) as IsIdentity, NUMERIC_SCALE as Scale FROM
INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = ‘AudienceRangeCode’ [0m
[4;36;1mAudienceRangeCode Load (0.000000) [0m [0;1mSELECT
PolyBook.AudienceRangeCode.* FROM PolyBook.AudienceRangeCode INNER JOIN
PolyBook.AudienceRangeItem ON
(PolyBook.AudienceRangeCode.AudienceRangeCode) =
(PolyBook.AudienceRangeItem.AudienceRangeCode) WHERE
((PolyBook.AudienceRangeItem.Itemnumber = ‘9780192828606’)) [0m
[4;35;1mEditionType Columns (0.031000) [0m [0mSELECT COLUMN_NAME
as ColName, COLUMN_DEFAULT as DefaultValue, DATA_TYPE as ColType,
IS_NULLABLE As IsNullable, COL_LENGTH(‘EditionType’, COLUMN_NAME) as
Length, COLUMNPROPERTY(OBJECT_ID(‘EditionType’), COLUMN_NAME,
‘IsIdentity’) as IsIdentity, NUMERIC_SCALE as Scale FROM
INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = ‘EditionType’ [0m
[4;36;1mSQL (0.032000) [0m [0;1mSELECT COLUMN_NAME as ColName,
COLUMN_DEFAULT as DefaultValue, DATA_TYPE as ColType, IS_NULLABLE As
IsNullable, COL_LENGTH(‘EditionType’, COLUMN_NAME) as Length,
COLUMNPROPERTY(OBJECT_ID(‘EditionType’), COLUMN_NAME, ‘IsIdentity’) as
IsIdentity, NUMERIC_SCALE as Scale FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = ‘EditionType’ [0m
[4;35;1mEditionType Load (0.000000) [0m [0mSELECT
PolyBook.EditionType.* FROM PolyBook.EditionType INNER JOIN
PolyBook.EditionItem ON (PolyBook.EditionType.EditionTypeCode) =
(PolyBook.EditionItem.EditionTypeCode) WHERE
((PolyBook.EditionItem.Itemnumber = ‘9780192828606’)) [0m
Completed in 10.29700 (0 reqs/sec) | Rendering: 0.12600 (1%) | DB:
9.65600 (93%) | 200 OK [http://psrv4/items/show/9780192828606]

4

Lionel B. wrote:

Daniel Smedegaard B. wrote the following on 27.11.2006 11:52 :

I’m trying to speed up my app by caching models so that the SQL server
gets a lower hit rate.

This might seem obvious, but are the memcache servers really running and
listening on these ports? What are the logs telling in development mode
(do you see memcache-client debug messsages)?

Okay, found out what the server thing was all about. I just read the
README from the robotcoop site, which didn’t mention anything about
servers, so it wasn’t until I stumbled across the win32 versions of the
server that it dawned on me.

So, now the server runs. But, I found out what the real problem was.
The CachedModel (I guess) removes apostrophes from the SELECT
statement, so that,
SELECT TOP 1 * FROM PolyBook.Item WHERE (PolyBook.Item.Itemnumber =
‘9780672328933’)
becomes,
SELECT TOP 1 * FROM PolyBook.Item WHERE (PolyBook.Item.Itemnumber =
9780672328933)

  • This turns a millisecond query into a ten second one! Must file a bug
    report, unless someone has a workaround for this?

Cheers,
Daniel :slight_smile: