Re: [GENERAL] Sorting performance vs. MySQL
Hi, On Tue, Feb 23, 2010 at 12:51 AM, Yang Zhang yanghates...@gmail.com wrote: When running the query in MySQL InnoDB: $ vmstat 10 procs ---memory-- ---swap-- -io --system-- -cpu-- r b swpd free buff cache si so bi bo in cs us sy id wa st 0 13 13733604 83020 5648 2193884 3 3 936 168 2 1 4 2 89 5 0 1 12 13749952 80164 5600 2178032 0 4354 908 4379 3586 2638 0 1 38 60 0 0 19 13762228 80576 5556 2145220 208 3527 1280 3690 3668 2635 1 1 39 59 0 0 19 13778632 79420 5560 2135228 52 4186 1046 4191 3682 2418 0 1 37 62 0 [snip] I'm guessing the swap numbers are because MySQL uses mmap? InnoDB doesn't use mmap. Baron -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] A new log analysis tool for Postgres is available
I've just committed a log-parsing front-end for mk-query-digest, a tool included in Maatkit, so it can parse and analyze Postgres log files. I encourage people to try this functionality and give feedback by replying here, adding to http://code.google.com/p/maatkit/issues/detail?id=535, or jumping on the Maatkit mailing list. A brief mental QA of things I expect people to ask: Q: Why another log analysis tool? Isn't pgfouine good enough? A: The mk-query-digest log analysis tool already existed, and lots of work has gone into making it very good. All I did was add a pg-capable parser to it. I am not trying to play king-of-the-mountain with any other log analysis tool, but in brief browsing around I saw complaints that a mere GB or three of log files was taking hours to parse in pgfouine. I don't have real-life files that big myself, but mk-query-digest has been extensively tuned for performance and has no problems with many gigs of files in other formats (but please do watch out for memory consumption; Perl hogs RAM. Run this on a non-critical server, please.) Additionally, mk-query-digest has some nice properties: just download-and-go with no installation necessary; written in Perl with minimal dependencies so no PHP install or CPAN libraries are necessary; and lots more. Finally, it's not a me-too tool; it is different from pgfouine and different might be a good thing. Q: How do I get it? A: At this point, it's unreleased, but you can get the latest SVN trunk in the usual Maatkit way: wget http://www.maatkit.org/trunk/mk-query-digest;. Q: How do I run it? A: Simple: perl mk-query-digest --type pglog /path/to/logfile should produce a report on the most important queries. Q: Where is the documentation? A: Maatkit's documentation is always embedded within the tools themselves. Use perldoc mk-query-digest and search for pglog to find docs on this specific feature, or use the --help option to get an overview of the tool in general. This is a complex and powerful tool, and I encourage you to learn more about what you can do with it. The default is to do something useful, as you should see by running the command above. Q: What's the development status? A: Dozens of PG-specific unit and integration test cases all pass cleanly. But I need real-life testing and bug reports, feature requests, etc. Also, the reporting format and perhaps some other functionality is not yet PG-aware. There are helpful little copy-paste ready shortcuts for things like examining the structure of tables found within queries; right now these are MySQL-centric. But that will change. That's all for now -- let me know what you think! - Baron -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Logging statement/duration on the same line
I'm writing a log parser front-end. I've seen some log samples that look like this, with the duration and statement on the same line: LOG: duration: 1.565 ms statement: SELECT * FROM users WHERE user_id='692' But in my 8.3.9 test installation, durations are always logged on a separate line. Is the sample above from a different version? Or is there a way to get this output with different configuration? Here's the type of output that I see in 8.3.9: 2010-02-08 15:31:50.872 EST LOG: statement: select 1; 2010-02-08 15:31:50.881 EST LOG: duration: 10.870 ms -- Baron Schwartz Percona Inc: Services and Support for MySQL http://www.percona.com/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Logging statement/duration on the same line
Thanks Tom, Depesz, On Wed, Feb 10, 2010 at 1:15 PM, Tom Lane t...@sss.pgh.pa.us wrote: turn off log_statement and instead set log_min_duration_statement = 0 to log everything via duration logging. That does the trick. Time to write more test cases. Thanks Baron -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Innotop for postgresl
Hi, Is there any software for postgresql like innotop for mysql ? I am not sure. A while ago I saw a demo of pgtop, but I haven't actually used it. I forget exactly who created it, but I think that at least Selena Deckelmann had contributed to it. I would like to monitor postgresql with Cacti, but i didn't find any module for that in cacti, do you know a module for that ? No, but perhaps it would be a good thing to add to http://code.google.com/p/mysql-cacti-templates/ (which despite the name has high-quality templates for quite a few different things). -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Announcement - PostgreSQL Performance Conference
For those who are interested in performance overall and want a good free technical conference, we're holding our first Performance Conference. Bullet points: * April 22 and 23, Santa Clara Convention Center, Santa Clara, California USA * Same time place as MySQL Conference http://www.mysqlconf.com/ * Free. No registration required (but optional registration appreciated). * Technical. Every speaker has been asked to keep it technical. * Intense. It runs all day long, and talks are short. * Not about databases, it's about performance. But we have lots of database talks on the schedule. * http://conferences.percona.com/ - schedule at http://conferences.percona.com/percona-performance-conference-2009/schedule.html - register at http://conferences.percona.com/percona-performance-conference-2009/registration.html As an example, Cary Millsap will be speaking on performance instrumentation. Cary is a leading figure in Oracle performance. His book //Optimizing Oracle Performance// should be required reading for all database developers, not just Oracle developers. And of course, there will be several names PostgreSQL folks will recognize, including Selena Deckelmann, Robert Treat, Robert Hodges, and David Fetter. I hope to see you there. Baron -- Baron Schwartz, Director of Consulting, Percona Inc. Our Blog: http://www.mysqlperformanceblog.com/ Our Services: http://www.percona.com/services.html -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Maximum transaction rate
I am jumping into this thread late, and maybe this has already been stated clearly, but from my experience benchmarking, LVM does *not* lie about fsync() on the servers I've configured. An fsync() goes to the physical device. You can see it clearly by setting the write cache on the RAID controller to write-through policy. Performance decreases to what the disks can do. And my colleagues and clients have tested yanking the power plug and checking that the data got to the RAID controller's battery-backed cache, many many times. In other words, the data is safe and durable, even on LVM. However, I have never tried to do this on volumes that span multiple physical devices, because LVM can't take an atomic snapshot across them, which completely negates the benefit of LVM for my purposes. So I always create one logical disk in the RAID controller, and then carve that up with LVM, partitions, etc however I please. I almost surely know less about this topic than anyone on this thread. Baron -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
