I am migrating my project from paradox to MySql 5.1. after migration of tables (mysql innodb engine) and also adjusted the coding which is written in Delphi 7.0, i am facing serious problem in speed. compare to Paradox it is 5 times slower.
In one place where i found very slow speed is in lookup tables. for example, i am using two tables with 20000 records in each. and these two tables used in two table components separately and proper index name is given in the table component. one table component is used with lookup field of key
information with other table to take the description.
Now when i navigate the records from first record to last record in both the table components it is taking 15 seconds but the same thing is used with paradox tables it is flashing in 2 secs of 40000 records in total loop.
when i don’t use index name in table components of paradox then it is taking 35 seconds. so here the difference is, when i use index name in the table component then it is flashing, but in mysql whether i use index name or no index name in the table components the result is same.
Expecting some solutions from our group as soon as possible. I am medding with this problem for the past 1 week.
Like what your query looks like?
What your table looks like (return output from SHOW CREATE TABLE yourTable)?
The execution plan of your query (return output from EXPLAIN EXTENDED [your query here]?
InnoDB configuration?
Hardware specs?
etc
Anyway the main parameter that you have to check is:
innodb_buffer_pool_size (should be set to 50-80% of the available memory or if you have a smaller database you can just set it large enough so that the database can fit into it.) If this is to small you will have bad performance with InnoDB tables.
But I would suggest that you turn on query logging in MySQL so you can see what query Delphi is actually sending to MySQL and then you can use this query to find out the execution plan and if you can improve anything on the DB design.
Because as long as you don’t break down where the performance problem actually is, you don’t know if it’s because Delphi does something odd when it accesses MySQL compared to Paradox, or if it actually is the queries issued against MySQL that is the problem.
I am using a dedicated server for my website ( Magneto webstore) amd mysql database contains more innodb tables. My site has around 5000 vistors/day and handling 8 lakhs apache request/day.
The server has 8 CPUS
Xeon(R) CPU 2.67 Ghz
Memory is 8Gb.
In mysql configuration file has the entries like
key_buffer: 128M
max_allowed_packet: 16M
table_cache: 4096
sort_buffer_size: 4M
read_buffer_size: 4M
read_rnd_buffer_size: 4M
myisam_sort_buffer_size: 32M
thread_cache_size: 128
query_cache_type: 1
query_cache_size: 196M
log_slow_queries=/var/log/mysqld.slowquery.log (not present)
max_connections: 125
wait_timeout: 3600
tmp_table_size: 128M
max_heap_table_size: 128M
innodb_buffer_pool_size: (not present)
innodb_additional_mem_pool_size: (not present)
But, when online visitors is increases, the server load will increase with range of 50,60,70 and server will going to slow
and will shows the error as ‘Too many connections’. How i set mysql configuration file to increase mysql speed and reduce the server load and connection problem
I have configured your kind of settings in my.ini. I have stopped MySql and configured it in my.ini file and then started again. even now the results seems to be same.
Should i need to run any plug-ins to take the new configurations?
I have configured the same kind of settings, the result was same as before i.e. slow speed. but i have reimported my databases from my old database (paradox) to mysql after configuring the my.ini file, then the result is quite good, i could see lot of improvements in the speed.
Now my question is, after configuring the my.ini file, should we necessarily need to reimport the data again to the MySQL database or is there any other way to upgrade our database to suit with adjusted my.ini file?
Eagerly waitng for valuable answers from our group.
No you don’t have to reimport the data for settings to take effect, a restart is enough.
If things are fast for you after a reimport then the possible reasons is instead that it is fast due to that a reimport automatically “packs” the data in a synchronous fashion.
Which means that running an OPTIMIZE TABLE would probably have solved it for you.
And generally if a OPTIMIZE TABLE makes such a big difference for you I would say that you are lacking indexes.
But my question is what setting you have on the innodb_buffer_pool_size? Since if you are running InnoDB tables then that is the MOST important setting.