Achieving the best possible MySQL Performance doesn’t have to be complicated. It’s all about knowing which tools are designed for the task at hand – along with some basic (yet often overlooked) best practices.
Join me Wednesday, May 14 at 10 a.m. Pacific for a free webinar titled, “Practical MySQL performance optimization.” I’ll be sharing the main areas for improving MySQL performance – along with what to specifically focus on in each. These will include:
- Hardware
- MySQL Configuration
- Schema and Queries
- Application Architecture
And as I mentioned earlier, I’ll also show you the best tools for the job and how to use them efficiently. This will help you optimize your time by focusing on the queries that are most important for your application.
At the end of this webinar, you will know how to optimize MySQL performance in the most practical way possible. The webinar is free but I recommend registering now to reserve your spot. I hope to see you on May 14!
Dear Peter,
I really like your article. Right now, I have this jsp web portal in tomcat6.5 running on CentOS machine. The database is MySQL. There is one table that is populating about 25,000 records everytime someone runs the application. I don’t do the insert because that is done by a java program, but I have to do a single select statement on the table(select number as thenumber, convert((hex(value)) using ascii)as filevalue, description as desc from table). Right now, the table is running a little bit slow because of the large amount of data. I was wondering if there’s a way to improve the performance on the query or is there’s a way to improve it on MySQL server. I read some of your posing and saw a select statement using between – would that help with? I know in Oracle you can do it by processing data by records – does mysql do something similar to oracle? Comments are welcome! Thank you!
Cheers,
Sam
Hi Peter,
Currently i am facing major mysql performance issues. Few tables in the DB has almost 2 Millions of records and all the searchable tables has proper indexed but still its impacting application performance. Slow query log is enabled and set it to 1 Sec but its blank. I tried to increased memory buffer and other variable memory values but no luck so far. Here is status variable values & warnings which is in red color,
+ Handler read rnd – 1.3 M : The number of requests to read a row based on a fixed position. This is high if you are doing a lot of queries that require sorting of the result. You probably have a lot of queries that require MySQL to scan whole tables or you have joins that don’t use keys properly.
+ Handler read rnd next – 1.2 M : The number of requests to read the next row in the data file. This is high if you are doing a lot of table scans. Generally this suggests that your tables are not properly indexed or that your queries are not written to take advantage of the indexes you have.
+ Innodb buffer pool reads – 8.7 k : The number of logical reads that InnoDB could not satisfy from buffer pool and had to do a single-page read.
+ Opened tables – 20 : The number of tables that have been opened. If opened tables is big, your table cache value is probably too small.
+ Sort merge passes – 2 : The number of merge passes the sort algorithm has had to do. If this value is large, you should consider increasing the value of the sort_buffer_size system variable.
Please help me out.