MySQL - performance problem

MySQL & MariaDB Other MySQL® Questions
1

Hi,

I have problem with opening a table through table component in Delphi through BDEODBCMySQL.

I have a table with size of 180 MB. It is imported from paradox table to Mysql – innodb plugin. It has around 2.5 lakh records with 150 fields.

It takes minimum 22 seconds to just opening the table through select * from .

My hard configuration is :
4x Intel(R) Xeon(R) CPU 5160 @ 3.00 GHZ, 3.2GB RAM
And I am using innodb plugin.

My current my.ini configuration is :
[mysql]
default-character-set=latin1
[mysqld]
character_set_server = utf8
default-storage-engine=INNODB
ignore-builtin-innodb
plugin-load=innodb=ha_innodb_plugin.dll;innodb_trx=ha_innodb _plugin.dll;innodb_locks=ha_innodb_plugin.dll;innodb_lock_wa its=h
a_innodb_plugin.dll;innodb_cmp=ha_innodb_plugin.dll;innodb_c mp_reset=ha_innodb_plugin.dll;innodb_cmpmem=ha_innodb_plugin .dll;
innodb_cmpmem_reset=ha_innodb_plugin.dll
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_type=0
query_cache_size=0
query_cache_limit=0
table_cache=64
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=0
thread_cache_size=8
innodb_additional_mem_pool_size=2M
innodb_flush_log_at_trx_commit=0
innodb_log_buffer_size=8M
innodb_buffer_pool_size=1G
innodb_read_io_threads=1
innodb_write_io_threads=1
innodb_data_file_path=ibdata1:400M: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=50
innodb_doublewrite=0
skip_name_resolve
[mysqldump]
quick
max_allowed_packet=16M
[mysql]
no-auto-rehash
[mysqlhotcopy]
interactive-timeout
[mysqld_safe]
open-files-limit=8192

Can anybody please suggest me good my.ini configuration ?

Thanks in advance.

2
[B]sridars wrote on Wed, 13 October 2010 15:41[/B]

It takes minimum 22 seconds to just opening the table through select * from .

1. Do you really want _all_ data from that table? That means that all this data has to be exported from MySQL through ODBC to BDE, usually with a bit of type conversion on the way. Use WHERE or LIMIT to limit the number of rows.

Try and run that query directly on the MySQL server without BDE->ODBC and check the speed.
Because my initial feeling is that I don’t think that MySQL config is your problem in this case.

I don’t use BDE but there has been quite a few posts here with people having performance issues with it so try searching this forum.

Good luck!

3

Hi,

Thanks for your reply.

Actually i need all the data, whether i am using it or not. i need to open a table component which is connected with a table, so there is no restrictions to be given.

also i have checked directly the query in Mysql server(query browser) with 'select * from it takes mininum 20 seconds to fetch all the records.

Anyway i will try checking any configuration to be done in Odbc or BDE.

Thanks

4
[B]sridars wrote on Fri, 15 October 2010 08:41[/B]

also i have checked directly the query in Mysql server(query browser) with 'select * from it takes mininum 20 seconds to fetch all the records.

And that is when you run the query browser on the same machine as the database, right?

What happens on the server when you issue the query?
High CPU or high I/O?

Are you sure that the table size is 180MB?
With a fast calculation I get:
180MB / 250,000 (if I understand lakhs right) / 150 fields = 4.8 bytes per field which sounds very small.

5

Hi,

I have 4 CPU’s, so i don’t see exact CPU usage. i have local machine which is single CPU there with same kind of data, i see it is showing 100% CPU Usage when i run the query.

It is the same machine with client/server.

Do you have any idea of ODBC advance options settings. there i could see an option called “Don’t cache result (forward only cursors)”, when i tag this option, it is very very speed, even in 1 sec i could open the table. but the problem is, i cannot open another table at the same moment, i need to close the first one then only i could open the second table. also while closing the first table, it takes minimum 1 seconds.

Kindly give me some idea related ODBC and also speed.

Thanks