MySQL Forums
Forum List  »  Performance

Re: Help in tuning MySQL 5.6 database
Posted by: Rick James
Date: July 18, 2014 12:12PM

> We also don't have any reference/foreign key in tables so we don't use joins.

I hope this does not mean that the data is unnormalized, but needs to be normalized.

FOREIGN KEYs are not a requirement for using JOIN. Suitable index(es) is strongly desired.

> Does the store procedure really improve performance?

General answer: No.
Exception: If the connection is remote and the SP involves multiple SQL statements that would otherwise have to go across a WAN, then an SP is likely to be faster.

> if we changes the values of innodb_buffer_pool_size, read_rnd_buffer_size, sort_buffer_size etc fields in my.ini/my.cnf files
> I don't know what should be the proper values

Yes; see
http://mysql.rjweb.org/doc.php/memory

> other ways to optimize & fine tune MySQL engine

long_query_time = 1
Turn on slowlog
Use pt_query_digest to see which queries are slow.
Then fix the index(es), queries, schema design, etc.

> RAM : 1 GB

Ouch! That is much too small for today. Yes, MySQL will work, but it is likely to spend a _lot_ of time doing I/O, especially on the tables with "some million records".

For 1GB, the link I gave you above does not apply. Instead, consider
innodb_buffer_pool_size = 100M
key_buffer_size = 10M
various other sizes should be, say, 1/4 of the size in the default my.cnf.
After setting them and restarting do SHOW VARIABLES and present it here for further advice.

Keep in mind that swapping is really bad for performance.

> All tables have more than 10 columns with a combination of numeric and textual data. All tables use innodb engine with numeric field as primary key. The tables are indexed on another numeric field, different than the primary key.

Use EXPLAIN SELECT ...; to see if your SELECTs really use the index(es) you have provided.

> We haven't created stored procedure for fetching data.

I prefer to build an application layer (in PHP or VB or Java or ...) to do the things that you might be tempted to do in Stored Procedures.

I'm sorry, but I see too many contradictions between your goal and your specifications:
> enterprise product ... on Windows
> OS : Windows 2000 SP4 Professional / Server; RAM : 1 GB

Options: ReplyQuote


Subject
Views
Written By
Posted
1691
July 17, 2014 05:26AM
Re: Help in tuning MySQL 5.6 database
830
July 18, 2014 12:12PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.