I need advice about huge databases

perrohunter

Hi !

Lately Ive been working with an amazing new world plagued with HUGE databases. While having fun in this world I came to wonder "Why are my querys taking so long" so I came to phpbuilder to ask for the experts advice =)

I have done most of the normalizations things everytwhere tell me to do , index, specificd data type , etc...

Now that from a table thats about 3,142,770 rows longs, the most optimized query i have come up with takes about 73 seconds to finish using myIsam , then I thoght "oh everyone is talking about innodb, i should try it out" after 1 hour of changing the database from myisam to innodb (obviusly using a copy of the orignal db) i found that the same query took 142 seconds !, the double , damn i thought , so heres my question

What can I do to speed up my querys ?

Ive been thinking to change the conf of my server , its just that i dont know how
Maybe learning to use innodb
maybe changing to postgresql ?

Ill try anything u suggest me , Im very interested in everyones point of view

NogDog

Lots and lots of info about MySQL optimization here: http://dev.mysql.com/doc/refman/5.0/en/optimization.html

In particular, you may want to look at Optimizing Queries with EXPLAIN

perrohunter

Already tryed EXPLAIN, on phpmyadmin , I dont quite understand well what it says =P

I just read that mysql is not that good for such a huge databases, should i keep trying mysql ? o are there other good suggestions ?

Ive always wondered how good is oracle express 10g o_O

NogDog

Certainly there will be some performance differences between different RDBMS's, if everything else is equal. But my guess is that if the database, the queries, and the database server are all optimized for your particular needs, that the particular RDBMS will have significantly less of an impact (though still an impact worth looking into) than the sum of all those other optimizations. That is why a good database consultant (and I am NOT one) can command some pretty hefty fees. 😉

So whether purchasing Oracle, configuring it, then possibly still hiring a consultant to optimize it is the way to go; or first hiring a MySQL consultant to optimize your current set-up before exploring other possibilities -- well, that's up to you and your budget. (Or, of course, you can continue to try to do it yourself, which is a great learning experience; but again you have to weigh that time versus spending money to just get it done -- and of course what sort of budget you have.)

Sorry for no concrete suggestions here, just trying to give you some food for thought.

perrohunter

tasty food =) lol

Actually theres a free version of Oracle =P

NogDog

perrohunter wrote:
tasty food =) lol

Actually theres a free version of Oracle =P

If there is, then it can't hurt (or at least not hurt as much) to give it a try. (Hopefully the free version isn't a "crippled" version?)

tomhath

What sort of hardware are you running on? Getting into big databases you should be talking about multiple CPUs, Gigs of memory, and at least RAID hard drives.

Databases I work with routinely involve joins of tables with tens of millions of rows that only take a couple of seconds. So something isn't adding up with your queries.

perrohunter

Currently running all these test on :

Mac OS X 10.4.9
2 GB DDR2
120 GB HDD
2.13 GHZ Core2Duo

using MAMP (Mac ,Apache,MySQL,PHP) default config

Planning to move to a linux enviroment, (very similar hardware)

sid

NogDog wrote:
(Hopefully the free version isn't a "crippled" version?)

top get your feet wet with oracle, this is more than sufficient actually - i have been working with 9i at university before and now use 10g Express at home, since it's free. and the restrictions really aren't too much of a problem for the typical personal user. But as always, there are pros and cons

perrohunter

geez, thanks sid, i was planning to install oracle tomorrow, but now i think about it, my database is larger than 4G =(:mad:

tomhath

At a minimum you should have a second disk for the data. But it would be much better to add least two disks in a RAID 1, or four in RAID 10. That'll give you a big speed boost and redundancy to protect the data in case of a disk failure.

Put MySQL's data files on their own physical disks (not just their own partitions, their own hard drives), nothing else should touch them. No software, no swap file, no logs, nothing else. Then tune the buffers and caches to suit the hardware.

http://dev.mysql.com/books/hpmysql-excerpts/ch06.html

MarkR

Forget changing database - you need to understand why the query is taking too long.

In most cases a query takes too long because it's doing a full table scan on a big table. This should be avoided if possible. Read the MySQL optimisation stuff for more info.

Essentially, if you're doing anything in your WHERE clause OTHER than a straight join (on indexed columns), = on indexed columns, or IN on indexed columns, it is PROBABLY doing a full table scan.

Specifically, if you start using LIKE, you can forget indexes - it will probably do a full table scan (Exception: If you're ANDing it with something which does use indexes and discards almost all the rows).

These limitations are not specific to MySQL.

Mark

Piranha

If you post the table setup and a few of the queries it may be possible that someone here can help you out by pointing on things that take to long to do.

Roger_Ramjet

You should also post the EXPLAIN output from the slow queries, someone can then explain them to you.

PS what ver of mysql are you using?

Sxooter

As MarkR mentioned, you need to understand why the query is slow before changing dbs.

Changing to Oracle or PostgreSQL and still asking the database to trundle throught 100,000 rows isn't going to make your queries faster.

Now, maybe after optimizing your queries you'll find a limitation of MySQL that means you need a different DB. But most likely you just need to optimize / change your queries to be more efficient.

Sxooter

perrohunter wrote:
Already tryed EXPLAIN, on phpmyadmin , I dont quite understand well what it says =P

I just read that mysql is not that good for such a huge databases, should i keep trying mysql ? o are there other good suggestions ?

Ive always wondered how good is oracle express 10g o_O

Oracle 10G is an amazing database. but if you find MySQL's Explain output confusing, you are probably not really ready to play with oracle just yet.

Post your query and the output of explain for the mysql gurus here to look at.

If you must play with another database, try PostgreSQL. The free version (they're all free) of postgresql isn't some stripped down one user version either, it's the real thing. It's no Oracle, but it's awefully close.