# MySQL - performance problem **URL:** \ **Category:** Other MySQL® Questions\ **Created:** [October 13, 2010, 8:11am UTC](https://forums.percona.com/t/mysql-performance-problem/1531 "2010-10-13T08:11:01Z")\ **Posts on this page:** 5\ **Page:** 1
**Author:** ![sridars](https://avatars.discourse-cdn.com/v4/letter/s/9e8a1a/32.png) [@sridars](https://forums.percona.com/u/sridars)\ **Post date:** [October 13, 2010, 8:11am UTC](https://forums.percona.com/t/mysql-performance-problem/1531/1 "2010-10-13T08:11:01Z")
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. ---
**Author:** ![sterin71](https://avatars.discourse-cdn.com/v4/letter/s/5e9695/32.png) [@sterin71](https://forums.percona.com/u/sterin71)\ **Post date:** [October 15, 2010, 12:58am UTC](https://forums.percona.com/t/mysql-performance-problem/1531/2 "2010-10-15T00:58:57Z")
| [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. 1. 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! | ---
**Author:** ![sridars](https://avatars.discourse-cdn.com/v4/letter/s/9e8a1a/32.png) [@sridars](https://forums.percona.com/u/sridars)\ **Post date:** [October 15, 2010, 1:11am UTC](https://forums.percona.com/t/mysql-performance-problem/1531/3 "2010-10-15T01:11:34Z")
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 ---
**Author:** ![sterin](https://avatars.discourse-cdn.com/v4/letter/s/3ab097/32.png) [@sterin](https://forums.percona.com/u/sterin)\ **Post date:** [October 15, 2010, 2:04am UTC](https://forums.percona.com/t/mysql-performance-problem/1531/4 "2010-10-15T02:04:15Z")
| [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. | ---
**Author:** ![sridars](https://avatars.discourse-cdn.com/v4/letter/s/9e8a1a/32.png) [@sridars](https://forums.percona.com/u/sridars)\ **Post date:** [October 15, 2010, 2:20am UTC](https://forums.percona.com/t/mysql-performance-problem/1531/5 "2010-10-15T02:20:59Z")
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