Sir/Mam,
Ask lang po sana ako sa inyo if anyone knows kung pano pa mapapabilis ang execution time ng mga queries kapag sabay sabay na gumagamit ang mga users?.
Thanks.
Forum Topic
MySQL Database
-
Depende sa query mo.
-
Maraming ways to speed up MySQL.
One big factor is yung server hardware speed.
Another is to enable caching on MySQL.
Also make sure your queries are optimized.
You might also consider what type of dbase engine to use(InnoDB or MyIsam).
InnoDB works great for transactional dbase where simultaneous updates are being made in dbase. MyISAM is faster when most queries use The Select statement (multiple reads from the dbase). -
i7 na po yung server.
napapansin ko kapag multiple na nag process ang mga users, dun sya bumabagal, like for example, may mag encode, then sasabayan ng viewing ng reports.
may begin,commit,rollback ang process ng encoding.
kapag kc may nagencode then di nya pa sinisave,
tapos may ibang user/computer na mag view ng report. bumabagal po sya. -
^double check the SQL queries, baka lang hindi optimized as i\'ve mentioned earlier.
Caching would definitely help improve speed especially pag same queries ang requested most of the time.
Also, be aware na pag merong UPDATE command sa SQL, naka-lock ang other table to prioritize the update, if MyISAM is used in this case.
That is why preferred ang Innodb engine due to row-level-locking, pag heavy ang concurrent operations sa dbase. -
Most of used table naman po is naka InnoDB
-
Activate nyo yun slow-query log dun makikita yun queries na hindi optimized.
-
Add more RAM and use SSD drives kung may pangupgrade.
-
ano ang
1. server specs ?
2 .mysql config my.cnf ?
3. using stored procedure ? -
Assessment ko dahil yan sa:
1. Hindi optimized ang tables and queries. Make sure may index / composite indices ang mga frequently and heavily accessed na columns and tables.
2. Review mo ang locking / transactions, assuming naka innodb ka na. Especially sabi mo bumabagal lapag may ng nageencode vs may nag rereport. -
1.
-i7 max po.
2.
query_cache_size : 134217728
version: 5.5.32-0ubuntu0.12.04.1
log_slow_queries: OFF
slow_launch_time: 2
slow_query_log: OFF
3.
wala po stored procedure.
magkaiba po ba yung begin,commit tru stored procedure at begin commit tru software development?.
ok naman po viewing ng report, mabilis sya kapag isa pa lang gumagamit.
bumabagal po sya kapag multiple users na po gumagamit ng system.
any suggestions po about saving new records or updating with begin, commit, na di maapektuhan ang processing time ng ibang query?.
Thank po. -
nag Indexing na po ako sa mga heavy and least tables, un 1st na ginawa ko sa database, nag improve naman yung processing time ng mga queries.
kaso yung locking of tables po ang hindi ko makuha how?. -
1.
-i7 max po.
ilang ang memory?
2.
query_cache_size : 134217728
version: 5.5.32-0ubuntu0.12.04.1
log_slow_queries: OFF
slow_launch_time: 2
slow_query_log: OFF
your innodb config
sample:
using ssd,
binary log disabled,
query_cache disabled
# BINARY LOGGING #
#log-bin = /var/lib/mysql/mysql-bin
#expire-logs-days = 14
#sync-binlog = 0
# CACHES AND LIMITS #
tmp-table-size = 32M
max-heap-table-size = 32M
query-cache-type = 0
query-cache-size = 0
max-connections = 500
thread-cache-size = 50
open-files-limit = 65535
table-definition-cache = 4096
table-open-cache = 4096
# INNODB #
innodb-flush-method = O_DIRECT
innodb-log-files-in-group = 2
innodb-log-file-size = 256M
innodb-flush-log-at-trx-commit = 1
innodb-file-per-table = 1
innodb-buffer-pool-size = 12G
innodb-buffer-pool-size importante sa INNODB
kung kayang ilagay ang database mo sa buffer pool mas maganda.
at least 80% nang memory lagay mo sa buffer pool
3.
wala po stored procedure.
magkaiba po ba yung begin,commit tru stored procedure at begin commit tru software development?.
gumamit ka nang stored procedure, para mas mabilis
sample
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_branch_insert`(
IN p_user_id SMALLINT UNSIGNED,
IN p_branch VARCHAR(50),
OUT o_branch_id TINYINT(3) UNSIGNED,
OUT o_branch VARCHAR(50),
OUT r TINYINT UNSIGNED
)
BEGIN
DECLARE v_branch_id TINYINT UNSIGNED;
DECLARE v_error TINYINT UNSIGNED DEFAULT 0;
DECLARE v_policy TINYINT(1) UNSIGNED DEFAULT 1;
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
SET r = 1;
ROLLBACK;
END;
DECLARE EXIT HANDLER FOR SQLWARNING
BEGIN
SET r = 2;
ROLLBACK;
END;
START TRANSACTION;
SELECT office_branch_id INTO v_branch_id FROM settings WHERE (id = 1) LIMIT 1;
SELECT fn_branch_access(p_user_id,v_branch_id,5001) INTO v_policy;
IF (v_policy = 1) THEN
INSERT INTO branch
(
branch
)
VALUES
(
p_branch
);
SET v_branch_id = LAST_INSERT_ID();
ELSE
SET v_error = 5;
END IF;
IF (v_error = 0) THEN
COMMIT;
SELECT branch_id, branch INTO o_branch_id, o_branch FROM branch WHERE (branch_id = v_branch_id) LIMIT 1;
END IF;
SET r = v_error;
END
sa complicated na processing umabot minsan nang 700+ lines :)
any suggestions po about saving new records or updating with begin, commit, na di maapektuhan ang processing time ng ibang query?.
para sa akin walang relation between query and insert update etc,,, sa speed
optimized mo lang mga queries mo. mahirap kasi di namin alam ang structure nang tables mo .
use EXPLAIN
para malaman mo kung saan may problema. baka minsan nag join ka wala naman index etc......
ilan na ba size nang database mo? -
kaso yung locking of tables po ang hindi ko makuha how?.
sa innodb automatic ang row level locking.
table locking di ko sure sa requirements mo.
para e locked ang table.
sample:
session 1:
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> update branch set branch = \'Default AA\' WHERE (branch_id = 1);
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 0
# Session 1 update Default AA
mysql> show engine innodb status;
------------
TRANSACTIONS
------------
Trx id counter 434122
Purge done for trx\'s n:o < 434121 undo n:o < 0 state: running but idle
History list length 547
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 434119, not started
MySQL thread id 1388, OS thread handle 0x7fd6b9ce4700, query id 18554 localhost root cleaning up
---TRANSACTION 434113, not started
MySQL thread id 1383, OS thread handle 0x7fd6b9d15700, query id 18567 localhost 127.0.0.1 root init
show engine innodb status
---TRANSACTION 0, not started
MySQL thread id 1382, OS thread handle 0x7fd6b9d46700, query id 18563 localhost 127.0.0.1 root cleaning up
---TRANSACTION 434121, ACTIVE 10 sec
2 lock struct(s), heap size 360, 1 row lock(s), undo log entries 1
MySQL thread id 1389, OS thread handle 0x7fd6b9cb3700, query id 18565 localhost root cleaning up
--------
session 2:
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> update branch set branch = \'Default BB\' WHERE (branch_id = 1);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql>
kung mapapansin mo hintayin muna ma released ang locked noong unang session. commit o rollback
kaya may error sa 2 sessions
# Session 2 : update Default BB
mysql> show engine innodb status;
------------
TRANSACTIONS
------------
Trx id counter 434123
Purge done for trx\'s n:o < 434121 undo n:o < 0 state: running but idle
History list length 547
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 434113, not started
MySQL thread id 1383, OS thread handle 0x7fd6b9d15700, query id 18578 localhost 127.0.0.1 root init
show engine innodb status
---TRANSACTION 0, not started
MySQL thread id 1382, OS thread handle 0x7fd6b9d46700, query id 18574 localhost 127.0.0.1 root cleaning up
---TRANSACTION 434122, ACTIVE 6 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 360, 1 row lock(s)
MySQL thread id 1388, OS thread handle 0x7fd6b9ce4700, query id 18576 localhost root updating
update branch set branch = \'Default BB\' WHERE (branch_id = 1)
------- TRX HAS BEEN WAITING 6 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 174 page no 3 n bits 72 index `PRIMARY` of table `bizz`.`branch` trx id 434122 lock_mode X locks rec but not gap waiting
------------------
---TRANSACTION 434121, ACTIVE 91 sec
2 lock struct(s), heap size 360, 1 row lock(s), undo log entries 1
MySQL thread id 1389, OS thread handle 0x7fd6b9cb3700, query id 18565 localhost root cleaning up