[mythtv-users] Slow MySQL query after delete

Yeechang Lee ylee at pobox.com
Tue Dec 4 03:17:41 UTC 2007 

f-myth-users at media.mit.edu <f-myth-users at media.mit.edu> says:
> Actually, you also won't see it if your scheduler runs really fast;
> I assume you have verbose logging enabled on your backend, so what's
> a typical time for your
> 
>   Scheduled 1247 items in 30.0 = 1.96 match + 28.08 place
> 
> lines?  Mine averages 43.2 seconds across a sample of the last 35
> items scheduled; smallest 27.52, largest 57.14.

5.96 mean, 4.5 median for past 73 items over the past 28
hours. Smallest 1.5, largest 43.7 (very much an outlier; other than
two others at 12.2 and 14.8 seconds during a mythfilldatabase run, no
other times are above 10 seconds).

> Of that, match is almost always 0.00, but half a dozen were around
> 0.1 seconds, and three were large: 4.75, 2.19, 1.96.

21 at 0.00, ten at 0.01, five at 0.02, one at 0.03, two at 0.04, two
at 0.09, 26 at 0.20-0.29, one at 0.37, one at 0.41, one at 0.45, one
at 1.46, one at 2.30, one at 4.33.

> Scheduled 1247 +- 1 items each run until mfdb intervened; then
> scheduled 1317 until a few hours ago, then 1312.

789 items +- ten items each run until I did my "look over the schedule
over the next two weeks and schedule the items I want" routine, which
I do every two weeks, after which the figure got as high as 890 (but
without a perceptible jump in the scheduler time; the 890-item run
finished in 4.5 seconds, or 0.25 + 4.28.)

> > 7072 in oldrecorded, 2903907 in recordedseek, 4577 in
> > recordedmarkup
> 
> 10797 in oldrecorded, 2624120 in recordedmarkup (no recordedseek
> 'cause that's not in 0.18.1)

11053 in oldrecorded
9890378 in recordedseek
5017 in recordedmarkup

> > 78 in record (all are currently always/any channel rules--though I
> > often add up to 20 find once/any channel rules for movies), 413 in
> > recordmatch, 437 in recorded.
> 
> 411 in record (of which maybe a dozen are always/any channel and
> most of the rest are always/one channel), 2090 in recordmatch, 372
> in recorded.

192 in record, 1192 in recordmatch, 1096 in recorded.

> Btw, 125 in channel, and 6 tuners.  Everything in SD, FWIW.

224 in channel, four tuners, all HD capable.

> > Every table in the DB is MyISAM with the exception of the new
> > MythWeather tables, which are InnoDB (see
> > http://www.gossamer-threads.com/lists/mythtv/dev/302941#302941 ).
> 
> All of my tables are MyISAM.

All of my tables are InnoDB except for nestitle; however, I did not
see any difference in performance with MyISAM.

The relevant portions of my /etc/my.cnf:

    skip-locking
    key_buffer = 384M # Ideally enough for all .MYI files
    max_allowed_packet = 16M
    table_cache = 256
    sort_buffer_size = 48M
    net_buffer_length = 8M
    read_buffer_size = 1M
    read_rnd_buffer_size = 4M
    myisam_sort_buffer_size = 64M
    thread_cache = 8
    query_cache_size= 16M

    innodb_data_home_dir = /var/lib/mysql/
    innodb_data_file_path = ibdata1:10M:autoextend
    innodb_log_group_home_dir = /var/lib/mysql/
    innodb_log_arch_dir = /var/lib/mysql/
    # You can set .._buffer_pool_size up to 50 - 80 %
    # of RAM but beware of setting memory usage too high
    innodb_buffer_pool_size = 256M
    innodb_additional_mem_pool_size = 6M
    # Set .._log_file_size to 25 % of buffer pool size
    innodb_log_file_size = 64M
    innodb_log_buffer_size = 8M
    innodb_flush_log_at_trx_commit = 1
    innodb_lock_wait_timeout = 50

    set-variable=thread_stack=256k

> The backend has 1G RAM and doesn't ever use the swap it has
> available; perhaps half of that is taken up by large ivtv 0.4.1
> buffers for the 5 tuners in that machine.  (I could get an exact
> figure if necessary; I documented it and even sent it in traffic to
> the ivtv list maybe a year or two ago.)

Pentium 4 3.0GHz frontend/backend with 2GB here. The database is on a
separate partition of the boot drive. This backend handles all the
recording duties to both the local 1.5GB RAID 5 array and the remote
backend's 7GB RAID 6 array, and does not run any user jobs;
conversely, the remote backend does commflagging and transcoding but
not recording. All relevant file systems use JFS and CIFS.

> You haven't tried the critical experiment (so far as you've told us),
> which is to do MORE THAN ONE in quick succession.

One delete for me (whether of a single item or a playlist) from
mythfrontend results in a UI pause of no more than a second. Two
deletes in quick succession results in a pause of . . . no more than a
second or two.

It is important to note that I have long run my own patched versions
of the ATrpms 0.20.x distribution. (I really ought to release my
patched src.rpm, with lots more patches--many backported from
0.21--one of these days.) Patches relevant to the discussion are:

-------------- next part --------------
A non-text attachment was scrubbed...
Name: patch-1660.cpp
Type: application/octet-stream
Size: 15303 bytes
Desc: patch-1660.cpp
Url : http://mythtv.org/pipermail/mythtv-users/attachments/20071203/26e9063c/attachment.obj 
-------------- next part --------------
A non-text attachment was scrubbed...
Name: patch-ThreadedFileWriter.cpp
Type: application/octet-stream
Size: 1003 bytes
Desc: not available
Url : http://mythtv.org/pipermail/mythtv-users/attachments/20071203/26e9063c/attachment-0001.obj 
-------------- next part --------------
A non-text attachment was scrubbed...
Name: patch-mainserver.cpp
Type: application/octet-stream
Size: 761 bytes
Desc: not available
Url : http://mythtv.org/pipermail/mythtv-users/attachments/20071203/26e9063c/attachment-0002.obj 
-------------- next part --------------
A non-text attachment was scrubbed...
Name: patch-1660-2.cpp
Type: application/octet-stream
Size: 4310 bytes
Desc: not available
Url : http://mythtv.org/pipermail/mythtv-users/attachments/20071203/26e9063c/attachment-0003.obj 
-------------- next part --------------

Notes:

* The RPM specfile needs a
     rm -rf libs/libmyth/asyncdb.*
  just before the last patch is applied.

* I don't use the slow-delete feature at the moment, but still believe
  that truncating in less-frequent, larger chunks makes sense. See my
  comments under ticket #1835.

-- 
Frontend/backend:	P4 3.0GHz, 1.5TB software RAID 5 array
Slave backend:		Quad-core Xeon 1.6GHz, 6.6TB sw RAID 6
Video inputs:		Four high-definition over FireWire/OTA
Accessories:		47" 1080p LCD, 5.1 digital, and MX-600

More information about the mythtv-users mailing list