Remote mysql too slow
Hello, I have been provided a muscular linux server to use as a Mysql server in our organization. The server is located just beside the web server and within the same network. This dedicated server has 8GB RAM, i5 processors and running mysql as service. No apache, php . nothing. All resources are dedicated to mysql only. Mysql version - mysql Ver 14.14 Distrib 5.1.49, for debian-linux-gnu (x86_64) The BIG hitch is; when we connect with this box the web sites become too slow. I have added the following at my.cnf under [mysqld] section ` ` ` ` ` skip_external_locking skip_name_resolve skip_host_cach ` ` ` ` ` ` The sql connection becomes little faster but still it is considerably slow; specially with such a muscular dedicated linx box just for Mysql. Is there anything else which I can add/configure to make the network latecy small or any such mechanism to make the query fast ? I run the mysqltuner directly on the remote mysql server; and here is the result ``` General Statistics -- [--] Skipped version check for MySQLTuner script [OK] Currently running supported MySQL version 5.1.49-3-log [OK] Operating on 64-bit architecture Storage Engine Statistics --- [--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster [--] Data in MyISAM tables: 1G (Tables: 4777) [--] Data in InnoDB tables: 3G (Tables: 5543) [--] Data in MEMORY tables: 0B (Tables: 136) [!!] Total fragmented tables: 5562 Performance Metrics - [--] Up for: 3d 23h 55m 27s (1M q [4.523 qps], 81K conn, TX: 23B, RX: 469M) [--] Reads / Writes: 74% / 26% [--] Total buffers: 2.2G global + 20.4M per thread (150 max threads) [OK] Maximum possible memory usage: 5.2G (66% of installed RAM) [OK] Slow queries: 2% (39K/1M) [OK] Highest usage of available connections: 18% (28/150) [OK] Key buffer size / total MyISAM indexes: 2.0G/268.5M [!!] Key buffer hit rate: 78.2% (5M cached / 1M reads) [OK] Query cache efficiency: 42.0% (327K cached / 778K selects) [!!] Query cache prunes per day: 1993 [OK] Sorts requiring temporary tables: 0% (48 temp sorts / 9K sorts) [!!] Temporary tables created on disk: 39% (91K on disk / 230K total) [OK] Thread cache hit rate: 99% (28 created / 81K connections) [!!] Table cache hit rate: 6% (16K open / 248K opened) [OK] Open file limit used: 36% (11K/32K) [OK] Table locks acquired immediately: 99% (695K immediate / 695K locks) [!!] InnoDB data size / buffer pool: 3.6G/8.0M Recommendations - General recommendations: Run OPTIMIZE TABLE to defragment tables for better performance When making adjustments, make tmp_table_size/max_heap_table_size equal Reduce your SELECT DISTINCT queries without LIMIT clauses Increase table_cache gradually to avoid file descriptor limits Variables to adjust: query_cache_size ( 128M) tmp_table_size ( 100M) max_heap_table_size ( 100M) table_cache ( 16000) innodb_buffer_pool_size (= 3G) Please note, every day mysql optimization as well as repairing is done by a cron at night. I have also tried with the suggestion multiple times before but every time it gives some new suggestion. I have 8GB physical RAM in this server and here is some statistics ` # free -m total used free sharedbuffers cached Mem: 7986 7913 73 0224 6133 -/+ buffers/cache: 1554 6431 Swap: 3813 0 3813 ` And here is the vmstat result with 10 count and 5 sec delay ` # vmstat 5 10 procs ---memory-- ---swap-- -io -system-- cpu r b swpd free buff cache si sobibo in cs us sy id wa 0 0 0 56328 230440 629967600257466 0 0 98 2 0 0 0 55700 230440 629974400 022 226 272 0 0 99 1 0 0 0 55964 230440 629985600 0 314 348 388 0 0 94 5 0 0 0 55452 230440 629995600 061 304 364 0 0 97 2 0 1 0 55592 230440 630042400 0 271 199 257 0 0 96 4 0 0 0 54584 230440 630090800 0 338 342 428 0 0 92 8 0 0 0 54800 230440 630107200 077 119 133 0 0 98 2 0 0 0 53964 230440 630153200 0 617 267 327 0 0 95 4 0 0 0 54468 230440 630154400 0 296 119 116 0 0 99 0 0 0 0 54212 230440 630164800 0 183 361 435 0 0 95 4 ` Is it possible to suggest something to tweak the server / mysql to get a fast remote mysql box ? Thanks -- MySQL General
Re: Remote mysql too slow
Am 09.04.2012 10:57, schrieb J. Bakshi: Hello, I have been provided a muscular linux server to use as a Mysql server in our organization. The server is located just beside the web server and within the same network. This dedicated server has 8GB RAM, i5 processors and running mysql as service. No apache, php . nothing. All resources are dedicated to mysql only. generally this depends on your network connection and yow your queries are written keep in mind that only connect has 15-20% overhead compared with a unix socket and if your network is too slow you notice latency more and more additionally your queries have more impact if you have usually very small results by optimized queries this makles the db-server himslef possibly better suited but keep in mind taht your querie himself must over the wire I run the mysqltuner directly on the remote mysql server; and here is the result [OK] Key buffer size / total MyISAM indexes: 2.0G/268.5M [!!] InnoDB data size / buffer pool: 3.6G/8.0M why are you wasting 2GB of RAM fpr key_buffer while your innodb_buffer_pool is way to small? __ your mysqltuner: [--] Up for: 3d 23h 55m 27s (1M q [4.523 qps], 81K conn, TX: 23B, RX: 469M) [OK] Slow queries: 2% (39K/1M) our mysqltuner: [--] Up for: 2d 10h 45m 2s (92M q [437.607 qps], 28K conn, TX: 12B, RX: 3B) [OK] Slow queries: 0% (1/92M) i do not think that is the cause of your problem but it shows that your queries are badly optimized on some places __ signature.asc Description: OpenPGP digital signature
Re: Remote mysql too slow
On Mon, 09 Apr 2012 11:47:01 +0200 Reindl Harald h.rei...@thelounge.net wrote: Am 09.04.2012 10:57, schrieb J. Bakshi: Hello, I have been provided a muscular linux server to use as a Mysql server in our organization. The server is located just beside the web server and within the same network. This dedicated server has 8GB RAM, i5 processors and running mysql as service. No apache, php . nothing. All resources are dedicated to mysql only. generally this depends on your network connection and yow your queries are written keep in mind that only connect has 15-20% overhead compared with a unix socket and if your network is too slow you notice latency more and more additionally your queries have more impact if you have usually very small results by optimized queries this makles the db-server himslef possibly better suited but keep in mind taht your querie himself must over the wire I run the mysqltuner directly on the remote mysql server; and here is the result [OK] Key buffer size / total MyISAM indexes: 2.0G/268.5M [!!] InnoDB data size / buffer pool: 3.6G/8.0M why are you wasting 2GB of RAM fpr key_buffer while your innodb_buffer_pool is way to small? __ Could you suggest the optimized settings ? Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Remote mysql too slow
Am 09.04.2012 11:56, schrieb J. Bakshi: On Mon, 09 Apr 2012 11:47:01 +0200 Reindl Harald h.rei...@thelounge.net wrote: Am 09.04.2012 10:57, schrieb J. Bakshi: Hello, I have been provided a muscular linux server to use as a Mysql server in our organization. The server is located just beside the web server and within the same network. This dedicated server has 8GB RAM, i5 processors and running mysql as service. No apache, php . nothing. All resources are dedicated to mysql only. generally this depends on your network connection and yow your queries are written keep in mind that only connect has 15-20% overhead compared with a unix socket and if your network is too slow you notice latency more and more additionally your queries have more impact if you have usually very small results by optimized queries this makles the db-server himslef possibly better suited but keep in mind taht your querie himself must over the wire I run the mysqltuner directly on the remote mysql server; and here is the result [OK] Key buffer size / total MyISAM indexes: 2.0G/268.5M [!!] InnoDB data size / buffer pool: 3.6G/8.0M why are you wasting 2GB of RAM fpr key_buffer while your innodb_buffer_pool is way to small? __ Could you suggest the optimized settings ? mysqltuner did innodb_buffer_pool is in the best case as large as the database what is mostly not possible but it makes clear that as big as possible is the best value key_buffer_size does never need to be bigger as the size of all keys, usually it can even be smaller without negatvie imapct - so reduce it to 200 MB and you have automatically 1800 MB additionally for innodb_buffer_size __ additionally there must be other buffers way too large [--] Total buffers: 2.2G global + 20.4M per thread (150 max threads) we have 1.2 MB per theard on the big inno_db-dbmail-server and 3.2 MB per theard on the big webserver for some hundret domains making the follwojing settings useless too big has a negative impact because the memory has to be assigend for every connection which is overhead and as long the query can not benefit from it it will be only useless overhead key_buffer_size = 256M sort_buffer_size= 512K myisam_sort_buffer_size = 5M join_buffer_size= 2M preload_buffer_size = 256K read_buffer_size= 256K read_rnd_buffer_size= 256K signature.asc Description: OpenPGP digital signature
Re: Remote mysql too slow
On Mon, 09 Apr 2012 12:06:42 +0200 Reindl Harald h.rei...@thelounge.net wrote: Am 09.04.2012 11:56, schrieb J. Bakshi: On Mon, 09 Apr 2012 11:47:01 +0200 Reindl Harald h.rei...@thelounge.net wrote: Am 09.04.2012 10:57, schrieb J. Bakshi: Hello, I have been provided a muscular linux server to use as a Mysql server in our organization. The server is located just beside the web server and within the same network. This dedicated server has 8GB RAM, i5 processors and running mysql as service. No apache, php . nothing. All resources are dedicated to mysql only. generally this depends on your network connection and yow your queries are written keep in mind that only connect has 15-20% overhead compared with a unix socket and if your network is too slow you notice latency more and more additionally your queries have more impact if you have usually very small results by optimized queries this makles the db-server himslef possibly better suited but keep in mind taht your querie himself must over the wire I run the mysqltuner directly on the remote mysql server; and here is the result [OK] Key buffer size / total MyISAM indexes: 2.0G/268.5M [!!] InnoDB data size / buffer pool: 3.6G/8.0M why are you wasting 2GB of RAM fpr key_buffer while your innodb_buffer_pool is way to small? __ Could you suggest the optimized settings ? mysqltuner did innodb_buffer_pool is in the best case as large as the database what is mostly not possible but it makes clear that as big as possible is the best value key_buffer_size does never need to be bigger as the size of all keys, usually it can even be smaller without negatvie imapct - so reduce it to 200 MB and you have automatically 1800 MB additionally for innodb_buffer_size __ I have reset these two now ` ` ` ` key_buffer_size=200M innodb_buffer_pool_size=4G ` ` ` ` After restarting the mysql; the mysqltuner suggest as following Storage Engine Statistics --- [--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster [--] Data in MyISAM tables: 1G (Tables: 4777) [--] Data in InnoDB tables: 3G (Tables: 5543) [--] Data in MEMORY tables: 0B (Tables: 136) [!!] Total fragmented tables: 5570 Performance Metrics - [--] Up for: 5m 6s (27K q [90.712 qps], 4K conn, TX: 42M, RX: 7M) [--] Reads / Writes: 87% / 13% [--] Total buffers: 438.0M global + 20.4M per thread (150 max threads) [OK] Maximum possible memory usage: 3.4G (43% of installed RAM) [OK] Slow queries: 0% (130/27K) [OK] Highest usage of available connections: 19% (29/150) [OK] Key buffer size / total MyISAM indexes: 200.0M/269.4M [OK] Key buffer hit rate: 99.2% (26K cached / 200 reads) [OK] Query cache efficiency: 21.9% (4K cached / 20K selects) [OK] Query cache prunes per day: 0 [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 78 sorts) [OK] Temporary tables created on disk: 24% (67 on disk / 270 total) [OK] Thread cache hit rate: 99% (29 created / 4K connections) [OK] Table cache hit rate: 24% (10K open / 41K opened) [OK] Open file limit used: 29% (9K/32K) [OK] Table locks acquired immediately: 100% (13K immediate / 13K locks) [!!] InnoDB data size / buffer pool: 3.6G/8.0M Recommendations - General recommendations: Run OPTIMIZE TABLE to defragment tables for better performance MySQL started within last 24 hours - recommendations may be inaccurate Variables to adjust: innodb_buffer_pool_size (= 3G) NOTE: Now the max memory usage has decreased ` ` ` ` Maximum possible memory usage: 3.4G (43% of installed RAM) ` ` ` ` The remote mysql has not become faster but I wounder what is the actual impact ? should I comment [ innodb_buffer_pool_size=4G ] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Remote mysql too slow
You may want to ensure the nic is connected to the network at the right speed and duplex. It seems that every new server I get now has to have the speed and duplex explicitly set instead of auto negotiate. Sent from my iPad On Apr 9, 2012, at 4:00 AM, J. Bakshi joydeep.bak...@infoservices.in wrote: Hello, I have been provided a muscular linux server to use as a Mysql server in our organization. The server is located just beside the web server and within the same network. This dedicated server has 8GB RAM, i5 processors and running mysql as service. No apache, php . nothing. All resources are dedicated to mysql only. Mysql version - mysql Ver 14.14 Distrib 5.1.49, for debian-linux-gnu (x86_64) The BIG hitch is; when we connect with this box the web sites become too slow. I have added the following at my.cnf under [mysqld] section ` ` ` ` ` skip_external_locking skip_name_resolve skip_host_cach ` ` ` ` ` ` The sql connection becomes little faster but still it is considerably slow; specially with such a muscular dedicated linx box just for Mysql. Is there anything else which I can add/configure to make the network latecy small or any such mechanism to make the query fast ? I run the mysqltuner directly on the remote mysql server; and here is the result ``` General Statistics -- [--] Skipped version check for MySQLTuner script [OK] Currently running supported MySQL version 5.1.49-3-log [OK] Operating on 64-bit architecture Storage Engine Statistics --- [--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster [--] Data in MyISAM tables: 1G (Tables: 4777) [--] Data in InnoDB tables: 3G (Tables: 5543) [--] Data in MEMORY tables: 0B (Tables: 136) [!!] Total fragmented tables: 5562 Performance Metrics - [--] Up for: 3d 23h 55m 27s (1M q [4.523 qps], 81K conn, TX: 23B, RX: 469M) [--] Reads / Writes: 74% / 26% [--] Total buffers: 2.2G global + 20.4M per thread (150 max threads) [OK] Maximum possible memory usage: 5.2G (66% of installed RAM) [OK] Slow queries: 2% (39K/1M) [OK] Highest usage of available connections: 18% (28/150) [OK] Key buffer size / total MyISAM indexes: 2.0G/268.5M [!!] Key buffer hit rate: 78.2% (5M cached / 1M reads) [OK] Query cache efficiency: 42.0% (327K cached / 778K selects) [!!] Query cache prunes per day: 1993 [OK] Sorts requiring temporary tables: 0% (48 temp sorts / 9K sorts) [!!] Temporary tables created on disk: 39% (91K on disk / 230K total) [OK] Thread cache hit rate: 99% (28 created / 81K connections) [!!] Table cache hit rate: 6% (16K open / 248K opened) [OK] Open file limit used: 36% (11K/32K) [OK] Table locks acquired immediately: 99% (695K immediate / 695K locks) [!!] InnoDB data size / buffer pool: 3.6G/8.0M Recommendations - General recommendations: Run OPTIMIZE TABLE to defragment tables for better performance When making adjustments, make tmp_table_size/max_heap_table_size equal Reduce your SELECT DISTINCT queries without LIMIT clauses Increase table_cache gradually to avoid file descriptor limits Variables to adjust: query_cache_size ( 128M) tmp_table_size ( 100M) max_heap_table_size ( 100M) table_cache ( 16000) innodb_buffer_pool_size (= 3G) Please note, every day mysql optimization as well as repairing is done by a cron at night. I have also tried with the suggestion multiple times before but every time it gives some new suggestion. I have 8GB physical RAM in this server and here is some statistics ` # free -m total used free sharedbuffers cached Mem: 7986 7913 73 0224 6133 -/+ buffers/cache: 1554 6431 Swap: 3813 0 3813 ` And here is the vmstat result with 10 count and 5 sec delay ` # vmstat 5 10 procs ---memory-- ---swap-- -io -system-- cpu r b swpd free buff cache si sobibo in cs us sy id wa 0 0 0 56328 230440 629967600257466 0 0 98 2 0 0 0 55700 230440 629974400 022 226 272 0 0 99 1 0 0 0 55964 230440 629985600 0 314 348 388 0 0 94 5 0 0 0 55452 230440 629995600 061 304 364 0 0 97 2 0 1 0 55592 230440 630042400 0 271 199 257 0 0 96 4 0 0 0 54584 230440 630090800 0 338 342 428 0 0 92 8 0 0 0 54800 230440 630107200 077 119 133 0 0 98 2 0 0 0 53964 230440 630153200
Re: Remote mysql too slow
Am 09.04.2012 13:05, schrieb J. Bakshi: I have reset these two now ` ` ` ` key_buffer_size=200M innodb_buffer_pool_size=4G [!!] InnoDB data size / buffer pool: 3.6G/8.0M there did go something terrible wrong what is the mysqld log saying at startup? waht about post your complete my.cnf? however, you will never get the same perfor,ance as with a local mysqld if there are many small queries because all of the queries must go over the wire and answers back i notice a hughe performance difference in the mailbackend if i move the mailserver on the other vmware-host as the web-backend is running even of gigabit ethernet signature.asc Description: OpenPGP digital signature
Re: Remote mysql too slow
On Mon, 09 Apr 2012 13:17:55 +0200 Reindl Harald h.rei...@thelounge.net wrote: Am 09.04.2012 13:05, schrieb J. Bakshi: I have reset these two now ` ` ` ` key_buffer_size=200M innodb_buffer_pool_size=4G [!!] InnoDB data size / buffer pool: 3.6G/8.0M there did go something terrible wrong what is the mysqld log saying at startup? waht about post your complete my.cnf? Here is the my.cnf of mine ` ` ` ` [client] port= 3306 socket = /var/run/mysqld/mysqld.sock [mysqld_safe] socket = /var/run/mysqld/mysqld.sock nice= 0 [mysqld] # # * Basic Settings # user= mysql pid-file= /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock port= 3306 basedir = /usr datadir = /var/lib/mysql tmpdir = /tmp language= /usr/share/mysql/english skip_external_locking skip_name_resolve skip_host_cach # # Instead of skip-networking the default is now to listen only on # localhost which is more compatible and is not less secure. #bind-address = 127.0.0.1 ## enable utf8 support### default-character-set=utf8 default-collation=utf8_general_ci ## It will automatically fix MyISAM tables ## as soon as they become corrupt # myisam-recover=backup,force # Both location gets rotated by the cronjob. # Be aware that this log type is a performance killer. # As of 5.1 you can enable the log at runtime! general_log_file= /var/log/mysql/mysql.log general_log = 1 # # Error logging goes to syslog due to /etc/mysql/conf.d/mysqld_safe_syslog.cnf. # # Here you can see queries with especially long duration log_slow_querie = /var/log/mysql/mysql-slow.log long_query_time = 2 log-queries-not-using-indexes # # The following can be used as easy to replay backup logs or for replication. # note: if you are setting up a replication slave, see README.Debian about # other settings you may need to change. #server-id = 1 log_bin = /var/log/mysql/mysql-bin.log expire_logs_days= 10 max_binlog_size = 100M #binlog_do_db = include_database_name #binlog_ignore_db = include_database_name concurrent_insert=1 key_buffer_size=200M max_allowed_packet=156M max_connections=150 max_connect_errors=200 wait_timeout=120 interactive_timeout=27800 max_tmp_table=100 query_cache_size=128M query_cache_limit=4M query_cache_type=1 read_buffer_size=10M read_rnd_buffer_size=8M #sort_buffer_size=16M #myisam_sort_buffer_size=16M table_cache=16000 tmp_table_size=100M thread_cache_size=500 thread_concurrency=8 #innodb_thread_concurrency=1000 thread_stack=256K max_heap_table_size=100M #innodb_buffer_pool_size=4G [mysqldump] quick quote-names max_allowed_packet = 200M [mysql] #no-auto-rehash # faster start of mysql but no tab completition [isamchk] key_buffer = 16M # # * IMPORTANT: Additional settings that can override those from this file! # The files must end with '.cnf', otherwise they'll be ignored. # !includedir /etc/mysql/conf.d/ ` ` ` -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Remote mysql too slow
On Mon, 2012-04-09 at 06:14 -0500, Johnny Withers wrote: right speed and duplex. It seems that every new server I get now has to have the speed and duplex explicitly set instead of auto negotiate. Many of the cisco switches are notorious for this. signature.asc Description: This is a digitally signed message part
Mysql too slow over the LAN
Hello, I have built an application that connects to a mysql database located on a remote server in our LAN. When running the application locally on the server and I open the main window (it as several forms and does a lot of queries to fill the fields, etc.) it takes about 2 to 3 seconds to open. However, when I use this application on some other client in the same LAN, this form takes about 20 to 30 seconds to open! Can someone help me with this? Thanks. - Email Enviado utilizando o serviço MegaMail - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Mysql too slow over the LAN
Hi, Look at mysqld.log and tell us what errors are. Regards, Gelu _ G.NET SOFTWARE COMPANY Permanent e-mail address : [EMAIL PROTECTED] [EMAIL PROTECTED] - Original Message - From: Avalon [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, May 21, 2002 6:46 PM Subject: Mysql too slow over the LAN Hello, I have built an application that connects to a mysql database located on a remote server in our LAN. When running the application locally on the server and I open the main window (it as several forms and does a lot of queries to fill the fields, etc.) it takes about 2 to 3 seconds to open. However, when I use this application on some other client in the same LAN, this form takes about 20 to 30 seconds to open! Can someone help me with this? Thanks. - Email Enviado utilizando o servio MegaMail - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL too slow....
If you use Delphi or VB, VC, there are some API program that let you access MySQL direct. Not worry about ODBC. Sommai Fongnamthip At 01:00 7/6/2001 -0700, Van wrote: Muhammad Asif wrote: i have a table with 1 records in MS Access and MySQL with no index in either database. I query both tables from ColdFusion using ODBC datasources and Data from Access took 13sec to display while Date from MySQL took 23sec to come up MySQL seems to be half way slow should i use index etc comments??? Muhammad: ODBC is slow in MySQL. Make sure you're using a current MyODBC driver. If that doesn't help, don't know what to tell you. Many people use straight network queries which avoid this overhead. Keep in mind, ODBC was a standard M$ put in place because their desktop OS is ubiquitous. Oracle wouldn't need an ODBC layer if M$ didn't have a monopoly on the desktop. There would be better db communications interfaces, instead. Indexing fields you query often is a given. But, you know this, I'm sure... Regards, Van -- = Linux rocks!!! http://www.dedserius.com = - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
MySQL too slow....
i have a table with 1 records in MS Access and MySQL with no index in either database. I query both tables from ColdFusion using ODBC datasources and Data from Access took 13sec to display while Date from MySQL took 23sec to come up MySQL seems to be half way slow should i use index etc comments??? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
MySQL too slow
i have MSAccess table with 1 records without any index i import same table in MySQL without any index When i access data from this table through ODBC in ColdFusion: Data from Access took 14 Sec and Data from MySQL took 24 Sec MySQL seems to be halfway back?? Does this the case realy?? Comments?? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL too slow....
Muhammad Asif wrote: i have a table with 1 records in MS Access and MySQL with no index in either database. I query both tables from ColdFusion using ODBC datasources and Data from Access took 13sec to display while Date from MySQL took 23sec to come up MySQL seems to be half way slow should i use index etc comments??? Muhammad: ODBC is slow in MySQL. Make sure you're using a current MyODBC driver. If that doesn't help, don't know what to tell you. Many people use straight network queries which avoid this overhead. Keep in mind, ODBC was a standard M$ put in place because their desktop OS is ubiquitous. Oracle wouldn't need an ODBC layer if M$ didn't have a monopoly on the desktop. There would be better db communications interfaces, instead. Indexing fields you query often is a given. But, you know this, I'm sure... Regards, Van -- = Linux rocks!!! http://www.dedserius.com = - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL too slow....
I want to use MySQL in web based applications. Can u tell what other way i can go except ODBC if i have to query the database(MySQL) through some scripting language(ASP,ColdFusion,JSP,Servlet) and have to display results in a web browser... Thx in advance for your time - Original Message - From: Van [EMAIL PROTECTED] To: Muhammad Asif [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Thursday, June 07, 2001 1:00 PM Subject: Re: MySQL too slow Muhammad Asif wrote: i have a table with 1 records in MS Access and MySQL with no index in either database. I query both tables from ColdFusion using ODBC datasources and Data from Access took 13sec to display while Date from MySQL took 23sec to come up MySQL seems to be half way slow should i use index etc comments??? Muhammad: ODBC is slow in MySQL. Make sure you're using a current MyODBC driver. If that doesn't help, don't know what to tell you. Many people use straight network queries which avoid this overhead. Keep in mind, ODBC was a standard M$ put in place because their desktop OS is ubiquitous. Oracle wouldn't need an ODBC layer if M$ didn't have a monopoly on the desktop. There would be better db communications interfaces, instead. Indexing fields you query often is a given. But, you know this, I'm sure... Regards, Van -- = Linux rocks!!! http://www.dedserius.com = - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL too slow....
Muhammad Asif wrote: I want to use MySQL in web based applications. Can u tell what other way i can go except ODBC if i have to query the database(MySQL) through some scripting language(ASP,ColdFusion,JSP,Servlet) and have to display results in a web browser... Thx in advance for your time Muhammad: If the ColdFusion server has PHP support (likely), then, you're all set. Check php.net. Write a test script in your application: Create a file called test.php and put the following line in it: ? php_info(); ? If that works, you can add the following line: ? $link = mysql_connect(localhost, somedude, real_secret);? If you get any response on that one like access denied, or something similar, you're golden. If it responds with something like I don't understand what you mean by MySQL, you have more work to do. If the above tests succeed you have php/mysql support and can write the web application using php/mysql support, which is extremely trivial. Hope that helps. Van -- = Linux rocks!!! http://www.dedserius.com = - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: MySQL too slow....
How about PhP? Simon -Original Message- From: Muhammad Asif [mailto:[EMAIL PROTECTED]] Sent: 07 June 2001 10:19 To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: Re: MySQL too slow I want to use MySQL in web based applications. Can u tell what other way i can go except ODBC if i have to query the database(MySQL) through some scripting language(ASP,ColdFusion,JSP,Servlet) and have to display results in a web browser... Thx in advance for your time - Original Message - From: Van [EMAIL PROTECTED] To: Muhammad Asif [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Thursday, June 07, 2001 1:00 PM Subject: Re: MySQL too slow Muhammad Asif wrote: i have a table with 1 records in MS Access and MySQL with no index in either database. I query both tables from ColdFusion using ODBC datasources and Data from Access took 13sec to display while Date from MySQL took 23sec to come up MySQL seems to be half way slow should i use index etc comments??? Muhammad: ODBC is slow in MySQL. Make sure you're using a current MyODBC driver. If that doesn't help, don't know what to tell you. Many people use straight network queries which avoid this overhead. Keep in mind, ODBC was a standard M$ put in place because their desktop OS is ubiquitous. Oracle wouldn't need an ODBC layer if M$ didn't have a monopoly on the desktop. There would be better db communications interfaces, instead. Indexing fields you query often is a given. But, you know this, I'm sure... Regards, Van -- = Linux rocks!!! http://www.dedserius.com = - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL too slow....
I use PHP, a very special scripting language for the web and has an excellent list of commands to interface with MySQL. I recommend the book professional PHP and also the site php.net thanks, Hasan Impex - Original Message - From: Muhammad Asif [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Thursday, June 07, 2001 2:19 PM Subject: Re: MySQL too slow I want to use MySQL in web based applications. Can u tell what other way i can go except ODBC if i have to query the database(MySQL) through some scripting language(ASP,ColdFusion,JSP,Servlet) and have to display results in a web browser... Thx in advance for your time - Original Message - From: Van [EMAIL PROTECTED] To: Muhammad Asif [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Thursday, June 07, 2001 1:00 PM Subject: Re: MySQL too slow Muhammad Asif wrote: i have a table with 1 records in MS Access and MySQL with no index in either database. I query both tables from ColdFusion using ODBC datasources and Data from Access took 13sec to display while Date from MySQL took 23sec to come up MySQL seems to be half way slow should i use index etc comments??? Muhammad: ODBC is slow in MySQL. Make sure you're using a current MyODBC driver. If that doesn't help, don't know what to tell you. Many people use straight network queries which avoid this overhead. Keep in mind, ODBC was a standard M$ put in place because their desktop OS is ubiquitous. Oracle wouldn't need an ODBC layer if M$ didn't have a monopoly on the desktop. There would be better db communications interfaces, instead. Indexing fields you query often is a given. But, you know this, I'm sure... Regards, Van -- = Linux rocks!!! http://www.dedserius.com = - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL too slow....
Hello! A lot of people use PHP4 or PERL or C++ and connect *directly using the script. PHP4 has MySql functions built right in for convenience. In PHP for example, just do this: MYSQL_CONNECT(localhost,yourID,yourPassword); mysql_select_db(yourDB); $maxi=mysql_query(select fieldname1, fieldname2, fieldname3 from tablename where ...); There are more examples from other languages in the archives, I believe. Cheers! -warren - Original Message - From: Muhammad Asif [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Thursday, June 07, 2001 2:19 AM Subject: Re: MySQL too slow I want to use MySQL in web based applications. Can u tell what other way i can go except ODBC if i have to query the database(MySQL) through some scripting language(ASP,ColdFusion,JSP,Servlet) and have to display results in a web browser... Thx in advance for your time - Original Message - From: Van [EMAIL PROTECTED] To: Muhammad Asif [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Thursday, June 07, 2001 1:00 PM Subject: Re: MySQL too slow Muhammad Asif wrote: i have a table with 1 records in MS Access and MySQL with no index in either database. I query both tables from ColdFusion using ODBC datasources and Data from Access took 13sec to display while Date from MySQL took 23sec to come up MySQL seems to be half way slow should i use index etc comments??? Muhammad: ODBC is slow in MySQL. Make sure you're using a current MyODBC driver. If that doesn't help, don't know what to tell you. Many people use straight network queries which avoid this overhead. Keep in mind, ODBC was a standard M$ put in place because their desktop OS is ubiquitous. Oracle wouldn't need an ODBC layer if M$ didn't have a monopoly on the desktop. There would be better db communications interfaces, instead. Indexing fields you query often is a given. But, you know this, I'm sure... Regards, Van -- = Linux rocks!!! http://www.dedserius.com = - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL too slow
On Thu, Jun 07, 2001 at 12:56:24PM +0500, Muhammad Asif wrote: i have MSAccess table with 1 records without any index i import same table in MySQL without any index When i access data from this table through ODBC in ColdFusion: Data from Access took 14 Sec and Data from MySQL took 24 Sec MySQL seems to be halfway back?? Does this the case realy?? Comments?? Index the data. -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878Fax: (408) 349-5454Cell: (408) 439-9951 MySQL 3.23.29: up 13 days, processed 80,941,079 queries (71/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
