On Thu, Mar 01, 2007 at 08:29:09PM -0800, Gary W. Smith wrote:
> > The table is MyISAM. I searched on google a bit for info on slow
> > updates with MyISAM and didn't really hit it on the nose. Can I ask
> > you to elaborate?
> >
>
> In /etc/my.cnf try adding:
>
> long_query_time = 1
> log-slow-queries=/var/lib/mysql/mysql-slow.log
Let me make the problem a step more frustrating ::-). All of the
queries and updates to this database are extremely simple and very
predictable. The database is a backend for DNS.
Inserts are of the form (updates are analogous):
insert into dns_records (zone, host, data, ... )
values ('domain.com', 'www', '1.2.3.4', ... );
Queries are of the form:
select ttl, type, mx_priority, case
when lower(type)='txt' then
concat('\"', data, '\"')
when lower(type) = 'soa' then
concat_ws(' ', data, resp_person, serial, refresh, retry, expire, minimum)
else data end from dns_records where
zone = 'domain.com' and host = 'www';
We've fixed a few data formatting issues that made the select queries
slow under certain circumstances, but we're still running into
occasional performance problems running the inserts/updates.
There are no joins, subqueries, transactions, or any of the usual muck
that complicates a performance issue.
--
Ross Vandegrift
ross@stripped
"The good Christian should beware of mathematicians, and all those who
make empty prophecies. The danger already exists that the mathematicians
have made a covenant with the devil to darken the spirit and to confine
man in the bonds of Hell."
--St. Augustine, De Genesi ad Litteram, Book II, xviii, 37
