?
Solved

Fine tuning MySQL for better performance with Java Application

Posted on 2008-09-30
6
Medium Priority
?
299 Views
Last Modified: 2010-05-18
Hi
previously i asked a question regarding having some issues with connections and have got some fairly reasonable answers

Yesterday i have seen these links regarding 'php' website connected to MySQL and optimising it so as to get better performance...the links for reference are
http://www-css.fnal.gov/dsg/external/freeware/mysqlTuning.html
http://www.getafreelancer.com/projects/sumitp83_302783.html

My application is a Java application and use the MySql and i wish if someone can help regarding similar topics like fine tuning MySQL to work perfectly(or somewhat better) using Java,Tomcat.
you can advice me on persistency,pooling,connection problems,stability,robustness ,faster processing tips etc...

Please provide some solid answers and as some the answers are too vague to actual implement and still requires me to do the homework.thanks
0
Comment
Question by:darien_software
  • 3
4 Comments
 
LVL 26

Expert Comment

by:Tomas Helgi Johannsson
ID: 22612400
      Hi!

I suggest you take a look at the Optimization part of the Manual
http://dev.mysql.com/doc/refman/5.0/en/optimization.html

there you can find all the necessary information to tune your MySQL db to your app.

Regards,
   Tomas Helgi
0
 
LVL 26

Expert Comment

by:Tomas Helgi Johannsson
ID: 22612423
Also what you can do is do an EXPLAIN on your queries ( http://dev.mysql.com/doc/refman/5.0/en/explain.html )
or the log ( http://dev.mysql.com/doc/refman/5.0/en/mysql-explain-log.html ).
If you find in the results that queries are doing TABLESCAN then you should add an index on the
matching columns in the query's where clause to change it into INDEXSCAN for optimal performance on the table.

Regards,
   Tomas Helgi
0
 

Author Comment

by:darien_software
ID: 22624703
hi  tomas
thanks for the details i will look into the material u provided
well i want some details like
what are the parameters have to be changed,a prose sort of to identify the factors which help in fine tuning mysql.
so that then it will be useful to get in details as mentioned in ur link
so if anybody else has any answers please let me know
thanks
0
 
LVL 26

Accepted Solution

by:
Tomas Helgi Johannsson earned 1000 total points
ID: 22624964

How to tune a database depends on how the application works i.e connects to
and manipulate the data in the db.
You have to look into the queries and see how they access the data and
configure the bufferpools, create neccesary indexes etc.
Ask your self:
How are the tables organized?
How are the relationship between the tables ?
Do you use MYISAM or InnoDB ?
Do you have massive inserts (batch), updates or deletes ?
Tuning the database to maximize the performance depends on how you
answer those questions as well as other questions related to disk and locking issues.


Regards,
   Tomas Helig

0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

In this post we will learn different types of Android Layout and some basics of an Android App.
In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
Viewers learn how to read error messages and identify possible mistakes that could cause hours of frustration. Coding is as much about debugging your code as it is about writing it. Define Error Message: Line Numbers: Type of Error: Break Down…
Viewers will learn one way to get user input in Java. Introduce the Scanner object: Declare the variable that stores the user input: An example prompting the user for input: Methods you need to invoke in order to properly get  user input:
Suggested Courses
Course of the Month13 days, 15 hours left to enroll

807 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