MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:Andy Eastham Date:September 18 2003 10:16am
Subject:RE: Performance Problems
View as plain text  
Matthias,

Can you send us your table index definitions and the output of an EXPLAIN
command on your query?

ie
DESCRIBE pool;
SHOW INDEX FROM pool;
EXPLAIN SELECT sendnr FROM pool where sendnr = 111073101180;

I'm pretty sure we can improve this - I've got a table with 55 million
records (though only 3 columns) and a search like the one you've got takes
0.07 seconds on a box similar to your dev box.

Andy

> -----Original Message-----
> From: Schonder, Matthias [mailto:Matthias.Schonder@stripped]
> Sent: 18 September 2003 10:25
> To: 'mysql@stripped'
> Subject: Performance Problems
>
>
> Hei :)
>
> I have an extreme performance problem with a MySQL-DB.
> The database consists of 21 tables where all except three are
> storing only a
> few records. Two have about 150.000 records but they are only used
> temporary. The main table is rather huge, it has 90 columns and now after
> three month it has 500.000 records... but in the end it has to
> store data of
> 36 month.
> But since the table has grown to over 350.000 records I ran into massive
> performance problems. Querying for one record (Example: SELECT sendnr FROM
> pool where sendnr = 111073101180) takes 8 seconds via command line!
> The table is indexed and i put the my-huge.cnf into /etc/ as my.cnf
>
> How can I get more performance out of the mysql?
> The server which currently only hosts this database and is
> running apache1.3
> with php4 for providing results via intranet.
> The OS is FreeBSD 5.1.
> We are running two servers with the same enviroment
> One is a DualXeon 2.4ghz (with HT), 3GB RAM, 105GB HD (RAID5)
> the other one is a P4 with 2.4ghz, 1GB RAM and 120GB HD
>
> The Dual is the Productionserver, the P4 the developement- and
> test server.
> The querey takes that long on BOTH machines so it seems clear the
> DB itself
> is causing the performance problem.
>
> So anyone can help? This is really urgend and will save my life :)
>
> Big thanks in advance.
>
> Pacem,
>
> Matthias Schonder
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=1
>
>


Thread
Performance ProblemsMatthias Schonder18 Sep
  • RE: Performance ProblemsAndy Eastham18 Sep
  • Re: Performance ProblemsHarald Tijink18 Sep
  • Re: Performance ProblemsScott Helms18 Sep