From: Date: February 17 2000 2:42pm
Subject: Re: power-hungry SELECT query - how to reduce its priority?
List-Archive: http://lists.mysql.com/mysql/28064
Message-Id: <4.2.0.58.20000217144202.04d5ff00@mail.welters.de>
MIME-Version: 1.0
Content-Type: text/plain; charset="iso-8859-1"; format=flowed
Content-Transfer-Encoding: quoted-printable

At 14:05 17.02.2000 , Szymon Grabowski wrote:
>Greetings from distant Poland!
>
>Care to help a newbie? :-)

Show the Query and table design. So someone might help.

>One of my Perl scripts (run every single hour via
>crontab) starts mysql SELECT query that takes
>approx. 5 minutes to finish. It operates on 1.3 million
>records and it's fairly sophisticated (for me, at least).
>The problem is that while that query is running other
>Perl & PHP3 scripts tend to timeout when they try to
>access MySQL. It looks that this hourly SELECT
>eats up to much of mysql daemon and nothing's left
>for other applications & scripts. Non-mysql stuff
>works without a fail even when that SELECT is
>running.
>
>Is it possible to reduce priority of that job, so that it
>doesn't consume so much of mysql daemon resources?
>It wouldn't be a problem if that SELECT took 50 minutes,
>but let other scripts access mysql as well.
>
>Any ideas?
>
>I'm running MySQL 3.22.22.
>
>Here are MySQL system variables:
>
>  back_log   5
>  connect_timeout   5
>  delayed_insert_limit   100
>  delayed_insert_timeout   300
>  delayed_queue_size   1000
>  join_buffer   131072
>  flush_time   0
>  key_buffer   8388600
>  language
>/usr/local/mysql-3.22.22-sun-solaris2.6-sparc/share/mysql/english/
>  log   OFF
>  log_update   OFF
>  long_query_time   10
>  low_priority_updates   OFF
>  max_allowed_packet   1048576
>  max_connections   500
>  max_connect_errors   10
>  max_delayed_insert_threads   20
>  max_join_size   4294967295
>  max_sort_length   1024
>  net_buffer_length   16384
>  port   3306
>  protocol-version   10
>  record_buffer   131072
>  skip_locking   OFF
>  socket   /tmp/mysql.sock
>  sort_buffer   2097144
>  table_cache   64
>  thread_stack   65536
>  tmp_table_size   1048576
>  tmpdir   /var/tmp/
>  version   3.22.22
>  wait_timeout   28800
>
>
>Thanks for your help!
>
>Cheers,
>
>Simon
>
>
>--
>---------------------------------------------------------------------
>Please check "http://www.mysql.com/Manual_chapter/manual_toc.html" before
>posting. To request this thread, e-mail mysql-thread28060@stripped
>
>To unsubscribe, send a message to:
>     <mysql-unsubscribe-mw-mysql=3Dwelters.de@stripped>

-
Mit freundlichen Gr=FC=DFen

Markus Welters

----
Welters Datentechnik   Grafenberger Allee 81  40237 Duesseldorf
Telefon +49-211-9660093  GSM 0172-2007477   Fax +49-211-9660091
http://www.welters.de    MW411-RIPE   mailto: markus@stripped
---------------------------------------------------------------