MySQL Forums
Forum List  »  Performance

Re: mysql + xmpp server +test loading - did I reach any physical limit?
Posted by: gigi kent
Date: June 11, 2012 10:54AM

Rick James Wrote:
-------------------------------------------------------
> Do you want to discuss it here? Or there?

Hi, let's keep it here.

First of all, I have miscalculated the numbers of queries/second ratio. Redone the math more accurately and there only is a rate of 600 queries / second during the first 1000 seconds. Judging after my graphs I really don't think there are any spikes above 1000 queries / second

>
> Please provide
> SHOW CREATE TABLE

mysql> SHOW CREATE TABLE;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1



Is this what I should provide ? -

-- QUERY START:

create table if not exists tig_users (
uid bigint unsigned NOT NULL auto_increment,

-- Jabber User ID
user_id varchar(2049) NOT NULL,
-- UserID SHA1 hash to prevent duplicate user_ids
sha1_user_id char(128) NOT NULL,
-- User password encrypted or not
user_pw varchar(255) default NULL,
-- Time the account has been created
acc_create_time timestamp DEFAULT CURRENT_TIMESTAMP,
-- Time of the last user login
last_login timestamp DEFAULT 0,
-- Time of the last user logout
last_logout timestamp DEFAULT 0,
-- User online status, if > 0 then user is online, the value
-- indicates the number of user connections.
-- It is incremented on each user login and decremented on each
-- user logout.
online_status int default 0,
-- Number of failed login attempts
failed_logins int default 0,
-- User status, whether the account is active or disabled
-- >0 - account active, 0 - account disabled
account_status int default 1,

primary key (uid),
unique key sha1_user_id (sha1_user_id),
key user_pw (user_pw),
key user_id (user_id(765)),
key last_login (last_login),
key last_logout (last_logout),
key account_status (account_status),
key online_status (online_status)
)
ENGINE=InnoDB default character set utf8 ROW_FORMAT=DYNAMIC;

-- QUERY END:

-- QUERY START:

create table if not exists tig_nodes (
nid bigint unsigned NOT NULL auto_increment,
parent_nid bigint unsigned,
uid bigint unsigned NOT NULL,

node varchar(255) NOT NULL,

primary key (nid),
unique key tnode (parent_nid, uid, node),
key node (node),
key uid (uid),
key parent_nid (parent_nid),
constraint tig_nodes_constr foreign key (uid) references tig_users (uid)
)
ENGINE=InnoDB default character set utf8 ROW_FORMAT=DYNAMIC;

-- QUERY END:

-- QUERY START:

create table if not exists tig_pairs (
nid bigint unsigned,
uid bigint unsigned NOT NULL,

pkey varchar(255) NOT NULL,
pval mediumtext,

key pkey (pkey),
key uid (uid),
key nid (nid),
constraint tig_pairs_constr_1 foreign key (uid) references tig_users (uid),
constraint tig_pairs_constr_2 foreign key (nid) references tig_nodes (nid)
)
ENGINE=InnoDB default character set utf8 ROW_FORMAT=DYNAMIC;

-- QUERY END:

-- QUERY START:

create table if not exists short_news (
-- Automatic record ID
snid bigint unsigned NOT NULL auto_increment,
-- Automaticly generated timestamp and automaticly updated on change
publishing_time timestamp,
-- Optional news type: 'shorts', 'minis', 'techs', 'funs'....
news_type varchar(10),
-- Author JID
author varchar(128) NOT NULL,
-- Short subject - this is short news, right?
subject varchar(128) NOT NULL,
-- Short news message - this is short news, right?
body varchar(1024) NOT NULL,
primary key(snid),
key publishing_time (publishing_time),
key author (author),
key news_type (news_type)
)
ENGINE=InnoDB default character set utf8 ROW_FORMAT=DYNAMIC;

-- QUERY END:

-- QUERY START:

create table if not exists xmpp_stanza (
id bigint unsigned NOT NULL auto_increment,
stanza text NOT NULL,

primary key (id)
)
ENGINE=InnoDB default character set utf8 ROW_FORMAT=DYNAMIC;

-- QUERY END:




