Largest Mysql Database....

Discussion in 'OT Technology' started by RaginBajin, Oct 29, 2004.

  1. RaginBajin

    RaginBajin Have you punched a donkey today?

    Joined:
    Dec 24, 2001
    Messages:
    8,740
    Likes Received:
    0
    Location:
    NoVA
    What is the largest MySQL Databaase that you guys have seen? How does it run? For example is it fast at queries, does it have a lot of load,etc.

    The reason I ask is that this new place I started working at has a Mysql DB that is 400 gigs. Things are getting a bit slow, but I'm wondering if it's because of the tuning of the system and database or if Mysql just can't handle that load.

    Theories, suggestions, etc.
     
  2. __23skidoo

    __23skidoo ...has the nuts

    Joined:
    Jan 3, 2002
    Messages:
    4,588
    Likes Received:
    0
    Location:
    Atlanta
    Thats pretty big.

    I have an Orcacle DB that is ~200GB and runs well.

    I have definately tuned the memory usage......try and track down and tune what is getting loaded into memory and what is actually being used. I've definately seen instances of systems using the hell out of memory and not utilizing processes as well.
     
  3. SLED

    SLED build an idiot proof device and someone else will

    Joined:
    Sep 20, 2001
    Messages:
    28,118
    Likes Received:
    0
    Location:
    AZ, like a bauce!
    I haven't seen one that big first hand but i remember this article:

    http://www.mysql.com/news-and-events/press-release/release_2003_21.html

    i believe they are still using it last i heard. What table types is your new employer using? If they're doing transaction friendly table types, then it might slow it down a bit. MySQL is a very lean and fast database out of the box, but there are many things you can add to it, that would slow it down, or use more processor. What type of box do they run it on? What OS? I'm really curious now.
     
  4. Joe_Cool

    Joe_Cool Never trust a woman or a government. Moderator

    Joined:
    Jun 30, 2003
    Messages:
    299,154
    Likes Received:
    500
    If MySQL is getting bogged down, you might want to look into PostgreSQL as an alternative (assuming you want to go with free software). It's supposed to be more robust and scalable than MySQL.

    But that's just what I've heard. 400gb is about 800 times bigger than the largest db I've ever worked on. :dunno:
     
  5. RaginBajin

    RaginBajin Have you punched a donkey today?

    Joined:
    Dec 24, 2001
    Messages:
    8,740
    Likes Received:
    0
    Location:
    NoVA
    Thanks for the article. I'll definitely take a look at it.

    We are using MyISAM table types. We decided InnoDB was going to slow down things. We are running on Mysql 4.1 which allows us to use row level locking and subselects which have helped things out a lot.

    We run it on a Dell PowerEdge 2650 with 1 CPU right now that's HyperThreaded so it's supposely two but I say it's 1.75. We are running Red Hat with Kernel 2.6 on it which really helped out with hyperthreading and memory management.

    One thing that makes the database so large is that we keep all the input files that we use for processing inside the actual database. I know there has been a lot of discussion all over the web about it, but it seemed that the consenses was that it's fine to keep it in there. Allows for easier clustering, etc.

    It seems I did a little bit of tuning to speed it up. Definitely the new kernel helped along with raising the key_cache and table_cache to match up with the size of tables. I just started this job, so I am really trying to learn and go everything they have. The next thing after the machine is running well is to look at all the queries they are using..
     
  6. CompiledMonkey

    CompiledMonkey New Member

    Joined:
    Oct 26, 2001
    Messages:
    8,528
    Likes Received:
    0
    Location:
    Richmond, VA
    I bet the OT database is rather small. Fazle dumps old threads very often.
     
  7. Astro

    Astro Code Monkey

    Joined:
    Mar 18, 2000
    Messages:
    2,047
    Likes Received:
    0
    Location:
    Cleveland Ohio
    Wait. You've got ALL the input files IN the database?

    Databases are good for storing and retrieving data and turning it into information.

    File systems are great for storing and retrieving files (file system level caching, etc). And files in a database or a file system are still just files - there's no tweaking of their data to turn them into "information."

    Pull the files out of the database and store the locally. Then just store the file path in the database.

    Optimizing the hardware and software the database is running from is one thing. I'd recommend looking at the queries you're running and seeing where the bottleneck is at. Its possible you need to normalize the data if its not normalized. Or if it is normalized, you may need to denormalize it to streamline the queries.

    I've got a 4gb MySQL database running on a now considered outdated desktop box which is now doing light video editing work. Its performing great, but I've also had to optimize the data for quick reporting (this is all raw data - no files loaded in the database).
     
  8. Astro

    Astro Code Monkey

    Joined:
    Mar 18, 2000
    Messages:
    2,047
    Likes Received:
    0
    Location:
    Cleveland Ohio
    Oracle is industrial strength and runs circles around MySQL when it comes to performance and features (and price!).
     
  9. CompiledMonkey

    CompiledMonkey New Member

    Joined:
    Oct 26, 2001
    Messages:
    8,528
    Likes Received:
    0
    Location:
    Richmond, VA
    :werd:
     
  10. Joe_Cool

    Joe_Cool Never trust a woman or a government. Moderator

    Joined:
    Jun 30, 2003
    Messages:
    299,154
    Likes Received:
    500
    This is some kind of a :greddy: joke, right?
     
  11. Joe_Cool

    Joe_Cool Never trust a woman or a government. Moderator

    Joined:
    Jun 30, 2003
    Messages:
    299,154
    Likes Received:
    500
    Lemme get this straight. You think Oracle, ($40,000 per CPU or $800 per user) has a better pricing structure than MySQL which is released under the GNU General Public License and is therefore freely distributable and costs $0 per license?

    :rofl:
     
  12. CompiledMonkey

    CompiledMonkey New Member

    Joined:
    Oct 26, 2001
    Messages:
    8,528
    Likes Received:
    0
    Location:
    Richmond, VA
    I think you interpreted that differently than everyone else. He’s talking about industrial strength pricing, not that it’s cheaper than MySQL.
     
  13. Joe_Cool

    Joe_Cool Never trust a woman or a government. Moderator

    Joined:
    Jun 30, 2003
    Messages:
    299,154
    Likes Received:
    500
    Elaborate, please? I'm obviously missing something. :o
     
  14. CompiledMonkey

    CompiledMonkey New Member

    Joined:
    Oct 26, 2001
    Messages:
    8,528
    Likes Received:
    0
    Location:
    Richmond, VA
    I really don't know how else to explain it. :dunno: :o
     
  15. CyberBullets

    CyberBullets I reach to the sky, and call out your name. If I c

    Joined:
    Nov 13, 2001
    Messages:
    11,865
    Likes Received:
    0
    Location:
    BC, Canada/Stockholm, Sweden
    i swore MySQL changed their licencing and now charges businesses that use their product? :dunno?
     
  16. Joe_Cool

    Joe_Cool Never trust a woman or a government. Moderator

    Joined:
    Jun 30, 2003
    Messages:
    299,154
    Likes Received:
    500
    I know they have corporate support contracts, but whatever the cost, I'm willing to bet it's less than just the licensing fees for Oracle.
     
  17. P07r0457

    P07r0457 New Member

    Joined:
    Sep 20, 2004
    Messages:
    28,491
    Likes Received:
    0
    Location:
    Southern Oregon
    nope. They released under a "dual-license". As long as you do not distribute mysql with your product, you can use it free-of-charge.

    from : http://www.mysql.com/company/legal/licensing/opensource-license.html
     
  18. Joe_Cool

    Joe_Cool Never trust a woman or a government. Moderator

    Joined:
    Jun 30, 2003
    Messages:
    299,154
    Likes Received:
    500
    Just to be clear, since the software is GPL, while they can charge for it, they can't stop you from downloading a copy and using it.
     
  19. Joe_Cool

    Joe_Cool Never trust a woman or a government. Moderator

    Joined:
    Jun 30, 2003
    Messages:
    299,154
    Likes Received:
    500
    Wait, it's not GPL? :uh: I thought it was.
     
  20. RaginBajin

    RaginBajin Have you punched a donkey today?

    Joined:
    Dec 24, 2001
    Messages:
    8,740
    Likes Received:
    0
    Location:
    NoVA

    The whole idea of keeping the data in the database or not is really a tough one. A lot of different people say differently. Some say it's great to put it in the db and some say don't. So I don't know what the real answer is. It almost seems to be like a chevy vs ford type of battle. I need a way to demostrate that keeping the files out of the db is the best way to go.


    It's really bad since a lot of people don't use MYSQL as much as they use oracle to get a real feeling for it. Even the people that use Oracle in large DB's don't really talk about the DB and how they keep their data. Maybe they do and I just haven't found the right outlet.
     
  21. Chaotic Reality

    Chaotic Reality New Member

    Joined:
    Jun 22, 2004
    Messages:
    29,052
    Likes Received:
    0
    Location:
    Phoenix, AZ
    heh i'd say that the server is what is slow, not the database.
     
  22. CompiledMonkey

    CompiledMonkey New Member

    Joined:
    Oct 26, 2001
    Messages:
    8,528
    Likes Received:
    0
    Location:
    Richmond, VA
    Free.
     
  23. Joe_Cool

    Joe_Cool Never trust a woman or a government. Moderator

    Joined:
    Jun 30, 2003
    Messages:
    299,154
    Likes Received:
    500
    I double-checked, and MySQL is GPL software. That means you are free to download, copy, distribute, modify, and even sell it.

    Download it free of charge from www.mysql.com
     

Share This Page