[mythtv] innodb vs myism preformance

[Yan-Fa Li]

Daniel Manjarres wrote:
> Yan-Fa Li wrote:
> 
>> Daniel Manjarres wrote:
>>  
>>
>>> Thanks for the quick response.
>>>
>>> Ok, well I decided to switch the database back to test it, and it is 
>>> slow as hell again. This time I did:
>>>
>>> for a in $(ls *frm | cut -f1 -d. ); do echo "use mythconverg; ALTER 
>>> TABLE $a TYPE=MYISAM;"|mysql ; done
>>>
>>>
>>>   
>>
>>
>> Do you have query cache turned on ?
>>
> 
> Well, deleting a recording isn't going to be sped up by cache, now is it?
> 

True, but I was wondering if the problem was related to re-reading the 
table after you deleted the show.  Setting the query cache might have 
helped that case.  I've been using mysql with myisam for quite some time
and I've not noticed multiple seconds of delay deleting shows, but maybe 
my hardware is really slow and I'm used to the performance as is.

> mysql> show variables like '%cache%';
> +-----------------------+------------+
> | Variable_name         | Value      |
> +-----------------------+------------+
> | bdb_cache_size        | 8388600    |
> | binlog_cache_size     | 32768      |
> | have_query_cache      | YES        |
> | max_binlog_cache_size | 4294967295 |
> | query_cache_limit     | 1048576    |
> | query_cache_size      | 26214400   |
> | query_cache_type      | ON         |
> | table_cache           | 64         |
> | thread_cache_size     | 0          |
> +-----------------------+------------+
> 9 rows in set (0.00 sec)
> 

OK, I think I see a problem.  Set your thread_cache_size to a non-zero 
value.  Starting up new threads is expensive for mysql.  I'm not sure
what the access pattern is for QTMYSQL.  Perhaps it's really smart about
keeping a connection open to the database, or perhaps it isn't.  It's
definitely worth a try.

mysql> show variables like "%cache%";
+-----------------------+------------+
| Variable_name         | Value      |
+-----------------------+------------+
| bdb_cache_size        | 8388600    |
| binlog_cache_size     | 32768      |
| have_query_cache      | YES        |
| max_binlog_cache_size | 4294967295 |
| query_cache_limit     | 1048576    |
| query_cache_size      | 4194304    |
| query_cache_type      | ON         |
| table_cache           | 128        |
| thread_cache_size     | 4          |
+-----------------------+------------+
9 rows in set (0.00 sec)

mysql> show status like "%cache%";
+-------------------------+---------+
| Variable_name           | Value   |
+-------------------------+---------+
| Qcache_queries_in_cache | 1252    |
| Qcache_inserts          | 58097   |
| Qcache_hits             | 766899  |
| Qcache_lowmem_prunes    | 0       |
| Qcache_not_cached       | 255251  |
| Qcache_free_memory      | 2806336 |
| Qcache_free_blocks      | 134     |
| Qcache_total_blocks     | 2678    |
| Threads_cached          | 3       |
+-------------------------+---------+
9 rows in set (0.00 sec)

See I have three threads cached, and I have two frontends in regular use 
and one backend.

Yan