| Extracting Multiple Values from Single Field Posted: 28 Mar 2013 05:39 PM PDT I'm an SQL beginner and I'm having trouble searching correctly for the answer to this question, although I'm sure that it's been asked before. I have an column in an SQL table that looks like this: custom_fields (this is the column name)
age_range=35-49
gender=Male
how_heard=Other (All three of these values are in the same field) Is there any way to reach inside of this field with an SQL statement to get the values individually? I'd like to make three lists of the individual values to export to CSV files in a vertical columns. Basically, the result should be a column like (for each value): age_range
35-49
45-55
25-35
... etc. I've been trying: SELECT custom_fields.age_range FROM arp3_cam WHERE 1 But this is obviously incorrect. I'd really appreciate any hints on how to filter for values inside the same field -- feel free to redirect me to an answer if this has already been asked somewhere else. Thanks! EDIT: based on other posts I've been reading, this setup violates first normal form, however, I still need to extract the data, if possible. I definitely wish the values in "custom_fields" had been added as individual columns. |
| MySQL: parallel restores at same time from backup files? Posted: 28 Mar 2013 08:54 PM PDT Slightly related to this question, but for MySQL: Would it be possible/wise to execute 2 parallel restores from a single backup? Is it possible/wise to do a parallel restore of two independent databases from two separate MySQL dump files? Restoring one of them seems to only take up about 10% of CPU cycles consistently and takes a long time to finish, so I'm thinking that a second restore shouldn't cause any load problems - but might it cause any other MySQL/DB issues? |
| Relating ExecutionInstanceGUID to the SSISDB Posted: 28 Mar 2013 04:06 PM PDT The 2012 release of SQL Server Integration Services, SSIS, has delivered an SSISDB catalog which tracks the operations of packages (among other things). The default package execution for solutions using the Project Deployment model will have logging to the SSISDB turned on. When a package executes, the System::ExecutionInstanceGUID is populated with a value that, if one were using explicit logging (to sys.sysdtslog90/sys.sysssislog) would record all the events for a specific package execution. What I'd like to know, is how do I tie an ExecutionInstanceGUID to anything in the SSISDB catalog. Alternatively, is an SSIS package executing in the SSISDB privy to the value of its catalog.executions.execution_id Ultimately, I am trying to use the existing, custom audit table and link it back to the detailed history in the SSISDB catalog but can't seem to find the link. |
| limitations of key-values store on top of mysql table [closed] Posted: 28 Mar 2013 02:20 PM PDT Consider some implementation of key-value store on top of one mysql table with only two field "key" and "value". Would this be a "good" key-values store, or creating it on top of mysql would have some limitations? |
| MySQL - ERROR 1045 (28000): Access denied for user Posted: 28 Mar 2013 06:37 PM PDT I just installed a fresh copy of Ubuntu 10.04.2 LTS on a new machine. I logged into MySQL as root: david@server1:~$ mysql -u root -p123
I created a new user called repl. I left host blank, so the new user can may have access from any location. mysql> CREATE USER 'repl' IDENTIFIED BY '123'; Query OK, 0 rows affected (0.00 sec)
I checked the user table to verify the new user repl was properly created. mysql> select host, user, password from user; +-----------+------------------+-------------------------------------------+ | host | user | password | +-----------+------------------+-------------------------------------------+ | localhost | root | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 | | server1 | root | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 | | 127.0.0.1 | root | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 | | ::1 | root | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 | | localhost | | | | server1 | | | | localhost | debian-sys-maint | *27F00A6BAAE5070BCEF92DF91805028725C30188 | | % | repl | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 | +-----------+------------------+-------------------------------------------+ 8 rows in set (0.00 sec)
I then exit, try to login as user repl, but access is denied. david@server1:~$ mysql -u repl -p123 ERROR 1045 (28000): Access denied for user 'repl'@'localhost' (using password: YES) david@server1:~$ mysql -urepl -p123 ERROR 1045 (28000): Access denied for user 'repl'@'localhost' (using password: YES) david@server1:~$
Why is access denied? |
| h2 Java server synchronization and caching Posted: 28 Mar 2013 10:26 AM PDT I'm happily coding a client-server application in Java using H2 as database. The server runs the database as an embedded local database while clients connect to it via TCP. Now more and more I see myself in need of syncing between clients and server which requires complex messaging and threads keeping track of the last change to the database. Caching is also a good thing I might need because right now each client requests the same table every time an update has been made. Now my question is: isn't there something for syncing and caching with Java and H2 which is quick and easy library not an entire stack of database layer like hibernate or Mongrel? I feel like reinventing the wheel for something not extremely complex to do. |
| Which database could handle storage of billions/trillions of records? Posted: 28 Mar 2013 03:36 PM PDT We are looking at developing a tool to capture and analyze netflow data, of which we gather tremendous amounts of. Each day we capture about ~1.4 billion flow records which would look like this in json format: { "tcp_flags": "0", "src_as": "54321", "nexthop": "1.2.3.4", "unix_secs": "1352234521", "src_mask": "23", "tos": "0", "prot": "6", "input": "105", "doctets": "186", "engine_type": "0", "exaddr": "2.3.4.5", "engine_id": "2", "srcaddr": "9.8.7.6", "dst_as": "12345", "unix_nsecs": "752265174", "sysuptime": "2943529544", "dst_mask": "24", "dstport": "80", "last": "2943523241", "srcport": "52672", "dpkts": "4", "output": "111", "dstaddr": "6.5.4.3", "first": "2943517993" }
We would like to be able to do fast searches (less than 10 seconds) on the data set, most likely over narrow slices of time (10 - 30 mintes intervals). We also want to index the majority of the data points so we can do searches on each of them quickly. We would also like to have an up to date view of the data when searches are executed. It would be great to stay in the open source world, but we are not opposed to looking at proprietary solutions for this project. The idea is to keep approximately one month of data, which would be ~43.2 billion records. A rough estimate that each record would contain about 480 bytes of data, would equate to ~18.7 terabytes of data in a month, and maybe three times that with indexes. Eventually we would like to grow the capacity of this system to store trillions of records. We have (very basically) evaluated couchbase, cassandra, and mongodb so far as possible candidates for this project, however each proposes their own challenges. With couchbase the indexing is done at intervals and not during insertion of the data so the views are not up to date, cassandra's secondary indexes are not very efficient at returning results as they typically require scanning the entire cluster for results, and mongodb looks promising but appears to be far more difficult to scale as it is master/slave/sharded. Some other candidates we plan to evaluate are elasticsearch, mysql (not sure if this is even applicable), and a few column oriented relational databases. Any suggestions or real world experience would be appreciated. |
| For a InnoDB only DB, which of these elements can be removed? Posted: 28 Mar 2013 10:46 AM PDT So, I'm trying to set up a Drupal 7 my.conf file that's combining best practices from various performance blogs. I'm realizing though that some of them are older than others, and many aren't assuming InnoDB. So of this list of configs, which are irrelevant if you're building for InnoDB. [client] port = 3306 socket = /var/run/mysqld/mysqld.sock [mysqld_safe] socket = /var/run/mysqld/mysqld.sock nice = 0 open-files-limit = 4096 [mysqld] port = 3306 user = mysql default_storage_engine default-storage-engine = InnoDB socket = /var/run/mysqld/mysqld.sock pid_file = /var/run/mysqld/mysqld.pid basedir = /usr tmpdir = /tmp lc-messages-dir = /usr/share/mysql local-infile = 0 automatic_sp_privileges = 0 safe-user-create = 1 secure-auth = 1 secure-file-priv = /tmp symbolic-links = 0 key_buffer_size = 32M myisam-recover = BACKUP,FORCE concurrent_insert = 2 max_allowed_packet = 16M max_connect_errors = 1000000 datadir = /var/lib/mysql tmp_table_size = 64M max_heap_table_size = 64M query_cache_type = 1 query_cache_size = 0 query_cache_limit = 8M query_cache_min_res_unit = 1K default-storage-engine = InnoDB thread_stack = 256K thread_cache_size = 128 max_connections = 128 open_files_limit = 65535 skip-locking skip-bdb server-id = 1 log_bin = /var/log/mysql/mysql-bin.log binlog_cache_size = 256K sync_binlog = 256 expire_logs_days = 14 max_binlog_size = 1G binlog_do_db = include_database_name binlog_ignore_db = include_database_name max_user_connections = 150 key_buffer = 16M key_cache_block_size = 4K bulk_insert_buffer_size = 8M myisam_sort_buffer_size = 64M join_buffer_size = 8M read_buffer_size = 2M sort_buffer_size = 3M read_rnd_buffer_size = 64M table_cache = 4096 table_definition_cache = 4096 table_open_cache = 16384 optimizer_search_depth = 4 collation-server = utf8_general_ci interactive_timeout = 400 wait_timeout = 300 connect_timeout = 10 thread_concurrency=8 back_log = 2048 open-files = 10000 query_prealloc_size = 65536 query_alloc_block_size = 131072
|
| Missing data after attaching .mdf file Posted: 28 Mar 2013 12:42 PM PDT Recently, one of my customers had a server crash. Unfortunately, the 3th party who was responsible for the backup was 'forgotten' to schedule a backup of the DB (I know, I should have notice that). After a professional recovery, I have the .mdf and .ldf files back. I was able to attach them back to SQL Server 2005 Express. At first glance, everything looked ok. However, the most recent changes (say from one month before crash) are missing. Has anyone seen that before? Does anyone knows a solution for that? Is it possible that it is still in the .ldf? I think the recovery model was Simple. As a side note, I'm sure that the data was there since there is a printed version. I'm also sure that it is the most recent .mdf / .ldf that is recovered and sent. I did already several DBCC checkDB with different parameters (every time on a fresh .mdf/.ldf combination of course). I know there is a consistency error somewhere but it doesn't block me to query the last 20000 rows. So expect to find the last records there (or I am wrong here?). The fix of this consistency error is lower prio since it occurs in the older data region. Worst case scenario, we can remove that part. So, please help me with the missing data. Thanks in advance! |
| How did this MV refresh automatically, when it is defined to refresh only "ON DEMAND"? Posted: 28 Mar 2013 04:03 PM PDT This seems a bit odd but I can't put my finger on the issue.. As this happened on the Production database, I am confident that no DB user has initiated the refresh from their side. Is there some know bug or other factors that could have caused the refresh? I defined an MV (materialized view) that is refreshed COMPLETE ON DEMAND using the below code: DBMS_MVIEW.REFRESH(ON_DEMAND_MV, 'C');
The Source of ON_DEMAND_MV is as follows: CREATE MATERIALIZED VIEW ON_DEMAND_MV REFRESH COMPLETE ON DEMAND AS SELECT ... FROM ...;
Recently I noticed that the MV had refreshed automatically in between of our on-demand refreshes that are triggered by Informatica Sessions (an ETL tool) - I confirmed the same by looking at the last_refresh_date column in the all_mviews table for corresponding MV. Is there anything I could do to further debug this issue..? Thank you. |
| What is the relationship between keys, indices, primary keys and clustered keys in RDBMS? Posted: 28 Mar 2013 06:13 PM PDT I am pretty confused about these terms in RDBMS: Keys, and indices: Are they the same concept? For example: CREATE TABLE FOO_TABLE ( THE_KEY INTEGER PRIMARY KEY FOO INTEGER NOT NULL, BAR INTEGER NOT NULL, QUZ INTEGER UNIQUE, CONSTRAINT UNQ_FOO UNIQUE (FOO, BAR) );
In this table, which is a key? Which can be called an index? Is there 'non-primary key' in this table? And how does they improve the efficiency of searching? Thanks. |
| Advise On SQL Server Data Replication Posted: 28 Mar 2013 10:49 AM PDT We have a huge enterprise system, with geographically seperated SQL servers. We have some requirements related with data replication between these servers. We have considered the Sync Framework option, but there is a requirement regarding multiple parallel data replication. Thus, Sync framework does not seem to be an option. Also, we do not have a central database; requirements state that user shall configure the topology and related conflict resolution steps. There are three conflict resolution options; "source wins", "destionation wins" and "timestamp based". Also, note that, database structure is a bit complicated and we have some deep hierarchies. Lastly, the only way of communication between these servers is using port 80; using other ports is not an option. I need to decide how to implement the replication structure, but I do not seem to have any options other than creating a replication structure manually and implementing all features. Do you know any tools, or have any ideas how to handle replication easily without any central SQL server and with user configurable topologies? |
| How does ORDER BY really work? [duplicate] Posted: 28 Mar 2013 11:03 AM PDT This question already has an answer here: I have one question that confusing me. By logical query processing, the SELECT clause comes before the ORDER BY clause, and in theory, every step in a query creates a virtual table. So, how is it even possible to order result by a column that is not listed in the SELECT clause, when the SELECT clause generates a virtual table with the selected columns? In case your query has a GROUP BY clause, then the behavior become normal, but in the absence of a GROUP BY clause, it seems a little tricky. Does anybody have an explanation? |
| Drupal 7 Uses InnoDB, so do I need other configs? Posted: 28 Mar 2013 10:47 AM PDT If I'm optimizing a database that will be delivering multiple Drupal 7 websites, all using InnoDB, do I need to worry about [myisamchk] & [isamchk] sections, or can I just worry about the stuff within [mysqld]? I'm assuming if I delete these sections, that the defaults will just kick in if they are needed. I'm trying to document, outline my.conf best practices here http://groups.drupal.org/node/289613 |
| MySQL Federated acces to PostgreSQL table Posted: 28 Mar 2013 04:35 PM PDT For example 2 DBases: - DBMS Postgresql
- DBMS MySQL
In each DB include one DB and one table in it. Can I work with table in PostgreSQL through MySQL? Is it possible to link as federated postgresql table to mysql database? For make insert/update/select/delete or join operations with such linked table. I can link MySQL to MySQL by code below and make all operations. CREATE TABLE `f_t21` ( `id` int(11) DEFAULT NULL, `name` varchar(255) DEFAULT NULL ) ENGINE=FEDERATED DEFAULT CHARSET=latin1 CONNECTION='mysql://user:pass@192.168.56.102:3306/db/table'
Can I link postgresql table of anyone postgresql version to someone mysql version? for it using as part of federated DB? |
| Inheritance and its alternatives in PostgreSQL Posted: 28 Mar 2013 05:20 PM PDT I make an archaeological database in PostgreSQL 9.2. The description of archaeological context has a subdivision between different types of context. I'm not sure how to implement it: 1) to violate third normal form It is the dirtiest and probably the worst solution. I could check potencial duplicities by triggers, but I suspect it would be the hardest way, for there are quite a lot of type specific columns. 2) inheritance This is better and might be good enough, but my "finds" table should have a foreign key to the "context" table regardles of its type. According to the manual this would cause problems. With all data in tables "layer", "cut", "structure" etc., would the foreign key to "context" table really point to the data stored in the inherited tables? If there's any caveat, is there some (relatively) easy workaround? 3) some sort of 1:1 relation The "finds" table would be connected to "context" table, and tables for various types of context would have a foreign key pointing to the "context" table. But how to assure that exactly one row in all the dependent tables corresponds to each row in "context"? 4) ??? There might be some other solution which I didn't know about. Which of these is easiest to implement (including avoiding duplicity)? And which is best in the terms of performance?
The database will never be really big, so slightly suboptimal performance is no problem, but big differences in performance might be more important than easiness to write it. I'd be glad for answers telling me which option I should use, why, and especially: how to overcome its drawbacks (or that I missed something and there's no real drawback). |
| Maintenance in MYSQL when innodb_file_per_table disabled Posted: 28 Mar 2013 03:57 PM PDT I have read your post and I completely understand OPTIMIZE TABLE to perform in an environment where innodb_file_per_table is disabled, does not shrink the global ibdata1 tablespace. But what if I need to perform index maintenance on InnoDB tables with ANALYZE command, it will grow the single tablespace also? What other alternatives are there to increase performance or doing some maintenance in Innodb engine, when using a single tablespace and without grow out of control the single ibdata1. Regards. Matthew |
| slow queries - set weight to token type in tsquery - postgresql Posted: 28 Mar 2013 07:57 PM PDT Postgresql version 9.2.3! I'm working on a database for mapping of chemical names. My main table contains aprox 91 million records and it's indexed by gin. I want to query it with multiple names (I'm trying now with 100 entries), which I first put in a query table, create a tsquery column of the names and index it with gist. Main table structure: CREATE TABLE IF NOT EXISTS pubchem_compounds_synonyms_all ( cid int NOT NULL references pubchem_compounds_index(cid) ON UPDATE CASCADE ON DELETE CASCADE, name VARCHAR(2000) NOT NULL, synonym VARCHAR(2000) NOT NULL, PRIMARY KEY (cid, name, synonym) ); CREATE TEMPORARY TABLE synonyms_temp AS SELECT * FROM pubchem_compounds_synonyms_all; COPY synonyms_temp FROM '...' delimiters E'\t'; INSERT INTO pubchem_compounds_synonyms_all SELECT DISTINCT * FROM synonyms_temp;
I was trying different approaches, thus for testing gin index I created a clone: CREATE TABLE synonyms_all_gin_tsvcolumn AS SELECT * FROM pubchem_compounds_synonyms_all;
then: ALTER TABLE synonyms_all_gin_tsvcolumn ADD COLUMN tsv_syns TSVECTOR; UPDATE synonyms_all_gin_tsvcolumn SET tsv_syns = to_tsvector(synonym); CREATE INDEX gin_tsv_colum_index ON synonyms_all_gin_tsvcolumn USING GIN (tsv_syns);
The query table is: CREATE TABLE IF NOT EXISTS cmap_names_query ( name VARCHAR (2000) PRIMARY KEY NOT NULL );
Same as in the main table, I fill it in with COPY from via a temp table and then I add the tsquery column: ..... ALTER TABLE cmap_names_query ADD COLUMN lexemes TSQUERY; UPDATE cmap_names_query SET lexemes = plainto_tsquery(name); CREATE INDEX cmap_gist_tsquery_index ON cmap_names_query USING gist (lexemes tsquery_ops);
The query is basically a join between both tables: SELECT DISTINCT ON (c.lexemes) c.name, s.cid, s.synonym, ts_rank(s.tsv_syns,c.lexemes) FROM synonyms_all_gin_tsvcolumn s JOIN cmap_names_query c ON c.lexemes @@ s.tsv_syns;
lexemes is the gist indexed tsquery column on my query table, whereas tsv_syns is the gin indexed tsvector column in the main names table, the one with 91 million records. The query is intended to match names, exact matches if possible. It works very well for such a large table. Normal names, containing only characters, can be retreated even in microseconds. The problem is when the string names contains numbers. The operation tsvector and tsquery create one token for each number, and all together makes the query for this sort of entries rather slow, well, slower. Instead of a few milliseconds, they take aprox 1-2 seconds each. I would like to reduce this query time to a few milliseconds like the other entries, but I don't know how. I have tested it with and without ts_rank to find out that ranking only add half a second to the total query, if it even makes a difference. so that's not my problem Some samples queries are: query: Limit (cost=32.13..36.14 rows=1 width=92) (actual time=40.001..40.002 rows=1 loops=1) Buffers: shared hit=201 -> Bitmap Heap Scan on synonyms_all_gin_tsvcolumn (cost=32.13..100.24 rows=17 width=92) (actual time=39.998..39.998 rows=1 loops=1) Recheck Cond: (tsv_syns @@ plainto_tsquery('10-methoxyharmalan'::text)) Buffers: shared hit=201 -> Bitmap Index Scan on gin_tsv_colum_index (cost=0.00..32.13 rows=17 width=0) (actual time=39.984..39.984 rows=1 loops=1) Index Cond: (tsv_syns @@ plainto_tsquery('10-methoxyharmalan'::text)) Buffers: shared hit=200 Total runtime: 40.037 ms
result: (cid |name|synonym|tsv vector) 5474706 | 6-Methoxyharmalan | 10-Methoxyharmalan | '10':1 'methoxyharmalan':2
query: Limit (cost=32.23..36.23 rows=1 width=92) (actual time=2215.127..2215.128 rows=1 loops=1) Buffers: shared hit=10086 -> Bitmap Heap Scan on synonyms_all_gin_tsvcolumn (cost=32.23..148.34 rows=29 width=92) (actual time=2215.125..2215.125 rows=1 loops=1) Recheck Cond: (tsv_syns @@ plainto_tsquery('1,4-chrysenequinone'::text)) Buffers: shared hit=10086 -> Bitmap Index Scan on gin_tsv_colum_index (cost=0.00..32.22 rows=29 width=0) (actual time=2215.108..2215.108 rows=1 loops=1) Index Cond: (tsv_syns @@ plainto_tsquery('1,4-chrysenequinone'::text)) Buffers: shared hit=10085 Total runtime: 2215.182 ms
result: 180933 | 1,4-Chrysenedione | 1,4-Chrysenequinone | '1':1 '4':2 'chrysenequinon':3
query: Limit (cost=32.00..36.02 rows=1 width=92) (actual time=2852.934..2852.936 rows=1 loops=1) Buffers: shared hit=7292 -> Bitmap Heap Scan on synonyms_all_gin_tsvcolumn (cost=32.00..36.02 rows=1 width=92) (actual time=2852.930..2852.930 rows=1 loops=1) Recheck Cond: (tsv_syns @@ plainto_tsquery('2-deoxy-D-glucose'::text)) Buffers: shared hit=7292 -> Bitmap Index Scan on gin_tsv_colum_index (cost=0.00..32.00 rows=1 width=0) (actual time=2852.897..2852.897 rows=121 loops=1) Index Cond: (tsv_syns @@ plainto_tsquery('2-deoxy-D-glucose'::text)) Buffers: shared hit=7291 Total runtime: 2852.992 ms
result: 40 | 2-deoxy-D-glucose | 2-deoxy-D-glucose | '2':1 'd':4 'deoxi':3 'deoxy-d-glucos':2 'glucos':5
I wonder what the best way to make this last queries faster would be. I have tried with a pre-processing script that removes all the numbers, it speeds up the search up to 3 seconds in total, but I miss the exact/closest match that I was looking for in some of the cases, so that's no use. Other approaches that came to mind where: Sol 1: assign priority weight to word tokens in contrast to numeric ones
I think this could be a potential good solution for me, but as far as I have seen cannot be done. Tsvectors/queries can be labelled, but not token types or IS THERE A WAY TO LABEL TOKENS DIFFERENTLY WITHIN THE SAME TSVECTOR? Sol 2: create a new configuration and delete numeric mappings with ALTER FULLTEXTMAPPING or add numbers and characters like - ( ) to the stopword list
Same as the parser, it might lead me to wrong matches, although since it keeps the positional information it me perform good. I'm not sure how i should do this though. My postgres.conf parameters: shared_buffers = 24GB checkpoint_segments=512 effective_cache_size=72GB work_mem = 1GB system specif. are: 4 x Intel X7550 (@2.00GHz) (total 32 Cores), 256GB Memory, CentOS 5.6 (64 bit), 18TB local disk (SATA-RAID) kernel.shmmax=64GB kernel.shmall=4GB
I have tried lower amounts of shared_buffer and effective_cache_size (16GB and 32GB respectively), no difference in performance from the current one, so I'm planing to change it back to those limits I tried a gist index on querytree lexemes, didn't make much difference I'm a little bit lost and I would appreciate any ideas or possible solutions to speed up my queries. Thanks :) PD: Any recommendations for nonSQL DBs that could improve performance? |
| how to import mysql system "mysql" database Posted: 28 Mar 2013 04:57 PM PDT This question might be already answered but it's almost impossible to google it. When I perform full backup of mysql server using mysqldump --all-databases
then reinstall it from scratch I want to import FULL dump of all databases, including "mysql". I successfully done that by executing mysql -u -p < dumpfile
but now even if all user records are in mysql.user these accounts are not working. I can't even change their password, I always get ERROR 1133 (42000): Can't find any matching row in the user table Restarting mysqld is of no help answer: It was fixed by executing MariaDB [mysql]> flush privileges; Query OK, 0 rows affected (0.26 sec)
now all users work as before |
| SUPER privilege not defined for master user in Amazon MySQL RDS Posted: 28 Mar 2013 12:57 PM PDT I have created one medium instance on amazon rds in asia pecific (singapore) region. i have created my master user with master password. and it is working/connecting fine with workbench installed on my local PC. When, I am going to create function on that instance, it show me following error ERROR 1418: This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you might want to use the less safe log_bin_trust_function_creators variable) At my instance, my variable (log_bin_trust_function_creators) shows OFF. now when I go to change with variable using SET GLOBAL log_bin_trust_function_creators = 1;
it gives me another error Error Code: 1227. Access denied; you need (at least one of) the SUPER privilege(s) for this operation I don't know how to solve this error. Can anybody help??? |
| MySQL optimization Posted: 28 Mar 2013 10:57 AM PDT I'm trying to optimize a MySQL Server to be able to serve as many connections as it can. The server is in AmazonAWS RDS and has currently the following resources: --7.5 GB memory, 4 ECUs (2 virtual cores with 2 ECUs each), 64-bit platform, High I/O Capacity I have run some stress test to check how many connections can serve but no matter what changes I make to the configuration, mysql can not serve more than 800. The rest of the processes are dropped. If someone can help I would be grateful. These are the variables in the configuration: connect_timeout=10 default_storage_engine=InnoDB innodb_adaptive_flushing=ON innodb_adaptive_hash_index=ON innodb_additional_mem_pool_size=2097152 innodb_autoextend_increment=8 innodb_autoinc_lock_mode=1 innodb_buffer_pool_instances=1 innodb_buffer_pool_size=5882511360 innodb_change_buffering=all innodb_checksums=ON innodb_commit_concurrency=0 innodb_concurrency_tickets=500 innodb_data_file_path=ibdata1:10M:autoextend innodb_data_home_dir=/rdsdbdata/db/innodb innodb_doublewrite=ON innodb_fast_shutdown=1 innodb_file_format=Antelope innodb_file_format_check=ON innodb_file_format_max=Antelope innodb_file_per_table=ON innodb_flush_log_at_trx_commit=1 innodb_flush_method=O_DIRECT innodb_force_load_corrupted=OFF innodb_force_recovery=0 innodb_io_capacity=200 innodb_large_prefix=OFF innodb_locks_unsafe_for_binlog=OFF innodb_lock_wait_timeout=50 innodb_log_buffer_size=8388608 innodb_log_files_in_group=2 innodb_log_file_size=134217728 innodb_log_group_home_dir=/rdsdbdata/log/innodb innodb_max_dirty_pages_pct=75 innodb_max_purge_lag=0 innodb_mirrored_log_groups=1 innodb_old_blocks_pct=37 innodb_old_blocks_time=0 innodb_open_files=300 innodb_purge_batch_size=20 innodb_purge_threads=0 innodb_random_read_ahead=OFF innodb_read_ahead_threshold=56 innodb_read_io_threads=4 innodb_replication_delay=0 innodb_rollback_on_timeout=OFF innodb_rollback_segments=128 innodb_spin_wait_delay=6 innodb_stats_method=nulls_equal innodb_stats_on_metadata=ON innodb_stats_sample_pages=8 innodb_strict_mode=OFF innodb_support_xa=ON innodb_sync_spin_loops=30 innodb_table_locks=ON innodb_thread_concurrency=0 innodb_thread_sleep_delay=10000 innodb_use_native_aio=ON innodb_use_sys_malloc=ON innodb_version=1.1.8 innodb_write_io_threads=4 lock_wait_timeout=31536000 lower_case_table_names=1 low_priority_updates=OFF max_allowed_packet=16777216 max_binlog_cache_size=18446744073709547520 max_binlog_size=134217728 max_binlog_stmt_cache_size=18446744073709547520 max_connections=2000 max_connect_errors=10 max_delayed_threads=20 max_error_count=64 max_heap_table_size=16777216 max_insert_delayed_threads=20 max_join_size 18446744073709551615 max_length_for_sort_data=1024 max_long_data_size=16777216 max_prepared_stmt_count=16382 max_relay_log_size=0 max_seeks_for_key=18446744073709551615 max_sort_length=1024 max_sp_recursion_depth=0 max_tmp_tables=32 max_user_connections=0 max_write_lock_count=18446744073709551615 metadata_locks_cache_size=1024 min_examined_row_limit=0 multi_range_count=256 open_files_limit=65535 range_alloc_block_size=4096 read_buffer_size=262144 read_only=OFF read_rnd_buffer_size=524288 skip_external_locking=ON skip_name_resolve=OFF skip_networking=OFF skip_show_database=OFF sort_buffer_size=2097152 storage_engine=InnoDB stored_program_cache=256 sync_binlog=0 sync_frm=ON sync_master_info=0 sync_relay_log=0 sync_relay_log_info=0 table_definition_cache=400 table_open_cache=2048 thread_cache_size=10240 thread_concurrency=10 thread_handling=one-thread-per-connection thread_stack=262144 tmp_table_size=16777216 transaction_alloc_block_size=8192 transaction_prealloc_size=4096 wait_timeout=28800 warning_count=0
|
| How to import table's data in MySql from Sql Server? Posted: 28 Mar 2013 11:57 AM PDT I am trying to export table from SQL Server 2008 R2 TO MySql 5.5. For this I am using SQL Server Import and Export Wizard of Sql Server 2008, but it is giving this error.  Here this error may be occurring because table in Sql Server has a column with data type image and table in MySql this column's data type is LONGBLOB. Please provide your expert answers. If not possible through SQL Server Import and Export Wizard than please suggest any other method for data transfer. |
| Is it possible to pipe the result of a mysqldump straight to rsync as the source argument? Posted: 28 Mar 2013 02:57 PM PDT Is it possible to pipe the result of a mysqldump straight to rsync as the source argument? Conceptually, I was thinking something like: mysqldump -u root -p database_to_backup > db_backup_file.sql | sudo rsync -avz db_backup_file.sql myuser@mysite.com:/var/www/db_backup_file.sql
I've seen people pipe the result to mysql for their one liner backup solution, but I was curious if it was possible with rsync. You know--- cause rsync is magic :) Thanks for your time! |
| Need help with long running query Posted: 28 Mar 2013 01:57 PM PDT UPDATE: Adding a clustered index to each work table covering all fields reduced the query run times to su-bsecond. I consider this question closed. Thanks for taking the time to read this post - it's a bit late so hopefully this is probably borderline coherent if I'm lucky. SQL 2008 on windows 2008. We've got a 3rd party budgeting application that's part of our new ERP system. Users are running a forecasting process. From what I can tell, there are a set of work tables that are populated. Values from these tables are then inserted into a results table. The process has been getting slower and slower over the past few weeks. 2 hours has now become a 24 hour run that doesn't end. I've got sp_whoisactive (a augemented sp_who2 of sorts) running and I see CPU and Reads going through the roof. 10 Billion logical reads during one of today's runs - for one query. The entire DB is cached in memory with only a few physical reads. It's been running for 20 min now and is at 500 million reads. I dug into the INSERT / SELECT statement and started running subsections of it, removing joins and implementing the index recommendations from the showplan. This got everything humming up to a certain point in the process. The work tables were all heaps. Now it's stuck again on a similar SELECT / INSERT and I can't capture the parameters it's currently running with, since I have a trace running with Batch Completed only being captured - at least I don't know a way how to. Wait_Info from sp_whoisactive shows nothing. So tomorrow morning we are going to run it again and I'll capture those parameters. Hopefully that will shed some light. Below is my 'test harness' for the query. I grab a batch id from one of the work tables and then use it to build parameters for the query. The app servers are running JAVA using the Microsoft JDBC drivers, which wraps everything in sp prepares and execs, complicating things a bit. The application vendor has supplied us with a script to insert the same dummy batchID a few thousand times into these temp tables, generate stats and then set them to NORECOMPUTE. However, this wasn't helping and I don't really understand how it would. We're using the Hallengren maintenance scripts for index and stats maintenance. No other systems are having performance problems running on this server at this time. I ran a stock Index and Stats maintenance plan just to be sure I hadn't misconfigured the Hallengren scripts. I'm looking for some additional guidance on how to see what this query is actually working on while it is churning away. SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED --All data is uncommitted at this point. DECLARE @batchid VARCHAR(8000) SELECT @batchid = BF_BATC_STUS_EXEC_ID FROM dbo.BF_ALOC_WORK_ACTY SELECT @batchid DECLARE @P0 VARCHAR(8000) DECLARE @P1 DECIMAL(38, 6) DECLARE @P2 VARCHAR(8000) DECLARE @P3 VARCHAR(8000) DECLARE @P4 VARCHAR(8000) DECLARE @P5 VARCHAR(8000) DECLARE @P6 VARCHAR(8000) DECLARE @P7 VARCHAR(8000) DECLARE @P8 VARCHAR(8000) DECLARE @P9 VARCHAR(8000) DECLARE @P10 VARCHAR(8000) DECLARE @P11 VARCHAR(8000) DECLARE @P12 VARCHAR(8000) DECLARE @P13 VARCHAR(8000) DECLARE @P14 VARCHAR(8000) DECLARE @P15 VARCHAR(8000) DECLARE @P16 VARCHAR(8000) DECLARE @P17 VARCHAR(8000) DECLARE @P18 VARCHAR(8000) DECLARE @P19 VARCHAR(8000) SET @P0 = 'S' SET @P1 = -0.084125 SET @P2 = @batchid SET @P3 = @batchid SET @P4 = @batchid SET @P5 = @batchid SET @P6 = @batchid SET @P7 = @batchid SET @P8 = @batchid SET @P9 = @batchid SET @P10 = @batchid SET @P11 = @batchid SET @P12 = @batchid SET @P13 = @batchid SET @P14 = @batchid SET @P15 = @batchid SET @P16 = @batchid SET @P17 = @batchid SET @P18 = 'FINAL_BUD_TOT' SET @P19 = 'FINAL_BUD_TOT' INSERT INTO BF_ALOC_RSLT ( BF_ALOC_RSLT.ACTY_CD , BF_ALOC_RSLT.BDOB_CD , BF_ALOC_RSLT.FUND_CD , BF_ALOC_RSLT.ORGN_CD , BF_ALOC_RSLT.PROG_CD , BF_ALOC_RSLT.PROJ_CD , BF_ALOC_RSLT.USER_DM1_CD , BF_ALOC_RSLT.USER_DM2_CD , BF_ALOC_RSLT.USER_DM3_CD , BF_ALOC_RSLT.USER_DM4_CD , BF_ALOC_RSLT.USER_DM5_CD , BF_ALOC_RSLT.USER_DM6_CD , BF_ALOC_RSLT.USER_DM7_CD , BF_ALOC_RSLT.USER_DM8_CD , BF_ALOC_RSLT.TYP , BF_ALOC_RSLT.DATA , BF_ALOC_RSLT.ALOC_LINE_GUID , BF_ALOC_RSLT.BF_BATC_STUS_EXEC_ID ) ( SELECT BF_ALOC_WORK_ACTY.RSLT , BF_ALOC_WORK_BDOB.RSLT , BF_ALOC_WORK_FUND.RSLT , BF_ALOC_WORK_ORGN.RSLT , BF_ALOC_WORK_PROG.RSLT , BF_ALOC_WORK_PROJ.RSLT , BF_ALOC_WORK_USER_DM1.RSLT , BF_ALOC_WORK_USER_DM2.RSLT , BF_ALOC_WORK_USER_DM3.RSLT , BF_ALOC_WORK_USER_DM4.RSLT , BF_ALOC_WORK_USER_DM5.RSLT , BF_ALOC_WORK_USER_DM6.RSLT , BF_ALOC_WORK_USER_DM7.RSLT , BF_ALOC_WORK_USER_DM8.RSLT , @P0 , ROUND(SUM(BF_DATA.DATA) * @P1, 2) , @P2 , @P3 FROM BF_ALOC_WORK_ACTY , BF_ALOC_WORK_BDOB , BF_ALOC_WORK_FUND , BF_ALOC_WORK_ORGN , BF_ALOC_WORK_PROG , BF_ALOC_WORK_PROJ , BF_ALOC_WORK_USER_DM1 , BF_ALOC_WORK_USER_DM2 , BF_ALOC_WORK_USER_DM3 , BF_ALOC_WORK_USER_DM4 , BF_ALOC_WORK_USER_DM5 , BF_ALOC_WORK_USER_DM6 , BF_ALOC_WORK_USER_DM7 , BF_ALOC_WORK_USER_DM8 , BF_DATA WHERE ( ( ( BF_DATA.BF_ACTY_CD = BF_ALOC_WORK_ACTY.SRC ) AND ( BF_ALOC_WORK_ACTY.BF_BATC_STUS_EXEC_ID = @P4 ) ) AND ( ( BF_DATA.BF_BDOB_CD = BF_ALOC_WORK_BDOB.SRC ) AND ( BF_ALOC_WORK_BDOB.BF_BATC_STUS_EXEC_ID = @P5 ) ) AND ( ( BF_DATA.BF_FUND_CD = BF_ALOC_WORK_FUND.SRC ) AND ( BF_ALOC_WORK_FUND.BF_BATC_STUS_EXEC_ID = @P6 ) ) AND ( ( BF_DATA.BF_ORGN_CD = BF_ALOC_WORK_ORGN.SRC ) AND ( BF_ALOC_WORK_ORGN.BF_BATC_STUS_EXEC_ID = @P7 ) ) AND ( ( BF_DATA.BF_PROG_CD = BF_ALOC_WORK_PROG.SRC ) AND ( BF_ALOC_WORK_PROG.BF_BATC_STUS_EXEC_ID = @P8 ) ) AND ( ( BF_DATA.BF_PROJ_CD = BF_ALOC_WORK_PROJ.SRC ) AND ( BF_ALOC_WORK_PROJ.BF_BATC_STUS_EXEC_ID = @P9 ) ) AND ( ( BF_DATA.BF_USER_DM1_CD = BF_ALOC_WORK_USER_DM1.SRC ) AND ( BF_ALOC_WORK_USER_DM1.BF_BATC_STUS_EXEC_ID = @P10 ) ) AND ( ( BF_DATA.BF_USER_DM2_CD = BF_ALOC_WORK_USER_DM2.SRC ) AND ( BF_ALOC_WORK_USER_DM2.BF_BATC_STUS_EXEC_ID = @P11 ) ) AND ( ( BF_DATA.BF_USER_DM3_CD = BF_ALOC_WORK_USER_DM3.SRC ) AND ( BF_ALOC_WORK_USER_DM3.BF_BATC_STUS_EXEC_ID = @P12 ) ) AND ( ( BF_DATA.BF_USER_DM4_CD = BF_ALOC_WORK_USER_DM4.SRC ) AND ( BF_ALOC_WORK_USER_DM4.BF_BATC_STUS_EXEC_ID = @P13 ) ) AND ( ( BF_DATA.BF_USER_DM5_CD = BF_ALOC_WORK_USER_DM5.SRC ) AND ( BF_ALOC_WORK_USER_DM5.BF_BATC_STUS_EXEC_ID = @P14 ) ) AND ( ( BF_DATA.BF_USER_DM6_CD = BF_ALOC_WORK_USER_DM6.SRC ) AND ( BF_ALOC_WORK_USER_DM6.BF_BATC_STUS_EXEC_ID = @P15 ) ) AND ( ( BF_DATA.BF_USER_DM7_CD = BF_ALOC_WORK_USER_DM7.SRC ) AND ( BF_ALOC_WORK_USER_DM7.BF_BATC_STUS_EXEC_ID = @P16 ) ) AND ( ( BF_DATA.BF_USER_DM8_CD = BF_ALOC_WORK_USER_DM8.SRC ) AND ( BF_ALOC_WORK_USER_DM8.BF_BATC_STUS_EXEC_ID = @P17 ) ) AND ( ( BF_DATA.BF_TM_PERD_CD = @P18 ) OR ( BF_DATA.BF_TM_PERD_CD IN ( SELECT BF_TM_PERD_RLUP.BF_TM_PERD_CHLD_CD FROM BF_TM_PERD_RLUP WHERE ( BF_TM_PERD_RLUP.BF_TM_PERD_PARN_CD = @P19 ) ) ) ) ) GROUP BY BF_ALOC_WORK_ACTY.RSLT , BF_ALOC_WORK_BDOB.RSLT , BF_ALOC_WORK_FUND.RSLT , BF_ALOC_WORK_ORGN.RSLT , BF_ALOC_WORK_PROG.RSLT , BF_ALOC_WORK_PROJ.RSLT , BF_ALOC_WORK_USER_DM1.RSLT , BF_ALOC_WORK_USER_DM2.RSLT , BF_ALOC_WORK_USER_DM3.RSLT , BF_ALOC_WORK_USER_DM4.RSLT , BF_ALOC_WORK_USER_DM5.RSLT , BF_ALOC_WORK_USER_DM6.RSLT , BF_ALOC_WORK_USER_DM7.RSLT , BF_ALOC_WORK_USER_DM8.RSLT )
Edits:
SHOWPLAN Text |--Table Insert(OBJECT:([PB].[dbo].[BF_ALOC_RSLT]), OBJECT:([PB].[dbo].[BF_ALOC_RSLT].[XIE1_ALOC_RSLT]), SET:([PB].[dbo].[BF_ALOC_RSLT].[ACTY_CD] = [PB].[dbo].[BF_ALOC_WORK_ACTY].[RSLT],[PB].[dbo].[BF_ALOC_RSLT].[BDOB_CD] = [PB].[dbo].[BF_ALOC_WORK_BDOB].[RSLT],[PB].[dbo].[BF_ALOC_RSLT].[FUND_CD] = [PB].[dbo].[BF_ALOC_WORK_FUND].[RSLT],[PB].[dbo].[BF_ALOC_RSLT].[ORGN_CD] = [PB].[dbo].[BF_ALOC_WORK_ORGN].[RSLT],[PB].[dbo].[BF_ALOC_RSLT].[PROG_CD] = [PB].[dbo].[BF_ALOC_WORK_PROG].[RSLT],[PB].[dbo].[BF_ALOC_RSLT].[PROJ_CD] = [PB].[dbo].[BF_ALOC_WORK_PROJ].[RSLT],[PB].[dbo].[BF_ALOC_RSLT].[USER_DM1_CD] = [PB].[dbo].[BF_ALOC_WORK_USER_DM1].[RSLT],[PB].[dbo].[BF_ALOC_RSLT].[USER_DM2_CD] = [PB].[dbo].[BF_ALOC_WORK_USER_DM2].[RSLT],[PB].[dbo].[BF_ALOC_RSLT].[USER_DM3_CD] = [PB].[dbo].[BF_ALOC_WORK_USER_DM3].[RSLT],[PB].[dbo].[BF_ALOC_RSLT].[USER_DM4_CD] = [PB].[dbo].[BF_ALOC_WORK_USER_DM4].[RSLT],[PB].[dbo].[BF_ALOC_RSLT].[USER_DM5_CD] = [PB].[dbo].[BF_ALOC_WORK_USER_DM5].[RSLT],[PB].[dbo].[BF_ALOC_RSLT].[USER_DM6_CD] = [PB].[dbo].[BF_ALOC_WORK_USER_DM6].[RSLT],[PB].[dbo].[BF_ALOC_RSLT].[USER_DM7_CD] = [PB].[dbo].[BF_ALOC_WORK_USER_DM7].[RSLT],[PB].[dbo].[BF_ALOC_RSLT].[USER_DM8_CD] = [PB].[dbo].[BF_ALOC_WORK_USER_DM8].[RSLT],[PB].[dbo].[BF_ALOC_RSLT].[TYP] = RaiseIfNullInsert([Expr1067]),[PB].[dbo].[BF_ALOC_RSLT].[DATA] = RaiseIfNullInsert([Expr1068]),[PB].[dbo].[BF_ALOC_RSLT].[ALOC_LINE_GUID] = RaiseIfNullInsert([Expr1069]),[PB].[dbo].[BF_ALOC_RSLT].[BF_BATC_STUS_EXEC_ID] = RaiseIfNullInsert([Expr1070]),[PB].[dbo].[BF_ALOC_RSLT].[DIV_CD] = NULL,[PB].[dbo].[BF_ALOC_RSLT].[PATN_CD] = NULL)) |--Compute Scalar(DEFINE:([Expr1067]=CONVERT_IMPLICIT(char(1),[@P0],0), [Expr1068]=CONVERT_IMPLICIT(numeric(27,6),round([Expr1066]*[@P1],(2)),0), [Expr1069]=CONVERT_IMPLICIT(varchar(32),[@P2],0), [Expr1070]=CONVERT_IMPLICIT(varchar(32),[@P3],0))) |--Top(ROWCOUNT est 0) |--Stream Aggregate(GROUP BY:([PB].[dbo].[BF_ALOC_WORK_ACTY].[RSLT], [PB].[dbo].[BF_ALOC_WORK_BDOB].[RSLT], [PB].[dbo].[BF_ALOC_WORK_FUND].[RSLT], [PB].[dbo].[BF_ALOC_WORK_ORGN].[RSLT], [PB].[dbo].[BF_ALOC_WORK_PROG].[RSLT], [PB].[dbo].[BF_ALOC_WORK_PROJ].[RSLT], [PB].[dbo].[BF_ALOC_WORK_USER_DM1].[RSLT], [PB].[dbo].[BF_ALOC_WORK_USER_DM2].[RSLT], [PB].[dbo].[BF_ALOC_WORK_USER_DM3].[RSLT], [PB].[dbo].[BF_ALOC_WORK_USER_DM4].[RSLT], [PB].[dbo].[BF_ALOC_WORK_USER_DM5].[RSLT], [PB].[dbo].[BF_ALOC_WORK_USER_DM6].[RSLT], [PB].[dbo].[BF_ALOC_WORK_USER_DM7].[RSLT], [PB].[dbo].[BF_ALOC_WORK_USER_DM8].[RSLT]) DEFINE:([Expr1066]=SUM([PB].[dbo].[BF_DATA].[DATA]))) |--Sort(ORDER BY:([PB].[dbo].[BF_ALOC_WORK_ACTY].[RSLT] ASC, [PB].[dbo].[BF_ALOC_WORK_BDOB].[RSLT] ASC, [PB].[dbo].[BF_ALOC_WORK_FUND].[RSLT] ASC, [PB].[dbo].[BF_ALOC_WORK_ORGN].[RSLT] ASC, [PB].[dbo].[BF_ALOC_WORK_PROG].[RSLT] ASC, [PB].[dbo].[BF_ALOC_WORK_PROJ].[RSLT] ASC, [PB].[dbo].[BF_ALOC_WORK_USER_DM1].[RSLT] ASC, [PB].[dbo].[BF_ALOC_WORK_USER_DM2].[RSLT] ASC, [PB].[dbo].[BF_ALOC_WORK_USER_DM3].[RSLT] ASC, [PB].[dbo].[BF_ALOC_WORK_USER_DM4].[RSLT] ASC, [PB].[dbo].[BF_ALOC_WORK_USER_DM5].[RSLT] ASC, [PB].[dbo].[BF_ALOC_WORK_USER_DM6].[RSLT] ASC, [PB].[dbo].[BF_ALOC_WORK_USER_DM7].[RSLT] ASC, [PB].[dbo].[BF_ALOC_WORK_USER_DM8].[RSLT] ASC)) |--Hash Match(Inner Join, HASH:([PB].[dbo].[BF_DATA].[BF_ACTY_CD])=([PB].[dbo].[BF_ALOC_WORK_ACTY].[SRC]), RESIDUAL:([PB].[dbo].[BF_DATA].[BF_ACTY_CD]=[PB].[dbo].[BF_ALOC_WORK_ACTY].[SRC])) |--Hash Match(Inner Join, HASH:([PB].[dbo].[BF_DATA].[BF_USER_DM2_CD])=([PB].[dbo].[BF_ALOC_WORK_USER_DM2].[SRC]), RESIDUAL:([PB].[dbo].[BF_DATA].[BF_USER_DM2_CD]=[PB].[dbo].[BF_ALOC_WORK_USER_DM2].[SRC])) | |--Hash Match(Inner Join, HASH:([PB].[dbo].[BF_ALOC_WORK_BDOB].[SRC])=([PB].[dbo].[BF_DATA].[BF_BDOB_CD]), RESIDUAL:([PB].[dbo].[BF_DATA].[BF_BDOB_CD]=[PB].[dbo].[BF_ALOC_WORK_BDOB].[SRC])) | | |--Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1008])) | | | |--Index Seek(OBJECT:([PB].[dbo].[BF_ALOC_WORK_BDOB].[IX_COM_BF_ALOC_WORK_BDOB]), SEEK:([PB].[dbo].[BF_ALOC_WORK_BDOB].[BF_BATC_STUS_EXEC_ID]=[@P5]) ORDERED FORWARD) | | | |--RID Lookup(OBJECT:([PB].[dbo].[BF_ALOC_WORK_BDOB]), SEEK:([Bmk1008]=[Bmk1008]) LOOKUP ORDERED FORWARD) | | |--Hash Match(Inner Join, HASH:([PB].[dbo].[BF_ALOC_WORK_USER_DM3].[SRC])=([PB].[dbo].[BF_DATA].[BF_USER_DM3_CD]), RESIDUAL:([PB].[dbo].[BF_DATA].[BF_USER_DM3_CD]=[PB].[dbo].[BF_ALOC_WORK_USER_DM3].[SRC])) | | |--Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1036])) | | | |--Index Seek(OBJECT:([PB].[dbo].[BF_ALOC_WORK_USER_DM3].[IX_COM_BF_ALOC_WORK_USER_DM3_EXEC_ID]), SEEK:([PB].[dbo].[BF_ALOC_WORK_USER_DM3].[BF_BATC_STUS_EXEC_ID]=[@P12]) ORDERED FORWARD) | | | |--RID Lookup(OBJECT:([PB].[dbo].[BF_ALOC_WORK_USER_DM3]), SEEK:([Bmk1036]=[Bmk1036]) LOOKUP ORDERED FORWARD) | | |--Hash Match(Inner Join, HASH:([PB].[dbo].[BF_ALOC_WORK_USER_DM1].[SRC])=([PB].[dbo].[BF_DATA].[BF_USER_DM1_CD]), RESIDUAL:([PB].[dbo].[BF_DATA].[BF_USER_DM1_CD]=[PB].[dbo].[BF_ALOC_WORK_USER_DM1].[SRC])) | | |--Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1028])) | | | |--Index Seek(OBJECT:([PB].[dbo].[BF_ALOC_WORK_USER_DM1].[IX_COM_BF_ALOC_WORK_USER_DM1_EXEC_ID]), SEEK:([PB].[dbo].[BF_ALOC_WORK_USER_DM1].[BF_BATC_STUS_EXEC_ID]=[@P10]) ORDERED FORWARD) | | | |--RID Lookup(OBJECT:([PB].[dbo].[BF_ALOC_WORK_USER_DM1]), SEEK:([Bmk1028]=[Bmk1028]) LOOKUP ORDERED FORWARD) | | |--Hash Match(Inner Join, HASH:([PB].[dbo].[BF_ALOC_WORK_USER_DM8].[SRC])=([PB].[dbo].[BF_DATA].[BF_USER_DM8_CD]), RESIDUAL:([PB].[dbo].[BF_DATA].[BF_USER_DM8_CD]=[PB].[dbo].[BF_ALOC_WORK_USER_DM8].[SRC])) | | |--Table Scan(OBJECT:([PB].[dbo].[BF_ALOC_WORK_USER_DM8]), WHERE:([PB].[dbo].[BF_ALOC_WORK_USER_DM8].[BF_BATC_STUS_EXEC_ID]=[@P17])) | | |--Hash Match(Inner Join, HASH:([PB].[dbo].[BF_ALOC_WORK_FUND].[SRC])=([PB].[dbo].[BF_DATA].[BF_FUND_CD]), RESIDUAL:([PB].[dbo].[BF_DATA].[BF_FUND_CD]=[PB].[dbo].[BF_ALOC_WORK_FUND].[SRC])) | | |--Table Scan(OBJECT:([PB].[dbo].[BF_ALOC_WORK_FUND]), WHERE:([PB].[dbo].[BF_ALOC_WORK_FUND].[BF_BATC_STUS_EXEC_ID]=[@P6])) | | |--Hash Match(Inner Join, HASH:([PB].[dbo].[BF_ALOC_WORK_USER_DM6].[SRC])=([PB].[dbo].[BF_DATA].[BF_USER_DM6_CD]), RESIDUAL:([PB].[dbo].[BF_DATA].[BF_USER_DM6_CD]=[PB].[dbo].[BF_ALOC_WORK_USER_DM6].[SRC])) | | |--Table Scan(OBJECT:([PB].[dbo].[BF_ALOC_WORK_USER_DM6]), WHERE:([PB].[dbo].[BF_ALOC_WORK_USER_DM6].[BF_BATC_STUS_EXEC_ID]=[@P15])) | | |--Hash Match(Inner Join, HASH:([PB].[dbo].[BF_ALOC_WORK_USER_DM5].[SRC])=([PB].[dbo].[BF_DATA].[BF_USER_DM5_CD]), RESIDUAL:([PB].[dbo].[BF_DATA].[BF_USER_DM5_CD]=[PB].[dbo].[BF_ALOC_WORK_USER_DM5].[SRC])) | | |--Table Scan(OBJECT:([PB].[dbo].[BF_ALOC_WORK_USER_DM5]), WHERE:([PB].[dbo].[BF_ALOC_WORK_USER_DM5].[BF_BATC_STUS_EXEC_ID]=[@P14])) | | |--Hash Match(Inner Join, HASH:([PB].[dbo].[BF_ALOC_WORK_PROJ].[SRC])=([PB].[dbo].[BF_DATA].[BF_PROJ_CD]), RESIDUAL:([PB].[dbo].[BF_DATA].[BF_PROJ_CD]=[PB].[dbo].[BF_ALOC_WORK_PROJ].[SRC])) | | |--Table Scan(OBJECT:([PB].[dbo].[BF_ALOC_WORK_PROJ]), WHERE:([PB].[dbo].[BF_ALOC_WORK_PROJ].[BF_BATC_STUS_EXEC_ID]=[@P9])) | | |--Hash Match(Inner Join, HASH:([PB].[dbo].[BF_ALOC_WORK_USER_DM4].[SRC])=([PB].[dbo].[BF_DATA].[BF_USER_DM4_CD]), RESIDUAL:([PB].[dbo].[BF_DATA].[BF_USER_DM4_CD]=[PB].[dbo].[BF_ALOC_WORK_USER_DM4].[SRC])) | | |--Table Scan(OBJECT:([PB].[dbo].[BF_ALOC_WORK_USER_DM4]), WHERE:([PB].[dbo].[BF_ALOC_WORK_USER_DM4].[BF_BATC_STUS_EXEC_ID]=[@P13])) | | |--Hash Match(Inner Join, HASH:([PB].[dbo].[BF_ALOC_WORK_USER_DM7].[SRC])=([PB].[dbo].[BF_DATA].[BF_USER_DM7_CD]), RESIDUAL:([PB].[dbo].[BF_DATA].[BF_USER_DM7_CD]=[PB].[dbo].[BF_ALOC_WORK_USER_DM7].[SRC])) | | |--Table Scan(OBJECT:([PB].[dbo].[BF_ALOC_WORK_USER_DM7]), WHERE:([PB].[dbo].[BF_ALOC_WORK_USER_DM7].[BF_BATC_STUS_EXEC_ID]=[@P16])) | | |--Hash Match(Inner Join, HASH:([PB].[dbo].[BF_ALOC_WORK_PROG].[SRC])=([PB].[dbo].[BF_DATA].[BF_PROG_CD]), RESIDUAL:([PB].[dbo].[BF_DATA].[BF_PROG_CD]=[PB].[dbo].[BF_ALOC_WORK_PROG].[SRC])) | | |--Table Scan(OBJECT:([PB].[dbo].[BF_ALOC_WORK_PROG]), WHERE:([PB].[dbo].[BF_ALOC_WORK_PROG].[BF_BATC_STUS_EXEC_ID]=[@P8])) | | |--Nested Loops(Inner Join, OUTER REFERENCES:([PB].[dbo].[BF_ALOC_WORK_ORGN].[SRC])) | | |--Table Scan(OBJECT:([PB].[dbo].[BF_ALOC_WORK_ORGN]), WHERE:([PB].[dbo].[BF_ALOC_WORK_ORGN].[BF_BATC_STUS_EXEC_ID]=[@P7])) | | |--Sort(DISTINCT ORDER BY:([PB].[dbo].[BF_DATA].[BF_ACTY_CD] ASC, [PB].[dbo].[BF_DATA].[BF_FUND_CD] ASC, [PB].[dbo].[BF_DATA].[BF_PROG_CD] ASC, [PB].[dbo].[BF_DATA].[BF_PROJ_CD] ASC, [PB].[dbo].[BF_DATA].[BF_USER_DM1_CD] ASC, [PB].[dbo].[BF_DATA].[BF_USER_DM2_CD] ASC, [PB].[dbo].[BF_DATA].[BF_USER_DM3_CD] ASC, [PB].[dbo].[BF_DATA].[BF_USER_DM4_CD] ASC, [PB].[dbo].[BF_DATA].[BF_USER_DM5_CD] ASC, [PB].[dbo].[BF_DATA].[BF_USER_DM6_CD] ASC, [PB].[dbo].[BF_DATA].[BF_USER_DM7_CD] ASC, [PB].[dbo].[BF_DATA].[BF_USER_DM8_CD] ASC, [PB].[dbo].[BF_DATA].[BF_BDOB_CD] ASC, [PB].[dbo].[BF_DATA].[BF_TM_PERD_CD] ASC)) | | |--Concatenation | | |--Nested Loops(Inner Join, OUTER REFERENCES:([PB].[dbo].[BF_DATA].[BF_ORGN_CD], [PB].[dbo].[BF_DATA].[BF_ACTY_CD], [PB].[dbo].[BF_DATA].[BF_PROG_CD], [PB].[dbo].[BF_DATA].[BF_PROJ_CD], [PB].[dbo].[BF_DATA].[BF_BDOB_CD], [PB].[dbo].[BF_DATA].[BF_USER_DM1_CD], [PB].[dbo].[BF_DATA].[BF_USER_DM2_CD], [PB].[dbo].[BF_DATA].[BF_USER_DM3_CD], [PB].[dbo].[BF_DATA].[BF_USER_DM4_CD], [PB].[dbo].[BF_DATA].[BF_USER_DM5_CD], [PB].[dbo].[BF_DATA].[BF_USER_DM6_CD], [PB].[dbo].[BF_DATA].[BF_USER_DM7_CD], [PB].[dbo].[BF_DATA].[BF_USER_DM8_CD], [PB].[dbo].[BF_DATA].[BF_FUND_CD], [PB].[dbo].[BF_DATA].[BF_TM_PERD_CD])) | | | |--Index Seek(OBJECT:([PB].[dbo].[BF_DATA].[XIF18_DATA]), SEEK:([PB].[dbo].[BF_DATA].[BF_TM_PERD_CD]=[@P18]), WHERE:([PB].[dbo].[BF_DATA].[BF_ORGN_CD]=[PB].[dbo].[BF_ALOC_WORK_ORGN].[SRC]) ORDERED FORWARD) | | | |--Clustered Index Seek(OBJECT:([PB].[dbo].[BF_DATA].[XPK_DATA]), SEEK:([PB].[dbo].[BF_DATA].[BF_ACTY_CD]=[PB].[dbo].[BF_DATA].[BF_ACTY_CD] AND [PB].[dbo].[BF_DATA].[BF_FUND_CD]=[PB].[dbo].[BF_DATA].[BF_FUND_CD] AND [PB].[dbo].[BF_DATA].[BF_ORGN_CD]=[PB].[dbo].[BF_DATA].[BF_ORGN_CD] AND [PB].[dbo].[BF_DATA].[BF_PROG_CD]=[PB].[dbo].[BF_DATA].[BF_PROG_CD] AND [PB].[dbo].[BF_DATA].[BF_PROJ_CD]=[PB].[dbo].[BF_DATA].[BF_PROJ_CD] AND [PB].[dbo].[BF_DATA].[BF_USER_DM1_CD]=[PB].[dbo].[BF_DATA].[BF_USER_DM1_CD] AND [PB].[dbo].[BF_DATA].[BF_USER_DM2_CD]=[PB].[dbo].[BF_DATA].[BF_USER_DM2_CD] AND [PB].[dbo].[BF_DATA].[BF_USER_DM3_CD]=[PB].[dbo].[BF_DATA].[BF_USER_DM3_CD] AND [PB].[dbo].[BF_DATA].[BF_USER_DM4_CD]=[PB].[dbo].[BF_DATA].[BF_USER_DM4_CD] AND [PB].[dbo].[BF_DATA].[BF_USER_DM5_CD]=[PB].[dbo].[BF_DATA].[BF_USER_DM5_CD] AND [PB].[dbo].[BF_DATA].[BF_USER_DM6_CD]=[PB].[dbo].[BF_DATA].[BF_USER_DM6_CD] AND [PB].[dbo].[BF_DATA].[BF_USER_DM7_CD]=[PB].[dbo].[BF_DATA].[BF_USER_DM7_CD] AND [PB].[dbo].[BF_DATA].[BF_USER_DM8_CD]=[PB].[dbo].[BF_DATA].[BF_USER_DM8_CD] AND [PB].[dbo].[BF_DATA].[BF_BDOB_CD]=[PB].[dbo].[BF_DATA].[BF_BDOB_CD] AND [PB].[dbo].[BF_DATA].[BF_TM_PERD_CD]=[PB].[dbo].[BF_DATA].[BF_TM_PERD_CD]) LOOKUP ORDERED FORWARD) | | |--Nested Loops(Inner Join, OUTER REFERENCES:([PB].[dbo].[BF_DATA].[BF_ORGN_CD], [PB].[dbo].[BF_DATA].[BF_ACTY_CD], [PB].[dbo].[BF_DATA].[BF_PROG_CD], [PB].[dbo].[BF_DATA].[BF_PROJ_CD], [PB].[dbo].[BF_DATA].[BF_BDOB_CD], [PB].[dbo].[BF_DATA].[BF_USER_DM1_CD], [PB].[dbo].[BF_DATA].[BF_USER_DM2_CD], [PB].[dbo].[BF_DATA].[BF_USER_DM3_CD], [PB].[dbo].[BF_DATA].[BF_USER_DM4_CD], [PB].[dbo].[BF_DATA].[BF_USER_DM5_CD], [PB].[dbo].[BF_DATA].[BF_USER_DM6_CD], [PB].[dbo].[BF_DATA].[BF_USER_DM7_CD], [PB].[dbo].[BF_DATA].[BF_USER_DM8_CD], [PB].[dbo].[BF_DATA].[BF_FUND_CD], [PB].[dbo].[BF_DATA].[BF_TM_PERD_CD], [Expr1077]) WITH UNORDERED PREFETCH) | | |--Nested Loops(Inner Join, OUTER REFERENCES:([PB].[dbo].[BF_TM_PERD_RLUP].[BF_TM_PERD_CHLD_CD])) | | | |--Index Seek(OBJECT:([PB].[dbo].[BF_TM_PERD_RLUP].[XIF1_TM_PERD_RLUP]), SEEK:([PB].[dbo].[BF_TM_PERD_RLUP].[BF_TM_PERD_PARN_CD]=[@P19]) ORDERED FORWARD) | | | |--Index Seek(OBJECT:([PB].[dbo].[BF_DATA].[XIF18_DATA]), SEEK:([PB].[dbo].[BF_DATA].[BF_TM_PERD_CD]=[PB].[dbo].[BF_TM_PERD_RLUP].[BF_TM_PERD_CHLD_CD]), WHERE:([PB].[dbo].[BF_DATA].[BF_ORGN_CD]=[PB].[dbo].[BF_ALOC_WORK_ORGN].[SRC]) ORDERED FORWARD) | | |--Clustered Index Seek(OBJECT:([PB].[dbo].[BF_DATA].[XPK_DATA]), SEEK:([PB].[dbo].[BF_DATA].[BF_ACTY_CD]=[PB].[dbo].[BF_DATA].[BF_ACTY_CD] AND [PB].[dbo].[BF_DATA].[BF_FUND_CD]=[PB].[dbo].[BF_DATA].[BF_FUND_CD] AND [PB].[dbo].[BF_DATA].[BF_ORGN_CD]=[PB].[dbo].[BF_DATA].[BF_ORGN_CD] AND [PB].[dbo].[BF_DATA].[BF_PROG_CD]=[PB].[dbo].[BF_DATA].[BF_PROG_CD] AND [PB].[dbo].[BF_DATA].[BF_PROJ_CD]=[PB].[dbo].[BF_DATA].[BF_PROJ_CD] AND [PB].[dbo].[BF_DATA].[BF_USER_DM1_CD]=[PB].[dbo].[BF_DATA].[BF_USER_DM1_CD] AND [PB].[dbo].[BF_DATA].[BF_USER_DM2_CD]=[PB].[dbo].[BF_DATA].[BF_USER_DM2_CD] AND [PB].[dbo].[BF_DATA].[BF_USER_DM3_CD]=[PB].[dbo].[BF_DATA].[BF_USER_DM3_CD] AND [PB].[dbo].[BF_DATA].[BF_USER_DM4_CD]=[PB].[dbo].[BF_DATA].[BF_USER_DM4_CD] AND [PB].[dbo].[BF_DATA].[BF_USER_DM5_CD]=[PB].[dbo].[BF_DATA].[BF_USER_DM5_CD] AND [PB].[dbo].[BF_DATA].[BF_USER_DM6_CD]=[PB].[dbo].[BF_DATA].[BF_USER_DM6_CD] AND [PB].[dbo].[BF_DATA].[BF_USER_DM7_CD]=[PB].[dbo].[BF_DATA].[BF_USER_DM7_CD] AND [PB].[dbo].[BF_DATA].[BF_USER_DM8_CD]=[PB].[dbo].[BF_DATA].[BF_USER_DM8_CD] AND [PB].[dbo].[BF_DATA].[BF_BDOB_CD]=[PB].[dbo].[BF_DATA].[BF_BDOB_CD] AND [PB].[dbo].[BF_DATA].[BF_TM_PERD_CD]=[PB].[dbo].[BF_DATA].[BF_TM_PERD_CD]) LOOKUP ORDERED FORWARD) | |--Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1032], [Expr1078]) WITH UNORDERED PREFETCH) | |--Index Seek(OBJECT:([PB].[dbo].[BF_ALOC_WORK_USER_DM2].[IX_COM_BF_ALOC_WORK_USER_DM2_EXEC_ID]), SEEK:([PB].[dbo].[BF_ALOC_WORK_USER_DM2].[BF_BATC_STUS_EXEC_ID]=[@P11]) ORDERED FORWARD) | |--RID Lookup(OBJECT:([PB].[dbo].[BF_ALOC_WORK_USER_DM2]), SEEK:([Bmk1032]=[Bmk1032]) LOOKUP ORDERED FORWARD) |--Table Scan(OBJECT:([PB].[dbo].[BF_ALOC_WORK_ACTY]), WHERE:([PB].[dbo].[BF_ALOC_WORK_ACTY].[BF_BATC_STUS_EXEC_ID]=[@P4]))
One other note, the query plan shows early termination due to plan timeout. |
| MySQL Workbench sync keeps requesting the same changes Posted: 28 Mar 2013 05:57 PM PDT I am using MySQL Workbench, and when I try to "synchronize" it with my remote database, it keeps detecting some changes to make. Specifically, the most recurrent ones are: - Dropping foreign keys and re-creating the same ones again
- Changing the collation
I was compliant and executed all the queries given to me (and added the semi-colon that they forgot). MySQL didn't complain and executed them. However it didn't help, I can run it 20 times in a row, it will still ask the same useless changes. |
| mysql performance / cache configuration enigma Posted: 28 Mar 2013 09:57 AM PDT I have two mysql 5.1 instances (say A, B) hosting the same database schema. If I run (with mysql workbench) the same query on both instances I don't understand why I get very different response times with subsequent requests. On instance A, first query execution takes 0.688s and second query execution takes 0.683s
On instance B, first query execution takes 0.688s and second query execution takes 0.027s It looks like there's a cache configuration difference between the two instances but I can't find it. Comparing the SHOW VARIABLES result on both instances gives only few distinct values (which I don't see how they may impact query execution time) : general_log_file : /path/to/file2.log VS /path/to/file1.log hostname : mysql2 VS mysql1 pid_file : /var/lib/mysql/mysql2.pid VS /var/lib/mysql/mysql1.pid slave_max_allowed_packet : 1073741824 VS (empty) slow_query_log_file : /var/lib/mysql/mysql2-slow.log VS /var/lib/mysql/mysql1-slow.log system_time_zone : CET VS CEST timestamp : 1352219171 VS 1352219229 version : 5.1.66-0ubuntu0.10.04.1 VS 5.1.62-0ubuntu0.10.04.1
Just to mention, instance A is our test environment and instance B is our production environment Edit : (recommended by @Rick James) The following variables are strictly identical on both environments SHOW VARIABLES LIKE '%buffer%' bulk_insert_buffer_size 8388608 innodb_buffer_pool_size 8388608 innodb_log_buffer_size 1048576 join_buffer_size 131072 key_buffer_size 16777216 myisam_sort_buffer_size 8388608 net_buffer_length 16384 preload_buffer_size 32768 read_buffer_size 131072 read_rnd_buffer_size 262144 sort_buffer_size 2097144 sql_buffer_result OFF SHOW VARIABLES LIKE 'innodb%' innodb_adaptive_hash_index ON innodb_additional_mem_pool_size 1048576 innodb_autoextend_increment 8 innodb_autoinc_lock_mode 1 innodb_buffer_pool_size 8388608 innodb_checksums ON innodb_commit_concurrency 0 innodb_concurrency_tickets 500 innodb_data_file_path ibdata1:10M:autoextend innodb_data_home_dir innodb_doublewrite ON innodb_fast_shutdown 1 innodb_file_io_threads 4 innodb_file_per_table OFF innodb_flush_log_at_trx_commit 1 innodb_flush_method innodb_force_recovery 0 innodb_lock_wait_timeout 50 innodb_locks_unsafe_for_binlog OFF innodb_log_buffer_size 1048576 innodb_log_file_size 5242880 innodb_log_files_in_group 2 innodb_log_group_home_dir ./ innodb_max_dirty_pages_pct 90 innodb_max_purge_lag 0 innodb_mirrored_log_groups 1 innodb_open_files 300 innodb_rollback_on_timeout OFF innodb_stats_method nulls_equal innodb_stats_on_metadata ON innodb_support_xa ON innodb_sync_spin_loops 20 innodb_table_locks ON innodb_thread_concurrency 8 innodb_thread_sleep_delay 10000 innodb_use_legacy_cardinality_algorithm ON
The actual SELECT : select post0_.id as id83_0_, thread1_.id as id81_1_, post0_.createDate as createDate83_0_, post0_.editCount as editCount83_0_, post0_.editDate as editDate83_0_, post0_.editor_id as editor7_83_0_, post0_.postType as postType83_0_, post0_.poster_id as poster8_83_0_, post0_.repliedTo_id as repliedTo9_83_0_, post0_.text as text83_0_, post0_.thread_id as thread10_83_0_, thread1_.created as created81_1_, thread1_.debate_id as debate16_81_1_, thread1_.description as descript4_81_1_, thread1_.lastPostDate as lastPost5_81_1_, thread1_.poster_id as poster17_81_1_, thread1_.status as status81_1_, thread1_.threadType as threadType81_1_, thread1_.title as title81_1_, thread1_.updated as updated81_1_, thread1_.viewCount as viewCount81_1_, thread1_.imageUrl as imageUrl81_1_, thread1_.mediaTypeValue as mediaTy11_81_1_, thread1_.mediaUrl as mediaUrl81_1_, thread1_.modoWeight as modoWeight81_1_, thread1_.shortName as shortName81_1_, thread1_.why as why81_1_, thread1_.theme_id as theme18_81_1_, thread1_.answer_debaterId as answer19_81_1_, thread1_.answer_pollId as answer20_81_1_, (SELECT COUNT(*) FROM Post p WHERE p.thread_id = thread1_.id) as formula9_1_ from Post post0_ inner join Thread thread1_ on post0_.thread_id=thread1_.id where post0_.postType in ( 'P', 'F' ) and thread1_.debate_id=69 and thread1_.threadType<>'B' and length(post0_.text)>0 order by createDate desc limit 5
The EXPLAIN SELECT (sam on both environments) : id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY thread1_ ref PRIMARY,FK9545FA2AEBB74893 FK9545FA2AEBB74893 5 const 3690 Using where; Using temporary; Using filesort 1 PRIMARY post0_ ref FK260CC0D74BC5F3 FK260CC0D74BC5F3 5 debate-atest.thread1_.id 2 Using where 2 DEPENDENT SUBQUERY p ref FK260CC0D74BC5F3 FK260CC0D74BC5F3 5 debate-atest.thread1_.id 2 Using where; Using index
The CREATE TABLE STATEMENT (exact same on both except the constraint names) : delimiter $$ CREATE TABLE `Post` ( `postType` varchar(1) NOT NULL, `id` int(11) NOT NULL AUTO_INCREMENT, `createDate` datetime DEFAULT NULL, `editCount` int(11) NOT NULL, `editDate` datetime DEFAULT NULL, `text` longtext, `editor_id` int(11) DEFAULT NULL, `poster_id` int(11) DEFAULT NULL, `repliedTo_id` int(11) DEFAULT NULL, `thread_id` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `createDateIx` (`createDate`), KEY `FK260CC0D74BC5F3` (`thread_id`), KEY `FK260CC07C1A4F95` (`poster_id`), KEY `FK260CC0960B3775` (`editor_id`), KEY `FK260CC0D7C95B5F` (`repliedTo_id`), CONSTRAINT `FK260CC0D7C95B5F` FOREIGN KEY (`repliedTo_id`) REFERENCES `Post` (`id`), CONSTRAINT `FK260CC07C1A4F95` FOREIGN KEY (`poster_id`) REFERENCES `Debater` (`id`), CONSTRAINT `FK260CC0960B3775` FOREIGN KEY (`editor_id`) REFERENCES `Debater` (`id`), CONSTRAINT `FK260CC0D74BC5F3` FOREIGN KEY (`thread_id`) REFERENCES `Thread` (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=148523 DEFAULT CHARSET=utf8$$
and delimiter $$ CREATE TABLE `Thread` ( `threadType` varchar(1) NOT NULL, `id` int(11) NOT NULL AUTO_INCREMENT, `created` datetime DEFAULT NULL, `description` varchar(700) DEFAULT NULL, `lastPostDate` datetime DEFAULT NULL, `status` int(11) NOT NULL, `title` varchar(100) DEFAULT NULL, `updated` datetime DEFAULT NULL, `viewCount` int(11) NOT NULL, `imageUrl` varchar(255) DEFAULT NULL, `mediaTypeValue` int(11) DEFAULT NULL, `mediaUrl` varchar(1000) DEFAULT NULL, `modoWeight` int(11) DEFAULT NULL, `shortName` varchar(15) DEFAULT NULL, `why` longtext, `debate_id` int(11) DEFAULT NULL, `poster_id` int(11) DEFAULT NULL, `theme_id` int(11) DEFAULT NULL, `answer_debaterId` int(11) DEFAULT NULL, `answer_pollId` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `answerPollIx` (`answer_pollId`), KEY `FK9545FA2AAF24B581` (`theme_id`), KEY `FK9545FA2A7C1A4F95` (`poster_id`), KEY `FK9545FA2AEBB74893` (`debate_id`), KEY `FK9545FA2A82957DB8` (`answer_debaterId`,`answer_pollId`), CONSTRAINT `FK9545FA2A82957DB8` FOREIGN KEY (`answer_debaterId`, `answer_pollId`) REFERENCES `Answer` (`debaterId`, `pollId`), CONSTRAINT `FK9545FA2A7C1A4F95` FOREIGN KEY (`poster_id`) REFERENCES `Debater` (`id`), CONSTRAINT `FK9545FA2AAF24B581` FOREIGN KEY (`theme_id`) REFERENCES `Thread` (`id`), CONSTRAINT `FK9545FA2AEBB74893` FOREIGN KEY (`debate_id`) REFERENCES `Debate` (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=49829 DEFAULT CHARSET=utf8$$
|
| Copy Postgres databases with indexes between instances Posted: 28 Mar 2013 12:54 PM PDT How do you copy a single Postgres database between installations without using pg_dump and without regenerating indexes? We have an application we deploy which uses Postgres 8.4.7 database in Linux. We have a rather large static data set (4GB) which is is indexed. Using pg_dump we have to index the data once it is restored to the second Postgres instance. Indexing this data can take up to 80 minutes, so we would rather deploy the database with the index already generated. From postgres documentation it appears that databases are contained in sub-directories under postgresql/8.4/main/base based on their OID. My first attempt was to create an empty database in Postgres, shutdown the instance, then copy the data from the existing instance to the directory of the OID for the empty database. This failed as my indexes were not working. Preforming a vacuum gave me the following error: WARNING: PD_ALL_VISIBLE flag was incorrectly set in relation "pg_type" page 6 WARNING: PD_ALL_VISIBLE flag was incorrectly set in relation "pg_depend" page 39 ERROR: xlog flush request 0/8B199518 is not satisfied --- flushed only to 0/8AF81680 CONTEXT: writing block 39 of relation base/17004/2608
|
| Getting max values from MySQL tables Posted: 28 Mar 2013 06:57 PM PDT I have three tables: - competitions (id, name, date)
- athletes (id,name)
- results (place, id_athlete, id_competition, ranking_points)
where: results.id_athlet=athlet.id results.id_competition=competitions.id
I need a query to select the latest ranking_points of each athlete based on competitions.date. |
| Minimizing Indexed Reads with Complex Criteria Posted: 28 Mar 2013 09:59 AM PDT I'm optimizing a Firebird 2.5 database of work tickets. They're stored in a table declared as such: CREATE TABLE TICKETS ( TICKET_ID id PRIMARY KEY, JOB_ID id, ACTION_ID id, STATUS str256 DEFAULT 'Pending' );
I generally want to find the first ticket that hasn't been processed and is in Pending status. My processing loop would be: - Retrieve 1st Ticket where
Pending - Do work with Ticket.
- Update Ticket Status =>
Complete - Repeat.
Nothing too fancy. If I'm watching the database while this loop runs I see the number of indexed reads climbs for each iteration. The performance doesn't seem to degrade terribly that I can tell, but the machine I'm testing on is pretty quick. However, I've received reports of performance degradation over time from some of my users. I've got an index on Status, but it still seems like it scans down the Ticket_Id column each iteration. It seems like I'm overlooking something, but I'm not sure what. Is the climbing number of indexed reads for something like this expected, or is the index misbehaving in some way? -- Edits for comments -- In Firebird you limit row retrieval like: Select First 1 Job_ID, Ticket_Id From Tickets Where Status = 'Pending'
So when I say "first", I'm just asking it for a limited record set where Status = 'Pending'. |
| Tools to migrate from SQLite to PostgreSQL Posted: 28 Mar 2013 04:16 PM PDT I need to continuously migrate from SQLite to PostgreSQL. By continuously, I mean that I will reimport the SQLite database into PostgreSQL every day. It would be nice if I could make changes to the tables in SQLite as I please without having to manually make any changes to the PostgreSQL database. I will run the migration from Python, but it could be any external tool, which can be controlled from command line. The tool needs to be available for Linux, and it would be nice if it also ran on Windows. |
No comments:
Post a Comment