Schema Caching does not work (?)

sieppl · December 13, 2011, 9:39pm

My config:




    'components'=>array(

	  'cache'=>array(

            'class'=>'system.caching.CApcCache',

		),


      'db'=>array(

        'connectionString' => 'mysql:host=localhost;dbname=MYDB',

        'emulatePrepare' => true,

        'username' => 'MYUSER',

        'password' => 'MYPASS',

        'charset' => 'utf8',

        'enableParamLogging'=>true,

        'enableProfiling'=>true,    

		'schemaCachingDuration' => 60,

      ),


...

Then in a TestController I check following:




	public function actionCacheTest() {

		print_r(Yii::app()->cache);		

		print_r(Yii::app()->db->schemaCachingDuration);

	}

Which gives me following output:




CApcCache Object ( [keyPrefix] => d78d0187 [behaviors] => Array ( ) [_initialized:CApplicationComponent:private] => 1 [_e:CComponent:private] => [_m:CComponent:private] => ) 60

I guess this is fine?

Running /yii/requirements/index.php shows me a green light for ApcCache.

In the queries I have lot of "SHOW TABLE…", "SHOW COLUMNS…" etc. The number of queries is the same with/without schema caching.

It seems I am missing a spot. Any help is highly appreciated!

urecheatu007 · December 13, 2011, 10:50pm

Sebastian K.:

My config:
    'components'=>array(

	  'cache'=>array(

            'class'=>'system.caching.CApcCache',

		),


      'db'=>array(

        'connectionString' => 'mysql:host=localhost;dbname=MYDB',

        'emulatePrepare' => true,

        'username' => 'MYUSER',

        'password' => 'MYPASS',

        'charset' => 'utf8',

        'enableParamLogging'=>true,

        'enableProfiling'=>true,    

		'schemaCachingDuration' => 60,

      ),


...
Then in a TestController I check following:
	public function actionCacheTest() {

		print_r(Yii::app()->cache);		

		print_r(Yii::app()->db->schemaCachingDuration);

	}
Which gives me following output:
CApcCache Object ( [keyPrefix] => d78d0187 [behaviors] => Array ( ) [_initialized:CApplicationComponent:private] => 1 [_e:CComponent:private] => [_m:CComponent:private] => ) 60
I guess this is fine?

Running /yii/requirements/index.php shows me a green light for ApcCache.

In the queries I have lot of "SHOW TABLE…", "SHOW COLUMNS…" etc. The number of queries is the same with/without schema caching.

It seems I am missing a spot. Any help is highly appreciated!

Hi,

This is my config, quite similar, but with a couple of differences.

	'db'=&gt;array(


		'connectionString' =&gt; 'mysql:host=localhost;dbname=xxxxxxx',


		'emulatePrepare' =&gt; true,


		'username' =&gt; 'user',


		'password' =&gt; 'pass',


		'charset' =&gt; 'utf8',


		'schemaCachingDuration' =&gt; '300',


  'enableProfiling'=&gt;true,


  'enableParamLogging'=&gt;true,


	),


	'cache'=&gt;array(


		'class'=&gt;'system.caching.CFileCache',


		'cachePath'=&gt;'cache',


		'directoryLevel'=&gt;1


	),

As you can see, the db setting are the same, but I enabled the file based cache component, and i preload it. The schema needs to be saved somewhere, and this seems to be the simplest solution.

dniznick · December 13, 2011, 11:36pm

I think if you do not have the file cache enabled you are ‘caching’ it to memory on each request (which is not saved).

sieppl · December 13, 2011, 11:49pm

No, I think APC should work stand-alone.

dniznick · December 14, 2011, 1:05am

APC caches opcodes not db schema. If it were working you wouldn’t be posting this here…

bglee · December 14, 2011, 2:54am

Yii’s CApcCache class uses APC for other functionality. (see CCache, for example) It has been used for schema caching in several examples. (one of which is in the book, Yii 1.1 Application Development Cookbook).

@Sebastian - you should measure performance with and without caching turned on. I’m not 100% certain, but I don’t believe that this type of caching stops the show table statements, etc., they’re simply satisfied from the cache rather than coming from the database. If performance is faster with caching on, you’ll know that it is working.

Edit: You should also increase ‘schemaCachingDuration’ => 60 to something a lot greater than 60 seconds. That could be your issue. Try 3600. From what I just read, the metadata is supposed to be stored when you’re using CActiveRecord. That sounds like it wouldn’t need to issue the show table statements.

sieppl · December 14, 2011, 5:31pm

So you say with APC show table statements still are fired, but are served somehow from cache. With CFileCache show table statements are not fired? Very interesting, maybe someone else with APC can confirm this?

sieppl · December 14, 2011, 5:33pm

Thanks for sharing your config. CFileCache works fine. I can see files are cached and the query count going down by 40 on my start page for example.

sieppl · December 14, 2011, 5:40pm

Okay, right now I have this:

No cache: 93 queries,

CFileCache: 55 queries, 25% lower load time

ApcCache: 93 queries, load time same like "no cache" scenario.

It seems ApcCache has no impact on schemCachingDuration.

Anybody here who would like to share his working ApcCache with enabled schemaCachingConfiguration?

bglee · December 14, 2011, 7:43pm

@Sebastian, I just configured a project I’m working on locally to use ApcCache with schema caching. It appears to be working. I don’t have time to do benchmarks, but I did run a few pages that query the same set of tables. The initial pages included SHOW TABLE, SHOW COLUMNS statements. These disappeared in later pages that used the same tables. (see my EDIT earlier on whether SHOW TABLES are expected).

My configuration is basically the same as yours except for the value:


                  

                        'schemaCachingDuration' => 300,

                        'enableProfiling' => true,

                        'enableParamLogging' => true,

...

Try running phpinfo() to confirm that APC Cache is installed and enabled.

bglee · December 14, 2011, 7:46pm

@Sebastian - One other idea. If you’re re-running the exact same URL, make sure that your browser is not caching the results and displaying the same profile info at the bottom. I noticed this when I re-ran one of my dynamic pages.

sieppl · December 14, 2011, 8:47pm

Thanks for your help guys!

Attached is my apc config from phpinfo.

Just tested and this is not the case either.

I am going to:

Test APC caching with own data, to check it is basically working
Prepare simple dummy project and test APC caching there
Test same project on other machine with APC.

Will post results here!

bglee · December 14, 2011, 10:00pm

My only other thought is that your server is out of available memory for the apc.shm_size (256M) in your settings. This causes the cache to flush every time you try to load something. Try reducing it to 32M. You can also take a look on your box to see what the free memory looks like.