Re: [GENERAL] advocacy: drupal and PostgreSQL
On jeu, 2008-01-17 at 21:25 +0100, Ivan Sergio Borgonovo wrote: Joshua posted the link to Edison's project that can support pg, MS SQL, Oracle, DB2(?)... but well I had the feeling that Edison is a bit ostracised. While I wouldn't define his work a DB AL... well it works so kudos! Unfortunately 6.X should be out soon and his work won't be included and it looks that 7.X infrastructure will be even better. Maybe some people hope to get rich fast enough they won't have to work with any other DB other than MySQL... I registered myself on Drupal devel mailing list and offered my services to fix ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] tcp_keepalives_idle ignored
We have a very busy setup using multiple clusters, slony, etc. My problem relates to the number of postgres procs increasing, and not decreasing when idle. I eventually end up with thousands of idle processes listening on /tmp/.s.PGSQL.5432 and not quitting (eventually bumping into max_connections). What about your client session timeouts? ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] PhpBB 3.x query review
Dear friends, I am reviewing some of PhpBB 3.x queries. This allows me to learn more about PostgreSQL. The thread can be read here: http://area51.phpbb.com/phpBB/viewtopic.php?f=3t=29260 Do not hesitate to post your review there. In pg_tables, I saw that there was an extensive use of sequential scans in phpbb_banlist, a small table of 60 rows. explain analyse SELECT ban_ip, ban_userid, ban_email, ban_exclude, ban_give_reason, ban_end FROM phpbb_banlist WHERE ban_email = '' AND (ban_userid = 100394 OR ban_ip '') Seq Scan on phpbb_banlist (cost=0.00..1.51 rows=13 width=33) (actual time=0.013..0.026 rows=19 loops=1) Filter: (((ban_email)::text = ''::text) AND ((ban_userid = 100394) OR ((ban_ip)::text ''::text))) Total runtime: 0.063 ms I set indexes on ban_email, ban_userid and ban_ip. But the query plan is still the same. Any idea? Kind regards, Jean-Michel ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] calculating shared data memory space
Dear Friends, I am running a phpBB forum with more than 400.000 messages. I would like to make sure that all indexes fit in shared memory. How can I calculate the needed space of all indexes? I remember this was part of VACUUM FULL ANALYSE or the like. Kind regards, Jean-Michel ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] Casting hexadecimal IPs to readable content
Dear Friends, My PhpBB forum with 4000 users was hacked in Switzerland. Enquirers (not my own idea) proposed that I look for certain IPs in my PostgreSQL 8.2 database. The problem is that PhpBB stored IPs as strings, which seem to be more or less encoded. In PhpBB, IPs are stored as Hexa: 54dc0636 52e1fcb6 How to cast these values to readable content? Any idea ? This is an important issue for me. Thank you for any help. Kind regards, Jean-Michel ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Tsearch2 / Create rule on select
Le samedi 10 mars 2007 à 08:32 +0300, Oleg Bartunov a écrit : btw, we're working on new FTS feature of PostgreSQL, it's basically tsearch integrated into the pg core. But it has some new features and new SQL commands for configuring of FTS, so if the matter is not pressing I'd recommend to check http://mira.sai.msu.su/~megera/pgsql/ftsdoc Great. Thank you very much Oleg. As suggested, I will play with tsearch_core-0.37.gz patch. After testing first, I might be interested in using tseach_core on a production database. It is a community forum with 200.000 messages. tseach-core would only be used for searching messages. There is no money at stake, the database is backed-up twice a day using cron jobs. Whenever a bug happens, are there chances that PostgreSQL database gets perverted? On only the parser? If there is no chance to destroy the database, I may be interested in using Tsearch-core in production, quickly. When will tsearch-core become part of CVS-head? Kind regards, Jean-Michel ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Tsearch2 / Create rule on select
Le vendredi 09 mars 2007 à 10:58 +0100, Magnus Hagander a écrit : No idea. Assuming you want to do it beforehand. otherwise, just create the index and see how large it got? Thank you for your comments. I will add TSeach2 support to phpBB 3.x soon. I had incredible response time on simple queries on more than plain-text 200.000 row. About 1 millisecond! Incredible but true. explain analyse verbose SELECT * FROM phpbb_posts_text WHERE idxfti @@ 'jmp'::tsquery limit 100; Limit (cost=0.00..444.47 rows=100 width=934) (actual time=0.046..0.824 rows=100 loops=1) - Index Scan using idxfti_idx on phpbb_posts_text (cost=0.00..1053.38 rows=237 width=934) (actual time=0.040..0.418 rows=100 loops=1) Index Cond: (idxfti @@ '''jmp'''::tsquery) [color=red]Total runtime: 1.068 ms TSearch2 will be used by wikimedia shortly (it is supported in beta version). IMHO, it would be better if PostgreSQL parser was able to find Tseach indexes alone, rewritting the query automatically. Maybe it is on the radar list of hackers. Kind regards, Jean-Michel ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Tsearch2 / Create rule on select
Dear Friends, I am very impressed by TSearch2 and would like to thank Oleg and the team for their hard work. I would like to migrate a phpBB forum with more that 200.000 messages to TSeach. Full text searches have become a bottleneck. When there are more than 200 users, the server slow dramatically. I need TSearch! So ... I installed TSearch2, a French dictionnary, everything is okay. My database is PostgreSQL 8.2.3, UTF-8. My questions now: 1) Should I migrate phpBB to TSeach2 in PHP code, rewriting the queries, which is quite obvious or easy. OR 2) Is there a smart way to catch queries on the fly in PostgreSQL, for example using CREATE RULE _RETURN AS ON SELECT TO t1 DO INSTEAD or the like. The idea would be that when I search on a text field, PostgreSQL rule system would rewrite the query to seach using Tsearch2. Is there any tool in TSeach2 doing this query rewriting rule? Obviously, it does not seem to exist, but I would like to be sure. 3) Gin indexes Are Gin indexes recommended for large databases? How to calculate the size in memory of an index? Kind regards, Jean-Michel ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
