Re: terrible performance in 6.1beta4
Kris Kennaway writes: Well there you go then..you're trying to access a file that is larger than RAM, so naturally you won't be able to fit it all in RAM, and with 1GB less RAM in your system you'll spend much more time reading bits of it from disk and later throwing them away. Not to mention the old system has SCSI and the new one has SATA. The poster didn't mention, but if the SCSI are 10K rpm or 15K rpm and the SATA are 7,200 rpm.. the SATA disks don't stand a chance.. specially with less memory. ___ freebsd-questions@freebsd.org mailing list http://lists.freebsd.org/mailman/listinfo/freebsd-questions To unsubscribe, send any mail to [EMAIL PROTECTED]
Re: terrible performance in 6.1beta4
On 3/30/06, Kris Kennaway [EMAIL PROTECTED] wrote: Actually I seem to recall that on Linux with default settings fsync() lies and does not actually sync data before returning, so maybe it's worth turning off on FreeBSD too if you're comfortable with the implications of this. A few months ago I installed a syslog server with syslog-ng inserting the events to postgresql. Since I had 5000+ events per second coming in postgresql was my bottleneck and I had to disable fsync (Even then it would get very slow sometimes). This was on a CentOS 4.2 running kernel 2.6.9-22.0.1.ELsmp This to say, I don't know for sure if Linux is lying or not on fsync, but even on Linux, turning fsync off makes a big difference. In my case if data was lost there was no damage hence my choice to keeping it off -- Joao Barros ___ freebsd-questions@freebsd.org mailing list http://lists.freebsd.org/mailman/listinfo/freebsd-questions To unsubscribe, send any mail to [EMAIL PROTECTED]
Re: terrible performance in 6.1beta4
On Wed, 29 Mar 2006 19:09:26 -0600 Miguel [EMAIL PROTECTED] wrote: Hi, im loading a lot of information to a postgresql 8.1.3 database, im using the copy command, using the same file and version of postgres in gentoo it spend a few minutes (~4-5), in freebsd 6.1-beta4 it has already spent 25 minutes and the server apparently is doing nothing, [...] Hey Miguel, I would agree with all the comments about memory and buffers - tweak them till you allocate the right amount (i.e., make sure you don't OVER allocate...though i'm sure you'd see those errors in the pgsql log. what does ktrace show when you attach it to the process that is importing the data (client and server processes,actually). - have you tried doing a binary export and import? I've found them to be faster, though they may not work for what you need (I think I'm using them for backups... ). my hardware is pretty much the same as yours, though 4 GB RAM and 4 drives., but my DB is somewhat larger and the reponse time of remote logins or running simple commands like ls -l /etc takes a lot of time (35 secs ort so), cpu usage is very low: ... and all these commands become responsive as usual the minute you kill the import process? btw, you definitely want to kill the indices / FKs in that table until you're finished with the initial import - it'll speed things up a lot. Beto ___ freebsd-questions@freebsd.org mailing list http://lists.freebsd.org/mailman/listinfo/freebsd-questions To unsubscribe, send any mail to [EMAIL PROTECTED]
Re: terrible performance in 6.1beta4
Beto, i believe he has given up. i would say that on a dedicated database machine 50-75% dedicated(shared) memory is not overkill. wasn't there some other switch which determined wether to page shared-memory out? regards, usleep On 4/3/06, Norberto Meijome [EMAIL PROTECTED] wrote: On Wed, 29 Mar 2006 19:09:26 -0600 Miguel [EMAIL PROTECTED] wrote: Hi, im loading a lot of information to a postgresql 8.1.3 database, im using the copy command, using the same file and version of postgres in gentoo it spend a few minutes (~4-5), in freebsd 6.1-beta4 it has already spent 25 minutes and the server apparently is doing nothing, [...] Hey Miguel, I would agree with all the comments about memory and buffers - tweak them till you allocate the right amount (i.e., make sure you don't OVER allocate...though i'm sure you'd see those errors in the pgsql log. what does ktrace show when you attach it to the process that is importing the data (client and server processes,actually). - have you tried doing a binary export and import? I've found them to be faster, though they may not work for what you need (I think I'm using them for backups... ). my hardware is pretty much the same as yours, though 4 GB RAM and 4 drives., but my DB is somewhat larger and the reponse time of remote logins or running simple commands like ls -l /etc takes a lot of time (35 secs ort so), cpu usage is very low: ... and all these commands become responsive as usual the minute you kill the import process? btw, you definitely want to kill the indices / FKs in that table until you're finished with the initial import - it'll speed things up a lot. Beto ___ freebsd-questions@freebsd.org mailing list http://lists.freebsd.org/mailman/listinfo/freebsd-questions To unsubscribe, send any mail to [EMAIL PROTECTED] ___ freebsd-questions@freebsd.org mailing list http://lists.freebsd.org/mailman/listinfo/freebsd-questions To unsubscribe, send any mail to [EMAIL PROTECTED]
Re: terrible performance in 6.1beta4
i just thought of another possible cause: what is the location of the to-be-imported file? local disk? because if it is on the network, check your NIC if you are running at the maximum rate, nfs buffers, etc ( i was fooled by such a situation once ). regards, usleep On 4/3/06, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Beto, i believe he has given up. i would say that on a dedicated database machine 50-75% dedicated(shared) memory is not overkill. wasn't there some other switch which determined wether to page shared-memory out? regards, usleep On 4/3/06, Norberto Meijome [EMAIL PROTECTED] wrote: On Wed, 29 Mar 2006 19:09:26 -0600 Miguel [EMAIL PROTECTED] wrote: Hi, im loading a lot of information to a postgresql 8.1.3 database, im using the copy command, using the same file and version of postgres in gentoo it spend a few minutes (~4-5), in freebsd 6.1-beta4 it has already spent 25 minutes and the server apparently is doing nothing, [...] Hey Miguel, I would agree with all the comments about memory and buffers - tweak them till you allocate the right amount (i.e., make sure you don't OVER allocate...though i'm sure you'd see those errors in the pgsql log. what does ktrace show when you attach it to the process that is importing the data (client and server processes,actually). - have you tried doing a binary export and import? I've found them to be faster, though they may not work for what you need (I think I'm using them for backups... ). my hardware is pretty much the same as yours, though 4 GB RAM and 4 drives., but my DB is somewhat larger and the reponse time of remote logins or running simple commands like ls -l /etc takes a lot of time (35 secs ort so), cpu usage is very low: ... and all these commands become responsive as usual the minute you kill the import process? btw, you definitely want to kill the indices / FKs in that table until you're finished with the initial import - it'll speed things up a lot. Beto ___ freebsd-questions@freebsd.org mailing list http://lists.freebsd.org/mailman/listinfo/freebsd-questions To unsubscribe, send any mail to [EMAIL PROTECTED] ___ freebsd-questions@freebsd.org mailing list http://lists.freebsd.org/mailman/listinfo/freebsd-questions To unsubscribe, send any mail to [EMAIL PROTECTED]
Re: terrible performance in 6.1beta4
On Mon, 3 Apr 2006 15:18:02 +0200 [EMAIL PROTECTED] wrote: Beto, i believe he has given up. :( without knowing WHAT is the problem? i would say that on a dedicated database machine 50-75% dedicated(shared) memory is not overkill. of course not. I am not sure I read his vmstat screenshot right, but it seemed to me that the box was using a lot of disk, but had lots of free memory - i.e., it was probably just reading the data and the disks maxed out? Miguel, what does gstat show ? (it'll show the % busy of each device in GEOM , which also includes non-RAID devices, so it's quite useful) wasn't there some other switch which determined wether to page shared-memory out? i dont actually recall - my DB project had to be put on hold about 75% ready for prod, trying to get back onto it soon. ___ freebsd-questions@freebsd.org mailing list http://lists.freebsd.org/mailman/listinfo/freebsd-questions To unsubscribe, send any mail to [EMAIL PROTECTED]
Re: terrible performance in 6.1beta4
[EMAIL PROTECTED] wrote: Miguel, you are not inserting into an indexed table, are you? yes, i am... is that a problem? it used to be a problem, but since your gentoo box is doing the same task in 4-5 minutes, i doubt it is currently the problem. i seem to recall some speedup of the copyin-command in one of the latest releases of PostgreSQL. i don't know the details, maybe they are deferring index-updates automagically when copying-in ( check PostgreSQL release notes ). Miguel, you have not yet confirmed the two postgresql.conf-files(gentoo vs fbsd) to be identical ( or i missed that ). please let me know. Yes, thay are identical, except for he shared_buffers, another issue pops to my mind: on 4.x i had to tweak some sysctl's regarding shared-memory ( default settings would not allow PGSQL to claim as much as i would like ). i don't know if this still needs to be done on 6.x. if i recall correctly, postgresql would not start if it could not allocate it's shared buffers. things may have changed: check you postgresql.log to see if it is complaining. please let us know if you achieved any speed update at all. i increased the shred_buffers to 35% of fisical RAM, that doesnt helped very much, neither do fsync off. thanks for all folks, ___ freebsd-questions@freebsd.org mailing list http://lists.freebsd.org/mailman/listinfo/freebsd-questions To unsubscribe, send any mail to [EMAIL PROTECTED]
Re: terrible performance in 6.1beta4
Miguel, On Wed, Mar 29, 2006 at 07:09:26PM -0600, Miguel wrote: Hi, im loading a lot of information to a postgresql 8.1.3 database, im using the copy command, using the same file and version of postgres in gentoo it spend a few minutes (~4-5), in freebsd 6.1-beta4 it has already spent 25 minutes and the server apparently is doing nothing, and the reponse time of remote logins or running simple commands like ls -l /etc takes a lot of time (35 secs ort so), cpu usage is very low: how do your postgresql.conf's compare? things like shared buffers, fsync... how big is the file? regards, usleep ___ freebsd-questions@freebsd.org mailing list http://lists.freebsd.org/mailman/listinfo/freebsd-questions To unsubscribe, send any mail to [EMAIL PROTECTED]
Re: terrible performance in 6.1beta4
Kris Kennaway wrote: On Wed, Mar 29, 2006 at 07:09:26PM -0600, Miguel wrote: Hi, im loading a lot of information to a postgresql 8.1.3 database, im using the copy command, using the same file and version of postgres in gentoo it spend a few minutes (~4-5), in freebsd 6.1-beta4 it has already spent 25 minutes and the server apparently is doing nothing, and the reponse time of remote logins or running simple commands like ls -l /etc takes a lot of time (35 secs ort so), cpu usage is very low: It's spending all its time reading from disk. What else is different between your two systems (hardware, database configuration)? This is a brand new server, with sata controller, 250G disks without array, the old one has a hp 5i controller, 6 x 70G 10k 320 scsi disks. --- Miguel ___ freebsd-questions@freebsd.org mailing list http://lists.freebsd.org/mailman/listinfo/freebsd-questions To unsubscribe, send any mail to [EMAIL PROTECTED]
Re: terrible performance in 6.1beta4
[EMAIL PROTECTED] wrote: Miguel, On Wed, Mar 29, 2006 at 07:09:26PM -0600, Miguel wrote: Hi, im loading a lot of information to a postgresql 8.1.3 database, im using the copy command, using the same file and version of postgres in gentoo it spend a few minutes (~4-5), in freebsd 6.1-beta4 it has already spent 25 minutes and the server apparently is doing nothing, and the reponse time of remote logins or running simple commands like ls -l /etc takes a lot of time (35 secs ort so), cpu usage is very low: how do your postgresql.conf's compare? things like shared buffers, fsync... the only diference is in shared_buffers, the new one has only 2G of ram, while the old one 3G, both has fsync = on (the default value) how big is the file? 3.0G --- Miguel ___ freebsd-questions@freebsd.org mailing list http://lists.freebsd.org/mailman/listinfo/freebsd-questions To unsubscribe, send any mail to [EMAIL PROTECTED]
Re: terrible performance in 6.1beta4
On Thu, Mar 30, 2006 at 08:52:43AM -0600, Miguel wrote: [EMAIL PROTECTED] wrote: Miguel, On Wed, Mar 29, 2006 at 07:09:26PM -0600, Miguel wrote: Hi, im loading a lot of information to a postgresql 8.1.3 database, im using the copy command, using the same file and version of postgres in gentoo it spend a few minutes (~4-5), in freebsd 6.1-beta4 it has already spent 25 minutes and the server apparently is doing nothing, and the reponse time of remote logins or running simple commands like ls -l /etc takes a lot of time (35 secs ort so), cpu usage is very low: how do your postgresql.conf's compare? things like shared buffers, fsync... the only diference is in shared_buffers, the new one has only 2G of ram, while the old one 3G, both has fsync = on (the default value) how big is the file? 3.0G Well there you go then..you're trying to access a file that is larger than RAM, so naturally you won't be able to fit it all in RAM, and with 1GB less RAM in your system you'll spend much more time reading bits of it from disk and later throwing them away. Kris pgpZ0mG3i3r9n.pgp Description: PGP signature
Re: terrible performance in 6.1beta4
Miguel, 3.0G i looked at your top-screenshot, i have the impression you could dedicate far more memory to postgresql. maybe it would be usefull to post your postgresql.conf ( this is in fact a postgresql question, but i don't care ). you might want to turn fsync off, my limited knowledge about this switch tells me it is only important in case of powerfailures. you want to give postgresql as much memory as it needs, cause else it will underperform heavily. this might be one of the reasons it is not catching on as quick as i would like. postgresql is the best in OSS though. bring on your .conf! regards, usleep ___ freebsd-questions@freebsd.org mailing list http://lists.freebsd.org/mailman/listinfo/freebsd-questions To unsubscribe, send any mail to [EMAIL PROTECTED]
Re: terrible performance in 6.1beta4
Kris, 3.0G Well there you go then..you're trying to access a file that is larger than RAM, so naturally you won't be able to fit it all in RAM, and with 1GB less RAM in your system you'll spend much more time reading bits of it from disk and later throwing them away. i know a bit of databases, and assume you do as well, so i am hesative to question your explanation but: the importing of a DB-file ( being tab-sep-copies or SQL dumps ) is a transformation process and does not require all data to be in core ( is that the right terminology? ) at the same moment. it transform x Gigs of input to y Gigs of output. i don't see why the 2GB machine would suffer this hard. unless the 3GB-file is one big table. is it MIguel? then it might be a postgresql hitch. i will have to wait on the .conf files, Miguel has not claimed them to be identical, so i am curious. regards, usleep Kris ___ freebsd-questions@freebsd.org mailing list http://lists.freebsd.org/mailman/listinfo/freebsd-questions To unsubscribe, send any mail to [EMAIL PROTECTED]
Re: terrible performance in 6.1beta4
[EMAIL PROTECTED] wrote: Kris, 3.0G Well there you go then..you're trying to access a file that is larger than RAM, so naturally you won't be able to fit it all in RAM, and with 1GB less RAM in your system you'll spend much more time reading bits of it from disk and later throwing them away. i know a bit of databases, and assume you do as well, so i am hesative to question your explanation but: the importing of a DB-file ( being tab-sep-copies or SQL dumps ) is a transformation process and does not require all data to be in core ( is that the right terminology? ) at the same moment. it transform x Gigs of input to y Gigs of output. i don't see why the 2GB machine would suffer this hard. unless the 3GB-file is one big table. is it MIguel? Yes, it is a dump of a single table. i want to tranfer the data from one server to another, and this is one of the biggest table. i will have to wait on the .conf files, Miguel has not claimed them to be identical, so i am curious. attached is my config file, shared_buffers are 25% of total RAM im guessing that this is a disk controlled bug or something, when i execute any query involving many rows, the server response is very low, ssh, su, even copy or rename a file, cpu usage remains ~87% idle though --- Miguel miguel # - # PostgreSQL configuration file # - # # This file consists of lines of the form: # # name = value # # (The '=' is optional.) White space may be used. Comments are introduced # with '#' anywhere on a line. The complete list of option names and # allowed values can be found in the PostgreSQL documentation. The # commented-out settings shown in this file represent the default values. # # Please note that re-commenting a setting is NOT sufficient to revert it # to the default value, unless you restart the postmaster. # # Any option can also be given as a command line switch to the # postmaster, e.g. 'postmaster -c log_connections=on'. Some options # can be changed at run-time with the 'SET' SQL command. # # This file is read on postmaster startup and when the postmaster # receives a SIGHUP. If you edit the file on a running system, you have # to SIGHUP the postmaster for the changes to take effect, or use # pg_ctl reload. Some settings, such as listen_addresses, require # a postmaster shutdown and restart to take effect. #--- # FILE LOCATIONS #--- # The default values of these variables are driven from the -D command line # switch or PGDATA environment variable, represented here as ConfigDir. #data_directory = 'ConfigDir' # use data in another directory #hba_file = 'ConfigDir/pg_hba.conf' # host-based authentication file #ident_file = 'ConfigDir/pg_ident.conf' # IDENT configuration file # If external_pid_file is not explicitly set, no extra pid file is written. #external_pid_file = '(none)' # write an extra pid file #--- # CONNECTIONS AND AUTHENTICATION #--- # - Connection Settings - listen_addresses = '*' # what IP address(es) to listen on; # comma-separated list of addresses; # defaults to 'localhost', '*' = all #port = 5432 max_connections = 40 # note: increasing max_connections costs ~400 bytes of shared memory per # connection slot, plus lock space (see max_locks_per_transaction). You # might also need to raise shared_buffers to support more connections. superuser_reserved_connections = 5 #unix_socket_directory = '' #unix_socket_group = '' #unix_socket_permissions = 0777 # octal #bonjour_name = '' # defaults to the computer name # - Security Authentication - #authentication_timeout = 60# 1-600, in seconds #ssl = off #password_encryption = on #db_user_namespace = off # Kerberos #krb_server_keyfile = '' #krb_srvname = 'postgres' #krb_server_hostname = '' # empty string matches any keytab entry #krb_caseins_users = off # - TCP Keepalives - # see 'man 7 tcp' for details #tcp_keepalives_idle = 0# TCP_KEEPIDLE, in seconds; # 0 selects the system default #tcp_keepalives_interval = 0# TCP_KEEPINTVL, in seconds; # 0 selects the system default #tcp_keepalives_count = 0 # TCP_KEEPCNT; # 0 selects the system default #--- # RESOURCE USAGE (except WAL) #--- # - Memory - shared_buffers = 65536
Re: terrible performance in 6.1beta4
On Thu, Mar 30, 2006 at 09:41:51PM +0200, [EMAIL PROTECTED] wrote: Miguel, 3.0G i looked at your top-screenshot, i have the impression you could dedicate far more memory to postgresql. maybe it would be usefull to post your postgresql.conf ( this is in fact a postgresql question, but i don't care ). you might want to turn fsync off, my limited knowledge about this switch tells me it is only important in case of powerfailures. you want to give postgresql as much memory as it needs, cause else it will underperform heavily. this might be one of the reasons it is not catching on as quick as i would like. postgresql is the best in OSS though. Yes, this is my impression of the problem too. Any time your process is waiting on disk I/O it is going to perform terribly (on any OS - disks are slow), and the way to fix this is to make sure it does as little I/O as possible (by allowing everything to be cached in RAM). Kris pgpygXu12DGkZ.pgp Description: PGP signature
Re: terrible performance in 6.1beta4
Miguel, Yes, it is a dump of a single table. i want to tranfer the data from one server to another, and this is one of the biggest table. ok, but gentoo performs the same task ok, so it is not a postgresql problem. you have not confirmed wether the gentoo-box is running with the same postgresql.conf. is it? if not, which are the differences? attached is my config file, shared_buffers are 25% of total RAM i my experience, you should go much higher. if the server is not in production yet, you might go as high as 75% ( assuming no other processes need these kind of resources ). i am not familiar with the work_mem flag, it was not there last time i tuned a postgresql. you might consider upping your wal-buffers, but i am not sure if they are used by copyin. im guessing that this is a disk controlled bug or something, when i execute any query involving many rows, the server response is very low, ssh, su, even copy or rename a file, cpu usage remains ~87% idle though ofcourse it might be hardware related: have you checked your diskperformance? what is your throughput? how does this throughput compare with the gentoo box? regards, usleep --- Miguel miguel ___ freebsd-questions@freebsd.org mailing list http://lists.freebsd.org/mailman/listinfo/freebsd-questions To unsubscribe, send any mail to [EMAIL PROTECTED]
Re: terrible performance in 6.1beta4
Kris, Yes, this is my impression of the problem too. Any time your process is waiting on disk I/O it is going to perform terribly (on any OS - disks are slow), and the way to fix this is to make sure it does as little I/O as possible (by allowing everything to be cached in RAM). just for my curiosity, do you share my opinion on the fsync issue? regards, usleep ___ freebsd-questions@freebsd.org mailing list http://lists.freebsd.org/mailman/listinfo/freebsd-questions To unsubscribe, send any mail to [EMAIL PROTECTED]
Re: terrible performance in 6.1beta4
Miguel attached is my config file, shared_buffers are 25% of total RAM im guessing that this is a disk controlled bug or something, when i execute any query involving many rows, the server response is very low, ssh, su, even copy or rename a file, cpu usage remains ~87% idle though you are not inserting into an indexed table, are you? regards, usleep --- Miguel miguel ___ freebsd-questions@freebsd.org mailing list http://lists.freebsd.org/mailman/listinfo/freebsd-questions To unsubscribe, send any mail to [EMAIL PROTECTED]
Re: terrible performance in 6.1beta4
On Thu, Mar 30, 2006 at 10:49:01PM +0200, [EMAIL PROTECTED] wrote: Kris, Yes, this is my impression of the problem too. Any time your process is waiting on disk I/O it is going to perform terribly (on any OS - disks are slow), and the way to fix this is to make sure it does as little I/O as possible (by allowing everything to be cached in RAM). just for my curiosity, do you share my opinion on the fsync issue? Actually I seem to recall that on Linux with default settings fsync() lies and does not actually sync data before returning, so maybe it's worth turning off on FreeBSD too if you're comfortable with the implications of this. Kris pgpfP4otKvRhD.pgp Description: PGP signature
Re: terrible performance in 6.1beta4
Miguel wrote: .. postgresql is slow for me and others wrote: ... you may have to dedicate more memory to it Sorry for the above, but I haven't seen the beginning of the thread. I don't know PostgreSQL much but I also has been recently running quite simple program on one quite large table (2 milions of rows) and was trying to tune it. I haven't benchmarked anything (only that it was several orders of magnitude faster to run it on much bigger machine (2x2.8G Xeon, 2GB RAM, 10/15kRPM SCSI disks vs. 1xP4 Celeron 2.4G, 256MB RAM and an IDE disk)). I did the tuning after reading a little on the Net but the _POSSIBLY_IMPORTANT_ message I found was that PostgreSQL is a little different to other DB engines that it normally doesn't eat comparatively much memory even on loaded system because the developers believe that when a data set is much bigger than available memory (usual for big databases) it doesn't make sense to cache much in the DB engine because the OS can do it too and you can save memory... Above is the core of what I wanted to say - PostgreSQL process size was quite small (~100MB) on the bigger machine yet I think I tuned it according to the recommendations and that it is probably expected and correct with PostgreSQL. After comparing my config file to yours I see some differences: I have set some limits lower than you (shared_buffers 65536 vs. 1, work_mem 83886 vs. 1) and one higher (max_fsm_pages 2 vs. 10). I suspect that the main difference in our configs is fsync setting though. I have fsync = off and you the default (on). This may be very important difference, maybe similar to MySQL's innodb_flush_log_at_trx_commit (which, when set to 2, raises the performance of MySQL with InnoDB on FreeBSD significantly). I seem to remember that I read somewhere that fsync is pretty expensive on FreeBSD in comparison to Linux (because they cheat, as usual :-)) so maybe you can give it a try on your PostgreSQL too. It probably is a little dangerous though. HTH Michal ___ freebsd-questions@freebsd.org mailing list http://lists.freebsd.org/mailman/listinfo/freebsd-questions To unsubscribe, send any mail to [EMAIL PROTECTED]
Re: terrible performance in 6.1beta4
[EMAIL PROTECTED] wrote: Miguel attached is my config file, shared_buffers are 25% of total RAM im guessing that this is a disk controlled bug or something, when i execute any query involving many rows, the server response is very low, ssh, su, even copy or rename a file, cpu usage remains ~87% idle though you are not inserting into an indexed table, are you? yes, i am... is that a problem? --- Miguel ___ freebsd-questions@freebsd.org mailing list http://lists.freebsd.org/mailman/listinfo/freebsd-questions To unsubscribe, send any mail to [EMAIL PROTECTED]
Re: terrible performance in 6.1beta4
Kris Kennaway [EMAIL PROTECTED] wrote: On Thu, Mar 30, 2006 at 10:49:01PM +0200, [EMAIL PROTECTED] wrote: Kris, Yes, this is my impression of the problem too. Any time your process is waiting on disk I/O it is going to perform terribly (on any OS - disks are slow), and the way to fix this is to make sure it does as little I/O as possible (by allowing everything to be cached in RAM). just for my curiosity, do you share my opinion on the fsync issue? Actually I seem to recall that on Linux with default settings fsync() lies and does not actually sync data before returning, so maybe it's worth turning off on FreeBSD too if you're comfortable with the implications of this. If you have fsync off and the system crashes, your PostgreSQL database will probably be corrupt beyond repair. I believe the official word from the PostgreSQL folks is that fsync is safe to turn off if you've got battery-backed cache on your disk controllers. Many high-end SCSI controllers have this as an option. Alternatively, if you're just putting the database on for the first time, you can temporarily turn fsync off while you're uploading the data. If the system crashes during this, just delete and recreate the database and try again. It's not generally a good idea to run in production with fsync off, however, unless you have a battery on your controller. -- Bill Moran Potential Technologies http://www.potentialtech.com ___ freebsd-questions@freebsd.org mailing list http://lists.freebsd.org/mailman/listinfo/freebsd-questions To unsubscribe, send any mail to [EMAIL PROTECTED]
Re: terrible performance in 6.1beta4
On Thu, Mar 30, 2006 at 07:24:37PM -0500, Bill Moran wrote: Kris Kennaway [EMAIL PROTECTED] wrote: On Thu, Mar 30, 2006 at 10:49:01PM +0200, [EMAIL PROTECTED] wrote: Kris, Yes, this is my impression of the problem too. Any time your process is waiting on disk I/O it is going to perform terribly (on any OS - disks are slow), and the way to fix this is to make sure it does as little I/O as possible (by allowing everything to be cached in RAM). just for my curiosity, do you share my opinion on the fsync issue? Actually I seem to recall that on Linux with default settings fsync() lies and does not actually sync data before returning, so maybe it's worth turning off on FreeBSD too if you're comfortable with the implications of this. If you have fsync off and the system crashes, your PostgreSQL database will probably be corrupt beyond repair. I believe the official word from the PostgreSQL folks is that fsync is safe to turn off if you've got battery-backed cache on your disk controllers. Many high-end SCSI controllers have this as an option. Alternatively, if you're just putting the database on for the first time, you can temporarily turn fsync off while you're uploading the data. If the system crashes during this, just delete and recreate the database and try again. It's not generally a good idea to run in production with fsync off, however, unless you have a battery on your controller. Yeah..but the thing to remember is that on Linux fsync isn't guaranteeing your data safety anyway, since it's not actually syncing data on disk before returning to the application. Kris pgpM50X6ixwc2.pgp Description: PGP signature
Re: terrible performance in 6.1beta4
Miguel [EMAIL PROTECTED] wrote: [EMAIL PROTECTED] wrote: Miguel attached is my config file, shared_buffers are 25% of total RAM im guessing that this is a disk controlled bug or something, when i execute any query involving many rows, the server response is very low, ssh, su, even copy or rename a file, cpu usage remains ~87% idle though you are not inserting into an indexed table, are you? yes, i am... is that a problem? Yes. BIG problem. 1) Create the tables without indexes 2) Insert data 3) Create the indexes This will be much, much faster than inserting 3G of data into an indexed table. -- Bill Moran Potential Technologies http://www.potentialtech.com ___ freebsd-questions@freebsd.org mailing list http://lists.freebsd.org/mailman/listinfo/freebsd-questions To unsubscribe, send any mail to [EMAIL PROTECTED]
Re: terrible performance in 6.1beta4
Bill, Actually I seem to recall that on Linux with default settings fsync() lies and does not actually sync data before returning, so maybe it's worth turning off on FreeBSD too if you're comfortable with the implications of this. If you have fsync off and the system crashes, your PostgreSQL database will probably be corrupt beyond repair. that is not true. that is not probable, at least, it never happened to me. and i encountered some panics over the last months ( i have been patching multimedia/pvr250 to support 500s ). in fact, PostgreSQL never corrupted on me in 5 years ( i used to use to mysql, which seemed to corrupt by looking at it ). however, i am not in the large number of transactions-business. there is a sustained number of updates though, at least once every 2 minutes. I believe the official word from the PostgreSQL folks is that fsync is safe to turn off if you've got battery-backed cache on your disk controllers. Many high-end SCSI controllers have this as an option. interesting, i didn't know. Alternatively, if you're just putting the database on for the first time, you can temporarily turn fsync off while you're uploading the data. If the system crashes during this, just delete and recreate the database and try again. my suggestion to turn fsync of was geared towards this particular problem, although i did not explicitly state that. it is a good thing you are pointing out potential problems. It's not generally a good idea to run in production with fsync off, however, unless you have a battery on your controller. While you might be very right, my server only has an UPS, and i am comfortable with that given the stability of FBSD. regards, usleep -- Bill Moran Potential Technologies http://www.potentialtech.com ___ freebsd-questions@freebsd.org mailing list http://lists.freebsd.org/mailman/listinfo/freebsd-questions To unsubscribe, send any mail to [EMAIL PROTECTED]
Re: terrible performance in 6.1beta4
Miguel, you are not inserting into an indexed table, are you? yes, i am... is that a problem? it used to be a problem, but since your gentoo box is doing the same task in 4-5 minutes, i doubt it is currently the problem. i seem to recall some speedup of the copyin-command in one of the latest releases of PostgreSQL. i don't know the details, maybe they are deferring index-updates automagically when copying-in ( check PostgreSQL release notes ). Miguel, you have not yet confirmed the two postgresql.conf-files(gentoo vs fbsd) to be identical ( or i missed that ). please let me know. another issue pops to my mind: on 4.x i had to tweak some sysctl's regarding shared-memory ( default settings would not allow PGSQL to claim as much as i would like ). i don't know if this still needs to be done on 6.x. if i recall correctly, postgresql would not start if it could not allocate it's shared buffers. things may have changed: check you postgresql.log to see if it is complaining. please let us know if you achieved any speed update at all. regards, usleep --- Miguel ___ freebsd-questions@freebsd.org mailing list http://lists.freebsd.org/mailman/listinfo/freebsd-questions To unsubscribe, send any mail to [EMAIL PROTECTED]
terrible performance in 6.1beta4
Hi, im loading a lot of information to a postgresql 8.1.3 database, im using the copy command, using the same file and version of postgres in gentoo it spend a few minutes (~4-5), in freebsd 6.1-beta4 it has already spent 25 minutes and the server apparently is doing nothing, and the reponse time of remote logins or running simple commands like ls -l /etc takes a lot of time (35 secs ort so), cpu usage is very low: napstats# top -S last pid: 10712; load averages: 0.13, 0.09, 0.02 up 2+03:43:32 18:59:57 122 processes: 3 running, 87 sleeping, 32 waiting CPU states: 5.3% user, 0.0% nice, 0.9% system, 0.2% interrupt, 93.6% idle Mem: 572M Active, 972M Inact, 266M Wired, 90M Cache, 213M Buf, 2960K Free Swap: 4096M Total, 216K Used, 4096M Free PID USERNAME THR PRI NICE SIZERES STATE C TIME WCPU COMMAND 11 root1 171 52 0K16K CPU1 1 51.6H 93.46% idle: cpu1 12 root1 171 52 0K16K RUN0 51.5H 92.92% idle: cpu0 10671 postgres1 -80 538M 516M biord 1 0:46 8.98% postgres 10384 postgres1 -80 538M 528M biord 0 3:23 0.00% postgres 37 root1 -32 -151 0K16K WAIT 0 2:51 0.00% swi4: clock sio 55 root1 200 0K16K syncer 0 1:44 0.00% syncer 8862 postgres1 -40 537M 524M ufs1 1:10 0.00% postgres 3 root1 -80 0K16K - 0 0:42 0.00% g_up 4 root1 -80 0K16K - 0 0:38 0.00% g_down 36 root1 -44 -163 0K16K WAIT 0 0:25 0.00% swi1: net 29 root1 -68 -187 0K16K WAIT 0 0:22 0.00% irq17: bge0 32 root1 -64 -183 0K16K WAIT 0 0:16 0.00% irq20: atapci1 54 root1 -160 0K16K psleep 0 0:12 0.00% bufdaemon 39 root1 -160 0K16K - 0 0:12 0.00% yarrow 2 root1 -80 0K16K - 0 0:08 0.00% g_event 61 root1 960 0K16K - 0 0:05 0.00% schedcpu 414 root1 960 7532K 1536K select 0 0:04 0.00% ntpd 53 root1 171 52 0K16K pgzero 0 0:03 0.00% pagezero 437 root1 960 9352K 2812K select 0 0:02 0.00% sendmail napstats# systat -vmstat 1 4 usersLoad 0.07 0.08 0.02 Mar 29 19:01 Mem:KBREALVIRTUAL VN PAGER SWAP PAGER Tot Share TotShareFree in out in out Act 5818124448 633656 6804 80896 count All 1937176660849724634413788 pages Interrupts Proc:r p d s wCsw Trp Sys Int Sof Fltcow4145 total 3 56 990 54 373 338 13 27 272880 wire 14: ata 585256 act 4 17: bge 0.7%Sys 0.0%Intr 1.9%User 0.0%Nice 97.4%Idl 1009296 inact 146 20: ata |||||||||| 77936 cache 21: uhc 2960 free 1997 lapic0: ti daefr 1998 lapic1: ti Namei Name-cacheDir-cache prcfr Calls hits% hits% react pdwake zfodpdpgs Disks ad4 ad8 ofodintrn KB/t 0.00 24.49 %slo-z 218464 buf tps 0 146 190 tfree 1727 dirtybuf MB/s 0.00 3.49 10 desiredvnodes % busy0 104 27208 numvnodes 24992 freevnodes i see a lot of fault when using vmstat : napstats# vmstat 1 10 procs memory pagedisks faults cpu r b w avmfre flt re pi po fr sr ad4 ad8 in sy cs us sy id 1 3 0 633036 78428 17 0 0 0 24 17 0 0 218 103 499 0 0 100 0 3 0 633036 76952 66 0 0 0 329 0 0 188 383 843 1286 1 1 98 0 3 0 633036 75008 68 0 0 0 477 0 0 119 314 1020 1007 1 1 98 0 3 0 633036 74032 60 0 0 0 244 0 0 175 371 295 1143 2 0 98 0 3 0 633036 71900 128 0 0 0 537 0 0 163 359 719 1100 3 1 96 0 3 0 633036 68680 151 0 0 0 801 0 1 136 329 989 1145 4 2 94 0 3 0 633036 67196 48 0 0 0 371 0 2 216 404 454 1273 2 1 97 0 3 0 633036 108616 115 7 0 0 657 10933 0 128 318 890 1090 3 4 93 0 3 0 633036 105504 145 0 0 0 676 0 0 173 328 842 1128 5 3 93 0 3 0 633036 103908 76 0 0 0 387 0 0 144 293 448 890 3 0 96 What should i check for
Re: terrible performance in 6.1beta4
On Wed, Mar 29, 2006 at 07:09:26PM -0600, Miguel wrote: Hi, im loading a lot of information to a postgresql 8.1.3 database, im using the copy command, using the same file and version of postgres in gentoo it spend a few minutes (~4-5), in freebsd 6.1-beta4 it has already spent 25 minutes and the server apparently is doing nothing, and the reponse time of remote logins or running simple commands like ls -l /etc takes a lot of time (35 secs ort so), cpu usage is very low: It's spending all its time reading from disk. What else is different between your two systems (hardware, database configuration)? 10671 postgres1 -80 538M 516M biord 1 0:46 8.98% postgres 10384 postgres1 -80 538M 528M biord 0 3:23 0.00% postgres 8862 postgres1 -40 537M 524M ufs1 1:10 0.00% postgres Kris pgpw10aIVmmuU.pgp Description: PGP signature
