InnoDB Sorting problem. (performance)
Johan Wahlström
I recently converted our 2GB (43 tables) database from MyISAM to InnoDB
tables.
The reasons was to avoid severe locking problems coused by many querrys to a
few
important tables. This was accomplished nicely, and the locking errors went
away.
However, we got a new problem instead, scince we moved to InnoDB the
"Sorting result"
may take "forever" (more than 8000s in some cases) to complete. The
processlist gets filled
up with these processes and then the server wont take any more connections.
Is this a bug, or some configuration issue we may have missed? If so, how
can we make
this work?
regards Johan Wahlström
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <mysql-th...@lists.mysql.com>
To unsubscribe, e-mail <mysql-unsubscribe-mysql=freebsd.csie...@lists.mysql.com>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
ryc
with certain versions. If you arent running the latest you should probably
upgrade.
ryan
> To unsubscribe, e-mail <mysql-unsubscribe-iii=binar...@lists.mysql.com>
Johan Wahlström
Server version: 3.23.42-log
The locking problem is only valid for MyISAM tables, and
the reason for switching to InnoDB. InnoDB have problems
with sorting Instead, so the final result i s similar: Our server
fills up its max_connections....
regards Johan
ryc
all other queries just seem to get stalled as well... and before you know it
you have a billion queries sitting in the queue waiting.. What kind of cpu
usage do you have when the server is taking a long time sorting? What kind
of disk usage?
ryan
> To unsubscribe, e-mail <mysql-unsubscribe-iii=binar...@lists.mysql.com>
Heikki Tuuri
There is a problem in ORDER BY querys. Obviously
the MySQL optimizer in some cases chooses a wrong
path and the sorting takes a very long time.
If it is disk-bound, then making the buffer pool
bigger will help.
What does the Unix 'top' or the Windows task manager
say about the CPU usage?
Another help may come from
EXPLAIN SELECT ...
If you see some obvious problem in EXPLAIN you can
try to guide the optimizer through the MySQL
STRAIGHT JOIN or USE INDEX keywords.
Also the following trick may help: use
CREATE temporarytable SELECT ...
where there is no ORDER BY. Then read the
result from the temporary table:
SELECT FROM temporarytable ORDER BY ...
MySQL generally tends to do sorts too early.
If the result set is small it would be
better to sort only the result set.
I will improve estimates for the optimizer
in version .43 which should be out next week.
Generally, I should find out why sometimes
query plans in ORDER BY queries are chosen
badly.
Regards,
Heikki
http://www.innodb.com
manyquerrys> to
>> > a
> > > few
>> > > important tables. This was accomplished nicely, and the locking errors
>> > went
> > > away.
> > >
>> > > However, we got a new problem instead, scince we moved to InnoDB the
>> > > "Sorting result"
>> > > may take "forever" (more than 8000s in some cases) to complete. The
>> > > processlist gets filled
>> > > up with these processes and then the server wont take any more
>> > connections.> > >
>> > > Is this a bug, or some configuration issue we may have missed? If so,
> how
>> > > can we make
> > > this work?
> > >
> > > regards Johan Wahlström
> > >
> > >
>> > > ---------------------------------------------------------------------
---------------------------------------------------------------------
Johan Wahlström
Thank you for your answer!
I would say it's not disk bound, CPU usage is above 80%.
We reduced InnoDB usage to only the single most congested
table, about 70000 records and a total size of 10MB.
Currently we have assigned "innodb_buffer_pool_size=128M".
While running like this the throughput of the server is about equal
to running with MyISAM tables and locking problems.
When having all tables InnoDB and
buffer_pool to 7-800M the performance is close to zero.
The servers configuration is dual pIII-550, 1GB RAM.
Linux 2.4.7-smp.
Regards Johan
> To unsubscribe, e-mail <mysql-unsubscribe-whejl=whej...@lists.mysql.com>
Heikki Tuuri
please send me
mysql>EXPLAIN SELECT ... ;
of some slow query as well as the create table
statements and the output of
mysql>show table status from yourdatabasename;
concerning the tables in the query.
You should also make mysqld to print InnoDB
Monitor output to the standard output with
mysql>create table innodb_monitor(a int) type = innodb;
and send me a sample of it during the query
execution.
You can stop the monitor by
mysql>drop table innodb_monitor;
Regards,
Heikki
http://www.innodb.com/ibman.html