Solved

Fine tuning MySQL for better performance with Java Application

Posted on 2008-09-30
6
288 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
6 Comments
 
LVL 25

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 25

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 25

Accepted Solution

by:
Tomas Helgi Johannsson earned 250 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Why my table column Id is not passed to java object? 4 39
MySQL Error Code 2 21
Running JavaFX on JDeveloper 12C 1 33
CSV file parsing thru Java 13 31
As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
Viewers learn about the third conditional statement “else if” and use it in an example program. Then additional information about conditional statements is provided, covering the topic thoroughly. Viewers learn about the third conditional statement …
Viewers learn about the “while” loop and how to utilize it correctly in Java. Additionally, viewers begin exploring how to include conditional statements within a while loop and avoid an endless loop. Define While Loop: Basic Example: Explanatio…

832 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