> SHOW TABLE STATUS
mysql> SHOW TABLE STATUS;
+-------------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+------------+----------------+---------------------+---------------------+------------+-----------------+----------+--------------------+---------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+-------------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+------------+----------------+---------------------+---------------------+------------+-----------------+----------+--------------------+---------+
| muc_history | MyISAM | 10 | Dynamic | 3 | 118 | 356 | 281474976710655 | 1024 | 0 | NULL | 2012-04-21 22:59:18 | 2012-05-16 12:55:21 | NULL | utf8_general_ci | NULL | | |
| short_news | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 49152 | 1429209088 | 1 | 2012-04-25 06:54:46 | NULL | NULL | utf8_general_ci | NULL | row_format=DYNAMIC | |
| tig_nodes | InnoDB | 10 | Compact | 382852 | 50 | 19447808 | 0 | 51544064 | 1429209088 | 2830977 | 2012-06-10 18:21:25 | NULL | NULL | utf8_general_ci | NULL | row_format=DYNAMIC | |
| tig_pairs | InnoDB | 10 | Compact | 129014 | 3201 | 413089792 | 0 | 13041664 | 1429209088 | NULL | 2012-06-10 18:23:46 | NULL | NULL | utf8_general_ci | NULL | row_format=DYNAMIC | |
| tig_users | InnoDB | 10 | Compact | 200325 | 243 | 48840704 | 0 | 100728832 | 1429209088 | 1738291 | 2012-06-10 18:20:28 | NULL | NULL | utf8_general_ci | NULL | row_format=DYNAMIC | |
| xmpp_stanza | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 0 | 1429209088 | 1 | 2012-04-25 06:54:46 | NULL | NULL | utf8_general_ci | NULL | row_format=DYNAMIC | |
+-------------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+------------+----------------+---------------------+---------------------+------------+-----------------+----------+--------------------+---------+
6 rows in set (0.22 sec)





> SHOW VARIABLES LIKE 'innodb%';
mysql> SHOW VARIABLES LIKE 'innodb%';
+-----------------------------------------+------------------------+
| Variable_name | Value |
+-----------------------------------------+------------------------+
| innodb_adaptive_hash_index | ON |
| innodb_additional_mem_pool_size | 1048576 |
| innodb_autoextend_increment | 8 |
| innodb_autoinc_lock_mode | 1 |
| innodb_buffer_pool_size | 1073741824 |
| 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 | O_DIRECT |
| 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 |
+-----------------------------------------+------------------------+
36 rows in set (0.00 sec)





> * Poorly configured --> 100 tx/sec
> * Typical --> 1000 tx/sec
> * Specially crafted queries, schema and tuning -->
> 100K tx/sec.




Thanks. I have recently come across mysqltuner, which has to say the following (note that the test session only took 1.5 - 2 hours, not the entire 14 hours the server has been up for; also, the query cache gave me nothing but trouble according to mysqltuner so I have decided against it):

root@XMPP:/var/log/mysql# mysqltuner

>> MySQLTuner 1.0.1 - Major Hayden <major@mhtx.net>
>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Run with '--help' for additional options and output filtering
[!!] Successfully authenticated with no password - SECURITY RISK!

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.1.62-0ubuntu0.11.10.1-log
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 356B (Tables: 1)
[--] Data in InnoDB tables: 459M (Tables: 5)
[!!] Total fragmented tables: 5

-------- Performance Metrics -------------------------------------------------
[--] Up for: 14h 50m 11s (795K q [14.895 qps], 145 conn, TX: 352M, RX: 422M)
[--] Reads / Writes: 81% / 19%
[--] Total buffers: 2.0G global + 192.3M per thread (30 max threads)
[OK] Maximum possible memory usage: 7.7G (53% of installed RAM)
[!!] Slow queries: 100% (795K/795K)
[OK] Highest usage of available connections: 50% (15/30)
[OK] Key buffer size / total MyISAM indexes: 1.0G/90.0K
[OK] Key buffer hit rate: 99.4% (338 cached / 2 reads)
[!!] Query cache is disabled
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 1 sorts)
[OK] Temporary tables created on disk: 0% (48 on disk / 250K total)
[OK] Thread cache hit rate: 89% (15 created / 145 connections)
[OK] Table cache hit rate: 39% (61 open / 155 opened)
[OK] Open file limit used: 4% (49/1K)
[OK] Table locks acquired immediately: 100% (1M immediate / 1M locks)
[OK] InnoDB data size / buffer pool: 459.1M/1.0G

-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
MySQL started within last 24 hours - recommendations may be inaccurate
Variables to adjust:
query_cache_size (>= 8M)

