Loading...
Answers
MenuWhat are the best ways to maximize database performance without simply adding tons of DRAM?
I have a large database (~180GB data + indexes; a couple very tall but thin tables of ~200-400m rows, and two ~20m "wide" tables) and don't have the resources to simply throw money at the problem and buy tons of RAM. It's running on a dedicated box with 2x500GB 15k drives in RAID1 and 32GB DDR2 memory. For some operations, it's fine now, but since the indexes don't fit in memory, if the sections of interest to a particular query haven't been recently read it can be slow as hell. Percona server 5.5 + InnoDB. I've tuned the InnoDB settings. Are there any good hacks or tips?
Answers
Can you describe the data in the table? I can think of a few solutions, most if which are based on the data you are storing.
For example, chances are when you have large tables you are probably not doing any summarization of your data. If possible pre-compute the result and spit out the answer from a shorter table or cache (a cache which is generate at the time of the summary being generated, only useful if you often request this data shortly after it being created - doesn't sound like you needed to do this though)
Another alternative would to be consider archiving of data. Is all the data required all the time? If not then consider a means of removing data you don't need (after x period of time) or switching to a new shard / table periodically.
The other, more obvious, I would mention are to reconsider your existing indexes or partitioning your tables, if you have not done this already.
You can start manually sharding tables across servers based on a hash of some kind but your Db is still relatively small (assuming you can summarize some of your data) and should not need this yet.
Assessing your IO, Memory & CPU loads is the first step - Knowing where the bottleneck is, is helpful for suggesting strategy.
Often times joins are handy when you first create your DB as it pulls your data back in a tidy normalized result. As your data grows those joins can become troublesome because you are joining multiple large tables (meaning index lookups/loops through all the pertinent indexes), plus you can be bound to a single thread. Sometimes breaking that joined query into multiple queries that leverage separate threads as well as free up memory and the number of combined index's referenced at an instant between calls can be useful. I have not used Percona however, so it may have its own functionality that mitigates these concerns.
Regardless of the number of rows tables have, it's how this data is assigned on disk (and how big that is), and then read into the applicable MySQL memory buffers that matter. The goal is to reduce the memory to disk ratio for "Hot" data. Optimizing indexes is an art. There are also many tricks, like using the right data type (INT not BIGINT), covering indexes (reduces looking at data). Depending on your queries, partitioning may help. Capturing the Query Execution Plan (QEP), learning how to read, understand and improve is necessary to solve your slow queries. http://effectivemysql.com/ has a number of introduction presentations for Optimizing SQL.
Related Questions
-
Can I efficiently sum/count total clicks/conversions from a separate table?
This is a question better suited for stackoverflow. If you message me the schema for the two tables I could give you the exact query that you want.DP
-
How do I deploy a Play Framework Scala app on AWS?
Here is an article about setting up AWS EC2 to run Play and Mongo: http://stevenwilliamalexander.wordpress.com/2012/06/10/amazon-ec2-mongo-play-scala-instance-setup-gu/ and there are also guides for binding to Amazon Simple DB and Dynamo in Scala as well that might be much easier setup than using MySQL. I've used a lot of AWS services for building composite services and I'm available for a call if you get stuck or need any advice on best practices.AW
-
how to make google assistant ask a question 12 Replies
I am trying to figure out how to make the Google Assistant ask me a question when externally
I am not sure what you are trying to ask but if you are asking how to get your google assistant to ask questions (and act on the replies perhaps?). You do so by building Google Actions. Google Actions are just like mobile apps but is made available in Google Assistant when a user ask for a particular action via an Invocation such as Ok Google, Talk to Keep Count. The Google Action called Keep Count would then take over. There in the action you can program it so that the google assistant interact with the user via a series of question and replies. You can find out how to do Google Action by visiting the Google Assistant developer website at https://developers.google.com/assistant. Happy to jump on a call if you need more specific directions. I am an expert in the field of Voice Technology. You can also learn from live streams on twitch at twitch.tv/goldzulu. Hope that helps.V"
the startups.com platform
Copyright © 2025 Startups.com. All rights reserved.