Drupal and MySQl located in two different servers:
Configuration: Drupal server: Dual core processor, 4GB RAM
MySQL Server: Xeon Processor, 4GB RAM
MySQL Enterprise edition 5
In an exercise to Pre populate the database with 1 million records, record insertions in the tables are very slow since the requests are getting queued up in the database. Insertion is done using a special tool that records our drupal application and plays back the scripts in a loop to populate the database.
Please see the below data collected by the team.
5minutes-25users-1051 records updated
5minutes-50users-1029 records updated
5minutes-100users- 1012 records updated
As users increases, the no of records creation decreases, also after the test has been stopped, it has been noticed that it is updating the database. Moreover if we are planning to run the test for long hours, I am afraid that the queue will increase and make the database to reach unsteady state.
What could be the cause for such an issue, what MySQl tuning is recommended?