[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

MySql optimization for large tables - memory requirements

Posted on 2006-05-25
4
Medium Priority
?
437 Views
Last Modified: 2008-02-01
I have mySQL and PHP running on a single dedicated server hosting a web application and will have aprox 10 - 30 concurrent connections.  The database contains a person table with 12 million records (2GB) and an address table with 8 million records (1.5GB).  The entire database including the other tables will be about 5 - 6 GB.  I am currently running a default installation of XAMPP and have not optimized any memery setting in mySQL.  The server has 1.5GB installed.  I know I am not giving enough information for a specific answer, but are there any obvious issues or bottlenecks limiting performance?  Is 1.5GB way to small for this database?  Most queries are optimized to use indexes but occasionally someone will run something that is not optimized and seems to bring things to a halt.
0
Comment
Question by:doughoman
2 Comments
 
LVL 29

Accepted Solution

by:
fibo earned 1000 total points
ID: 16767880
No idea on the hardware side of your question.
Just looking at "occasionally someone will run something that is not optimized and seems to bring things to a halt". Have you some info on these non-optimized queries? You might look more closely to them, in case some of them might benefit from an additional index.

You did not mention if your base is just running SELECTs or if you have a significant INSERT or UPDATE activity.
If mainly SELECTs, you may check (eg, using stats from phpMyAdmin) if some of your could be "optimized", ie records physically reorganized so that they would match the "natural order" of ONE of the indexes, the one you would select when doing this optimization.
0
 
LVL 22

Assisted Solution

by:NovaDenizen
NovaDenizen earned 1000 total points
ID: 16769442
It's always possible for a database user to issue a poorly-designed (or malicious) query that will bring your server to its knees.  If you can't trust someone to be responsible, then you need to force them to work through an api, use stored procedures, or set up a replication slave server that they can bog down to their heart's content.
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
In this article, we’ll look at how to deploy ProxySQL.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses
Course of the Month18 days, 11 hours left to enroll

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question