MySQL Database performance issue-urgent help plz

MySQL & MariaDB Other MySQL® Questions
1

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.

2

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.

3

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.