Speed problem - Mysql - innodb

[sridars]

Hi,

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.

[sterin]

Do you have anything tangible to present?

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

[sridars]

Hi,

there is no query involved, everything is in table component in Delphi and not a query component with SQL statements.

There is a problem in speed, so i want to increase the speed like in paradox.

I don’t know where should i fine tune to improve the performance.

Suggestions are welcome.

thanks

[sridars]

Hi,

there is no query involved, everything is in table component in Delphi and not a query component with SQL statements.

There is a problem in speed, so i want to increase the speed like in paradox.

I don’t know where should i fine tune to improve the performance.

Expecting some suggestions from our group regarding my speed problem.

innodb configuration : my.ini file (base file is used)
hardware configuration : 2.4ghz, 2gb RAM, intel pentium 4 CPU

thanks

[sterin71]

And with “base file” do you mean my-small.ini or?

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.

[sridars]

Thanks I shall check it out

[beenakm]

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® 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

[sridars]

Hi,

Thanks

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?

Please reply

thanks

[sridars]

Hi,

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.

Thanks in advance.

[sterin]

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.

And beenakms post with the:

[B]Quote:[/B]

innodb_buffer_pool_size: (not present) innodb_additional_mem_pool_size: (not present)

I do not know what it means since he says:

[B]Quote:[/B]

mysql database contains more innodb tables

he should have a setting for these two variables since the default is far to small for any decent size database.

[sridars]

Hi,

Thanks for your info. i will check for more index options for my tables.

i have set

innodb_buffer_pool_size=1g
innodb_additional_mem_pool_size = 16M

i couldn’t set more than 1gb on innodb_buffer_pool_size. if i set more then MySql is not starting up. Do you have more suggestions.

Sysem configuration is :
intel® Xeon® CPU 5160 @ 3.00 GHZ, 6GB RAM

my.ini settings are :
default-character-set=latin1
default-storage-engine=INNODB
sql-mode=" STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTI ON "
max_connections=100
max_connect_errors = 10
table_open_cache = 2048
max_allowed_packet = 16M
binlog_cache_size = 1M
max_heap_table_size = 64M
query_cache_size = 64M
query_cache_limit = 2M
ft_min_word_len = 4
tmp_table_size=64M
log-bin=mysql-bin
transaction_isolation = REPEATABLE-READ
binlog_format=mixed
slow_query_log
long_query_time = 2
thread_cache_size=8
innodb_additional_mem_pool_size = 16M
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=8M
innodb_buffer_pool_size=1g
innodb_data_file_path = ibdata1:10M:autoextend
innodb_file_io_threads = 4
innodb_log_file_size=24M
innodb_thread_concurrency=16
thread_concurrency = 8
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 120

[mysqldump]

Do not buffer the whole result set in memory before writing it to

file. Required for dumping very large tables

quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash

Only allow UPDATEs and DELETEs that use keys.

#safe-updates

[myisamchk]
key_buffer_size = 512M
sort_buffer_size = 512M
read_buffer = 8M
write_buffer = 8M

[mysqlhotcopy]
interactive-timeout

[mysqld_safe]

Increase the amount of open files allowed per process. Warning: Make

sure you have set the global system limit high enough! The high value

is required for a large number of opened tables

open-files-limit = 8192

---

Give me suggestions to imrpove the speed/performance

Thanks