# MySQL Database performance issue-urgent help plz

**URL:** <https://forums.percona.com/t/mysql-database-performance-issue-urgent-help-plz/1572>\
**Category:** Other MySQL® Questions\
**Created:** [December 3, 2010, 7:04am UTC](https://forums.percona.com/t/mysql-database-performance-issue-urgent-help-plz/1572 "2010-12-03T07:04:20Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![Mohammed786](https://avatars.discourse-cdn.com/v4/letter/m/3bc359/32.png) [@Mohammed786](https://forums.percona.com/u/Mohammed786)\
**Post date:** [December 3, 2010, 7:04am UTC](https://forums.percona.com/t/mysql-database-performance-issue-urgent-help-plz/1572/1 "2010-12-03T07:04:20Z")

</div>

Hi,

We have MYSQL database and having very bad performance issue. Please help me to resolve.

# Here is configuration details

Server: Windows Server 2003

Edition: Standard Edition

Services Pack: Service pack 1

CPU: Intel Xenon(R) CPU E5320

Processor: 1.86 GHz

# RAM: 16 GB

# Software Configuration:

MYSQL server database Version: 5.0

Here is my.ini file which is located under the C:\Program Files\MySQL\MySQL Server 5.0\

[mysql]

default-character-set=latin1

[mysqld]

# The default storage engine that will be used when create new tables when

default-storage-engine=INNODB

# Set the SQL mode to strict

sql-mode=" STRICT\_TRANS\_TABLES,NO\_AUTO\_CREATE\_USER,NO\_ENGINE\_SUBSTITUTI ON "

max\_connections=100  
query\_cache\_size=0  
table\_cache=256  
tmp\_table\_size=18M  
thread\_cache\_size=8

#\*\*\* MyISAM Specific options

myisam\_max\_sort\_file\_size=100G  
myisam\_max\_extra\_sort\_file\_size=100G  
myisam\_sort\_buffer\_size=35M  
key\_buffer\_size=25M  
read\_buffer\_size=64K  
read\_rnd\_buffer\_size=256K  
sort\_buffer\_size=256K

#\*\*\* INNODB Specific options \*\*\*

# #skip-innodb innodb\_additional\_mem\_pool\_size=2M innodb\_flush\_log\_at\_trx\_commit=1 innodb\_log\_buffer\_size=1M innodb\_buffer\_pool\_size=47M innodb\_log\_file\_size=24M innodb\_thread\_concurrency=10

# ================= Database Details:

Size of Database: around 2 GB

Databaes Engine: INNODB

Number of Tables: 15

Maximum count row in each table:

> select count(\*) from audit\_trail:

output:

385567

# table size: 91 MB and Index Size is 59 MB

select count(\*) from follwup\_complaints

353988

# table size: 73 MB and Index Size is 50 MB

select count(\*) from mail\_audit;

447237

# table Size: 597 MB and index size is 35 MB

select count(\*) from mis\_reports;

# 148959

Please let me know what will be the optimal memory parameters need to be setup to get good performance.

We are using default my.ini parameter file.

The database response is very poor,

Thanks

Mohammed.

---

<div class="post-metadata">

**Author:** ![sterin](https://avatars.discourse-cdn.com/v4/letter/s/3ab097/32.png) [@sterin](https://forums.percona.com/u/sterin)\
**Post date:** [December 6, 2010, 7:09am UTC](https://forums.percona.com/t/mysql-database-performance-issue-urgent-help-plz/1572/2 "2010-12-06T07:09:51Z")

</div>

The by far most important setting for you is the innodb\_buffer\_pool\_size which in your case is ridiculously small (47M), it defines how much RAM you allow MySQL to use for cache of InnoDB data. Max recommended size on a dedicated DB server is about 80% of RAM or if the DB is smaller than RAM (as in your case) then you could set it to a smaller value more representative of the actual size of the DB size.

Start by changing to these and restart MySQL:

innodb\_buffer\_pool\_size=2Ginnodb\_additional\_mem\_pool\_size = 16M

And see if it solves your problem.  
Remember that you can look at the example my.ini that comes with the MySQL distribution to get an idea about suitable configuration values.

---

<div class="post-metadata">

**Author:** ![Mohammed786](https://avatars.discourse-cdn.com/v4/letter/m/3bc359/32.png) [@Mohammed786](https://forums.percona.com/u/Mohammed786)\
**Post date:** [December 6, 2010, 1:05pm UTC](https://forums.percona.com/t/mysql-database-performance-issue-urgent-help-plz/1572/3 "2010-12-06T13:05:52Z")

</div>

Sterin,

Thanks for your response and looks like you are expert in MYSQL db because I am looking your all articals and it was very fantastic.

Already I have implemented this parameters from your articals.

Please let me know is there any parameter for all DML statement to improve the performance of database and is there any best practies document available? for database maintanance on weekly basis.

like rebuild indexes and what method we need to use to rebuild the indexes or update the statistics or optimize the table.

Please request you to provide some more details on this.

Thanks in advance for your help.

Thanks

Mohammed.