Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!


the best server for huge mysql database?
New on LowEndTalk? Please Register and read our Community Rules.

All new Registrations are manually reviewed and approved, so a short delay after registration may occur before your account becomes active.

the best server for huge mysql database?

I'm working on a website have a huge database ~30GB
my current server is :
RAM: 8GB
CPU: Intel Xeon E3-1230 3.20Ghz
HDD: 2x 1TB Raid 1

but I think I will need upgrade that queries is delayed.
I still didn't change the configuration file my.cnf yet and searching for the best configuration for this huge database. But first I need some suggestion and opinion if this will work good or I will need upgrade!
I found the server with online.net with 16GB RAM and it will be in my budget range !! is this will perform faster for this huge database ? or I need to change the server E3-1230 !!
this is query as example from my current server altering table to add primary key

mysql> ALTER TABLE Topics ADD PRIMARY KEY(Id);
Query OK, 8776987 rows affected (2 min 22.06 sec)
Records: 8776987 Duplicates: 0 Warnings: 0

Thank you

Comments

  • Try an SSD?

    Thanked by 1KwiceroLTD
  • Void_Whisperer said: Try an SSD?

    would you please suggest a dedicated specs ?

  • gbshousegbshouse Member, Host Rep

    Your current server should work fine with even 2-3 size database. I would guess that first step should be configuration and data structure optimization. Almost 9 million rows table without primary key? LOL.

    Try to run most common queries using TOAD and check for bottlenecks, adjust and re-test. Read some proper online materials (like Percona's blog or official MySQL documentation) for performance tuning.

    It's easy to upgrade hardware but I think you should start with understanding what's under the hood.

    https://tools.percona.com/

    http://www.percona.com/live/mysql-conference-2013/sessions/mysql-56-performance-benchmarks-tuning-and-“best”-practices

    Thanked by 1KwiceroLTD
  • ztecztec Member

    using percona is highly recommended. If you upgrade to percona you probably don't even need to upgrade your specs.

  • Online.net Dedibox LT Server should help a lot compare your actual configuration (twice the memory, multithreaded CPU ...)
    http://www.online.net/fr/serveur-dedie/dedibox-lt2k14

  • Adding a primary key to a ~9-million-row table is like inserting 9 million rows to an index. Of course it will take some time.

    To solve your slow queries you need to do or you need to find someone who can do performance tuning on your database.

    Throwing hardware at it will not solve the problem.

    Thanked by 1vedran
  • Thank you all for your suggestion ,
    I will try use percona , if still slow I will upgrade to the 16GB server especially my current one at the same datacenter from oneprovider so I can transfer my data easily

  • udkudk Member

    http://mysqltuner.pl is your friend. your current hardware is probably overkill for your needs, learn to optimize/tune SQL

    Thanked by 1netomx
  • Hire a DBA to have a look at your structure and queries.

    Beyond that, have you considered picking up one of these: http://www.ovh.co.uk/dedicated_servers/enterprise/

    And putting your DB in RAM.

  • ztecztec Member

    It might be a stupid question that I might be able to google easily myself. But let's just go for it.

    Do you have a tutorial for me that explains how to put stuff in Ramdisk?

  • Virtovo said: Hire a DBA to have a look at your structure and queries

    thank you for your suggestion, I already made some changes in the database and in the php side and I think it's very fast now.

  • AleksZAleksZ Member
    edited April 2014

    Percona + ram + ssd or 24gb ssh cache + query optimization. Try
    Ovh.com?http://www.soyoustart.com/ie/offers/sys-e32-4.xml

  • I was in a similar situation with a client some months back. Gradually I tried to "audit" the DBs and get a "fair" knowledge by interacting to the App developers, site operators and owners and found that a major part of DB 8gb out of 15gb was a total redundant unused spare log of mailers and coupons and newsletter sort of thing, which never needs to be queried or even retained.
    Though its still under process, but a major chunk has been removed now.

    Hence, while hardware upgrade is an easy chance... as someone already suggested, yet gradually re-structuring and optimizing DB and possibly the Application can be a huge saving and relief in the long run. I know it isn't a remedy to the problem but a step forward in the right direction if you can take.


    By the way... are their any "DBAs" on LET ? would love to know.

  • ricardoricardo Member
    edited August 2015

    100% depends on how your database is used.

    Normally you'd want all indexes to fit in RAM, and naturally, indexes in the right place.

    SSD is great for lots of random reads. For comparison I have a custom program that has a 10GB of index in memory that contains byte offsets to a file. Random reads of the file is around 100-150 with HDD and 2500-3000 with a cheap SSD. Have a HDD handy for logging.

    While hardware helps, often reevaluating how you store and use the data can bring just as much benefit, for instance separating tables for reads and writes, or batch processing to avoid contention.

  • DillybobDillybob Member
    edited August 2015

    What engine INNODB or MYISAM?

  • Trying to setup the Ashley Madison database? /jk

    Thanked by 1yomero
  • BlazeMuisBlazeMuis Member
    edited August 2015

    @Amitz said:
    Trying to setup the Ashley Madison database? /jk

    That database (~4.2GB of emails and ~13GB of usernames and preferences) is running just fine on an Opteron 1381 with 8GB of RAM :P

    Thanked by 3Amitz jar KwiceroLTD
  • it was an old article at that time I remember that the response time for one page was 5-10 seconds and sometimes it takes 20 seconds to display the page
    I used percona and I found that it was so hard to get an SSD because I have a lot of huge files which will need a huge capacity, the problem that some of quires take a a lot of cpu resources then I tried to simplified the queries and collect all the quries in such one simple as it possible , for the pagination it was take some resources so I made some special query for the pagination by not using offset which I found it take a lot of resources , so I make a simple query for pagination to fetch the articles where id > offset limit x
    this was really helped me a lot to save a lot of server resources


    this were so good and the response time was great compared by the first situation
    however after some researches and I found some other solutions which will make the website faster however it was need to some tutorial and studies
    the best solution is rebuilding the website and the database with nodejs and mongodb
    nodejs faster a lot than php
    mongodb faster a lot than mysql / percona

    the real issue which I faced the transformation between mysql database to mongodb and this actually take a lot of time however it's done finally

    now for all my projects I'm using Nodejs and mongodb and I think this is the future

  • rm_rm_ IPv6 Advocate, Veteran

    @joodle I wonder do you have a permission from all those people to publish their E-Mails on a random forum.

    Thanked by 1BlazeMuis
  • now for all my projects I'm using Nodejs and mongodb and I think this is the future

    They're good for proof of concept though highly doubtful you'll get more performance.

  • @rm_ said:
    joodle I wonder do you have a permission from all those people to publish their E-Mails on a random forum.

    Blacked out the emails, forgot that, sorry.

  • ricardo said: They're good for proof of concept though highly doubtful you'll get more performance.

    >

    yes the performance now so good, it just need some patient by using the callback functions :(

  • I'd advice to optimize the code before changing the hardware.

    Use indexes, have good queries and then see how it runs and if you really need better hardware, and if that's the case just change what you need: found the bottleneck and fix it - If it's IO read: more RAM - IO write: an SSD - CPU that's too slow a better CPU...

  • BG32BG32 Member

    I hope you know it's illegal to pocess that stuff

  • @BG32 said:
    I hope you know it's illegal to pocess that stuff

    Actually it's not. I've got about 2 TB of stolen material leaked online. It's in public domain. So fuck your wannabe law shit.

  • @joodle said:

    Damn you, you have a part I don't have. I guess I'll start hunting again.

  • J1021J1021 Member
    edited August 2015

    KwiceroLTD said: Actually it's not. I've got about 2 TB of stolen material leaked online. It's in public domain. So fuck your wannabe law shit.

    How old are you?

    I know you're a resident of the United Kingdom, where the activity you've just described is a criminal offence under the Computer Misuse Act of 1990.

    I'd like to see you tell a judge to "fuck your wannabe law shit".

    Ironically, it seems you're in the business of providing a client information management product to web hosts. With your blatant disregard for the integrity of other people's data, I wouldn't trust you to host a static blog.

Sign In or Register to comment.