Today's Messages (OFF)
| Unanswered Messages (ON)
| Forum: MySQL |
|---|
| Topic: Mysql database for telecommunication application |
|---|
| Mysql database for telecommunication application [message #3453] |
Fri, 22 August 2008 00:07 |
 Murugaa
Messages: 1
Registered: August 2008
Location: Sri Lanka |
Junior Member |
|
|
Telecommunication is a wide range of application relates to database. Most of the telecommunication industries use Mnesia(Default database for Erlang)with Erlang(Ericson Language)
I tried to use Mysql database for telecommunication application with Erlang instead of Mnesia database.This is due to handle the huge loads which is not efficiently handle by the Mnesia.
The main issue is to handle 1000s of concurrent insertaion, updation and deleation operation at a time. The current requirement is 4000 insertaion, 2000 updation and 5000 deletion per seconds with additional select quries.
If anyone had experience with mysql to support on this level?
Regards,
Muru.
|
|
| | Topic: My MySQL datafile seems to growing alarmingly |
|---|
| My MySQL datafile seems to growing alarmingly [message #3444] |
Sun, 17 August 2008 21:36 |
tanveer
Messages: 1
Registered: August 2008 |
Junior Member |
|
|
Hi Friends,
I have a MYSQL database which is currently 40GB size. Normally it grows 100 MB per day. I created a dump last saturday (16-August-2008) using mysqldump. And found that the datafile grew significantly by 10 GB more.
My question is: Did it grow due to my operation? How can deallocate the unused space? Please help me out.
Regards,
Tanveer.
|
|
| | Topic: Partitioning Design |
|---|
| Partitioning Design [message #3430] |
Thu, 14 August 2008 11:27 |
mutpup
Messages: 1
Registered: August 2008 |
Junior Member |
|
|
I'm working on setting up a new schema which will store call detail records for a growing phone company. Right now the size is fairly small (10K inserts per day) but this is growing quickly.
Data is really only looked at in monthly reports and so I figure partitioning the best way to handle the growth.
My idea is to partition by Year and then sub-partition by Month. If I understand right I can't use month(datetime) to partition by and I also cannot have a primary key.
My idea is to use the application to set a year and month. Is that the best way to go or am I missing something fundamental?
Any help would be appreciated.
CREATE TABLE `cdr` (
`id` varchar(64) character set utf8 NOT NULL,
`GATEWAY` varchar(27) character set utf8 NOT NULL,
`calling` varchar(30) character set utf8 NOT NULL,
`called` varchar(30) character set utf8 NOT NULL,
`ingress_tg` varchar(23) character set utf8 NOT NULL,
`egress_tg` varchar(23) character set utf8 NOT NULL,
`duration` float NOT NULL,
`calling_rc` varchar(45) character set utf8 NOT NULL,
`calling_region` varchar(45) character set utf8 NOT NULL,
`calling_lata` varchar(5) character set utf8 NOT NULL,
`calling_ocn` varchar(45) character set utf8 NOT NULL,
`called_rc` varchar(45) character set utf8 NOT NULL,
`called_region` varchar(3) character set utf8 NOT NULL,
`called_lata` varchar(5) character set utf8 NOT NULL,
`called_ocn` varchar(45) character set utf8 NOT NULL,
`btn` varchar(10) character set utf8 NOT NULL,
`orig_datetime` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
`year` smallint(5) unsigned NOT NULL,
`month` tinyint(3) unsigned NOT NULL,
`rate` float NOT NULL default '0',
`type` tinyint(3) unsigned default NULL,
`cond` tinyint(3) unsigned default NULL,
KEY `monthyear` (`year`,`month`),
KEY `tg` USING BTREE (`ingress_tg`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
|
|
| | Topic: load data infile "ENCLOSED BY "clause |
|---|
| load data infile "ENCLOSED BY "clause [message #3429] |
Thu, 14 August 2008 02:31 |
sudhir
Messages: 4
Registered: August 2008 |
Junior Member |
|
|
Hi Friends,
I have problem in load data infile ENCLOSED BY clause. Can anyone suggest solution to my following problem:
I m running the following command :
LOAD DATA INFILE 'myfile' \
INTO TABLE TB_MRR \
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'\
IGNORE 1 LINES \
;"
on running the above command the error encounterd is as follows:
unexpected EOF while looking for matching `''
[Updated on: Thu, 14 August 2008 02:35]
|
|
| | Topic: Why split table got worse performance |
|---|
| Why split table got worse performance [message #3427] |
Wed, 13 August 2008 23:04 |
huilingwei
Messages: 1
Registered: August 2008 |
Junior Member |
|
|
We have a big table for user call. And this table can have at most 100M records. The storage for this table is MyISAM. And since this table will be updated frequently, the table_lock_waited is very high. To reduce it, we tried to split the big table into 10 small ones: t1, t2…t10.
After that we found the performance is even worse if we access multiple small tables at the same time. 
In our testing, in each small table, there are 2.5M records. And all tables are in the same format, the data stored are also similar. The only difference is the data stores data for different users. We used two threads to do the testing: Thread1, Thread2.
1. When thread1 query user data from t1, thread2 also query data from t1, the average response time is about 143ms
Thread1: access table t1
Thread2: access table t1
2. When thread1 query data from t1, and thread2 query data from t2, the average response time is about 338ms.
Thread1: access table t1
Thread2: access table t2
3. If only one thread thread1 query data from t1 and t2 (add a counter, if the counter%10=0, read data from t1, if counter%10=1, read data from t2), the average response time is about 137ms.
Thread1: access table t1 and table t2 alternately
4. Only one thread thread1 read data from t1, the average response time is 77ms.
Thread1: only access table t1
5. We tried use two thread to access MyISAM table and Innodb table, there is no such affect. Thread1 read data from t1, thread2 read data from innodb table test, the response time for thread1 is also about 80ms.
Thread1: access table t1
Thread2: access table test (innodb)
So when we use multiple threads, the response will be slow than only one thread. This can be explained by our testing method. In our testing, after the first response returned, the second request will be sent immediately. The CPU usage will be always 100% even when only one thread is reading data from DB. So when two threads are used, mysql response time will become longer.
But we don’t know why when two tables are accessed at the same time will have worse performance than only one table is accessed.
The variable table_cache=494. And we checked open_table=120, opened_table=140.
The following is my MyISAM table information:
user_id | int(11)
call_id | int(11)
call_direct | tinyint(4)
my_number | varchar(255)
start_time | datetime
end_time | datetime
The index for the table is: user_id+call_id
Thanks and regards,
Myra
|
|
| | Topic: BULK INSERT command not running successfully |
|---|
| BULK INSERT command not running successfully [message #3414] |
Tue, 12 August 2008 03:00 |
sudhir
Messages: 4
Registered: August 2008 |
Junior Member |
|
|
Hi friends
gd mornig to all .
I m trying to insert "," seperated data of a file "Myfile" into table "Mytable" using BULK INSERT command. while i m running the following script it gives an error in Mysql syntax :
BULK INSERT Mydb 'Mytable' FROM 'Myfile'
WITH
(
FIELDTERMINATOR = ',' ,
)
can anyone suggest me how i can run above comand successfully.
any help will be appreciated.
|
|
| | Topic: 1.8% memory usage Still swap memory utilized |
|---|
| 1.8% memory usage Still swap memory utilized [message #3409] |
Mon, 11 August 2008 19:48 |
phoneynk
Messages: 1
Registered: August 2008 |
Junior Member |
|
|
I have mysql installed on a SUSe box it is working as slave.
It has 8GB of physical RAM and mostly is being used only for reporting (Rare execution of complex queries).
the result of top command shows that mysql is utilizing only 1.8% of memory. And other process are using 0% memory each.
but it is showing 204K of swap usage
top - 16:35:38 up 43 days, 12:04, 1 user, load average: 0.00, 0.00, 0.00
Tasks: 98 total, 1 running, 97 sleeping, 0 stopped, 0 zombie
Cpu(s): 0.0%us, 0.0%sy, 0.0%ni,100.0%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Mem: 8125980k total, 8104884k used, 21096k free, 472224k buffers
Swap: 8393952k total, 204k used, 8393748k free, 898100k cached
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
18782 mysql 16 0 604m 146m 4412 S 0 1.8 0:22.30 mysqld
3046 root 16 0 31412 5912 2920 S 0 0.1 0:00.07 snmpd
I was wondering, which process may be using so much of memory.
Please help
Thanks
Ankur
vaish.ankur@gmail.com
|
|
| | Topic: creating index is not always good |
|---|
| creating index is not always good [message #3401] |
Sun, 10 August 2008 09:56 |
tera7
Messages: 1
Registered: August 2008 |
Junior Member |
|
|
Hello i have a db with 2 million rows my id are index so i issue the following :
SELECT * FROM table WHERE cat=3 ORDER BY id DESC LIMIT 100,50;
50 rows in set (0.00 sec)
mysql> explain SELECT * FROM table WHERE cat=3 ORDER BY id DESC LIMIT 100,50;
+----+-------------+----------+-------+---------------+----- -+---------+------+-
--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref |
rows | Extra |
+----+-------------+----------+-------+---------------+----- -+---------+------+-
--------+-------------+
| 1 | SIMPLE | table | index | NULL | id | 4 | NULL |
2818801 | Using where |
+----+-------------+----------+-------+---------------+----- -+---------+------+-
--------+-------------+
1 row in set (0.32 sec)
this is very fast.
If i make the cat field index it takes 12 seconds.At explain it uses as key the cat field reducing the rows scanned to 400000 aprx. but using filesort which is explode the time needed to 12 seconds.
So based to my tests using indexes may not having your desired results.
Also i have another website in which indexes make a big diferrence but i think that mysql must be optimized per case there is not universal standarts.
i am sorry for my bad english.
|
|
| | Topic: Need help for optimizing query |
|---|
| Need help for optimizing query [message #3368] |
Tue, 05 August 2008 03:23 |
Carsten_H.
Messages: 5
Registered: August 2006 |
Junior Member |
|
|
Hello!
I'm running a website where the members can manage their own music collection. The database is growing by each day and so the results of the queries too. Which makes some of them rather slow by this time. I need special help with one query, who reads all the cds, lps etc. of a members collection and sorts them in a user defined order (artist name first or cd title first etc.). Here are the main tables for this:
Table for artist information (excerpt):
---------------------------------------
CREATE TABLE `artist` (
`aID` int(10) unsigned NOT NULL auto_increment,
`aName` varchar(100) NOT NULL default '',
`aFName` varchar(50) NOT NULL default '',
`aLand` varchar(3) NOT NULL default 'XXX',
`aBanned` tinyint(1) NOT NULL default '0',
PRIMARY KEY (`aID`),
KEY `aName` (`aName`),
KEY `aBanned` (`aBanned`),
) ENGINE=InnoDB;
Table for information about cds, lps etc. (excerpt):
----------------------------------------------------
CREATE TABLE `medium` (
`mID` int(10) unsigned NOT NULL auto_increment,
`uID` int(10) unsigned NOT NULL default '0',
`lID` int(10) unsigned NOT NULL default '0',
`mEAN` varchar(15) NOT NULL default '',
`mName` varchar(150) NOT NULL default '',
`mTypeName` varchar(30) NOT NULL default '',
`mYear` smallint(4) NOT NULL default '0',
`mYearOriginal` smallint(4) NOT NULL default '0',
`mLand` varchar(3) NOT NULL default '',
`mSampler` tinyint(1) NOT NULL default '0',
`mBootleg` tinyint(1) NOT NULL default '0',
`mBanned` tinyint(1) NOT NULL default '0',
PRIMARY KEY (`mID`),
KEY `mYear` (`mYear`),
KEY `uID` (`uID`),
KEY `mSampler` (`mSampler`),
KEY `mBootleg` (`mBootleg`),
KEY `mEAN` (`mEAN`),
KEY `mBanned` (`mBanned`)
) ENGINE=InnoDB;
Table which connects the artists with the media:
------------------------------------------------
CREATE TABLE `rel_am` (
`relAMID` int(10) unsigned NOT NULL auto_increment,
`aID` int(10) unsigned NOT NULL default '0',
`mID` int(10) unsigned NOT NULL default '0',
PRIMARY KEY (`relAMID`),
UNIQUE KEY `aID_2` (`aID`,`mID`),
KEY `aID` (`aID`),
KEY `mID` (`mID`)
) ENGINE=InnoDB;
Table which connects the users with the media:
----------------------------------------------
CREATE TABLE `rel_mc` (
`relMCID` int(10) unsigned NOT NULL auto_increment,
`mID` int(10) unsigned NOT NULL default '0',
`uID` int(10) unsigned NOT NULL default '0',
PRIMARY KEY (`relMCID`),
KEY `mID` (`mID`),
KEY `uID` (`uID`)
) ENGINE=InnoDB;
Typical query for this:
-----------------------
(Sort all media in my collection by: sampler at the end, artist names first, year of release next and than the name of the record and it's type)
SELECT a.aID, a.aName, a.aFName, a.aLand, m.mID, m.mName, m.mTypeName, m.mYear, m.mYearOriginal, m.mLand, m.mSampler, m.mBootleg
FROM rel_mc AS mc
LEFT JOIN (`medium` AS m, rel_am AS am, artist AS a)
ON (m.mID = mc.mID AND am.mID = mc.mID AND a.aID = am.aID)
WHERE mc.uID = 1
AND m.mBanned < 4
AND a.aBanned < 2
GROUP BY m.mID
ORDER BY m.mSampler ASC, a.aName ASC, a.aFName ASC, m.mYearOriginal ASC, m.mName ASC, m.mTypeName ASC
LIMIT 0, 100;
A query of this type could take from one up to three or more seconds, depending on how big the collection of one user is.
The problem is: the ORDER BY can vary from member to member. So I can't put an index on the fields in the ORDER statement.
Any good hint how to optimize this is well appreciated.
|
|
| | Topic: Between php5 and MySQL they eat memory and swap |
|---|
| Between php5 and MySQL they eat memory and swap [message #3362] |
Sun, 03 August 2008 12:55 |
fher98
Messages: 1
Registered: August 2008
Location: Guatemala |
Junior Member |
|
|
Hi!
I have a debian lenny running on a little 512ram 64x personal system, everything was running smoothly until recently. I have a couple of blogs and some friends blogs running, DB are less than 20MB. And the mangos DB about 130MB.
Heres my performance:
Output from TOP command:
SWAP DATA COMMAND
233m 46m php5-cgi
199m 194m mysqld
#vmstat 5 2
procs -----------memory---------- ---swap-- -----io---- -system-- ----cpu----
r b swpd free buff cache si so bi bo in cs us sy id wa
0 0 360908 4408 2220 45256 4 3 31 17 89 182 0 0 96 3
0 0 360908 4416 2228 45204 9 0 9 10 556 824 0 0 99 0
I guess since today is sunday performance its ok, but I can get swap filled up to 70% and i/o waits up to 100% for 15 or more seconds.
Can I get a little help to get the swap use i/o waits down?
thanks!
|
|
| | Topic: LOAD DATA locks out ALL innoDB tables. |
|---|
| LOAD DATA locks out ALL innoDB tables. [message #3359] |
Thu, 31 July 2008 14:55 |
wwwwizard
Messages: 1
Registered: July 2008 |
Junior Member |
|
|
I'm attempting to load a 3GB file, 850K rows, into an _unused_ InnoDB table using LOAD DATA INFILE (local). When I do this, all _other_ InnoDB tables in the db freeze up, and hundreds of update queries start waiting, and timing out after table_lock_wait_timeout (50). I'm thinking some kind of disk contention, perhaps size of tmp or something.
This may be associated... not sure...
I have set innodb_buffer_pool_size to 512M, and in fact it shows as
innodb_buffer_pool_size 536870912
in the "system variables" pane of mysql administrator.
However! On the "status variables" pane of mysql administrator, which shows the real values I have the following:
innodb_buffer_pool_size 32768
innodb_buffer_free_buffers 0
innodb_buffer_pages_created 170169
innodb_buffer_pages_read 6298
innodb_buffer_pages_written 170169
This tells me that mysql needs more buffer space, but for some reason is not growing the pool size to the size that it is allowed to grow. Why would this be, and is it related to my problem of not being able to import a large file into an innodb table?
Thanks in advance. any help appreciated!
SHOW VARIABLES
auto_increment_increment = 1
auto_increment_offset = 1
automatic_sp_privileges = ON
back_log = 50
basedir = /usr/
bdb_cache_size = 8388600
bdb_home = /var/lib/mysql/
bdb_log_buffer_size = 131072
bdb_logdir =
bdb_max_lock = 10000
bdb_shared_data = OFF
bdb_tmpdir = /tmp/
binlog_cache_size = 32768
bulk_insert_buffer_size = 8388608
character_set_client = utf8
character_set_connection = utf8
character_set_database = latin1
character_set_filesystem = binary
character_set_results = utf8
character_set_server = latin1
character_set_system = utf8
character_sets_dir = /usr/share/mysql/charsets/
collation_connection = utf8_general_ci
collation_database = latin1_swedish_ci
collation_server = latin1_swedish_ci
completion_type = 0
concurrent_insert = 1
connect_timeout = 5
datadir = /var/lib/mysql/
date_format = %Y-%m-%d
datetime_format = %Y-%m-%d %H:%i:%s
default_week_format = 0
delay_key_write = ON
delayed_insert_limit = 100
delayed_insert_timeout = 300
delayed_queue_size = 1000
div_precision_increment = 4
engine_condition_pushdown = OFF
expire_logs_days = 0
flush = OFF
flush_time = 0
ft_boolean_syntax = + -><()~*:""&|
ft_max_word_len = 84
ft_min_word_len = 4
ft_query_expansion_limit = 20
ft_stopword_file = (built-in)
group_concat_max_len = 1024
have_archive = NO
have_bdb = YES
have_blackhole_engine = NO
have_compress = YES
have_crypt = YES
have_csv = NO
have_example_engine = NO
have_federated_engine = NO
have_geometry = YES
have_innodb = YES
have_isam = NO
have_ndbcluster = NO
have_openssl = DISABLED
have_query_cache = YES
have_raid = NO
have_rtree_keys = YES
have_symlink = YES
init_connect =
init_file =
init_slave =
innodb_additional_mem_pool_size = 20971520
innodb_autoextend_increment = 1000
innodb_buffer_pool_awe_mem_mb = 0
innodb_buffer_pool_size = 536870912
innodb_checksums = ON
innodb_commit_concurrency = 0
innodb_concurrency_tickets = 500
innodb_data_file_path = ibdata1:3000M: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_arch_dir =
innodb_log_archive = OFF
innodb_log_buffer_size = 8388608
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_support_xa = ON
innodb_sync_spin_loops = 20
innodb_table_locks = ON
innodb_thread_concurrency = 8
innodb_thread_sleep_delay = 10000
interactive_timeout = 28800
join_buffer_size = 10481664
key_buffer_size = 268435456
key_cache_age_threshold = 300
key_cache_block_size = 1024
key_cache_division_limit = 100
language = /usr/share/mysql/english/
large_files_support = ON
large_page_size = 0
large_pages = OFF
license = GPL
local_infile = ON
locked_in_memory = OFF
log = OFF
log_bin = ON
log_bin_trust_function_creators = OFF
log_error =
log_slave_updates = OFF
log_slow_queries = OFF
log_warnings = 1
long_query_time = 10
low_priority_updates = OFF
lower_case_file_system = OFF
lower_case_table_names = 0
max_allowed_packet = 16000000
max_binlog_cache_size = 4294967295
max_binlog_size = 1073741824
max_connect_errors = 10
max_connections = 5000
max_delayed_threads = 20
max_error_count = 64
max_heap_table_size = 16777216
max_insert_delayed_threads = 20
max_join_size = 4294967295
max_length_for_sort_data = 1024
max_prepared_stmt_count = 16382
max_relay_log_size = 0
max_seeks_for_key = 4294967295
max_sort_length = 1024
max_sp_recursion_depth = 0
max_tmp_tables = 32
max_user_connections = 0
max_write_lock_count = 4294967295
multi_range_count = 256
myisam_data_pointer_size = 6
myisam_max_sort_file_size = 2147483647
myisam_recover_options = OFF
myisam_repair_threads = 1
myisam_sort_buffer_size = 67108864
myisam_stats_method = nulls_unequal
net_buffer_length = 16384
net_read_timeout = 30
net_retry_count = 10
net_write_timeout = 60
new = OFF
old_passwords = OFF
open_files_limit = 25010
optimizer_prune_level = 1
optimizer_search_depth = 62
pid_file = /var/run/mysqld/mysqld.pid
prepared_stmt_count = 0
port = 3306
preload_buffer_size = 32768
protocol_version = 10
query_alloc_block_size = 8192
query_cache_limit = 1048576
query_cache_min_res_unit = 4096
query_cache_size = 16777216
query_cache_type = ON
query_cache_wlock_invalidate = OFF
query_prealloc_size = 8192
range_alloc_block_size = 2048
read_buffer_size = 1998848
read_only = OFF
read_rnd_buffer_size = 4190208
relay_log_purge = ON
relay_log_space_limit = 0
rpl_recovery_rank = 0
secure_auth = OFF
server_id = 1
skip_external_locking = ON
skip_networking = OFF
skip_show_database = OFF
slave_compressed_protocol = OFF
slave_load_tmpdir = /tmp/
slave_net_timeout = 3600
slave_skip_errors = OFF
slave_transaction_retries = 10
slow_launch_time = 2
socket = /var/lib/mysql/mysql.sock
sort_buffer_size = 256000000
sql_mode =
sql_notes = ON
sql_warnings = ON
storage_engine = MyISAM
sync_binlog = 0
sync_frm = ON
system_time_zone = CDT
table_cache = 256
table_lock_wait_timeout = 50
table_type = MyISAM
thread_cache_size = 8
thread_stack = 196608
time_format = %H:%i:%s
time_zone = SYSTEM
timed_mutexes = OFF
tmp_table_size = 33554432
tmpdir =
transaction_alloc_block_size = 8192
transaction_prealloc_size = 4096
tx_isolation = REPEATABLE-READ
updatable_views_with_limit = YES
version = 5.0.22-log
version_bdb = Sleepycat Software: Berkeley DB 4.1.24: (May 25 = 2006)
version_comment = Source distribution
version_compile_machine = i686
version_compile_os = redhat-linux-gnu
wait_timeout = 28800
SHOW STATUS
Aborted_clients = 7051
Aborted_connects = 962
Binlog_cache_disk_use = 18
Binlog_cache_use = 428667
Bytes_received = 187
Bytes_sent = 259
Com_admin_commands = 0
Com_alter_db = 0
Com_alter_table = 0
Com_analyze = 0
Com_backup_table = 0
Com_begin = 0
Com_change_db = 1
Com_change_master = 0
Com_check = 0
Com_checksum = 0
Com_commit = 0
Com_create_db = 0
Com_create_function = 0
Com_create_index = 0
Com_create_table = 0
Com_dealloc_sql = 0
Com_delete = 0
Com_delete_multi = 0
Com_do = 0
Com_drop_db = 0
Com_drop_function = 0
Com_drop_index = 0
Com_drop_table = 0
Com_drop_user = 0
Com_execute_sql = 0
Com_flush = 0
Com_grant = 0
Com_ha_close = 0
Com_ha_open = 0
Com_ha_read = 0
Com_help = 0
Com_insert = 0
Com_insert_select = 0
Com_kill = 0
Com_load = 0
Com_load_master_data = 0
Com_load_master_table = 0
Com_lock_tables = 0
Com_optimize = 0
Com_preload_keys = 0
Com_prepare_sql = 0
Com_purge = 0
Com_purge_before_date = 0
Com_rename_table = 0
Com_repair = 0
Com_replace = 0
Com_replace_select = 0
Com_reset = 0
Com_restore_table = 0
Com_revoke = 0
Com_revoke_all = 0
Com_rollback = 0
Com_savepoint = 0
Com_select = 2
Com_set_option = 2
Com_show_binlog_events = 0
Com_show_binlogs = 0
Com_show_charsets = 0
Com_show_collations = 0
Com_show_column_types = 0
Com_show_create_db = 0
Com_show_create_table = 0
Com_show_databases = 0
Com_show_errors = 0
Com_show_fields = 0
Com_show_grants = 0
Com_show_innodb_status = 0
Com_show_keys = 0
Com_show_logs = 0
Com_show_master_status = 0
Com_show_ndb_status = 0
Com_show_new_master = 0
Com_show_open_tables = 0
Com_show_privileges = 0
Com_show_processlist = 0
Com_show_slave_hosts = 0
Com_show_slave_status = 0
Com_show_status = 1
Com_show_storage_engines = 0
Com_show_tables = 0
Com_show_triggers = 0
Com_show_variables = 0
Com_show_warnings = 0
Com_slave_start = 0
Com_slave_stop = 0
Com_stmt_close = 0
Com_stmt_execute = 0
Com_stmt_fetch = 0
Com_stmt_prepare = 0
Com_stmt_reset = 0
Com_stmt_send_long_data = 0
Com_truncate = 0
Com_unlock_tables = 0
Com_update = 0
Com_update_multi = 0
Com_xa_commit = 0
Com_xa_end = 0
Com_xa_prepare = 0
Com_xa_recover = 0
Com_xa_rollback = 0
Com_xa_start = 0
Compression = OFF
Connections = 5525
Created_tmp_disk_tables = 0
Created_tmp_files = 8
Created_tmp_tables = 1
Delayed_errors = 0
Delayed_insert_threads = 0
Delayed_writes = 0
Flush_commands = 2
Handler_commit = 0
Handler_delete = 0
Handler_discover = 0
Handler_prepare = 0
Handler_read_first = 0
Handler_read_key = 0
Handler_read_next = 0
Handler_read_prev = 0
Handler_read_rnd = 0
Handler_read_rnd_next = 0
Handler_rollback = 0
Handler_savepoint = 0
Handler_savepoint_rollback = 0
Handler_update = 0
Handler_write = 130
Innodb_buffer_pool_pages_data = 32734
Innodb_buffer_pool_pages_dirty = 42
Innodb_buffer_pool_pages_flushed = 344512
Innodb_buffer_pool_pages_free = 1
Innodb_buffer_pool_pages_latched = 0
Innodb_buffer_pool_pages_misc = 33
Innodb_buffer_pool_pages_total = 32768
Innodb_buffer_pool_read_ahead_rnd = 87
Innodb_buffer_pool_read_ahead_seq = 26
Innodb_buffer_pool_read_requests = 67692652
Innodb_buffer_pool_reads = 2644
Innodb_buffer_pool_wait_free = 0
Innodb_buffer_pool_write_requests = 11645744
Innodb_data_fsyncs = 793874
Innodb_data_pending_fsyncs = 1
Innodb_data_pending_reads = 0
Innodb_data_pending_writes = 0
Innodb_data_read = 105140224
Innodb_data_reads = 3525
Innodb_data_writes = 894304
Innodb_data_written = 1643344384
Innodb_dblwr_pages_written = 344512
Innodb_dblwr_writes = 9346
Innodb_log_waits = 0
Innodb_log_write_requests = 6950991
Innodb_log_writes = 770955
Innodb_os_log_fsyncs = 778708
Innodb_os_log_pending_fsyncs = 1
Innodb_os_log_pending_writes = 0
Innodb_os_log_written = 3236460544
Innodb_page_size = 16384
Innodb_pages_created = 160179
Innodb_pages_read = 6284
Innodb_pages_written = 344512
Innodb_row_lock_current_waits = 1
Innodb_row_lock_time = 60876042
Innodb_row_lock_time_avg = 335
Innodb_row_lock_time_max = 5194
Innodb_row_lock_waits = 181202
Innodb_rows_deleted = 467013
Innodb_rows_inserted = 1386569
Innodb_rows_read = 55444397
Innodb_rows_updated = 488241
Key_blocks_not_flushed = 0
Key_blocks_unused = 206174
Key_blocks_used = 25786
Key_read_requests = 5003135
Key_reads = 82701
Key_write_requests = 494454
Key_writes = 120223
Last_query_cost = 0.000000
Max_used_connections = 896
Not_flushed_delayed_rows = 0
Open_files = 88
Open_streams = 0
Open_tables = 209
Opened_tables = 0
Qcache_free_blocks = 90
Qcache_free_memory = 16089184
Qcache_hits = 27542
Qcache_inserts = 73232
Qcache_lowmem_prunes = 0
Qcache_not_cached = 79181
Qcache_queries_in_cache = 267
Qcache_total_blocks = 645
Questions = 1349114
Rpl_status = NULL
Select_full_join = 0
Select_full_range_join = 0
Select_range = 0
Select_range_check = 0
Select_scan = 1
Slave_open_temp_tables = 0
Slave_retried_transactions = 0
Slave_running = OFF
Slow_launch_threads = 0
Slow_queries = 0
Sort_merge_passes = 0
Sort_range = 0
Sort_rows = 0
Sort_scan = 0
Ssl_accept_renegotiates = 0
Ssl_accepts = 0
Ssl_callback_cache_hits = 0
Ssl_cipher =
Ssl_cipher_list =
Ssl_client_connects = 0
Ssl_connect_renegotiates = 0
Ssl_ctx_verify_depth = 0
Ssl_ctx_verify_mode = 0
Ssl_default_timeout = 0
Ssl_finished_accepts = 0
Ssl_finished_connects = 0
Ssl_session_cache_hits = 0
Ssl_session_cache_misses = 0
Ssl_session_cache_mode = NONE
Ssl_session_cache_overflows = 0
Ssl_session_cache_size = 0
Ssl_session_cache_timeouts = 0
Ssl_sessions_reused = 0
Ssl_used_session_cache_entries = 0
Ssl_verify_depth = 0
Ssl_verify_mode = 0
Ssl_version =
Table_locks_immediate = 601047
Table_locks_waited = 21465
Tc_log_max_pages_used = 0
Tc_log_page_size = 0
Tc_log_page_waits = 1
Threads_cached = 6
Threads_connected = 356
Threads_created = 3414
Threads_running = 6
Uptime = 56966
|
|
| | Topic: Why does MySQL chew so much virtual memory on Windows? |
|---|
| Why does MySQL chew so much virtual memory on Windows? [message #3354] |
Wed, 30 July 2008 08:15 |
brashquido
Messages: 1
Registered: March 2007 |
Junior Member |
|
|
Hi All,
Have a fairly busy MySQL server using MyISAM and InnoDB tables on Win 2003 with plenty of available RAM (total 4GB, with about 1.5GB unused) which is using a massive amount of virtual memory despite having tweaked MySQL to use as much memory as it needs. RAM usage never gets much above 400MB, however Windows is reporting that MySQL is using a massive 1.5GB of virtual memory.
Most other processes use a similar amount of RAM and virtual memory, so why would MySQL be using 4~5 times more virtual memory than physical memory? Especially when there is so much free physical memory?
|
|
| | Topic: (NOT) NULL Indexes |
|---|
| (NOT) NULL Indexes [message #3352] |
Tue, 29 July 2008 19:04 |
mbor
Messages: 2
Registered: July 2008 |
Junior Member |
|
|
Hi,
I have little question.
What index should I set on varchar(800) NULL field, if I use it only (beside in SELECT) in WHERE statement with IS (NOT) NULL?
thx
|
|
| | Topic: Calculate Disk Space Overhead? |
|---|
| Calculate Disk Space Overhead? [message #3351] |
Tue, 29 July 2008 11:40 |
afflictedd2
Messages: 3
Registered: July 2008
Location: Texas |
Junior Member |
|
|
Hi everyone,
I was wondering, is there a way to calculate the overhead in diskspace for a table that contains column types that are only regular ints and floats. Basically I'm looking for a formula  . (for the MyISAM storage type)
Thanks in advance,
Ed.
|
|
| | Topic: Query Optimization (2 queries) |
|---|
 Query Optimization (2 queries) [message #3344] |
Thu, 24 July 2008 15:23 |
MonkeyTech
Messages: 4
Registered: September 2007 |
Junior Member |
|
|
Hey,
I was just wondering if anyone could take a look at these queries and drop me some tips on improvements/suggestions on improving the method.
Heres the first:
UPDATE tablea
INNER JOIN tableb
ON (LEFT(tablea.forename, 1)=LEFT(tableb.forename, 1)
AND tablea.surname=tableb.surname
AND LEFT(tablea.addr1, 5)=LEFT(tableb.addr1, 5)
AND tablea.postcode=tableb.postcode)
SET tablea.dupe="1";
It's to compare two tables and find the duplicates between them then flag a boolen in the "dupe" column of tablea. Both tables are structured the same,
DROP TABLE IF EXISTS `gas`;
CREATE TABLE `gas` (
`FORENAME` varchar(20) NOT NULL,
`SURNAME` varchar(20) NOT NULL,
`ADDR1` varchar(30) NOT NULL,
`ADDR2` varchar(30) NOT NULL,
`ADDR3` varchar(30) NOT NULL,
`TOWN` varchar(30) NOT NULL,
`COUNTY` varchar(30) NOT NULL,
`POSTCODE` varchar(8) NOT NULL,
`URN` varchar(10) NOT NULL,
KEY `Index` (`FORENAME`,`SURNAME`,`ADDR1`,`POSTCODE`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
At the moment this is just running on my machine so the computer spec's are fairly irrelivant as it could end up running on any machine.
I actualy don't have too many problems with this query, it's just a little slow; i was wondering what the most efficient index's would be (i've tried quite a few combinations of all the fields with very little improvement) and if anyone has suggestions for improving the efficiencey/performance..
Heres the other:
SELECT * FROM (tablea LEFT JOIN tableb ON (tablea.lastname=tableb.lastname AND LEFT(CONCAT(tablea.premises, " ", tablea.street), 5)=LEFT(tableb.addr1, 5) AND tablea.postcode=tableb.postcode)) LEFT JOIN tablec ON tableb.postcode = tablec.postcode WHERE tableb.lastname="LASTNAME";
The idea is to join 3 tables together (the data within all the tables is constantly changing), all of which contain different information on people then look up a particular person within the result set.
Again, not a huge amount of problems with the query, its just another general performance question but sometimes the tables being joined (tableb/tablec) have "null" for all their fields.
Also considering the address in one table is split between house number (tablea.premises) and street (tablea.street) and the other table with address info is in one field (tablea.addr1) can anyone suggest the best fields to index?
I'm not a MYSQL DBA or anything, these are queries for a couple of friends (and so sadly i don't have control over table structures, just index's) so i'd appreciate someone with a little more knowlage than me giving them a quick look.
Thanks in advance.
|
|
| | Topic: Recovery after ibdata1 delete. |
|---|
| Recovery after ibdata1 delete. [message #3343] |
Thu, 24 July 2008 09:48 |
espetr
Messages: 1
Registered: July 2008 |
Junior Member |
|
|
Hello, All.
After a some backup operations we have lost an ibdata1 file. We have an option innodb_table_per_file enabled in our my.cnf, so we have all .frm and .ibd files not corrupted. When we tried to start database mysql shows next messages:
080724 13:46:28 InnoDB error:
Cannot find table Spot/AsearchChangeLog from the internal data dictionary
of InnoDB though the .frm file for the table exists. Maybe you
have deleted and recreated InnoDB data files but have forgotten
to delete the corresponding .frm files of InnoDB tables, or you
have moved .frm files to another database?
Look from section 15.1 of http://www.innodb.com/ibman.html
how you can resolve the problem.
080724 13:46:28 [ERROR] /usr/sbin/mysqld: Can't open file: 'AsearchChangeLog.ibd' (errno: 1)
How can we recover our data?
I had used instructions found here but mysql gave me another error when i tried to IMPORT TABLESPACE:
80724 16:54:55 InnoDB: Error: tablespace id in file './Spot/AsearchChangeLog.ibd' is 656314, but in the InnoDB
InnoDB: data dictionary it is 1.
InnoDB: Have you moved InnoDB .ibd files around without using the
InnoDB: commands DISCARD TABLESPACE and IMPORT TABLESPACE?
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/mysql/en/InnoDB_troubleshooting_dat adict.html
InnoDB: for how to resolve the issue.
080724 16:54:55 InnoDB: cannot find or open in the database directory the .ibd file of
InnoDB: table `Spot/AsearchChangeLog`
InnoDB: in ALTER TABLE ... IMPORT TABLESPACE
How can I avoid this and import tablespace?
I know information schema for my database and tables, i know all tables names, rows and their sizes. How can I extract data from an ibd file? That should be enough for me.
[Updated on: Thu, 24 July 2008 13:04]
|
|
| | Topic: strange performance times |
|---|
| strange performance times [message #3337] |
Wed, 23 July 2008 09:00 |
afflictedd2
Messages: 3
Registered: July 2008
Location: Texas |
Junior Member |
|
|
Hi everyone,
I have worked with a time function provided by the operating system for measuring the time it takes for a program to insert 50000 rows into a table. The database is created and populated with the program. I am trying out different engines to see which one does best. But the results I get seem strange.
MyISAM
user-time: 27.465
system-time: 1.782s
elasped-time: 0:36.21 80.7%
I/O: 0+0io
page faults: 9pf+0w
InnoDB
user-time: 35.007
system-time: 5.594
elapsed-time: 1:41.46
cpu-usage: 40.0%
I/O: 0+0io
page faults: 88pf+0w
I'm not sure where that extra elapse-time is being spent but my guess is that it could be time spent writing to disk. Is it required to change certain variables in the mysql environment to make BDB or InnoDB work faster?
Thanks,
Ed
|
|
| | Topic: MySQL Cluster performance |
|---|
| | Topic: What can cause queries to block on 'opening tables'? |
|---|
| What can cause queries to block on 'opening tables'? [message #3277] |
Fri, 11 July 2008 16:16 |
|
I have an InnoDB table used for logging ('log') and an archive of that table in MyISAM ('log_archive'). There is a trigger on 'log' that, on insert, inserts an identical record into 'log_archive'. The 'log' table is emptied nightly; the 'log_archive' table is never emptied.
We currently have no queries that retrieve data from either table.
log has a few thousand records (we insert about 250,000 per day); log_archive has about 9 million.
Inserts into the log table get stuck frequently on 'opening tables,' sometimes for more than a minute. I can't figure out why, though, since all we're doing are simple inserts, and the MySQL manual says opening table should complete instantaneously unless the thread is blocked.
Usually all of the inserts will eventually complete and the server will go about its business, but when they get stuck for upwards of a minute, the max connections is reached and load spikes on the server.
The server is not too heavily loaded.
I'm running 5.0.22 (64-bit).
|
|
| | Topic: How to reduce the time taken ...... ? |
|---|
| How to reduce the time taken ...... ? [message #3254] |
Mon, 07 July 2008 08:05 |
spocke
Messages: 1
Registered: July 2008 |
Junior Member |
|
|
Hi,
I have a table, wherein i am storing all attempts/access for every client. From this table, i would be populating another (report) table. From this report table, i would be populating a graph which shows the attempts/access form a particular client for last 7 days. To make the graph more readable, while populating i am grouping the access time by 4 hour slot i.e,
mysql> select CONCAT(DATE(HOUR)," ",SEC_TO_TIME(CEIL(TIME_TO_SEC(HOUR)/14400)*14400-1)) as ProcessedTIME,HOUR from Attempts_Vs_Client limit 1;
+---------------------+---------------------+
| ProcessedTIME | HOUR |
+---------------------+---------------------+
| 2008-07-01 11:59:59 | 2008-07-01 10:45:00 |
+---------------------+---------------------+
1 row in set (0.00 sec)
In production, this query is taking more time and below is an extract from slow query log file.
# Time: 080701 17:27:49
# User@Host: root[root] @ localhost [127.0.0.1]
# Query_time: 39.437500 Lock_time: 0.000000 Rows_sent: 21 Rows_examined: 40031
SET timestamp=1214913469;
select (MIN(AVCT_ID)-1) as attempt_min, MAX(AVCT_ID) as attempt_max, RID as resid, CONCAT(DATE(HOUR)," ",SEC_TO_TIME(CEIL(TIME_TO_SEC(HOUR)/14400)*14400-1)) as TIME from Attempt_Vs_Client where Attempt_Vs_Client.AVCT_ID <= 5143601 and Attempt_Vs_Client.AVCT_ID >4947183 group by RID,TIME order by NULL;
Additional Details :-
1. Table type -> MyISAM
2. Indexed Columns :- AVCT_ID(PK), HOUR, RID
3. MySQL :- 5.1
4. Total Rows :- ~ 5,00,000.
Below is the output of explain query,
mysql> explain select (MIN(AVCT_ID)-1) as Attempt_min, MAX(AVCT_ID) as Attempt_max, RID as resid, CONCAT(DATE(HOUR)," ",SEC_TO_TIME(CEIL(TIME_TO_SEC(HOUR)/14400)*14400-1)) as TIME from Attempt_Vs_Client where Attempt_Vs_Client.AVCT_ID <= 5143601 and Attempt_Vs_Client.AVCT_ID >4947183 group by RID,TIME order by NULL;
+----+-------------+---------------+-------+---------------+ ---------+---------+------+-------+------------------------- -----+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------------+-------+---------------+ ---------+---------+------+-------+------------------------- -----+
| 1 | SIMPLE | Attempt_Vs_Client | range | PRIMARY | PRIMARY | 8 | NULL | 42202 | Using where; Using temporary |
+----+-------------+---------------+-------+---------------+ ---------+---------+------+-------+------------------------- -----+
1 row in set (0.00 sec)
Appreciate, if you guys can help me to reduce the amount of time consumed by this particular query ?
Thanks in advance,
-S-
|
|
| | Topic: Transaction not committed properly |
|---|
| Transaction not committed properly [message #3245] |
Wed, 02 July 2008 08:33 |
newtomysql
Messages: 28
Registered: February 2007 |
Junior Member |
|
|
Dear All,
I have this application which is using Innodb engine and .netconnector 5.0.8.1.So I am using C# for my coding. So I am not sure either is the engine or the connector problem? So I want to run transaction base appplication where either all my sql statement go through or all fail. So the funny part I notice it works well for most of my cases but for certain cases the second sql statement go through. I really cant figure out why is this happening. I have attached the code below. So the problem there is that my myInsertQuery1 goes through but not my myUpdateQuery1 ? So whyis this happening because both are under the same transactions right. I have separately attached below my function for thetransactionConnectionLocal1 where I keep the connection details.
int rollbackBoolean = 0;
MySqlTransaction transactionLocal1 = null;
MySqlConnection connectionLocal1 = null;
transactionConnectionLocal1 callTransactionConnectionLocal1= null;
try
{
callTransactionConnectionLocal1 = newtransactionConnectionLocal();
connectionLocal1=callTransactionConnectionLocal1.localConnec tion1;
connectionLocal1.Open();
transactionLocal1 =connectionLocal1.BeginTransaction();
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
rollbackBoolean = 1;
MessageBox.Show("Error From Database Connection " +ex.Message);
}
catch (System.Net.Sockets.SocketException ex)
{
rollbackBoolean = 1;
MessageBox.Show("Error Sockets From Database Connection" + ex.Message);
}
String myUpdateQuery1 = "Update tblStock " +"Set tblStock.stockStatus = 'b'" +"Where tblStock.stockSerial ='" +serial + "'" ;
MySqlCommand myCommand1 = newMySqlCommand(myUpdateQuery1);
try
{
myCommand1.Connection = connectionLocal1;
myCommand1.Transaction = transactionLocal1;myCommand1.ExecuteNonQuery();
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
rollbackBoolean = 1;
MessageBox.Show("Error From myUpdateQuery1 " +ex.Message);
}
catch (System.Net.Sockets.SocketException ex)
{
rollbackBoolean = 1;
MessageBox.Show("Error Sockets From myUpdateQuery1 " +ex.Message);
}
finally
{
myCommand1.Dispose();
}
String myInsertQuery1 = "Insert into tblTemp " +"Set stockSerial ='" + serial+ "'," +"tempTimeStamp='" +DateTime.Now.ToString(("yyyy:MM:dd HH:mm:ss")) + "';";MySqlCommand myCommand2 = newMySqlCommand(myInsertQuery1);
try
{
myCommand2.Connection = connectionLocal1;
myCommand2.Transaction = transactionLocal1;
myCommand2.ExecuteNonQuery();
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
rollbackBoolean = 1;MessageBox.Show("Error From myInsertQuery1 " +ex.Message);
}
catch (System.Net.Sockets.SocketException ex)
{
rollbackBoolean = 1;
MessageBox.Show("Error Sockets From myInsertQuery1 " +ex.Message);
}
finally{myCommand2.Dispose();
}
if (rollbackBoolean == 1)
{
transactionLocal1.Rollback();
}
else
{
try
{
transactionLocal1.Commit();
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
try
{
transactionLocal1.Rollback();
}
catch (MySqlException ex1){MessageBox.Show("An exception of type " +ex.GetType() +" was encountered while insertingthe data.");
if (transactionLocal1.Connection != null)
{
MessageBox.Show("An exception of type " +ex1.GetType() +" was encountered whileattempting to rollback the transaction.");
}
}
}
catch (System.Net.Sockets.SocketException ex){rollbackBoolean = 1;MessageBox.Show("Error Sockets From Commit Process" + ex.Message);
}
finally{connectionLocal1.Close();
}
}
// connection details function
using System;
using System.Collections.Generic;
using System.Text;
using MySql.Data.MySqlClient;
namespace mSytemNonFranchise
{
public class transactionConnectionLocal1
{
public MySqlConnection localConnection1;
public MySqlCommand command;
public transactionConnectionLocal1()
{
this.localConnection1 = newMySqlConnection("Address='localhost';Database='localDB';UserName='root';Pas sword='local12';Pooling='false'");
// this.command = this.localConnection1.CreateCommand();
// this.localConnection1.Open();}
// destructor - explicitly
~transactionConnectionLocal1()
{//this.command.Dispose();this.localConnection1.Close();this .localConnection1.Dis pose();
}
}
}
|
|
| | Topic: Dynamic Create table using cursor ?? |
|---|
| Dynamic Create table using cursor ?? [message #3234] |
Tue, 01 July 2008 05:01 |
focusora
Messages: 5
Registered: June 2008 |
Junior Member |
|
|
Hi all,
Is it possible to create Dynamic temporary table in pl/sql .
using cursor to stores values.
my code works for static table ., very well
but my need is in Dynamic table to store the values and fetch
that values in cursor and insert into another tables.
is it possible ??
=========
DELIMITER $$
CREATE PROCEDURE "testproc"()
BEGIN
declare abc varchar(50);
declare SSS cursor for
select id from
[ DYNAMIC_TEMP_TABLE ] where number = 11;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET l_last_row_fetched=1;
SET l_last_row_fetched=0;
open SSS;
SSS_loop: LOOP
fetch SSS into abc;
IF l_last_row_fetched=1 THEN
LEAVE f_loop;
END IF;
insert into newtable (id) values ([values stored in abc]);
===============
any ideas or assist to create dynamic table in pl/sql
OS : windows 2000
DB : MySQL 5.0
|
|
| | Topic: Problem with slow mysql select count(id) |
|---|
 Problem with slow mysql select count(id) [message #3227] |
Sun, 29 June 2008 02:00 |
student
Messages: 3
Registered: June 2008 |
Junior Member |
|
|
hello,
I have a mysql database run web site.
Table has 7000 rows.
I have fulltext search on three columns.
when I look into mysql_slow_queries log, there are great many slow queries like this:
# Fri Jun 27 18:18:47 2008
# Query_time: 4 Lock_time: 0 Rows_sent: 1 Rows_examined: 43
SELECT COUNT(id) FROM table WHERE MATCH (title, keywords, description) AGAINST ('information' IN BOOLEAN MODE )
In most of these logs the query_time is 2.
I have almost no problem with other queries in my scripts.
id is my primary key.
EXPLAIN for this query is :
id : 1
select_type : SIMPLE
table : table
type : fulltext
possible_keys :search
key :search
key_len :0
ref :
rows :1
Extra: Using where
when I run SQL for this query in phpmyadmin, i always get the result in less than 1 second.
I have enabled profiling in phpmyadmin and the result shows like this:
Status Time
(initialization) 0.000008
Opening tables 0.000016
System lock 0.000003
Table lock 0.000003
init 0.000004
optimizing 0.000002
statistics 0.000004
preparing 0.000004
executing 0.000019
Sending data 0.000014
end 0.000002
query end 0.000003
freeing items 0.000004
closing tables 0.000002
removing tmp table 0.000011
closing tables 0.000003
logging slow query 0.000002
There is no overload for the table.
I optimize the table every hour because i update the data very often.
I have MYISAM tables.
I do not have any CPU exceeded logs.
I have shared hosting. I doubt if shared hosting is responsible for slow queries.
I need your expert advice to solve these slow queries.
Let me know if you wish to know more information.
Please help me to solve these slow queries.
Thank you.
|
|
| | Topic: Master-Master or dedicated app/db servers? |
|---|
| Master-Master or dedicated app/db servers? [message #3226] |
Sat, 28 June 2008 10:30 |
edsuit
Messages: 14
Registered: April 2007
Location: The Netherlands |
Junior Member |
|
|
We're running several websites on Windows 2003 servers in an ASP VBScript / MySQL environment. Right now they are divided over two servers on two different locations.
I don't really know which variables give a good impression of server load, but I'm going to put the pageviews per day for every website.
Server A (Intel Xeon 2.8 Ghz / 2 GB RAM)
Website 1 - 23,000 pageviews per day
Server B (Intel Pentium 4 2.8 Ghz / 1 GB RAM)
Website 1 - 23,000 pageviews per day
Website 2 - 4700 pageviews per day
Website 3 - 7100 pageviews per day
The websites all have mostly the same structure, and they're dynamic for the most part, so pretty much every pageview involves database interaction.
Each server also hosts several smaller websites, but these generate hardly any traffic compared to the 'big' ones only a few use MySQL.
On server B, the websites have seen rapidly growing amounts of visitors lately, and we've been having quite a bit of performance problems. I've almost run out of optimization options, so we're thinking about buying a new server and putting it on the same location as server A. We will probably then use server B to host all the smaller websites.
So then we have two servers on the same location. My question is, what would be the best configuration?
- Two dedicated servers; one application server and one database server
- Divide the websites over the two servers and set up a Master-Master or Master-Slave relationship for MySQL
Or maybe another option?
|
|
| | Topic: Blobs for property bags |
|---|
| Blobs for property bags [message #3224] |
Fri, 27 June 2008 14:29 |
Jjhuff
Messages: 5
Registered: October 2007 |
Junior Member |
|
|
In several places we want the ability to store a 'property bag', i.e. a set of key:value pairs for some object (like a file or user). We don't query or sort on their values.
One option is to do something like:
CREATE TABLE file_properties (
nFileId BIGINT NOT NULL,
strKey VARCHAR(64) ASCII NOT NULL,
strValue VARCHAR(512),
PRIMARY KEY (nFileId, strKey)
) ENGINE=InnoDB CHARACTER SET utf8;
The other:
CREATE TABLE file_properties (
nFileId BIGINT NOT NULL,
blobProps BLOB,
PRIMARY KEY (nFileId)
) ENGINE=InnoDB;
Files typically have 6 or 7 props and are typically write once, ready many. We also have a cleanup process that deletes these in batch jobs. In the BLOB approach, we'd probably store things as a pickled (and probably compressed) python dictionary.
What do people think? Is there an obvious winner here?
thanks!!
|
|
| | Topic: slow perfomance with multiple LEFT JOINs |
|---|
| slow perfomance with multiple LEFT JOINs [message #3223] |
Fri, 27 June 2008 06:34 |
phlype
Messages: 4
Registered: October 2006 |
Junior Member |
|
|
I have a query that is very slow and I am wondering how to improve the speed; here are the details:
The slow query (table description can be found at the end of the post):
SELECT * FROM users u LEFT JOIN (user_bookmark ub LEFT JOIN review r ON r.site=ub.bookmark) ON ub.userid=u.userid
The EXPLAIN of this query tells me
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE u ALL NULL NULL NULL NULL 3000
1 SIMPLE ub ALL NULL NULL NULL NULL 31220
1 SIMPLE r ref site site 128 db1.ub.bookmark 2
and what I find strange is that for ub no index is used (although as you can see from the definition, all fields are indexed).
The goal of the query is simply to create an overview table of users with all their bookmarks (if they have marked any) and the accompanying site reviews (if any are available).
If I tryout a first step of the enrichment ie create a table of users extended with their bookmarks I get a very fast query:
SELECT * FROM users u LEFT JOIN user_bookmark ub ON ub.userid=u.userid
Does this mean that the "serial" LEFT JOINs kill the efficiency? Is there a way to solve this problem?
Table definitions
CREATE TABLE `users` (
`userid` int(11) NOT NULL auto_increment,
`name` varchar(100) NOT NULL default '',
PRIMARY KEY (`userid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=3001 ;
CREATE TABLE `user_bookmark` (
`bookmark` varchar(128) NOT NULL default '',
`userid` int(11) NOT NULL default '0',
KEY `bookmark` (`bookmark`),
KEY `userid` (`userid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
CREATE TABLE `review` (
`site` varchar(128) NOT NULL default '',
`review` text NOT NULL,
KEY `site` (`site`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
|
|
| | Topic: join_buffer_size |
|---|
| join_buffer_size [message #3220] |
Wed, 25 June 2008 12:08 |
howachen
Messages: 7
Registered: February 2007 |
Junior Member |
|
|
Hello,
1. Is it true that if my table join is using index, then this value is meaningless?
2. On the other hand, what value should I set? Assuming I have 4GB of main memory and do a lot of large table join.
3. The mysql tunning script (www.day32.com/MySQL) said more than 4MB is not advised, is it too conservative?
Many Thanks.
|
|
| | Topic: More than 400 concurrent small queries |
|---|
| More than 400 concurrent small queries [message #3218] |
Wed, 25 June 2008 05:40 |
gacilu
Messages: 1
Registered: June 2008 |
Junior Member |
|
|
Hello guys,
I am new to this forum and also kind of new to mysql too.
I have a multi-thread application written in Ruby. The application is reading one table that has two columns (Father, Children). As you might see, this is a tree. The fields are foreign keys to a second table, but the second table is not involved in the problem. The table has around 100.000 rows.
What the code does is simple. It starts in the root and it goes to the leafs. The first thread takes the root and runs a select to get all the children. Then it triggers new threads per children and it ends, leaving the other threads alive. Every thread does exactly the same until they reach the leafs.
When the threads reach the leafs, they read the description from the other table, write the value in a global array and leave.
With a few rows, the algorithm is very fast. The problem starts when each node has many children. To give you an idea, in one point in time there are more than 600 threads running, but for some reason I always see no more than two queries running in parallel from the MySQL Administrator.
Each thread open a new connection, runs the select and it close the connection. I have the default maximum connections, 100. So I should see more queries in parallel than only two or three. All the connections are constantly used while the algorithm runs.
The other symptom I can see is that when I start the script, there are up to 30 or 40 queries in parallel, but then the number goes down quickly until it reaches only 2 or 3 until the end.
I've started playing around with the caches and memory values for MySQL server, but to be honest, I am just guessing and the performance does not change.
I am with Mac OS X Leopard in a very fast machine and MySQL 5.1.
Any ideas why is this happening?
Thanks,
|
|
| | Topic: Optimize IP Range Join |
|---|
 Optimize IP Range Join [message #3211] |
Sun, 22 June 2008 14:04 |
tmarket
Messages: 1
Registered: June 2008 |
Junior Member |
|
|
I have 2 tables: an IP Address table, and an IP Ranges table. I want to retrieve a list of ranges in the ranges table for which one or more of the IP addresses in the IP address table fall within that range. The IP addresses are represented as integers.
What I'm trying to use is this:
SELECT title FROM ranges JOIN ips ON ip BETWEEN start AND stop
but it takes too long. Is there a better way?
Here is the structure:
mysql> describe ranges;
+------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+------------------+------+-----+---------+----------------+
| range_id | int(20) unsigned | NO | PRI | NULL | auto_increment |
| start | int(10) unsigned | NO | UNI | NULL | |
| stop | int(10) unsigned | NO | UNI | NULL | |
| title | varchar(200) | NO | | NULL | |
+------------+------------------+------+-----+---------+----------------+
mysql> describe ips;
+------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+------------------+------+-----+---------+----------------+
| ip_id | int(20) unsigned | NO | PRI | NULL | auto_increment |
| ip | int(10) unsigned | NO | MUL | NULL | |
+------------+------------------+------+-----+---------+----------------+
This is similar but different from this: http://forums.mysql.com/read.php?115,106747,106747#msg-10674 7
I've tried several different combinations of indexes, but no success.
|
|
| | Topic: Help Me optimize my query |
|---|
| Help Me optimize my query [message #3208] |
Fri, 20 June 2008 05:58 |
delta2cain1
Messages: 7
Registered: May 2008 |
Junior Member |
|
|
Hi I have a table with 2350k records I have the following query
SELECT Name ,
count(CASE WHEN P_A = 'Y' and CC = 'Y' THEN Name END),
count(CASE WHEN P_A = 'Y' and CC = 'Y' and flag = '1' THEN Name END),
count(CASE WHEN P_A = 'Y' and CC = 'Y' and flag = '0' and Score >= 76 and Score < 300 THEN Name END),
count(CASE WHEN P_A = 'Y' and CC = 'Y' and flag = '0' and Score >= 300 and Score <= 330 THEN Name END)
FROM Table1
WHERE IF($P{StartDate} = $P{EndDate} ,Date_from_Table like CONCAT(DATE_FORMAT(IF(length($P{SelectDate}) > 4,$P{SelectDate},curdate()), '%m%d%Y'),"%"), date_format(str_to_date(CONCAT(SUBSTRING(Date_from_Table,1,2 ), "-",LOWER(SUBSTRING(Date_from_Table,4,3)),"-",'20',SUBSTRING(Date_from_Table,8,2)), "%d-%b-%Y"), "%Y-%m-%d") BETWEEN IF(length($P{StartDate}) > 4,$P{StartDate},curdate()) AND IF(length($P{EndDate}) > 4,$P{EndDate},curdate()))
GROUP BY ClientName;
Now The first part of the IF condition works quit fast on its own like .15 sec but when I extend it to consider a range of Date It takes about a minute or so.
Date_from_Table unfortunately is in string format and I cannot at the moment alter the Table.
IF(length($P{SelectDate}) > 4,$P{SelectDate},curdate()) part is for if no date is specified
Also I would like to get the total of the columns . Can I do that??
|
|
| | Topic: UNION question |
|---|
| UNION question [message #3207] |
Thu, 19 June 2008 19:02 |
rmarshsj
Messages: 1
Registered: June 2008 |
Junior Member |
|
|
Hi All,
I am creating a temporary table which is the union of three selects each producing an ID and a score. If I use UNION on its own I lose any duplicated rows. If I use UNION ALL I get some rows with the same ID but different scores. What I want instead is to end up with unique IDs with the score column being the sum of the scores for any duplicate rows. Is this possible? Does anyone have any suggestions?
Thank you,
Rob
|
|
| | Topic: Date field index not working when using > operator |
|---|
| Date field index not working when using > operator [message #3186] |
Tue, 17 June 2008 04:35 |
leonch
Messages: 1
Registered: June 2008 |
Junior Member |
|
|
Hi we have a database table with about 1 million rows. Quite often we do selects using the greater than operator on a date field so:
SELECT id,user FROM data WHERE pub_date > "2008-06-09";
We have an index on pub_date , this weekend the query stopped using the index and does a full table scan so if I do an explain it shows almost 1 million rows have been scanned which is very slow and it shows it is not using the key.
Even weirder if I change the date to 2008-06-10 and run explain it uses the index and scans far less rows.
This is very bizare behaviour. I am thinking of doing:
SELECT min(id) FROM data WHERE pub_date = "2008-06-09";
to get the id to start searching on and then using this in the second query
SELECT id,user FROM data WHERE pub_date > "2008-06-09" and id> idfromfirstquery;
This will obviously force a lot less rows to be scanned, however this isn't ideal. Does anyone have any idea what might be going on with my date field index?
Thankyou
|
|
| | Topic: Table/Column Layout for multiple pricing |
|---|
| Table/Column Layout for multiple pricing [message #3184] |
Tue, 17 June 2008 00:29 |
Jeremyreger
Messages: 1
Registered: June 2008 |
Junior Member |
|
|
Hello all,
I am wanting to structure a database that will allow for multiple prices per products. I also want to be able to upload an image and enter details about the product.
My initial thought would be to have a product_db and have a price table, a product table, and a description table all linked together with product id's
I am lost...
I would really appreciate your help on the table and column layout for achieving the mulitple pricing...
|
|
| | Topic: Joining rows based on their timestamp |
|---|
| Joining rows based on their timestamp [message #3173] |
Wed, 11 June 2008 21:02 |
pytrin
Messages: 1
Registered: June 2008 |
Junior Member |
|
|
I'm building a simple message-board like app. For every topic there could be several posts, and I would like to present the topic in a list with some content from the latest post in that topic.
Simplifying it, say I have two tables:
Topics:
- id (INT / AI )
- subject (VARCHAR(150))
- created (TIMESTAMP)
Posts:
- id (INT / AI)
- topic_id (INT / FK)
- content (TEXT)
- created (TIMESTAMP)
I want to retrieve rows from the Topics table, along with the latest row for each topic from the Posts table.
Can anyone suggest the most efficient way of accomplishing this (hopefully without a GROUP BY clause)?
|
|
| | Topic: STR_TO_DATE does not function when using only year and month? |
|---|
| STR_TO_DATE does not function when using only year and month? [message #3170] |
Tue, 10 June 2008 10:50 |
festerwim
Messages: 3
Registered: May 2008
Location: Belgium |
Junior Member |
|
|
Either STR_TO_DATE does not work properly or I'm doing something wrong. Consider this statement:
SELECT DATE_FORMAT( NOW(), '%Y%m');
This will output '200806' since we are currently June in the year 2008.
Then do this:
SELECT STR_TO_DATE(DATE_FORMAT( NOW(), '%Y%m'), '%Y%m');
This outputs:
31-mei-2008 0:00:00
I would expect it to have printed out:
1-june-2008 0:00:00
Anybody has an idea why this might happen?
I need this because I want to group by month and want to have a column 'CalculatedDate' which contains a real date with the first of each month in it.
regards,
Wim
|
|
| | Topic: Memory tables max size |
|---|
|
|
Members
Search
Help
Register
Login
Home