PS. I am troubled by the line "[OK] Table cache hit rate: 39% (61 open / 155 opened)"
My uneducated guess is that it should help if this was in the 90% range or so. Follows the my.cnf file, having one bolded line at table_open_cache = 200 - thisis something I have placed in just now, I am retesting with such new variable as I am typing here.

root@XMPP:/opt/tigase# egrep "^[^#]." /etc/mysql/my.cnf
[client]
port = 3306
socket = /var/run/mysqld/mysqld.sock
[mysqld_safe]
socket = /var/run/mysqld/mysqld.sock
nice = 0
[mysqld]
user = mysql
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
skip-external-locking
bind-address = 0.0.0.0
key_buffer = 1024M
max_allowed_packet = 16M
thread_stack = 192K
thread_cache_size = 8
myisam-recover = BACKUP
max_connections = 30
table_open_cache = 200
thread_concurrency = 16
sort_buffer_size = 32M
read_buffer_size = 32M
read_rnd_buffer_size = 128M
query_cache_size = 0 #128M
query_cache_type = 0 # better disable this nuissance
query_cache_limit = 8M #not going to help by increasing it or soit seems
innodb_buffer_pool_size = 1024M
innodb_flush_method = O_DIRECT
log_error = /var/log/mysql/error.log
log_slow_queries = /var/log/mysql/mysql-slow.log
long_query_time = 0
min_examined_row_limit = 0
log-queries-not-using-indexes
expire_logs_days = 10
max_binlog_size = 100M
[mysqldump]
quick
quote-names
max_allowed_packet = 16M
[mysql]
[isamchk]
key_buffer = 16M
!includedir /etc/mysql/conf.d/
time-zone = '+00:00'



>
> XML has a lot of processing overhead, yet you have
> not hit 100% CPU. On the other hand, how many
> cores do you have? How many threads are you
> running?

I must say this machine is running a virtualized ubuntu linux, 64 bit. I tend not to believe what "cat /proc/cpuinfo" has to say:

root@XMPP:/var/log/mysql# cat /proc/cpuinfo
processor : 0
vendor_id : GenuineIntel
cpu family : 6
model : 2
model name : QEMU Virtual CPU version 0.14.1
stepping : 3
cpu MHz : 2393.946
cache size : 4096 KB
physical id : 0
siblings : 16
core id : 0
cpu cores : 16
apicid : 0
initial apicid : 0
fpu : yes
fpu_exception : yes
cpuid level : 4
wp : yes
flags : fpu de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pse36 clflush mmx fxsr sse sse2 ht syscall nx lm rep_good nopl pni cx16 popcnt hypervisor lahf_lm
bogomips : 4787.89
clflush size : 64
cache_alignment : 64
address sizes : 40 bits physical, 48 bits virtual
power management:

------------ following some other such 15 processor descriptions----------------- pretty unbelievable - 16 CPUs x 16 cores each ?!...
*Last minute info, the bare metal machine is 2 CPUs * 4 cores each - 2 x Intel Xeon E5620
*the available RAM looks solid though, 15GB in VM from 48G total of bare metal


Number of threads running in the XMPP server currently (it is under test):
root@XMPP:/var/log/mysql# ps -eLF | grep -w -o "29298" | wc -l
1297



---------------New results from mysqltuenr, still getting those exceptions in this testing session, after having adjsuted the table_open_cache to 200:

-------- Performance Metrics -------------------------------------------------
[--] Up for: 58m 20s (717K q [204.880 qps], 122 conn, TX: 327M, RX: 389M)
[--] Reads / Writes: 83% / 17%
[--] Total buffers: 2.0G global + 192.3M per thread (30 max threads)
[OK] Maximum possible memory usage: 7.7G (53% of installed RAM)
[!!] Slow queries: 100% (717K/717K)
[OK] Highest usage of available connections: 56% (17/30)
[OK] Key buffer size / total MyISAM indexes: 1.0G/90.0K
[OK] Key buffer hit rate: 99.4% (312 cached / 2 reads)
[!!] Query cache is disabled
[OK] Temporary tables created on disk: 0% (48 on disk / 224K total)
[OK] Thread cache hit rate: 86% (17 created / 122 connections)
[OK] Table cache hit rate: 40% (65 open / 159 opened)
[OK] Open file limit used: 4% (49/1K)
[OK] Table locks acquired immediately: 100% (1M immediate / 1M locks)
[OK] InnoDB data size / buffer pool: 459.1M/1.0G



Regards,
kellogs

Options: ReplyQuote




Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.