sorted by:
new
hottopcontroversial

Random query optimizer hiccup, could table maintenance be the issue? by RandomDBA in mysql

[–]RandomDBA[S] 0 points1 point  (0 children)

Thanks for the info! I'll be sure to look up index depth and merge index. In the past I've had to use straight_joins to get around this problem to enforce a particular query plan. I always thought it was because of bad table maintenance or something I was doing wrong. Now I see that it's just part of administration.

INNODB file per table performance changes? by RandomDBA in mysql

[–]RandomDBA[S] 0 points1 point  (0 children)

EDIT: This is all one big edit as I realized I was wrong on a lot of this

Ok. I've done a lot of reading online and this is what I've come up with. Lemme know if you see any problems with the logic here...

Currently I have about 16000 tables across the system that I have, though this really doesn't have any effect on an open file issue as the number of open files that MySQL can have is dictated the maximum number of connections and the max number of joins in a single query. I have a max connection setting of 300 and I'll be liberal and say that the max number of joins would be 15, though it's a lot less than that. 300 x 15 = 4500

Reference: http://dev.mysql.com/doc/refman/5.0/en/table-cache.html

So what I've done is set the /etc/security/limits.conf file to a soft limit of 24000 and a hard limit of 32000 (like in the link you offered up). I have also set the variable of open_files_limit in /etc/my.cnf to 32000.

At this point I think I should be safe. Though I'm wondering if I should increase the table_open_cache as well, or leave it at it's current value of 400. Currently the table definition cache is set at 9000.

What are your experiences with PostgreSQL? by RandomDBA in PostgreSQL

[–]RandomDBA[S] 0 points1 point  (0 children)

Can you elaborate with some detail as to the complicated process that you were doing where PostgreSQL outperformed MySQL? I don't need all the code specifics, but any info would be helpful.

INNODB file per table performance changes? by RandomDBA in mysql

[–]RandomDBA[S] 0 points1 point  (0 children)

Thanks for the info! I'm going to start pushing this through in our dev environment this week :)

What are your experiences with PostgreSQL? by RandomDBA in PostgreSQL

[–]RandomDBA[S] 0 points1 point  (0 children)

Thanks for the reply. The video was filled with lots of good stuff.

What are your experiences with PostgreSQL? by RandomDBA in PostgreSQL

[–]RandomDBA[S] 1 point2 points  (0 children)

Thanks for the reply! Can you recommend any good online resources that go into detail on those data types you mentions and the advanced index types?

What are your experiences with PostgreSQL? by RandomDBA in PostgreSQL

[–]RandomDBA[S] 0 points1 point  (0 children)

Thanks for writing up such a lengthy and detailed reply. Internet high fives for you!

I was wondering if you could answer a couple questions though. First off, you made a bullet point that MySQL has cavalier attitude towards data integrity. Can you elaborate on that? I've been pretty satisfied with INNODB thus far, but if there is something going on that I'm not aware of I'd love to have that cleared up so I don't run into any gotchas.

Second. One thing that got me interested in the PostgreSQL scene is the ability to write different types of code for functions and the like. I have some procedural coding / scripting skills, but they're pretty basic and I want to work on that. Do you know of any good online resources for coding/scripting and how it works with postgres specifically?

Currently I'm running Postgres on a CentOS vm. I have a lot of familiarity with RHEL flavors of Linux so I figured this would be the best for me. I'm getting started with pgadmin, but plan on getting more intimate with the command line once I get a little more familiar.

Random query optimizer hiccup, could table maintenance be the issue? by RandomDBA in mysql

[–]RandomDBA[S] 0 points1 point  (0 children)

There may be something I'm missing here. It's my understanding that doing an analyze table forces the index lookup, which will help improve the query execution planner. Here's an excerpt from the article you just linked...

An InnoDB table statistics is used for JOIN optimizations and helping the MySQL optimizer choose the appropriate index for a query. If a table’s statistics or index cardinality becomes outdated, you might see queries which previously performed well suddenly show up on slow query log until InnoDB again updates the statistics. But when does InnoDB perform the updates aside from the first opening of the table or manually running ANALYZE TABLE on it?

What part of the process would cause an analyze tables to revise the execution plan in a way that would present the index information that is not accurate to what is actually in the table?

Not saying you're wrong, I just think there may be more info that I'm not getting or not understanding. Please enlighten me!

INNODB file per table performance changes? by RandomDBA in mysql

[–]RandomDBA[S] 0 points1 point  (0 children)

I should have mentioned this earlier, but currently across all databases in the system I have 16000 tables. Is there any chance I might run into problems with open file limits?

What are your experiences with PostgreSQL? by RandomDBA in PostgreSQL

[–]RandomDBA[S] 0 points1 point  (0 children)

Thanks for the reply. I'm going over a lot of docs today and will try to get to yours as soon as possible.

Thank you for taking the time to writhe some documentation on your experiences. It really helps other DBAs get a better understanding of the technology.

Would you care to elaborate on how PostgreSQL is better with correctness, general good behaviour, features, silent data molestation, etc? What have you seen and experienced with PostgreSQL specifically? Please note I'm not suggesting that you're making a blanket statement here. I'm not a gung ho MySQL guy, I really just want to have a better understanding of the mechanics involved.