RE: Machine requirements
I suppose you're doing this on your workstation (Windows?)? Ideally, Mysql should be on it's own machine - it (or any database) is designed to suck up resources. Also, you definitely need to tweak the configuration file. MySQL's default config is very anemic (annoying I know) as opposed to Oracle or SQL Server where they gear the configuration towards heavy/large databases by default. Here is my configuration (probably not optimal, but a step in the right direction) for a dedicated dual 2.8GHz Xeon with 1GB ram and dual channel raid running RedHat Enterprise Linux 2.1: [EMAIL PROTECTED] adam]$ cat /etc/my.cnf # my.cnf [mysqld] user=mysql set-variable = sort_buffer=2M set-variable = thread_cache_size=40 set-variable = record_buffer=2M set-variable = table_cache=730 set-variable = key_buffer=256M set-variable = tmp_table_size=256M set-variable = interactive_timeout=7200 set-variable = wait_timeout=40 set-variable = max_connections=200 set-variable = max_user_connections=195 set-variable = max_connect_errors=20 # innodb stuff added 05/16/02 innodb_data_home_dir = innodb_data_file_path = /data/mysql/ibdata:1G innodb_log_group_home_dir = /var/lib/mysql/iblogs set-variable = innodb_buffer_pool_size=1G set-variable = innodb_additional_mem_pool_size=20M set-variable = innodb_log_files_in_group=7 set-variable = innodb_log_file_size=50M set-variable = innodb_log_buffer_size=8M innodb_flush_log_at_trx_commit=0 set-variable = innodb_file_io_threads=4 set-variable = innodb_lock_wait_timeout=50 set-variable = innodb_thread_concurrency=8 default-table-type=innodb blackrat said: I've just started using mySql. I'm an experience programmer with SQL based database, but know nothing about mySql. I'm setting up a single user database to be accessed with some Perl programs, with one main table, which will contain about 3 million lines. I'm running on an Athlon 1250 with 384meg ram, and finding the response time too slow. Just getting a count of the number of records by typing in an SQL query [not using Perl] takes over a minute, also when access is going on, I can't do anything else on them machine like access the web, it becomes too slow to be usable. Am I better setting up the database on another computer and accessing it through TCP/IP. I have available a spare machine which is a Celeron 450. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Machine requirements
methinks you have other troubles. i've been running MySQL on K6II-266 for a long time and its been running fine serving up several databases with multiple users. 512MB of RAM currently but it had only 128 until recently. Its running Linux (RedHat 7.2). I recently switched to 4.03, but it had been running 3.23.nn for quite some time with no difficulties. I recently upgraded the machine to a K6II-500. I also have another machine running a Duron 1GHz along with DNS and Sendmail its also working quite nicely. Curtis blackrat said: I've just started using mySql. I'm an experience programmer with SQL based database, but know nothing about mySql. I'm setting up a single user database to be accessed with some Perl programs, with one main table, which will contain about 3 million lines. I'm running on an Athlon 1250 with 384meg ram, and finding the response time too slow. Just getting a count of the number of records by typing in an SQL query [not using Perl] takes over a minute, also when access is going on, I can't do anything else on them machine like access the web, it becomes too slow to be usable. Am I better setting up the database on another computer and accessing it through TCP/IP. I have available a spare machine which is a Celeron 450. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Machine requirements
I think something is definitely wrong with your setup or your query. A query for a count of the number of rows should be almost instantaneous. How are your querying? You should be using: select count(*) from tablename Any other query that you filter on should have a index on the search field. At 3 million rows, nothing is going to be fast without creating indexes. Try to doing an explain before your query to see what MySQL is doing. I develop on a 300Mhz G3 Mac Portable width 384MB RAM, and while slow, nothing as slow as what you are seeing on a far faster system. On Friday, June 13, 2003, at 01:37 PM, blackrat wrote: I've just started using mySql. I'm an experience programmer with SQL based database, but know nothing about mySql. I'm setting up a single user database to be accessed with some Perl programs, with one main table, which will contain about 3 million lines. I'm running on an Athlon 1250 with 384meg ram, and finding the response time too slow. Just getting a count of the number of records by typing in an SQL query [not using Perl] takes over a minute, also when access is going on, I can't do anything else on them machine like access the web, it becomes too slow to be usable. Am I better setting up the database on another computer and accessing it through TCP/IP. I have available a spare machine which is a Celeron 450. -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Machine requirements
MySQL should have no problems running on your system, and if it runs better on another box, that would indicate a configuration problem or background processes clogging up the works. If you post your my.cnf file, someone here might be able to rule out the former. Edward Dudlik Becoming Digital www.becomingdigital.com - Original Message - From: blackrat [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, 13 June, 2003 13:37 Subject: Machine requirements I've just started using mySql. I'm an experience programmer with SQL based database, but know nothing about mySql. I'm setting up a single user database to be accessed with some Perl programs, with one main table, which will contain about 3 million lines. I'm running on an Athlon 1250 with 384meg ram, and finding the response time too slow. Just getting a count of the number of records by typing in an SQL query [not using Perl] takes over a minute, also when access is going on, I can't do anything else on them machine like access the web, it becomes too slow to be usable. Am I better setting up the database on another computer and accessing it through TCP/IP. I have available a spare machine which is a Celeron 450. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Machine requirements
What table type is this? If it's MyISAM select count(*) should be instantaneous. If it's InnoDb and you've got a large dataset (say 1 million+) then select count(*) will be slow b/c it doesn't store the # of records in the table, thus it has to count records. MyISAM does store the count so it will be much faster. Becoming Digital [EMAIL PROTECTED] 06/13/03 01:49PM MySQL should have no problems running on your system, and if it runs better on another box, that would indicate a configuration problem or background processes clogging up the works. If you post your my.cnf file, someone here might be able to rule out the former. Edward Dudlik Becoming Digital www.becomingdigital.com - Original Message - From: blackrat [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, 13 June, 2003 13:37 Subject: Machine requirements I've just started using mySql. I'm an experience programmer with SQL based database, but know nothing about mySql. I'm setting up a single user database to be accessed with some Perl programs, with one main table, which will contain about 3 million lines. I'm running on an Athlon 1250 with 384meg ram, and finding the response time too slow. Just getting a count of the number of records by typing in an SQL query [not using Perl] takes over a minute, also when access is going on, I can't do anything else on them machine like access the web, it becomes too slow to be usable. Am I better setting up the database on another computer and accessing it through TCP/IP. I have available a spare machine which is a Celeron 450. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
