seeking MySql guru
- Thread starter fan27
- Start date
OK MySQL and web based. Pray tells us more about the system/software architecture.
Are you Java/C/PHP/Pyphon/Ruby & Rails e.t.c ???
What are you using for O/R mapping? How many end users, How many concurrent DB connections, what are you using for DB pooling. Is your SQL dynamic (i.e generated via the O/R mapping layer) or static.
How is you data sliced if at all. What type of transaction management have you implemented ? Where is your performance issue?
This link is to a project I've implemented in MySQL. You can download the DB and the DDL + Schema are included in the docs dir.
http://code.google.com/p/trade-manager/
I would need to know a lot more info see above before I could give you specific advice. But PM/email me if you think I could help.
James
Are you Java/C/PHP/Pyphon/Ruby & Rails e.t.c ???
What are you using for O/R mapping? How many end users, How many concurrent DB connections, what are you using for DB pooling. Is your SQL dynamic (i.e generated via the O/R mapping layer) or static.
How is you data sliced if at all. What type of transaction management have you implemented ? Where is your performance issue?
This link is to a project I've implemented in MySQL. You can download the DB and the DDL + Schema are included in the docs dir.
http://code.google.com/p/trade-manager/
I would need to know a lot more info see above before I could give you specific advice. But PM/email me if you think I could help.
James
If you're serious about performance, the guys over at Percona are the definitive authority on such matters. They charge a premium rate, but they get the job done.
If you are insistent on doing it yourself, firstly you want to look at your slow query log. Enable slow query logging, check out the queries that are running slow, and see if you can optimize them. Majority of the time, slow database performance with MySQL is a result of improper indexing and improper use of caching. Also, look into the query caching and/or caching layers for your application.
Beyond that, optimization largely comes down to hard drive throughput, potentially clustering or setting up replication, and further hardware modifications. I cannot stress this enough - before going into hardware modification, go into query and application optimization if possible.
Give the guys at Percona a call (http://www.percona.com/), and inquire about the following:
- Analyzing query performance based on slow query log
- What queries can benefit from indexing and by how much
- Are you using the right table engines? If not, what table engine should you use, and what impact might this have on coding (VERY IMPORTANT: Modifying your table engine might require very intricate coding changes if your application relies on certain types of table locking)
- What my.cnf settings are ideal for you given your application and hardware configuration?
- If the application is largely read-based, can MySQL replication help you out? If so, make sure to inquire about data consistency across nodes in real-time if all queries must operate on the must up-to-date data (using replication might result in certain queries returning results based on outdated data if not configured properly. By outdated, we're usually talking seconds, or milliseconds). Keep in mind that MySQL cluster and MySQL replication are NOT the same thing. MySQL cluster uses a table engine called NDB, which does not support all the features of MyISAM and InnoDB such as foreign keys.
Also, liquidweb.com has some very fast ssd based servers. Try benchmarking your server on application on a smart ssd server with a raid configuration from liquidweb - chances are you will see a performance boost. Also, most enterprise-level hosting providers offer support regarding MySQL optimization.
Disclaimer: I do NOT work for liquidweb or Percona.
If you are insistent on doing it yourself, firstly you want to look at your slow query log. Enable slow query logging, check out the queries that are running slow, and see if you can optimize them. Majority of the time, slow database performance with MySQL is a result of improper indexing and improper use of caching. Also, look into the query caching and/or caching layers for your application.
Beyond that, optimization largely comes down to hard drive throughput, potentially clustering or setting up replication, and further hardware modifications. I cannot stress this enough - before going into hardware modification, go into query and application optimization if possible.
Give the guys at Percona a call (http://www.percona.com/), and inquire about the following:
- Analyzing query performance based on slow query log
- What queries can benefit from indexing and by how much
- Are you using the right table engines? If not, what table engine should you use, and what impact might this have on coding (VERY IMPORTANT: Modifying your table engine might require very intricate coding changes if your application relies on certain types of table locking)
- What my.cnf settings are ideal for you given your application and hardware configuration?
- If the application is largely read-based, can MySQL replication help you out? If so, make sure to inquire about data consistency across nodes in real-time if all queries must operate on the must up-to-date data (using replication might result in certain queries returning results based on outdated data if not configured properly. By outdated, we're usually talking seconds, or milliseconds). Keep in mind that MySQL cluster and MySQL replication are NOT the same thing. MySQL cluster uses a table engine called NDB, which does not support all the features of MyISAM and InnoDB such as foreign keys.
Also, liquidweb.com has some very fast ssd based servers. Try benchmarking your server on application on a smart ssd server with a raid configuration from liquidweb - chances are you will see a performance boost. Also, most enterprise-level hosting providers offer support regarding MySQL optimization.
Disclaimer: I do NOT work for liquidweb or Percona.
Baron
ET Founder
It seems like you are putting the cart before the horse. Why don't you put your app into production and if see if there are any bottlenecks based on the traffic you are getting. Then you can analyze metrics like the slow query log to see what issues there are, if any, and what config options can be tweaked to fix those problems. I have been to many MySQL conferences and listened to plenty of experts, and they pretty much will tell you what I just said above.
Thanks for the recommendations.
We have a prototype in place and performance is not quite where we want it for a go live system. The plus to this exercise is I am learning a lot about MySql/SQL as I have been writing simulations to see which query techniques get the fastest results.
My fear though is that I may be overlooking something that may be very obvious to an "expert".
thanks
fan27
We have a prototype in place and performance is not quite where we want it for a go live system. The plus to this exercise is I am learning a lot about MySql/SQL as I have been writing simulations to see which query techniques get the fastest results.
My fear though is that I may be overlooking something that may be very obvious to an "expert".
thanks
fan27
i use Zend framework... its way awesome for Rapid application development.. and alot of people know the methodology behind it and such.. its alot like ruby on rails.. fusbox cfwheels.. etc.. etc..
http://framework.zend.com/manual/en/zend.cache.theory.html
why reinvent the wheel is what i always say...
http://framework.zend.com/manual/en/zend.cache.theory.html
why reinvent the wheel is what i always say...