Re: improving performance of server
sure, i am inserting the data with: LOAD DATA LOCAL INFILE 'lightning-data.txt' INTO TABLE strikes; does that clear things up? i assume that the data is first inserted into the table and then the index is created afterwards. following the creation of the database files i see that first one is filled up, which then remains static while the other one (the index, i assume) is populated. it is the second step that takes such a long time (although the cpu is doing very little and ram is barely occupied, it spends all its time swapping). so, my question really is how to tell the server to use ram instead of swap? i am using the default storage engine. Could you post the actual code you are using for the INSERT? Also, what storage engine are you using? Jay andrew collier wrote: hello, i am having some trouble getting mysql to perform decently on my machine. it is a 2 GHz dual core AMD 64 machine (although i am presently running a 32 bit linux system) with 1 Gb RAM and 1 Gb swap partition. the data i am using is loaded into a table described by: CREATE TABLE IF NOT EXISTS strikes ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, epoch DATETIME, usecMEDIUMINT UNSIGNED, fdate DOUBLE, lat FLOAT(6,4), lon FLOAT(7,4), error TINYINT UNSIGNED, nstat TINYINT UNSIGNED ); the data itself is pretty big: 70082053 records. during the loading process mysqlq pretty much hogs the CPU but uses only around 5% of the RAM. it takes 13m50s to load the data. there is a lot of disk activity, but this is just reading the data and there is virtually no swap space in use. adding the following index: INDEX coords (lat,lon) takes a really long time. once again the hard disk is working hard, but there is no swapping, so obviously this is just due to database reads. CPU usage is about the same. in the end after 60 hours i gave up: had to reboot to windows to do some other stuff. but it was just taking unreasonably long anyway. i am pretty sure that a lot more of this processing could be done without that much disk activity and i am guessing that is what is slowing the whole process down. the configuration i have in my.cnf is: [client] port= 3306 socket = /var/run/mysql/mysql.sock [mysqld] port= 3306 socket = /var/run/mysql/mysql.sock skip-locking key_buffer = 32M max_allowed_packet = 1M table_cache = 4 sort_buffer_size = 128K read_buffer_size = 256K read_rnd_buffer_size = 256K net_buffer_length = 2K thread_stack = 64K server-id = 1 [mysqldump] quick max_allowed_packet = 16M [mysql] no-auto-rehash [isamchk] key_buffer = 8M sort_buffer_size = 8M [myisamchk] key_buffer = 8M sort_buffer_size = 8M [mysqlhotcopy] interactive-timeout please could someone give me an idea of how i might go about making this whole thing a little more efficient? thanks! best regards, andrew collier. -- Click to consolidate debt and lower month expenses http://tags.bluebottle.com/fc/CAaCMPJklgxQ4NVfi4KJjZZEBd8Cw1Pv/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: improving performance of server
Hi Andrew, Since the default storage engine is myisam, increase the below parameter and then try your index operation. myisam_sort_buffer_size. This parameter is used for index creation in myisam. Also Set key_buffer to 5 - 50% of your RAM depending on how much you use MyISAM tables, but keep key_buffer_size + InnoDB buffer pool size 80% of your RAM. Please let us know if this helps your activity. regards anandkl On 4/2/07, andrew collier [EMAIL PROTECTED] wrote: sure, i am inserting the data with: LOAD DATA LOCAL INFILE 'lightning-data.txt' INTO TABLE strikes; does that clear things up? i assume that the data is first inserted into the table and then the index is created afterwards. following the creation of the database files i see that first one is filled up, which then remains static while the other one (the index, i assume) is populated. it is the second step that takes such a long time (although the cpu is doing very little and ram is barely occupied, it spends all its time swapping). so, my question really is how to tell the server to use ram instead of swap? i am using the default storage engine. Could you post the actual code you are using for the INSERT? Also, what storage engine are you using? Jay andrew collier wrote: hello, i am having some trouble getting mysql to perform decently on my machine. it is a 2 GHz dual core AMD 64 machine (although i am presently running a 32 bit linux system) with 1 Gb RAM and 1 Gb swap partition. the data i am using is loaded into a table described by: CREATE TABLE IF NOT EXISTS strikes ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, epoch DATETIME, usecMEDIUMINT UNSIGNED, fdate DOUBLE, lat FLOAT(6,4), lon FLOAT(7,4), error TINYINT UNSIGNED, nstat TINYINT UNSIGNED ); the data itself is pretty big: 70082053 records. during the loading process mysqlq pretty much hogs the CPU but uses only around 5% of the RAM. it takes 13m50s to load the data. there is a lot of disk activity, but this is just reading the data and there is virtually no swap space in use. adding the following index: INDEX coords (lat,lon) takes a really long time. once again the hard disk is working hard, but there is no swapping, so obviously this is just due to database reads. CPU usage is about the same. in the end after 60 hours i gave up: had to reboot to windows to do some other stuff. but it was just taking unreasonably long anyway. i am pretty sure that a lot more of this processing could be done without that much disk activity and i am guessing that is what is slowing the whole process down. the configuration i have in my.cnf is: [client] port= 3306 socket = /var/run/mysql/mysql.sock [mysqld] port= 3306 socket = /var/run/mysql/mysql.sock skip-locking key_buffer = 32M max_allowed_packet = 1M table_cache = 4 sort_buffer_size = 128K read_buffer_size = 256K read_rnd_buffer_size = 256K net_buffer_length = 2K thread_stack = 64K server-id = 1 [mysqldump] quick max_allowed_packet = 16M [mysql] no-auto-rehash [isamchk] key_buffer = 8M sort_buffer_size = 8M [myisamchk] key_buffer = 8M sort_buffer_size = 8M [mysqlhotcopy] interactive-timeout please could someone give me an idea of how i might go about making this whole thing a little more efficient? thanks! best regards, andrew collier. -- Click to consolidate debt and lower month expenses http://tags.bluebottle.com/fc/CAaCMPJklgxQ4NVfi4KJjZZEBd8Cw1Pv/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: improving performance of server
At 03:23 AM 4/2/2007, you wrote: sure, i am inserting the data with: LOAD DATA LOCAL INFILE 'lightning-data.txt' INTO TABLE strikes; does that clear things up? i assume that the data is first inserted into the table and then the index is created afterwards. Only if the table is empty when you first start the Load Data command. If you have data in the table then the index is maintained as the rows are loaded (much slower). If this is the case then you may want to disable the indexes prior to Load Data and then re-enable the indexes later will rebuild them. following the creation of the database files i see that first one is filled up, which then remains static while the other one (the index, i assume) is populated. it is the second step that takes such a long time (although the cpu is doing very little and ram is barely occupied, it spends all its time swapping). so, my question really is how to tell the server to use ram instead of swap? For MyISAM tables, you need to increase the Key_Buffer_Size to up to 30% of available memory. This is where the keys are built. Increasing this value (get more RAM if you need to), will dramatically speed up the performance of building the index, as much as 100x faster. So if you don't have enough RAM, beg borrow or steal some for a few days and bump up Key_Buffer_Size and you should notice a big difference. i am using the default storage engine. I assume then it is MyISAM. You can of course override it and make InnoDb the default in which case the Key_Buffer_Size won't have any effect. Mike Could you post the actual code you are using for the INSERT? Also, what storage engine are you using? Jay andrew collier wrote: hello, i am having some trouble getting mysql to perform decently on my machine. it is a 2 GHz dual core AMD 64 machine (although i am presently running a 32 bit linux system) with 1 Gb RAM and 1 Gb swap partition. the data i am using is loaded into a table described by: CREATE TABLE IF NOT EXISTS strikes ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, epoch DATETIME, usecMEDIUMINT UNSIGNED, fdate DOUBLE, lat FLOAT(6,4), lon FLOAT(7,4), error TINYINT UNSIGNED, nstat TINYINT UNSIGNED ); the data itself is pretty big: 70082053 records. during the loading process mysqlq pretty much hogs the CPU but uses only around 5% of the RAM. it takes 13m50s to load the data. there is a lot of disk activity, but this is just reading the data and there is virtually no swap space in use. adding the following index: INDEX coords (lat,lon) takes a really long time. once again the hard disk is working hard, but there is no swapping, so obviously this is just due to database reads. CPU usage is about the same. in the end after 60 hours i gave up: had to reboot to windows to do some other stuff. but it was just taking unreasonably long anyway. i am pretty sure that a lot more of this processing could be done without that much disk activity and i am guessing that is what is slowing the whole process down. the configuration i have in my.cnf is: [client] port= 3306 socket = /var/run/mysql/mysql.sock [mysqld] port= 3306 socket = /var/run/mysql/mysql.sock skip-locking key_buffer = 32M max_allowed_packet = 1M table_cache = 4 sort_buffer_size = 128K read_buffer_size = 256K read_rnd_buffer_size = 256K net_buffer_length = 2K thread_stack = 64K server-id = 1 [mysqldump] quick max_allowed_packet = 16M [mysql] no-auto-rehash [isamchk] key_buffer = 8M sort_buffer_size = 8M [myisamchk] key_buffer = 8M sort_buffer_size = 8M [mysqlhotcopy] interactive-timeout please could someone give me an idea of how i might go about making this whole thing a little more efficient? thanks! best regards, andrew collier. -- Click to consolidate debt and lower month expenses http://tags.bluebottle.com/fc/CAaCMPJklgxQ4NVfi4KJjZZEBd8Cw1Pv/ -- 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: improving performance of server
Could you post the actual code you are using for the INSERT? Also, what storage engine are you using? Jay andrew collier wrote: hello, i am having some trouble getting mysql to perform decently on my machine. it is a 2 GHz dual core AMD 64 machine (although i am presently running a 32 bit linux system) with 1 Gb RAM and 1 Gb swap partition. the data i am using is loaded into a table described by: CREATE TABLE IF NOT EXISTS strikes ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, epoch DATETIME, usecMEDIUMINT UNSIGNED, fdate DOUBLE, lat FLOAT(6,4), lon FLOAT(7,4), error TINYINT UNSIGNED, nstat TINYINT UNSIGNED ); the data itself is pretty big: 70082053 records. during the loading process mysqlq pretty much hogs the CPU but uses only around 5% of the RAM. it takes 13m50s to load the data. there is a lot of disk activity, but this is just reading the data and there is virtually no swap space in use. adding the following index: INDEX coords (lat,lon) takes a really long time. once again the hard disk is working hard, but there is no swapping, so obviously this is just due to database reads. CPU usage is about the same. in the end after 60 hours i gave up: had to reboot to windows to do some other stuff. but it was just taking unreasonably long anyway. i am pretty sure that a lot more of this processing could be done without that much disk activity and i am guessing that is what is slowing the whole process down. the configuration i have in my.cnf is: [client] port= 3306 socket = /var/run/mysql/mysql.sock [mysqld] port= 3306 socket = /var/run/mysql/mysql.sock skip-locking key_buffer = 32M max_allowed_packet = 1M table_cache = 4 sort_buffer_size = 128K read_buffer_size = 256K read_rnd_buffer_size = 256K net_buffer_length = 2K thread_stack = 64K server-id = 1 [mysqldump] quick max_allowed_packet = 16M [mysql] no-auto-rehash [isamchk] key_buffer = 8M sort_buffer_size = 8M [myisamchk] key_buffer = 8M sort_buffer_size = 8M [mysqlhotcopy] interactive-timeout please could someone give me an idea of how i might go about making this whole thing a little more efficient? thanks! best regards, andrew collier. -- Need cash? Click to get a payday loan http://tags.bluebottle.com/fc/CAaCMPJktTdJ7Iu6tODsIDX46jYJlhKA/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
improving performance of server
hello, i am having some trouble getting mysql to perform decently on my machine. it is a 2 GHz dual core AMD 64 machine (although i am presently running a 32 bit linux system) with 1 Gb RAM and 1 Gb swap partition. the data i am using is loaded into a table described by: CREATE TABLE IF NOT EXISTS strikes ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, epoch DATETIME, usecMEDIUMINT UNSIGNED, fdate DOUBLE, lat FLOAT(6,4), lon FLOAT(7,4), error TINYINT UNSIGNED, nstat TINYINT UNSIGNED ); the data itself is pretty big: 70082053 records. during the loading process mysqlq pretty much hogs the CPU but uses only around 5% of the RAM. it takes 13m50s to load the data. there is a lot of disk activity, but this is just reading the data and there is virtually no swap space in use. adding the following index: INDEX coords (lat,lon) takes a really long time. once again the hard disk is working hard, but there is no swapping, so obviously this is just due to database reads. CPU usage is about the same. in the end after 60 hours i gave up: had to reboot to windows to do some other stuff. but it was just taking unreasonably long anyway. i am pretty sure that a lot more of this processing could be done without that much disk activity and i am guessing that is what is slowing the whole process down. the configuration i have in my.cnf is: [client] port= 3306 socket = /var/run/mysql/mysql.sock [mysqld] port= 3306 socket = /var/run/mysql/mysql.sock skip-locking key_buffer = 32M max_allowed_packet = 1M table_cache = 4 sort_buffer_size = 128K read_buffer_size = 256K read_rnd_buffer_size = 256K net_buffer_length = 2K thread_stack = 64K server-id = 1 [mysqldump] quick max_allowed_packet = 16M [mysql] no-auto-rehash [isamchk] key_buffer = 8M sort_buffer_size = 8M [myisamchk] key_buffer = 8M sort_buffer_size = 8M [mysqlhotcopy] interactive-timeout please could someone give me an idea of how i might go about making this whole thing a little more efficient? thanks! best regards, andrew collier. -- Need cash? Click to get a payday loan http://tags.bluebottle.com/fc/CAaCMPJktTdJ7Iu6tODsIDX46jYJlhKA/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
